tags:

views:

173

answers:

1

Hey Guys

I'm reading an excel file with C# and OleDB (12.0). There I have to specify the select statement with the name of the sheet I wish to read ([Sheet1$]).

this.dataAdapter = 
    new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);

Is it possible to select the first sheet, no matter what name?

Thank you.

+3  A: 

See this answer on how to get the list of sheet names in order: http://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order

And here's my version which is a little shorter:

public static IEnumerable<string> GetExcelSheetNames(string excelFile)
{
    var connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
          "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
    using (var connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        using (var dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
        {
            return (dt ?? new DataTable())
                .Rows
                .Cast<DataRow>()
                .Select(row => row["TABLE_NAME"].ToString());
        }
    }
}
Darin Dimitrov
This works, but be aware that it seems like it also returns other things than just sheets so you might have to filter those out (I can't remember now but I think it was named ranges or something like that that showed up in the list as well).
ho1
Thanks. BTW "Extended Properties=Excel 8.0" doesn't open xlsx, one has to use "Extended Properties=Excel 12.0".
Dänu