Reading Excel sheet in ASP.NET

             One day I got a requirement of reading excel sheet (excel sheet may be either 2003 or 2007 version). To implement this I started googling and found some interesting solutions from Microsoft website and finally I developed code to read the excel sheet in the following way.
             Here I created a web application and added an upload user control to a page (assume Default.aspx). Once the user uploads the excel file I will store it into the server machine and read the excel sheet from the server machine. Here is the code.
I created a class to read the excel file depending on the excel version.


using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.OleDb;
using System.Collections.Generic;

public class ExcelReading
public ExcelReading()
// TODO: Add constructor logic here
    //open the excel file using OLEDB
    OleDbConnection con;
//List of Worksheet names
    public string[] workSheetNames = new string[] { };
    private string fileName;  //Path and file name of the Excel Workbook
    private string connectionString;

    public string[] ExcelRead(string fileNamePath,string extn)
        this.fileName = fileNamePath;

        switch (extn)
            //Excel 97-2003 file
            case ".xls":
            //Excel 2007 file
            case ".xlsx":


        return workSheetNames;


    private void OpenExcelFile(bool isOpenXMLFormat)
//while reading excel sheet Oledbdata Reader scans first 8 rows
        //and based on the majority data types it treats them as either integer or text.
        //The non matching data types are treated as NULL or empty. To solve this problem
        //use IMEX=1 property extender        

        if (isOpenXMLFormat)
            //read a 2007 file
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +@fileName + ";Extended Properties="Excel 8.0;HDR=YES;"";
            //read a 97-2003 file
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@fileName + ";Extended Properties='Excel 8.0;IMEX=1'";
            con = new OleDbConnection(connectionString);

            //get all the available sheets
            System.Data.DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            //get the number of sheets in the file
            workSheetNames = new String[dataSet.Rows.Count];
            int i = 0;
            foreach (DataRow row in dataSet.Rows)
                //insert the sheet's name in the current element of the array
                //and remove the $ sign at the end
                workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { ''','$' });
            if (dataSet != null)
        catch(Exception e)
            throw e;



    public System.Data.DataTable GetWorksheet(string worksheetName,string range)
        OleDbConnection con = new OleDbConnection();
        System.Data.DataTable excelDataTable = new System.Data.DataTable();
            con = new System.Data.OleDb.OleDbConnection(connectionString);
            OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(
                "select * from [" + worksheetName + "$" + range + "]", con);

        catch { }

        return excelDataTable;


Here is the code of the page and its code behind file from which I uploaded the excel sheet.

<asp:FileUpload ID="ctrlFileUpload"  runat="server" EnableViewState="true" Width="100%" onkeypress="return false;" onkeydown="return false;" /></br>
<asp:Button ID="btnUpload" runat="server" Enabled="true" Text="Upload" OnClick=" btnUpload _Click"/>


protected void btnUpload_Click(object sender, EventArgs e)


      string FileName = ctrlFileUpload.PostedFile.FileName;

      string _strUploadedFileExtn = System.IO.Path.GetExtension(ctrlFileUpload.PostedFile.FileName);

//Saving the file into server machine

      ctrlFileUpload.PostedFile.SaveAs(Server.MapPath(".") + "\UploadedExcel\" + FileName);

//Reading the file from the previously saved location.

string path = @"../UploadedExcel/" + FileName;

      string fileNamePath = Server.MapPath(path);

System.Data.DataTable dataTable = new System.Data.DataTable();

string[] workSheetNames = excelReading.ExcelRead(fileNamePath, _strUploadedFileExtn);

//Read the required worksheet from the array. Here I am reading first sheet

string sheetName= workSheetNames[1];

//Specify the range of the data which you want to read from excel sheet

string range = "B8:B100";

dataTable = excelReading.GetWorksheet(sheetName, range);

//Read the data from datatable object

if (dataTable.Rows.Count > 0)


          foreach (DataRow row in dataTable.Rows)


      //Here write the code to read the data from datatable row.




Another way of reading excel sheet:

We can also read the excel sheet by referring ‘Microsoft.Interop.Excel.dll’ but before going in that way I suggest you to go through below link  because microsoft is not recommending this way.
References for reading excel sheet:

One thought on “Reading Excel sheet in ASP.NET

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s