Reading Excel file with C# - Choose sheet

Question

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.

Community · Accepted Answer · 2017-05-23 12:01:15Z

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());
        }
    }
}

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered May 16, 2010 at 16:32

Darin Dimitrov

1.0m276 gold badges3.3k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hans Olsson Over a year ago

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).

Dänu Over a year ago

Thanks. BTW "Extended Properties=Excel 8.0" doesn't open xlsx, one has to use "Extended Properties=Excel 12.0".

Collectives™ on Stack Overflow

Reading Excel file with C# - Choose sheet

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related