Getting the first sheet from an Excel document regardless of sheet name with OleDb

Question

I have users that name their sheets all sorts of crazy things, but I want to be able to get the first sheet of the Excel document regardless of what it is named.

I currently use:

OleDbDataAdapter adapter = new OleDbDataAdapter(
"SELECT * FROM [sheetName$]", connString);

How would I go about getting the first sheet no matter what it is named?

Thank you.

Answer 1

You can use the GetOleDbSchemaTable (VB) or GetOleDbSchemaTable (C#).

Using the Tables Enum it will return a list of all the worksheet names, which you can then use to dynamically build the required SQL.

You can use:

MySchemaTable = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

All the Worksheets names will be returned as part of a DataTable you can them itterate through.

Using the OleDbSchemaGuid information can be retrieved on

• Columns
• Foreign keys
• Indexes
• Primary keys
• Tables
• Views

Full MSDN documentation available here

Answer 2

ended up using this:

using (OleDbConnection conn = new OleDbConnection(connString))
{
    conn.Open();
    dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    Sheet1= dtSchema.Rows[0].Field<string>("TABLE_NAME");
}

Answer 3

See http://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order