在c#中使用OLEDB读取密码保护的excel文件

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.

在c#应用程序中，我使用OLEDB连接字符串“Provider=Microsoft.Jet.OLEDB.4.0;数据源=c:\test.xls;Extended Properties= " \"Excel 8.0;HDR=NO;ReadOnly=true;IMEX=1\"来读取Excel文件。为了读取密码保护文件，我尝试在连接字符串中添加密码字段，但无法读取文件。我想知道，如果我事先知道OLEDB的密码，是否可以使用OLEDB来读取密码保护的Excel文件。

4 个解决方案

#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.

以下是连接到Excel文件的不同方法，包括OLEDB。因此，不能使用标准方法打开密码保护文件。你得找个变通的办法。

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.

如果Excel工作簿是由密码保护的，那么即使通过提供正确的连接字符串的密码，也不能打开它来访问数据。如果您尝试，您将收到以下错误消息:“无法解密文件。”

This is the solution, albeit not in C#, but you could easily adapt it for your purposes.

这是一种解决方案，虽然不在c#中，但是您可以根据自己的目的轻松地调整它。

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.

完整的细节。

#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.

如果您使用查询来读取excel文件，那么是否保护了一些表并不重要:这两种方法都可以。

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

#3

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

您可以使用OoXmlCrypto流访问Office 2007加密文件。开源，包括修改后的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();
}

#4

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.

在我反复研究之后，我终于发现了两件事。1。使用OLEDB时，它不能读取密码保护的excel文件。2。即使Interop可以读取excel文件，无论密码是否受到保护，其性能都不如OLEDB好。

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

我将1合并到下面的代码中。OLEDB有很好的性能2。互操作，可以读取每个excel文件。

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

最后，如果您想在从excel中检索数据时删除空行，请检查这个链接和下面的代码

SELECT * FROM NAMED_RANGE WHERE [YourColumnTitle] IS NOT NULL

#1