Monday, December 20, 2010

Get Excel Sheet Names In Asp.net

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
Submit this story to DotNetKicks

2 comments:

Shuaib Rameh December 22, 2010 at 2:21 AM  

Thanks for the great post. Can you show how to read an image inside the Excel sheet? Let's say there is a column that has images. How can you read that in asp.net using C#?

Unknown February 10, 2021 at 11:42 PM  

How to get excel multiple sheets in one xlsx excel file in dropdown list and select which file open in gridview using dropdown list using asp.net c#