Obtaining First Excel Sheet Name With OleDbConnection

Question

I have one problem. I need to get the excel sheet name in a work book, which looks on the very left sheets tab -the first one from my point of view.

I am using this code:

public static string GetFirstExcelSheetName(OleDbConnection connToExcel)
{
    DataTable dtSheetName = 
    connToExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    List<String> lstExcelSheet = new List<string>(dtSheetName.Rows.Count);

    foreach (DataRow row in dtSheetName.Rows)
        lstExcelSheet.Add(row["TABLE_NAME"].ToString());

    return lstExcelSheet[0];
}

The problem here is it is returning the rows not in the visual tab order but in a very different order - most probably the row created date.

How can it be possible to get the sheetnames table ordered according to their tab order so that I can easily get the 1st excel sheet name?

Thanks, kalem keki

Answer 1

It should be the zero-th item in the workbooks(?) collection. I think you have the right index, wrong collection.

Sorry, didn't notice you're using the rows collection of a datatable. That's a different problem. How do you create the datatable? You might have to change the sort property of the dataview.

Answer 2

Dim dtSheetnames As DataTable = oleDBExcelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
Dim FirstSheetName As String = dtSheetnames.Rows(0)!TABLE_NAME.ToString

Answer 3

The row 0 is not the first sheet in the excel file, rows are sorted by alphabetical order in this collection :/

Answer 4

I recommend using the NPOI library (http://npoi.codeplex.com/) rather than OleDB to retrieve data (including metadata) from Excel.

IIRC, OleDB will also fail for sheet names that include spaces or dollar signs.

Answer 5

   OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Session["path"].ToString() + "; Extended Properties=Excel 12.0;Persist Security Info=False;");

            oconn.Open();
            myCommand.Connection = oconn;
            DataTable dbSchema = oconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,  null);
            if (dbSchema == null || dbSchema.Rows.Count < 1)
            {
                throw new Exception("Error: Could not determine the name of the first worksheet.");
            }
            string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();