Get Data From Multiple Sheets of Excel in ASP.Net Web Application

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.



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



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



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".



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
  

  <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>
  <asp:GridView ID="GridView2" runat="server"></asp: GridView > < asp: GridView ID = "GridView3"
  runat = "server" > < /asp:GridView>

It will look as in the following page.


Step 2

 

Add 2 namespaces to 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 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.

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 sheets data and bind that data to the grids
    BindData(strConn);

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

Here is the "BindData()" method that will get the sheets data and bind that to the grids.

private void BindData(string strConn){
    OleDbConnection objConn = new OleDbConnection(strConn);
    objConn.Open();

    // Get the data table containg the schema guid.
    DataTable dt = null;
    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    objConn.Close();

    if (dt.Rows.Count > 0)    {
        int i = 0;

        // Bind the sheets to the Grids
        foreach(DataRow row in dt.Rows)        {
            DataTable dt_sheet = null;
            dt_sheet = getSheetData(strConn, row["TABLE_NAME"].ToString());
            switch (i)            {
                case 0:
                    GridView1.DataSource = dt_sheet;
                    GridView1.DataBind();
                    break;
                case 1:
                    GridView2.DataSource = dt_sheet;
                    GridView2.DataBind();
                    break;
                case 2:
                    GridView3.DataSource = dt_sheet;
                    GridView3.DataBind();
                    break;
            }
            i++;
        }
    }
}

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.

private DataTable getSheetData(string strConn, string sheet){
    string query = "select * from [" + sheet + "]";
    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();
    return dt;
}

Step 3

Run the page.



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