Get Excel Sheet Names In Asp.net

Share |

In Previous article named Reading Excel File in Asp.net , we discussed about Uploading excel file with One sheet, where we assumed that we know the name of the sheet i.e. Reading from "Sheet1" of uploaded excel file.(Demo Code)

In this article we will discuss to read data from excel file where are not sure about the number of Sheets present in Excel file and name of those excel sheet. This is very common requirement for developers who are playing with Excel Data. We will keep logic of Uploading file similar to previous article. In this article we assume that we have uploaded the file and saved it at predefined location. Now we will only consider reading from excel file which contains number of excel sheets with names not known to us.


Just check out following code, where we consume excel file and read all Sheet names as Table names in one Data Table, and then display all Excel sheets one by one.

public DataSet 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();
        }
        DataSet objDataset = new DataSet();

        DataTable dtName = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        
        for (int _rowCount = 0; _rowCount < dtName.Rows.Count; _rowCount++)
        {
            Response.Write(dtName.Rows[_rowCount]["TABLE_NAME"].ToString() + "
");

            OleDbCommand objCmdSelect = new OleDbCommand("Select * from [" + dtName.Rows[_rowCount]["TABLE_NAME"].ToString() + "]", objConn);
            OleDbDataAdapter objAdapter = new OleDbDataAdapter();
            objAdapter.SelectCommand = objCmdSelect;
            objAdapter.Fill(objDataset, dtName.Rows[_rowCount]["TABLE_NAME"].ToString());
            objAdapter.Dispose();
            objCmdSelect.Dispose();
        }

        objConn.Close();
        return objDataset;

    }

We are simply reading all excel sheets present in the Excel file and adding them in Data Set.
Check out Demo code for the reference. Getting Excel sheet Names in Asp.net is an easy task. We can simply bind this data to grids and display it or push this data directly to database.

We need to note one thing, that sheet names in Excel are read as "SheetName + $", where "$" does not belong to sheet name.

If we need to do operations related to Range of Excel sheet Cells, refer my previous article

Download Demo Code here --> GetExcelSheetNamesInAsp.zip

Read more...

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

Read more...