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

score 1 · Answer 1 · answered Aug 20 '09 at 14:45

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.

answered Aug 20 '09 at 14:45

Beth

9,531
1
24
43

I use this line to obtain the Tables data-table: DataTable dtSheetName = connToExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); – pencilCake Aug 20 '09 at 14:54
check out http://support.microsoft.com/kb/309488 I think you can change your second param from null to the name of the worksheet or zero to limit the tables returned to your target – Beth Aug 20 '09 at 16:37

score 1 · Answer 2 · answered Sep 22 '09 at 22:34

1

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

answered Sep 22 '09 at 22:34

Kyle B.

5,737
6
39
57

score 1 · Answer 3 · answered Dec 13 '13 at 11:02

1

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

answered Dec 13 '13 at 11:02

Pachanka

492
2
7
19

score 0 · Answer 4 · edited Dec 14 '12 at 14:38

   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();

score 0 · Answer 5 · answered Nov 18 '09 at 09:28

0

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.

answered Nov 18 '09 at 09:28

richardtallent

34,724
14
83
123

Obtaining First Excel Sheet Name With OleDbConnection

5 Answers5