how to open password protected excel file in .net

Ramesh gawade

error : External table is not in the expected format.

code:

connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + fName + ";" + @"Jet OLEDB:Database Password=Test" + ";" + @"Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 8.0" + Convert.ToChar(34).ToString() + ";";

OleDbConnection OleDbcon = new OleDbConnection(connectionString);
OleDbDataAdapter adp = new OleDbDataAdapter("select * from [Sheet1$]", OleDbcon);

adp.FillSchema(dt1, SchemaType.Source);
adp.Fill(dt1);

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Uehara Ayaka9y ago
You could use spire.xls to load an encrypted Excel file to a workbook object.
Workbook wb = new Workbook();
wb.OpenPassword = "openPassword";
wb.LoadFromFile("Encrypted.xlsx");
+1
Nilesh Jadav9y ago
"External table is not in the expected format." typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

Using the following connection string seems to fix most problems.

public static string path = @"C:\src\RedirectApplication\RedirectApplication\301s.xlsx"; public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
Change your extension according to the version of excel you are using, also add HDR ="Yes" or "No". Hope that helps !
+2