Read password protected excel file using OLEDB in C#

Question

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

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.