Get sheet names in Excel95 workbook using OleDB

Question

I am having trouble using the Jet provider and OleDb to get the sheet names in an Excel95 workbook.

I have a workbook with 3 sheets, Sheet1, Sheet2, Sheet3. I am using the following code to extract the sheet name:

var connectionString =
String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"", filePath);

using (var connection = new OleDbConnection(connectionString))
{
    connection.Open();
    var dtXlsSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);        
}

This ends up returning a datatable with only one record in it, for Sheet1. I'm guessing there is something wrong with the connection string?

Answer 1

OleDb will not be aware of empty sheets.

As long as the sheets have some data in them, you should get their names.