Clever Geek Handbook

How to read an Excel file?

I want to read an excell file using 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 I run the program, I get this error:

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

Additional information: The external table is not in the expected format.

How can I solve this?

+3


source to share


4 answers


Your excel file is version 2007, * .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;";
+3


source


He already answers the question in the last post. Please follow the link below.

Reply 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\"";
0


source


you have a diff version of the Excel file, get the filename 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);
0


source


I would guess this Libary is called FileHelpers . Since I discovered this, I have never written any plumbing code myself. This allows you to focus on real business logic.

0


source






More articles:


All Articles