2

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);
gunr2171
  • 16,104
  • 25
  • 61
  • 88
Nish
  • 137
  • 1
  • 10

1 Answers1

1

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.

Community
  • 1
  • 1
Icemanind
  • 47,519
  • 50
  • 171
  • 296
  • Thanks icemanind for links. So, if i know the order, then another solution could be hard code the sheet name like string sql = string.Format("select * from [{0}]", "Sheet1$"); right? – Nish Oct 01 '14 at 15:32
  • @Nish - Yes. I haven't tried this myself, but from what I've read, that seems correct. – Icemanind Oct 01 '14 at 15:41