在 C# 中使用 OLEDB 读取受密码保护的 excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1356051/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Read password protected excel file using OLEDB in C#
提问by UJ.
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;Data Source=c:\test.xls;Extended Properties=\"Excel 8.0;HDR=NO;ReadOnly=true;IMEX=1\"”来读取 Excel 文件。为了读取受密码保护的文件，我尝试在连接字符串中添加密码字段，但无法读取文件。我想知道如果我事先知道它的密码，是否有任何方法可以使用 OLEDB 读取受密码保护的 Excel 文件。
回答by Mr. Smith
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 projectand 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);
}
回答by dbkk
You can use OoXmlCrypto streamto 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();
}
回答by Michelle Ouzts
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;
}
回答by Frank Myat Thu
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 linkand below code
最后，如果您想在从 excel 检索数据时删除空行，请检查此链接和以下代码
SELECT * FROM NAMED_RANGE WHERE [YourColumnTitle] IS NOT NULL
