how to open password protected excel file in .net

Question

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

Uehara Ayaka · Answer

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

Nilesh Jadav · Answer

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 !