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.

Arwood

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.

Kuhl

Answer 2

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

Hit

Answer 3

After I researched again and again, finally I found 2 things.
1.Using OLEDB , It cannot read excel file which is password protected.
2.Even though Interop can read excel file no matter whether password protected or not, its performance is not as good as OLEDB.

So, I create below code by combining
1. OLEDB which has very nice performance and
2. Interop which can read every excel files.

public DataTable ReadPasswordProtectedExcel(string ExcelFilePath, string Password)
{
    String TempExcelFilePath = string.Empty;            
    DataTable _DataTable = new DataTable();

    #region Get ExcelFile and Remove Password
    {
        String TempExcelFileName = string.Empty;
        String DirectoryPath = string.Empty;
        Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.Application();
        excelapp.Visible = false;

        Microsoft.Office.Interop.Excel.Workbook newWorkbook = excelapp.Workbooks.Open(ExcelFilePath, 0,
                                            true, 5, Password, "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
                                            false, 0, true, false, false);

        TempExcelFileName = string.Format("{0}_{1}", "__", Path.GetFileName(ExcelFilePath)); // __xxx.xlsx
        TempExcelFilePath = String.Format("{0}/{1}", Path.GetDirectoryName(ExcelFilePath), TempExcelFileName);

        /// Create new excel file and remove password.
        newWorkbook.SaveAs(TempExcelFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, "", "",
        false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        newWorkbook.Close(true, "", false);

        excelapp.Quit();
        Marshal.ReleaseComObject(excelapp);
    }
    #endregion

    #region Get data from excel file by using OLEDB
    {
        _DataTable = ReadExcelFileInOLEDB(TempExcelFilePath);
        ///Delete excel file
        File.Delete(TempExcelFilePath);
    }
    #endregion

    return _DataTable;
}

public DataTable ReadExcelFileInOLEDB(string _ExcelFilePath)
{
    string ConnectionString = string.Empty;
    string SheetName = string.Empty;           
    DataTable _DataTable = null;
    DataSet _DataSet = null;

    try
    {
        ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0;'", _ExcelFilePath);
        using (OleDbConnection _OleDbConnection = new OleDbConnection(ConnectionString))
        {
            _OleDbConnection.Open();
            _DataTable = _OleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (_DataTable == null)
                return null;

            SheetName = _DataTable.Rows[0]["TABLE_NAME"].ToString();
            ConnectionString = string.Format("SELECT * FROM [{0}]", SheetName);

            using (OleDbCommand _OleDbCommand = new OleDbCommand(ConnectionString, _OleDbConnection))
            {
                using (OleDbDataAdapter _OleDbDataAdapter = new OleDbDataAdapter())
                {
                    _OleDbDataAdapter.SelectCommand = _OleDbCommand;

                    _DataSet = new DataSet();
                    _OleDbDataAdapter.Fill(_DataSet, "PrintInfo");
                    return _DataSet.Tables["PrintInfo"];
                }
            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Finally If you want to remove empty row while retrieving data from excel, please check this link and below code

SELECT * FROM NAMED_RANGE WHERE [YourColumnTitle] IS NOT NULL 

Educational

Answer 4

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

Warmongering

if the file is encrypted then is it applicable? – Bursary