Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
655 views
in Technique[技术] by (71.8m points)

c# - Order of sheets in Excel file while reading with GetOleDbSchemaTable

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);
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...