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.

<asp:FileUpload ID="FileUpload1" runat="server" />
     <asp:Button ID="Button1" runat="server" Text="Load Excel"
             OnClick="Button1_Click" />
     <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.

using System.IO;
using System.Data.OleDb;
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.

protected void Button1_Click(object sender, EventArgs e)
    {
        //if File is not selected then return
        if (Request.Files["FileUpload1"].ContentLength <= 0)
        { return; }

        //Get the file extension
        string fileExtension = Path.GetExtension(Request.Files["FileUpload1"].FileName);

        //If file is not in excel format then return
        if (fileExtension != ".xls" && fileExtension != ".xlsx")
        { return; }

        //Get the File name and create new path to save it on server
        string fileLocation = Server.MapPath("\\") + Request.Files["FileUpload1"].FileName;

        //if the File is exist on serevr then delete it
        if (File.Exists(fileLocation))
        {
            File.Delete(fileLocation);
        }
        //save the file lon the server before loading
        Request.Files["FileUpload1"].SaveAs(fileLocation);

        //Create the QueryString for differnt version of fexcel file
        string strConn = "";
        switch (fileExtension)
        {
            case ".xls": //Excel 1997-2003
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
                break;
            case ".xlsx": //Excel 2007-2010
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0 xml;HDR=Yes;IMEX=1\"";
                break;
        }

        //Get the data from the excel sheet1 which is default
        string query = "select * from  [Sheet1$]";
        OleDbConnection objConn;
        OleDbDataAdapter oleDA;
        DataTable dt = new DataTable();
        objConn = new OleDbConnection(strConn);
        objConn.Open();
        oleDA = new OleDbDataAdapter(query, objConn);
        oleDA.Fill(dt);
        objConn.Close();
        oleDA.Dispose();
        objConn.Dispose();

        //Bind the datatable to the Grid
        GridView1.DataSource = dt;
        GridView1.DataBind();

        //Delete the excel file from the server
        File.Delete(fileLocation);
    }

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.