如何在 C# 中使用只读模式编写连接字符串以使用 Oledb 读取 excel

本文关键字：连接 字符串 excel 读取 Oledb 模式 只读 | 更新日期: 2023-09-27 18:32:44

    string _filePath = @"C:'Users'szhao'Downloads'USER_UploadCSV.xlsx";
    FileInfo fi = new FileInfo(_filePath);
    string _connectionString = "Provider=Microsoft.JET.OLEDB.4.0;Mode=Read;Data Source=" + _filePath + "; Extended Properties='"Excel 8.0;HDR=Yes;'"";
    if (fi.Extension.Equals(".xlsx") || fi.Extension.Equals(".xls"))
    {
        // For Excel 2007 File  Format
        _connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Data Source=" + _filePath + " ;Extended Properties='"Excel 12.0 Xml;HDR=YES;'"";
    }
    OleDbConnection _conn = new OleDbConnection(_connectionString);
    _conn.Open();
    Console.WriteLine("Connect!");

连接字符串中的Mode=Read应指示这是只读模式，这将允许多应用程序读取单个 excel 而不写入。但是它在_conn.Open();中抛出异常，所以我只是认为可能是我的连接字符串是错误的？

以下是异常消息

Exception Message:
External table is not in the expected format.
Stack Trace:
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at TIDEAPI.BLL.AExcelFile..ctor(TestAdministration pTestAdministration, String pFilePath, UploadFileSpecification pUploadFileSpecification) in c:'Projects'PreProcessing'TIDE'Online'TIDEAPI'BLL'FileUpload'Abstraction'AExcelFile.cs:line 41
   at TIDEAPI.BLL.ExcelFile..ctor(TestAdministration pTestAdministration, String pFilePath, UploadFileSpecification pUploadFileSpecification) in c:'Projects'PreProcessing'TIDE'Online'TIDEAPI'BLL'FileUpload'ExcelFile.cs:line 14
   at TIDEAPI.BLL.AcceptableFileFactory.GetAcceptableFile(TestAdministration pTestAdministration, String pFilePath, UploadFileSpecification pUploadFileSpecification) in c:'Projects'PreProcessing'TIDE'Online'TIDEAPI'BLL'FileUpload'AcceptableFileFactory.cs:line 22
   at TIDEAPI.BLL.AFileProcessor..ctor(Int64 pTestAdministartionKey, String pFilePath, UploadFileSpecification pUploadFileSpecification, FrequencyDistributionLogger fdLogger, DataExchangeErrorLogger errorLogger, IDictionary`2 dataExchangeProperties) in c:'Projects'PreProcessing'TIDE'Online'TIDEAPI'BLL'FileUpload'Abstraction'AFileProcessor.cs:line 108
   at TIDEAPI.BLL.UserFileProcessor..ctor(Int64 pTestAdministrationKey, String pTideFile, UploadFileSpecification pUploadFileSpecification, FrequencyDistributionLogger fdLogger, DataExchangeErrorLogger errorLogger, IDictionary`2 dataExchangeProperties) in c:'Projects'PreProcessing'TIDE'Online'TIDEAPI'BLL'FileUpload'UserFileProcessor.cs:line 32
   at TIDEAPI.BLL.FileProcessorFactory.GetFileProcessor(Int64 pTestAdministrationKey, String pFilePath, UploadFileSpecification pUploadFileSpecification, FrequencyDistributionLogger fdLogger, DataExchangeErrorLogger errorLogger, FileProcessorTypes pFileProcessorType, IDictionary`2 dataExchangeProperties) in c:'Projects'PreProcessing'TIDE'Online'TIDEAPI'BLL'FileUpload'FileProcessorFactory.cs:line 42
   at PreIdService.PreIdFile.Load(Object pWorkerThreadParameters) in c:'Projects'PreProcessing'TIDE'Online'Tools'TideBatchUploadService'PreIdFile.cs:line 62

如果我删除 Mode=Read，它将不允许多应用程序读取，但单个用户将起作用。

如何在 C# 中使用只读模式编写连接字符串以使用 Oledb 读取 excel

xls 和 xlsx 的连接字符串是不同的。

if (fi.Extension.Equals(".xls"))
{
   _connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
       _filePath + " ;Extended Properties='"Excel 8.0;HDR=No;IMEX=1'"";
}
else if (fi.Extension.Equals(".xlsx"))
{
   _connectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + 
       _filePath + " ;Extended Properties='"Excel 12.0;HDR=No;IMEX=1'"";
}
相关文章：