Read password protected excel file using OLEDB in C#

Question

Hello,

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.

Answer 1

Unfortunately Jet OLEDB and the Excel ISAM do not support password protected Excel files.

Answer 2

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.

Answer 3

You can use OoXmlCrypto stream to access Office 2007 encrypted files. Open source, includes modified ExcelPackage.

Sample code:

using (OfficeCryptoStream stream = OfficeCryptoStream.Open("a.xlsx", "password"))
{
    // Do stuff (e.g. create System.IO.Packaging.Package or 
    // ExcelPackage from the stream, make changes and save)

    // Change the password (optional)
    stream.Password = "newPassword";

    // Encrypt and save the file
    stream.Save();
}