In my c# application I am using OLEDB connection string "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Extended Properties=\"Excel 8.0;HDR=NO;ReadOnly=true;IMEX=1\"
" to read Excel files.
In order to read a password protected file I tried adding password field in connection string but was unable to read file.
I want to know is there any way to read password protected Excel files using OLEDB if I know its password beforehand.
Open the workbook that you want to change or remove the password for. On the Review tab, click Protect Sheet or Protect Workbook. Click Unprotect Sheet or Protect Workbook and enter the password. Clicking Unprotect Sheet automatically removes the password from the sheet.
You don't have to have office or the office data connectivity installed, you can use the Jet for OleDB engine which is installed on pretty much every windows machine in existence. However it's very old technology and is limited to 32 bit.
If you use a query to read the excel file, it doesn't matter if some of the sheets are protected: It works either way.
private string ExcelConnection(string fileName)
{
return
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=" + fileName + ";" +
@"Extended Properties=" + Convert.ToChar(34).ToString() +
@"Excel 8.0" + Convert.ToChar(34).ToString() + ";";
}
private DataTable readExcel(string fileName, string sql)
{
OleDbConnection conn = new OleDbConnection(ExcelConnection(fileName));
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataAdapter adp = new OleDbDataAdapter();
adp.SelectCommand = cmd;
DataTable dt = new DataTable();
try
{
adp.FillSchema(dt, SchemaType.Source);
adp.Fill(dt);
}
catch
{
}
return dt;
}
Here are different ways to connect to an Excel file, including OLEDB. According to this, you can't open a password protected file with standard methods. You have to use a workaround.
If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file.
This is the solution, albeit not in C#, but you could easily adapt it for your purposes.
If you don't KNOW the password yourself, an alternative is to re-write the file without a password. You can use this handy project and add the following routine to it:
public void SaveFile()
{
this.excelWorkbook.SaveAs(
this.excelWorkbook.FullName,
vk_format,
"",
vk_write_res_password,
vk_read_only,
null,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
null,
vk_add_to_mru,
null,null,vk_local);
}
Full detail here.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With