我们有一些代码，可以从Excel(XLSX和XLS(文件打开并加载数据。我们使用Microsoft Access数据库引擎(ACE(的OLEDBCONNECTION。
有时，当相关文件在网络共享上，用户在Excel中打开文件时，此代码会引发异常。我着手尝试解决此问题，我的假设是我可以将设置添加到连接字符串中以配置仅读取访问。
在我的研究中，我发现这个问题提出了很多次，并以各种推荐的解决方案回答。不幸的是，我发现它们都没有起作用，并且在Excel的连接字符串设置上找不到任何官方的Microsoft文档。
我开始认为我想做的是不可能的，并且很高兴任何帮助。
这是我的测试代码：
const string excelFile = @"\serverfolderfile.xlsx";
var connStrings = new[] {
// Base, no "read only" configuration
$"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelFile};Extended Properties="Excel 12.0;IMEX=1"",
// Mode=Read
//
// C# ace oledb 12 read-only file
// https://stackoverflow.com/questions/45165570/c-sharp-ace-oledb-12-read-only-file
//
// OleDbConnection Read Only Mode
// https://social.msdn.microsoft.com/Forums/office/en-US/498cd52a-b0ee-4c8d-8943-2b76055b4130/oledbconnection-read-only-mode?forum=accessdev
$"Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Data Source={excelFile};Extended Properties="Excel 12.0;IMEX=1"",
// READONLY=TRUE (and variations) in Extended Properties
//
// Excel source read only?
// https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d03e4b1a-6be0-4b3c-8b31-42d6fc79bf39/excel-source-read-only?forum=sqlintegrationservices
//
// Working with MS Excel(xls / xlsx) Using MDAC and Oledb
// https://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled
$"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelFile};Extended Properties="Excel 12.0;IMEX=1;READONLY=TRUE"",
$"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelFile};Extended Properties="Excel 12.0;IMEX=1;ReadOnly=true;"",
$"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelFile};Extended Properties="Excel 12.0;IMEX=1;MODE=READ;READONLY=TRUE"",
// Wild guesses
$"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelFile};Extended Properties="Excel 12.0;IMEX=1;READONLY=1"",
$"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelFile};Extended Properties="Excel 12.0;IMEX=1;MODE=READ;READONLY=1""
};
for ( var i = 0; i < connStrings.Length; i++ ) {
var conn = new OleDbConnection( connStrings[i] );
try {
conn.Open();
Console.WriteLine( $"{i}: Success" );
conn.Close();
}
catch ( OleDbException ex ) {
Console.WriteLine( $"{i}: FAIL: {ex.Message}" );
}
finally {
conn.Dispose();
}
}
当目标文件在网络共享上的Excel中打开时，所有连接字符串的变化都如下所示：
0: FAIL: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
1: FAIL: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
2: FAIL: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
3: FAIL: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
4: FAIL: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
5: FAIL: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
6: FAIL: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
您可以创建文件的副本以绕过只读访问。
尝试System.IO名称空间的File.Copy()方法将Excel复制到C：/temp。
然后使用OLEDB将其打开以读取和关闭/删除