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?

SLaks SLaks · Accepted Answer · 2010-02-11T18:47:55

OleDb will not be aware of empty sheets.

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

Get sheet names in Excel95 workbook using OleDB

2 Answers