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.

ExcelReading.cs:

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":
                OpenExcelFile(false);
                break;
            //Excel 2007 file
            case ".xlsx":
                OpenExcelFile(true);
                break;
            default:
                break;

        }

        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;"";
        else
            //read a 97-2003 file
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@fileName + ";Extended Properties='Excel 8.0;IMEX=1'";
        try
        {
            con = new OleDbConnection(connectionString);
            con.Open();

            //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[] { ''','$' });
                i++;
            }
            if (dataSet != null)
                dataSet.Dispose();
        }
        catch(Exception e)
        {
            throw e;
        }

        finally
        {
            con.Close();
            con.Dispose();
        }

    }

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

            con.Open();
            cmd.Fill(excelDataTable);
        }
        catch { }
        finally
        {
            con.Close();
            con.Dispose();
        }

        return excelDataTable;
    }

}

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

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

Default.aspx.cs:

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.

http://support.microsoft.com/kb/257757
References for reading excel sheet:

http://support.microsoft.com/kb/306572

http://support.microsoft.com/kb/316934

http://support.microsoft.com/kb/316934

http://code.msdn.microsoft.com/NitoExcel

Advertisements