Order of sheets in Excel file while reading with GetOleDbSchemaTable

2
votes

I am using an ACE.OLEDB as below to read an excel file in C#. My Excel file has the following Sheets and in below order:

Sheet1, Sheet1 (2), Sheet2

The variable sheetName in my code takes 'Sheet1 (2)' as first sheet instead of 'Sheet1'.

My question is how does it determines the order of sheets? 

string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
// Extra blank space cannot appear in Office 2007 and the last version. And we need to pay attention on semicolon.


using (OleDbConnection conn = new OleDbConnection(connstring))
{
    conn.Open();
    System.Data.DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "Table"});
    string firstSheetName = sheetsName.Rows[0][2].ToString();
    string sql = string.Format("select * from [{0}]", firstSheetName);
    OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
    DataSet set = new DataSet();
    ada.Fill(set);
1
c#exceloledbconnection

1 Answers

1
votes

According to this post, the moderator says I am afraid that OLEDB does not preserve the sheet order as they were in Excel..

While Googling though, I found this SO answer that might help you out.