How can I read Excel file?

Question

I want to read excell file with this code:

var fileName = @"d:\1.xlsx"; 
var connectionString = string.Format(
    "Provider=Microsoft.Jet.OLEDB.4.0; data source="+fileName+
    "; Extended Properties=Excel 8.0;", fileName);

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

adapter.Fill(ds, "anyNameHere");

DataTable data = ds.Tables["anyNameHere"];

But when run the program I get this error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: External table is not in the expected format.

How can i solve that?

This answer is very highly rated: http://stackoverflow.com/a/1363480/49 try it maybe — Leon Bambrick, Apr 28 '15 at 06:51
you should take a look at this site https://www.connectionstrings.com/excel/ — Shell, May 08 '15 at 11:30

score 3 · Answer 1 · answered Apr 28 '15 at 06:53

Your excel file is 2007 version, *.xlsx and you are using the wrong provider(Microsoft.Jet.OLEDB.4.0).

Try this approach:

var fileName = @"d:\1.xlsx"; 
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0;";

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

Its already answer the question in the past post Please follow the below link

Answer link

The answer is very simple

sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test.xlsx;Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";

edited May 23 '17 at 10:24

Community

1
1

answered Apr 28 '15 at 07:09

Abhilash Thomas

833
2
12
23

score 0 · Answer 3 · answered Apr 28 '15 at 07:18

you have diff version of Excel file, get the file name, if its extension is .xlsx, use this

var fileName = @"d:\1.xlsx"; 
var connectionString = string.Format(
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="{0}";Extended Properties=Excel 12.0;", fileName);

score 0 · Answer 4 · answered Apr 28 '15 at 07:23

0

I would suggest this Libary called FileHelpers. Since discovering it i have never written the plumbing code myself. It lets you concentrate on the actual business logic.

answered Apr 28 '15 at 07:23

bhupendra patel

3,139
1
25
29

How can I read Excel file?

4 Answers4