Reading Excel File in Asp.net

Share |

One of the very common requirement in asp.net is end user uploaded Excel file and in Code behind, we need to Read the uploaded Excel file and Make sense out of data, i.e. Reading Excel File in Asp.net (Demo Code). 

Reading Excel File in Asp.net is Very simple task, but if there are standard operations need to be done, then we have to specify the Excel format and ask end user to upload file in certain format only.

Below we will discuss how to read and Excel File. (Demo Code)

Before we go ahead check out folder structure of Read Excel File in Asp.net

Also after we upload excel file it gets bind to a grid view, all these operations are done in Default.aspx

Html of the Default.aspx page is as below

         
Select Date :
         
         
         
Upload Excel File :
         


         

    

    

On Click of Upload Button we upload excel file and Save it in UploadedFiles folder. We then Read file from UploadedFiles folder.

C# code on Upload Button click is as below, code is very easy to understand, you can also download Demo Code for reference.

protected void btnUpload_Click(object sender, EventArgs e)
    {
        
        if (FileUpload1.FileName.ToString() != "" && FileUpload1.ToString().Contains("."))
        {
            String filepath = Server.MapPath("UploadedFiles");
            FileUpload1.SaveAs(filepath + "\\" + FileUpload1.FileName.Split('\\')[FileUpload1.FileName.Split('\\').Length - 1]);
            string excelPath = (filepath + "\\" + FileUpload1.FileName.Split('\\')[FileUpload1.FileName.Split('\\').Length - 1]);

            ExcelGridView.DataSource = GetExcelData(excelPath);
            ExcelGridView.DataBind();
        }
    }

In above code we pass Excel Path to "GetExcelData" function which returns DataTable which we in turn bind to GridView
GetExcelData function is as below

public DataTable GetExcelData(string ExcelFilePath)
    {
        string OledbConnectionString = string.Empty;
        OleDbConnection objConn = null;
        OledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
        objConn = new OleDbConnection(OledbConnectionString);

        if (objConn.State == ConnectionState.Closed)
        {
            objConn.Open();
        }

        OleDbCommand objCmdSelect = new OleDbCommand("Select * from [Sheet1$]", objConn);
        OleDbDataAdapter objAdapter = new OleDbDataAdapter();
        objAdapter.SelectCommand = objCmdSelect;
        DataSet objDataset = new DataSet();
        objAdapter.Fill(objDataset, "ExcelDataTable");
        objConn.Close();
        return objDataset.Tables[0];

    }

In above Code check out OleDbCommand; we are just writing a select query which fetch data from Sheet1 of uploaded Excel, similarly if we can give Sheet Name instead of Sheet1. If your Excel Data starts from some other cell then change select query like "Select * from [Sheet1$B7:F200]"; first row of this range will come as columns in Data Table

Now we are done with Excel Upload and Reading the file, there are certain things which we needs to be take care before uploading Excel file which are as below

• It certain column in Excel contains number do format that Column for number and make sure all the entries in that column are number, if some entries are in text format then, in Data Table we get those entries as blank entries (i.e. DataTable has empty cell for that respective excel cell.)
• Define the Range of Operation if your data does not start from A1 cell.
• All cells in 1st row (i.e. First row of the range you specify) are considered as column name, and in C# Data Table they appear as exact.
• To Specify the Range modify select query as Select * from [Sheet2$B7:F200] ; you can use your own sheet name instead of Sheet1 or Sheet2

Download Ready to Use Demo Code -- > ReadExcelFileInAsp.Net.zip