使用OleDb以只读模式打开Excel文件？

Question

我们有一些代码，可以打开并从Excel（XLSX和XLS）文件加载数据。我们使用Microsoft Access Database Engine（ACE）的OleDbConnection。

偶尔，当问题中的Excel文件位于网络共享上并且用户在Excel中打开该文件时，此代码会引发异常。我试图修复它，我的假设是可以添加一个设置到连接字符串中来配置只读访问。

在我的研究中，我发现这个问题被多次提出，并且用各种推荐的解决方案进行了回答。不幸的是，我发现它们都不起作用，而且我找不到任何关于Excel连接字符串设置的官方Microsoft文档。

我开始觉得我想做的事情是不可能的，并且会感激任何帮助。

以下是我的测试代码：

const string excelFile = @"\\server\folder\file.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.

- Darryl

---

它已被其他用户独占性地打开。嗯，这听起来好像即使只有读取权限也是被禁止的。你能测试一下 Excel 如何打开这些文件吗？ - TaW

叹口气。有时候你看不到就在眼前的东西，谢谢。如果你想把这个作为答案发出来，我会选择它。我还找到了这个问题，它帮助验证了似乎正在发生的事情：https://superuser.com/questions/491020/how-do-i-prevent-excel-from-locking-files-by-default - Darryl

@TaW 我遇到了同样的错误，当我在Excel中打开文件时，它告诉我该文件被用户XYZ“锁定以供编辑”。但是，我可以单击只读按钮仍然查看文件。因此，只读访问不是被禁止的，只是似乎没有办法使用代码强制执行。 - Mike Lowery

Answer 1

您可以创建文件的副本以绕过只读访问。

尝试使用System.IO命名空间中的File.Copy()方法将您的Excel复制到另一个位置，如C:/temp。

然后使用OLEDB打开它以进行读取并关闭/删除它。

- Anson