使用Microsoft.ACE.OLEDB.12.0读取.xlsx正常但.xls无法获取工作表名

问题场景 #

将BizTalk 2016迁移至全新服务器的BizTalk 2020后，已安装Access OleDB Provider，读取.xlsx文件正常，但读取.xls文件时出现以下异常：

• 调用GetOleDbSchemaTable动态获取工作表名时，schemaTable.Rows.Count返回0，触发ArgumentException
• 硬编码工作表名（已添加$后缀）时，报错：The Microsoft Access database engine could not find the object 'Sheet1$'

原BizTalk 2016环境使用ACE.OLEDB.10.0连接字符串可正常读取.xls，但新环境使用该Provider会提示"未注册"；尝试将连接字符串的Extended Properties改为Excel 8.0;HDR=Yes仍无效。

测试环境：Visual Studio 2019、.NET Framework 4.7.2

调试代码 #

static void testAccessOleDBAdapterForExcel()
{
    string methodPrefix = "method: testAccessOleDBAdapterForExcel  - ";

    string excelFilename = null;
    string worksheetName = null;

    int fileNumber = 1;  //switch between two files and corresponding sheet name 

    if (fileNumber == 1)
    {
        excelFilename = @"\\Share1\SampleExcelFiles\PublishPowerMeterData_-15_05012023060600_2023-05-01T06-04-02.xls";
        worksheetName = "Sheet1";
    }
    else
    {
        excelFilename = @"\\Share1\SampleExcelFiles\PublishForwardPrice_powerfolio_extract_05_01_2023_08_00_2023-05-01T14-56-04.xlsx"; 
        worksheetName = "South";
    }

    // We have to add  on the end of the actual Worksheet 
    worksheetName = worksheetName + "$"; 


    // original code on BT20106 used ACE.OLEDB.10.0 
    //var connectionStringOld = @"Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" + excelFilename + ";Extended Properties=\"Excel 10.0;HDR=YES\"";
    var connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilename + ";Extended Properties=\"Excel 12.0;HDR=YES\"";

    Console.WriteLine(methodPrefix + "FileName=" + excelFilename);
    Console.WriteLine(methodPrefix + "attempt spreadsheet read with Access OleDB Adapter");
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        Console.WriteLine(methodPrefix + "Conn.Open() succeeded");

        var schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        // The following if statement throws the ArgumentException with the .xls 
        if (schemaTable.Rows.Count < 1) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
        string dynWorksheet = schemaTable.Rows[0]["table_name"].ToString().Replace("'", "");

        Console.WriteLine(methodPrefix + "dynWorksheet = " + dynWorksheet);

        OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
        ("select * from [" + dynWorksheet + "]", conn);
        DataSet excelDataSet = new DataSet();
        objDA.Fill(excelDataSet);
        Console.WriteLine("method: testAccessOleDBAdapterForExcel  - was able to fill dataset object"); 
    }
    
}

解决建议 #

• 匹配Provider位数与运行环境
  确保安装的Microsoft Access Database Engine 2010 Redistributable（包含ACE.OLEDB.12.0）位数与BizTalk服务、测试程序一致（32位/64位）。若BizTalk以32位模式运行，必须安装32位ACE Provider，反之亦然。
• 调整.xls专属连接字符串
  针对.xls文件，使用指定旧版格式的连接字符串，添加IMEX=1避免数据类型解析异常：

  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\Share1\SampleExcelFiles\yourfile.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1;"
  

• 排查文件权限与路径问题
  确认BizTalk服务账户（或测试程序运行账户）拥有共享文件夹的读取权限，可先将.xls文件复制到本地路径测试，排除网络共享的影响。
• 验证工作表名称准确性
  部分.xls文件的工作表名称可能包含空格或特殊字符，需完全匹配（含大小写）。可通过Excel的VBA编辑器查看工作表的Name属性，确认OLEDB识别的实际名称。

内容的提问来源于stack exchange，提问作者NealWalters