List the name of all EXcel Sheets in ASP .NET with OLEDB

When manipulating data with Excel file with ADO .NET there is always limitation with the name of the sheets. Basically if you don't change the name of the sheets it comes in specific order. But when you have all the sheets with different names, you will be in trouble.


Here is a simple example that will provide you all the sheets in an Excel file. This method will return string array of sheetnames.

private String[] GetExcelSheetNames(string excelFile)

{

    OleDbConnection objConn = null;

    System.Data.DataTable dt = null;

    try

    {

        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

            "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";

        objConn = new OleDbConnection(connString);

        objConn.Open();

        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt == null)

        {

            return null;

        }

        String[] excelSheets = new String[dt.Rows.Count];

        int i = 0;

        // Add the sheet name to the string array.

        foreach (DataRow row in dt.Rows)

        {

            excelSheets[i] = row["TABLE_NAME"].ToString();

            i++;

        }

        return excelSheets;

    }

    catch

    {

        return null;

    }

    finally

    {

        // Clean up.

        if (objConn != null)

        {

            objConn.Close();

            objConn.Dispose();

        }

        if (dt != null)

        {

            dt.Dispose();

        }

    }

}