Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

I cannot open .xlsx file

Tags:

c#

excel

oledb

I want to open an xlsx file, I have tried the below code,but neither does it open nor does it thrown any error.

Can anyone throw any light upon it

string path = "C:\\examples\\file1.xlsx";
string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
OleDbConnection cn = new OleDbConnection(connString);
cn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", cn);
DataTable dt = new DataTable();
adapter.Fill(dt);
like image 493
cmrhema Avatar asked Feb 28 '23 05:02

cmrhema


1 Answers

In December 2010 Microsoft (finally!) published a a 64-bit OLEDB driver for CSV and XLSX files.

You'll need the 64-bit Microsoft Access Database Engine 2010 Redistributable. Make sure to download the 64-bit version (AccessDatabaseEngine_X64.exe). You'll need to uninstall any 32-bit Office apps (including Sharepoint Designer!) in order to install it.

If you want CSV, you'll want the Microsoft Access Text Driver (*.txt, *.csv) driver name, but I haven't been able to find a full connection string yet using this driver from OLEDB (although if you have, leave a commend and I'll amend this answer). Note that 64-bit names are different from the 32-bit versions.

For reading XLSX files, use a connection string like this:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
 + FilePath
 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

For XLS (pre-2007 Excel) files use a connection string like this:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
 + FilePath
 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

Many thanks to this blog post and this answer for pointing me in the right direction when I ran into the same problem, and for providing content I adapted to write this answer.

like image 168
Justin Grant Avatar answered Mar 07 '23 17:03

Justin Grant