Sometimes the client wants to get the data from the multiple sheets of Excel file and show that the data on the page uss C# in ASP.Net so this article explains how to do that.

Initially I need to create an Excel file to get the data, so there is a file named "MyExcel.xlsx" with 3 sheets with some data like as in the following.

Data of Employees in Employee Sheet with Employee ID and Name Columns.

employee sheet

Data of Students in Student Sheet with Roll Number and Name Columns.

student sheet

Data of Teachers in Teacher Sheet with Teacher ID and Name Columns.

teacher sheet

Now to get the data from the preceding Excel file, I need to work on a page as in the following:

  • Add the "FileUpload" Control to upload the file
  • Add 3 Gridviews to show the data of all 3 sheets
  • Add a button with click event
  • Write some code on button click event in the code file
To learn more about this, check the following procedure.

Step 1

Add a new "Website" named "Website1".

new website

Add some controls to the default page named "Defaut.aspx".
  • Add the "FileUpload" Control to upload the file
  • Add 3 Gridviews to show the data of all 3 sheets
  • Add a button with click event
    1. <asp: FileUpload ID = "FileUpload1"
    2. runat = "server" / > < asp: Button ID = "Button1"
    3. runat = "server"
    4. Text = "Load Excel"
    5. OnClick = "Button1_Click" / > < asp: GridView ID = "GridView1"
    6. runat = "server" > < /asp:GridView>
    7. <asp:GridView ID="GridView2" runat="server"></asp: GridView > < asp: GridView ID = "GridView3"
    8. runat = "server" > < /asp:GridView>
gridview

It will look as in the following page.

on page
Step 2
Add 2 namespaces to the top of the code file.
  1. using System.IO;
  2. using System.Data.OleDb;
  3. using System.Data;
  • "System.IO" is used for the "File" and "Path" classes to access the Excel file.
  • "System.Data.OleDb" is used for the "OleDbConnection" and "OleDbConnection" classes to connect with an Excel file.
  • "System.Data" is used for the "DataTable" class.
Note

Microsoft Excel is like a database and OleDb is used to connect with many kinds of databases.

Add the following code to the button Click event.
  1. protected void Button1_Click(object sender, EventArgs e)
  2. {
  3. //if File is not selected then return
  4. if (Request.Files["FileUpload1"].ContentLength <= 0)
  5. {
  6. return;
  7. }
  9. //Get the file extension
  10. string fileExtension = Path.GetExtension(Request.Files["FileUpload1"].FileName);
  12. //If file is not in excel format then return
  13. if (fileExtension != ".xls" && fileExtension != ".xlsx")
  14. {
  15. return;
  16. }
  18. //Get the File name and create new path to save it on server
  19. string fileLocation = Server.MapPath("\\") + Request.Files["FileUpload1"].FileName;
  21. //if the File is exist on serevr then delete it
  22. if (File.Exists(fileLocation))
  23. {
  24. File.Delete(fileLocation);
  25. }
  26. //save the file lon the server before loading
  27. Request.Files["FileUpload1"].SaveAs(fileLocation);
  29. //Create the QueryString for differnt version of fexcel file
  30. string strConn = "";
  31. switch (fileExtension)
  32. {
  33. case ".xls":
  34. //Excel 1997-2003
  35. strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
  36. break;
  37. case ".xlsx":
  38. //Excel 2007-2010
  39. strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0 xml;HDR=Yes;IMEX=1\"";
  40. break;
  41. }
  42. //Get the sheets data and bind that data to the grids
  43. BindData(strConn);
  45. //Delete the excel file from the server
  46. File.Delete(fileLocation);
  47. }
Here is the "BindData()" method that will get the sheets data and bind that to the grids.
  1. private void BindData(string strConn)
    {
  2. OleDbConnection objConn = new OleDbConnection(strConn);
  3. objConn.Open();
  5. // Get the data table containg the schema guid.
  6. DataTable dt = null;
  7. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  8. objConn.Close();
  10. if (dt.Rows.Count > 0)
    {
  11. int i = 0;
  13. // Bind the sheets to the Grids
  14. foreach(DataRow row in dt.Rows)
    {
  15. DataTable dt_sheet = null;
  16. dt_sheet = getSheetData(strConn, row["TABLE_NAME"].ToString());
  17. switch (i)
    {
  18. case 0:
  19. GridView1.DataSource = dt_sheet;
  20. GridView1.DataBind();
  21. break;
  22. case 1:
  23. GridView2.DataSource = dt_sheet;
  24. GridView2.DataBind();
  25. break;
  26. case 2:
  27. GridView3.DataSource = dt_sheet;
  28. GridView3.DataBind();
  29. break;
  30. }
  31. i++;
  32. }
  33. }
  34. }
Note

The row["TABLE_NAME"] column in the datatable stores the sheet's names sorted alphabetically.

Here is the "getSheetData ()" method that will get the sheet name and return the datatable.
  1. private DataTable getSheetData(string strConn, string sheet)
    {
  2. string query = "select * from [" + sheet + "]";
  3. OleDbConnection objConn;
  4. OleDbDataAdapter oleDA;
  5. DataTable dt = new DataTable();
  6. objConn = new OleDbConnection(strConn);
  7. objConn.Open();
  8. oleDA = new OleDbDataAdapter(query, objConn);
  9. oleDA.Fill(dt);
  10. objConn.Close();
  11. oleDA.Dispose();
  12. objConn.Dispose();
  13. return dt;
  14. }
Step 3

Run the page.

run the page

Select the Excel file that I have created first and click on the "Load Excel" Button. Here is the result.

load excel