Obtaining First Excel Sheet Name With OleDbConnection

Question

Hi,

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 much 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

i have the same problem plz suugest any solution

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.