1

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.

Dänu
  • 5,791
  • 9
  • 43
  • 56

1 Answers1

5

See this answer on how to get the list of sheet names in order: 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());
        }
    }
}
Community
  • 1
  • 1
Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
  • 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). – Hans Olsson May 16 '10 at 16:40
  • Thanks. BTW "Extended Properties=Excel 8.0" doesn't open xlsx, one has to use "Extended Properties=Excel 12.0". – Dänu May 16 '10 at 16:54