Read Excel using c# .net Issue

I read data from excel sheet using C#.

Here is my code and it is working.

var fileName = @"C:\Users\yohan\Desktop\Bran开发者_如何学Godix\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

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];

Try adding HDR=NO to the extended properties.

see this link for details

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.

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