How to Read Excel File in Data Set Using ASP.Net

Introduction

This article explains how to read an Excel file into a Data Set Using ASP.NET.
 
Step 1
 
First of all, open a new Excel Sheet and enter the information that you want to add.
 
Now start Visual Studio and create a Web Application.
 
Right-click on this application and add the Excel File to this application.
 
Step 2
 
Now you need to add a Drop Down List, a List Item, a Label, and a Grid View to your application.
  1. <asp:DropDownList ID="dropdown1" runat="server" OnSelectedIndexChanged="ddlSlno_SelectedIndexChanged"  
  2.     AutoPostBack="true" AppendDataBoundItems="True">  
  3.     <asp:ListItem Selected="True" Value="Choose">- Choose -</asp:ListItem>  
  4. </asp:DropDownList>  
  5. <asp:GridView ID="Grid1" runat="server">  
  6. </asp:GridView>  
  7. <asp:Label ID="lbl1" runat="server" /> 
Step 3
 
Now add this code in the selected index change of the drop-down list:
  1. GenerateExcelData(dropdown1.SelectedValue); 
For the GenerateExcelData method you need to add this code:
  1. private void GenerateExcelData(string SlnoAbbreviation)  
  2. {  
  3.     try  
  4.     {  
  5.         string read = System.IO.Path.GetFullPath(Server.MapPath("~/empdetail.xlsx"));  
  6.   
  7.         if (Path.GetExtension(read) == ".xls")  
  8.         {  
  9.             x = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + read + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");  
  10.         }  
  11.         else if (Path.GetExtension(read) == ".xlsx")  
  12.         {  
  13.             x = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + read + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");  
  14.         }  
  15.         x.Open();  
  16.         OleDbCommand y = new OleDbCommand();  
  17.         OleDbDataAdapter z = new OleDbDataAdapter();  
  18.         DataSet dset = new DataSet();  
  19.         y.Connection = x;  
  20.         y.CommandType = CommandType.Text;  
  21.         y.CommandText = "SELECT distinct([Slno]) FROM [Sheet1$]";  
  22.         z = new OleDbDataAdapter(y);  
  23.         z.Fill(dset, "Slno");  
  24.         dropdown1.DataSource = dset.Tables["Slno"].DefaultView;  
  25.         if (!IsPostBack)  
  26.         {  
  27.             dropdown1.DataTextField = "Slno";  
  28.             dropdown1.DataValueField = "Slno";  
  29.             dropdown1.DataBind();  
  30.         }  
  31.         if (!String.IsNullOrEmpty(SlnoAbbreviation) && SlnoAbbreviation != "Choose")  
  32.         {  
  33.             y.CommandText = "SELECT [Slno], [EmpName], [Salaray], [Location]" +  
  34.                 "  FROM [Sheet1$] where [Slno]= @Slno_Abbreviation";  
  35.             y.Parameters.AddWithValue("@Slno_Abbreviation", SlnoAbbreviation);  
  36.         }  
  37.         else  
  38.         {  
  39.             y.CommandText = "SELECT [Slno],[EmpName],[Salary],[Location] FROM [Sheet1$]";  
  40.         }  
  41.         z = new OleDbDataAdapter(y);  
  42.         z.Fill(dset);  
  43.   
  44.         Grid1.DataSource = dset.Tables[1].DefaultView;  
  45.         Grid1.DataBind();  
  46.     }  
  47.     catch (Exception ex)  
  48.     {  
  49.         lbl1.Text = ex.ToString();  
  50.     }  
  51.     finally  
  52.     {  
  53.         x.Close();  
  54.     }  

This code will check both types of files to determine whether it's a .xls file or a .xlsx file.
 
After that I provided the connection for the Excel Sheet, in the starting it will show all the data but after that, it will fetch the data according to a Serial Number provided by you.
 
Step 4
 
Its complete code will be like this:
  1. using System;  
  2. using System.Data.OleDb;  
  3. using System.Data;  
  4. using System.IO;  
  5.    
  6. namespace ReadExcelInToDataSet  
  7. {  
  8.     public partial class Default : System.Web.UI.Page  
  9.     {  
  10.         OleDbConnection x;  
  11.         protected void Page_Load(object sender, EventArgs e)  
  12.         {  
  13.             if (!IsPostBack)  
  14.             {  
  15.                 GenerateExcelData("Choose");  
  16.             }  
  17.         }  
  18.    
  19.         protected void ddlSlno_SelectedIndexChanged(object sender, EventArgs e)  
  20.         {  
  21.             GenerateExcelData(dropdown1.SelectedValue);  
  22.         }  
  23.    
  24.         private void GenerateExcelData(string SlnoAbbreviation)  
  25.         {  
  26.             try  
  27.             {  
  28.                 string read = System.IO.Path.GetFullPath(Server.MapPath("~/empdetail.xlsx"));  
  29.    
  30.                 if (Path.GetExtension(read) == ".xls")  
  31.                 {  
  32.                     x = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + read + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");  
  33.                 }  
  34.                 else if (Path.GetExtension(read) == ".xlsx")  
  35.                 {  
  36.                     x = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + read + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");  
  37.                 }  
  38.                 x.Open();  
  39.                 OleDbCommand y = new OleDbCommand();  
  40.                 OleDbDataAdapter z = new OleDbDataAdapter();  
  41.                 DataSet dset = new DataSet();  
  42.                 y.Connection = x;  
  43.                 y.CommandType = CommandType.Text;  
  44.                 y.CommandText = "SELECT distinct([Slno]) FROM [Sheet1$]";  
  45.                 z = new OleDbDataAdapter(y);  
  46.                 z.Fill(dset, "Slno");  
  47.                 dropdown1.DataSource = dset.Tables["Slno"].DefaultView;  
  48.                 if (!IsPostBack)  
  49.                 {  
  50.                     dropdown1.DataTextField = "Slno";  
  51.                     dropdown1.DataValueField = "Slno";  
  52.                     dropdown1.DataBind();  
  53.                 }  
  54.                 if (!String.IsNullOrEmpty(SlnoAbbreviation) && SlnoAbbreviation != "Choose")  
  55.                 {  
  56.                     y.CommandText = "SELECT [Slno], [EmpName], [Salaray], [Location]" +  
  57.                         "  FROM [Sheet1$] where [Slno]= @Slno_Abbreviation";  
  58.                     y.Parameters.AddWithValue("@Slno_Abbreviation", SlnoAbbreviation);  
  59.                 }  
  60.                 else  
  61.                 {  
  62.                     y.CommandText = "SELECT [Slno],[EmpName],[Salary],[Location] FROM [Sheet1$]";  
  63.                 }  
  64.                 z = new OleDbDataAdapter(y);  
  65.                 z.Fill(dset);  
  66.    
  67.                 Grid1.DataSource = dset.Tables[1].DefaultView;  
  68.                 Grid1.DataBind();  
  69.             }  
  70.             catch (Exception ex)  
  71.             {  
  72.                 lbl1.Text = ex.ToString();  
  73.             }  
  74.             finally  
  75.             {  
  76.                 x.Close();  
  77.             }  
  78.         }  
  79.     }  

Output
 
On page load, it will show all the data.
 
readexcel1.jpg
 
Now when we provide the specific Serial Number then it will show that data only.
 
readexcel2.jpg