如何强制 ADO.Net 在阅读器 TableSchema 中仅使用 System.String 数据类型

Question

我正在使用 OleDbConnection 查询 Excel 2007 电子表格。我想强制 OleDbDataReader 仅使用字符串作为列数据类型。

系统正在查看前 8 行数据并推断数据类型为 Double。问题是，在第 9 行，该列中有一个字符串，并且 OleDbDataReader 返回 Null 值，因为它无法转换为 Double。

我使用了这些连接字符串：

Provider=Microsoft.ACE.OLEDB.12.0;数据源=“ExcelFile.xlsx”;持久安全信息=False;扩展属性=“Excel 12.0;IMEX=1;HDR=否”

Provider=Microsoft.Jet.OLEDB.4.0;数据源=“ExcelFile.xlsx”;持久安全信息=False;扩展属性=“Excel 8.0;HDR=No;IMEX=1”

查看 reader.GetSchemaTable().Rows[7].ItemArray[5]，它是数据类型为双精度。

此架构中的第 7 行与我遇到问题的 Excel 中的特定列相关。 ItemArray[5] 是其 DataType 列

是否可以为阅读器创建自定义 TableSchema，以便在访问 ExcelFiles 时，我可以将所有单元格视为文本，而不是让系统尝试推断数据类型？

---

我在此页面找到了一些很好的信息：使用 ADO.NET 阅读 Excel 电子表格的提示

ADO.NET 接口的主要特点是数据类型的处理方式。 （您会注意到，在阅读电子表格时，我一直在小心地避免返回哪些数据类型的问题。）您准备好了吗？ ADO.NET 扫描前 8 行数据，并据此猜测每列的数据类型。然后它尝试将该列中的所有数据强制转换为该数据类型，只要强制失败就返回 NULL！

谢谢你，
Keith

---

这是我的代码的简化版本：

using (OleDbConnection connection = new OleDbConnection(BuildConnectionString(dataMapper).ToString()))
{
    connection.Open();
    using (OleDbCommand cmd = new OleDbCommand())
    {
        cmd.Connection = connection;
        cmd.CommandText = SELECT * from [Sheet1$];
        using (OleDbDataReader reader = cmd.ExecuteReader())
        {
            using (DataTable dataTable = new DataTable("TestTable"))
            {
                dataTable.Load(reader);
                base.SourceDataSet.Tables.Add(dataTable);
            }
        }
    }
}

Answer 1

正如您所发现的，OLEDB 使用 Jet，但它的调整方式受到限制。如果您设置使用 OleDbConnection 读取 Excel 文件，则需要将 HKLM\...\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows 值设置为零，以便系统将扫描整个结果集。

也就是说，如果您愿意使用替代引擎读取 Excel 文件，您可以考虑尝试 ExcelDataReader。它将所有列读取为字符串，但允许您使用 dataReader.Getxxx 方法来获取键入的值。下面是填充 DataSet 的示例：

DataSet result;
const string path = @"....\Test.xlsx";
using ( var fileStream = new FileStream( path, FileMode.Open, FileAccess.Read ) )
{
    using ( var excelReader = ExcelReaderFactory.CreateOpenXmlReader( fileStream ) )
    {
        excelReader.IsFirstRowAsColumnNames = true;
        result = excelReader.AsDataSet();
    }
}

Answer 2

对于 64 位操作系统，请注意此处：

My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

Answer 3

在此页面上查看最终答案。

---

刚刚注意到您引用的页面说了同样的事情...

---

更新：

问题似乎出在 JET 引擎本身而不是 ADO 上。一旦 JET 决定了类型，它就会坚持下去。此后所做的任何事情都没有效果；就像在 SQL 中将值转换为字符串（例如 Cstr([Column])）一样，只会导致返回空字符串。

此时（如果没有其他答案）我会选择其他方法：修改电子表格；修改注册表（这并不理想，因为您会弄乱使用 JET 的每个其他应用程序的设置）； Excel 自动化或不使用 JET 的第三方组件。

如果自动化选项速度很慢，那么也许只需使用它以更容易处理的不同格式保存电子表格。

Answer 4

我也遇到过同样的问题，并且确定这是很多人都经历过的事情。这里有一些建议的解决方案，其中许多是我尝试实施的：

---

1. 将以下内容添加到您的连接字符串中(Source< /a>):

TypeGuessRows=0;ImportMixedTypes=文本

2. 将以下内容添加到连接字符串中(来源，更多讨论，更多）：

IMEX=1；HDR=否；

3. 编辑以下注册表设置，禁用“TypeGuessRows”，并将“ImportMixedTypes”设置为“Text”(来源，不推荐，更多文档）：

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

4. 考虑使用替代库来读取 Excel 文件：

   • EPPlus
   • ExcelDataReader（也建议使用@Thomas）
   • OpenXml

5. 将源文件中的所有数据格式化为文本（至少前 8 行），尽管我知道这通常不切实际（来源，虽然这与SSIS有关，但概念相同）

6. 在导入文件之前使用 Schema.ini 文件定义数据类型，我发现这与直接使用“Jet.OleDb”有关，可能需要您修改连接字符串。这可能仅适用于 CSV 我还没有尝试过这种方法。（来源, 相关文章)

---

这些都对我不起作用（尽管我相信它们对其他人有用）。我同意@Asher表达的观点，这个问题确实没有好的解决方案。在我的软件中，我只是向用户显示一条错误消息（如果任何必需的列包含空值），指示他们 将所有列格式设置为“文本”。

老实说，我认为这本书更适用情况。已经多次指出的问题是：

• “目标的数据类型是 varchar，但假定的数据
  “double”类型会使任何不适合的数据无效。”(源)

• "但问题实际上出在 OLEDBDataReader 上。问题
  是如果它看到一列中大部分是数字，它就会假设一切
  是一个数字 - 如果正在读取的行项目不是数字，它只是
  将其设置为空！哎呀！”(来源)

• “问题似乎出在 JET 引擎本身，而不是 ADO。一次
  JET 决定类型，并坚持下去。”(@Asher)

虽然我还没有找到任何以官方身份记录的内容，但我认为很明显这是一个有意的设计决策，并且Jet 数据库库 有效 我犹豫是否称这个库完全无用，因为我认为对于很多人来说，其中一些解决方案。确实有效，但到目前为止，对于我的项目，我得出的结论是，该库无法读取单列中的多种数据类型，并且不适合一般数据检索。