Excel Sql Query-原材料追溯（c#）

首先解决excel共享只读打开的问题

https://stackoverflow.com/questions/49619452/open-an-excel-file-in-read-only-mode-using-oledb

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

When the target file is open in Excel on a network share, all connection string variations fail like so:

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.

already opened exclusively by another program, it share read-only access