Read Excel using c# .net Issue

Question

I read data from excel sheet using C#.

Here is my code and it is working.

var fileName = @"C:\Users\yohan\Desktop\Brandix\y.xlsx";
var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source=                  {0}; Extended Properties=Excel 12.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [BOM$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds);
DataTable data = ds.Tables[0];

But it always skip the top row of the excel sheet why is that ? Please help ...!!

Thank You yohan

If i am not mistaken, it takes the first row as Column Names. Just a hint on your connectionString: if(fileName.Trim().EndsWith(".xlsx")) { conString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", fileName); } else if(fileName.Trim().EndsWith(".xls")) { conString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", fileName); } — Willem
– Willem, Commented Mar 31, 2011 at 10:27

Nuno Rodrigues · Accepted Answer · 2015-06-11 12:31:37Z

3

That is the normal behaviour of the DataAdapter. The top row is considered as an Header Row or a "Column Name" Row.

To change this behaviour add to the Extended Properties of your connection string the property "HDR=NO"

Example:

var fileName = @"C:\Users\yohan\Desktop\Brandix\y.xlsx";
var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=""Excel 12.0;HDR=NO;""", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [BOM$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds);
DataTable data = ds.Tables[0];

edited Jun 11, 2015 at 12:31

answered Mar 31, 2011 at 10:30

Nuno Rodrigues

2,1321 gold badge12 silver badges6 bronze badges

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

Comments

markpsmith · Accepted Answer · 2011-03-31 10:27:01Z

1

Try adding HDR=NO to the extended properties.

see this link for details

answered Mar 31, 2011 at 10:27

markpsmith

4,9182 gold badges38 silver badges65 bronze badges

Comments

Stefan Steiger · Accepted Answer · 2011-03-31 11:07:31Z

1

Try changing your connection string to ...

   "Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=\"Excel 12.0;HDR:No\""

See this page for more possible connection strings to try. The HDR setting determines wether the provider considers the top row to be column names.

edited Mar 31, 2011 at 11:07

Stefan Steiger

83k70 gold badges405 silver badges461 bronze badges

answered Mar 31, 2011 at 10:25

Chris Meek

5,8599 gold badges38 silver badges44 bronze badges

1 Comment

HABJAN Over a year ago

you were faster for 20 seconds :-)

Kalpesh · Accepted Answer · 2015-07-29 10:37:51Z

0

Recommended way to read an Excel file in Server side app is Open XML.

You can try installing ACE component but still it remains un-recommended and unsupported.

For using Open XML you can go through below links which will help you using Open XML in your code for reading excel files -

Link1

Link2

Link3

answered Jul 29, 2015 at 10:37

Kalpesh

1491 gold badge2 silver badges13 bronze badges

Collectives™ on Stack Overflow

Read Excel using c# .net Issue

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related