Get Data From Single Sheet of Excel in ASP.Net Web Application

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:

  • Add the "FileUpload" Control to upload the file.
  • Add a GridView to show the data.
  • Add a button with click event.
  • Write some code in the code file for the button click event.

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:

  • Add the "FileUpload" control to upload the file.
  • Add a GridView to show the data.
  • Add a button with click event.
  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;  
  • "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 the 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 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; }  
  6.   
  7.         //Get the file extension  
  8.         string fileExtension = Path.GetExtension(Request.Files["FileUpload1"].FileName);  
  9.   
  10.         //If file is not in excel format then return  
  11.         if (fileExtension != ".xls" && fileExtension != ".xlsx")  
  12.         { return; }  
  13.   
  14.         //Get the File name and create new path to save it on server  
  15.         string fileLocation = Server.MapPath("\\") + Request.Files["FileUpload1"].FileName;  
  16.   
  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);  
  24.   
  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.         }  
  36.   
  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();  
  49.           
  50.         //Bind the datatable to the Grid  
  51.         GridView1.DataSource = dt;  
  52.         GridView1.DataBind();  
  53.   
  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.