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"));
  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);
  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. }
  55. }
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;
  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. }
  19. protected void ddlSlno_SelectedIndexChanged(object sender, EventArgs e)
  20. {
  21. GenerateExcelData(dropdown1.SelectedValue);
  22. }
  24. private void GenerateExcelData(string SlnoAbbreviation)
  25. {
  26. try
  27. {
  28. string read = System.IO.Path.GetFullPath(Server.MapPath("~/empdetail.xlsx"));
  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);
  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. }
  80. }
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