Obtaining First Excel Sheet Name With OleDbConnection

excel oledbconnection
pencilCake picture pencilCake · Aug 20, 2009 · Viewed 7.8k times · Source

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

Beth picture Beth · Aug 20, 2009

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.

Related questions

the 'microsoft.ace.oledb.12.0' provider is not registered on the local machine while reading a excel file in c#

I am reading an excel file and I want to access its contents. I am using this connection string: connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", Server.MapPath(fileName)); But I see this error: The …

Read More
Excel cell-values are truncated by OLEDB-provider

I'm using the OleDbConnection class to retrieve data from an Excel 2000/2003 workbook: string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;IMEX=1\";"; OleDbConnection connection = new OleDbConnection(connectionString); connection.Open(); // code to get table name from schema omitted …

Read More
Get column names from excel file of a specific sheet using c# with OleDbConnection

So far I have managed to get the column names of the whole excel file, but what I would like to do is to get the column names of the excel file of a given table (sheet). How could I …

Read More