0

[Sample.xlsx]

Column 0, Row 0 = "ItemA"    
Column 0, Row 1 = "ItemB"    
Column 0, Row 2 = "ItemC"    
Column 0, Row 3 = "ItemD"

[Application]

DataSet dsData = new DataSet();    
string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties='Excel 12.0;'";    
OleDbDataAdapter daGetExcel = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);    
daGetExcel.Fill(dsData);        

foreach (DataRow row in dsData.Tables[0].Rows) 
{ 
    lbExcelData.Items.Add(row[0].ToString()); 
}

lbExcelData is a ListBox control on the form.

[RESULTS]

"ItemB", "ItemC", "ItemD"

[PROBLEM]

Why is the first item, "ItemA", being ignored?

Pratik
  • 11,534
  • 22
  • 69
  • 99
03B
  • 3
  • 1
  • 2

2 Answers2

4

For Excel, set HDR=NO in the Extended Properties setting of the connection string.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties='Excel 12.0;HDR=NO'"

http://connectionstrings.com/excel-2007

harpo
  • 41,820
  • 13
  • 96
  • 131
  • 1
    To clarify, this is because the first row is being interpreted as the column names. – John Gietzen Apr 03 '09 at 19:07
  • You should pay attention to put single quotation, if you have just one parameter for extended properties before HDR=NO. – efeyc Apr 03 '14 at 12:59
0

I believe your connection string in this case should be:

string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;
    Data Source=Sample.xlsx;Extended Properties='Excel 12.0;HDR=NO;'";
Justin Niessner
  • 242,243
  • 40
  • 408
  • 536