Introduction

Sometimes the client wants to get the data from the single sheet of Excel file and show that specific data on the page using C# in ASP.NET so this article explains how to do that.

At the start I need to create an Excel file to get the data, so there is a file named "MyExcel.xlsx" with 2 columns with some data like:



Note: I have not changed the sheet name in the Excel file, otherwise I need to get the sheet name. You can also find the Excel file inside the attached documents.

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

To learn more about this, check the following procedure.

Step 1

Add a new "Website" named "Website1" as in the following:



Add some controls on the default page named "Defaut.aspx" as in the following:

  1. <asp:FileUpload ID="FileUpload1" runat="server" />
  2. <asp:Button ID="Button1" runat="server" Text="Load Excel"
  3. OnClick="Button1_Click" />
  4. <asp:GridView ID="GridView1" runat="server"></asp:GridView>



The page will look like as in the following:



Step 2

Add 2 namespaces on the top of the code file.

  1. using System.IO;
  2. using System.Data.OleDb;
  3. using System.Data;

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

Add the following code for 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. { return; }
  7. //Get the file extension
  8. string fileExtension = Path.GetExtension(Request.Files["FileUpload1"].FileName);
  10. //If file is not in excel format then return
  11. if (fileExtension != ".xls" && fileExtension != ".xlsx")
  12. { return; }
  14. //Get the File name and create new path to save it on server
  15. string fileLocation = Server.MapPath("\\") + Request.Files["FileUpload1"].FileName;
  17. //if the File is exist on serevr then delete it
  18. if (File.Exists(fileLocation))
  19. {
  20. File.Delete(fileLocation);
  21. }
  22. //save the file lon the server before loading
  23. Request.Files["FileUpload1"].SaveAs(fileLocation);
  25. //Create the QueryString for differnt version of fexcel file
  26. string strConn = "";
  27. switch (fileExtension)
  28. {
  29. case ".xls": //Excel 1997-2003
  30. strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation
    +     ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
  31. break;
  32. case ".xlsx": //Excel 2007-2010
  33. strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation
    +     ";Extended Properties=\"Excel 12.0 xml;HDR=Yes;IMEX=1\"";
  34. break;
  35. }
  37. //Get the data from the excel sheet1 which is default
  38. string query = "select * from [Sheet1$]";
  39. OleDbConnection objConn;
  40. OleDbDataAdapter oleDA;
  41. DataTable dt = new DataTable();
  42. objConn = new OleDbConnection(strConn);
  43. objConn.Open();
  44. oleDA = new OleDbDataAdapter(query, objConn);
  45. oleDA.Fill(dt);
  46. objConn.Close();
  47. oleDA.Dispose();
  48. objConn.Dispose();
  50. //Bind the datatable to the Grid
  51. GridView1.DataSource = dt;
  52. GridView1.DataBind();
  54. //Delete the excel file from the server
  55. File.Delete(fileLocation);
  56. }

Note: I have not changed the sheet name of the Excel file, otherwise I need to get the sheet name.

Step 3

Run the page.



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