如何强制ADO.Net在读取表架构时仅使用System.String数据类型

Question

如何强制ADO.Net在读取表架构时仅使用System.String数据类型

12

我正在使用OleDbConnection查询Excel 2007电子表格。我希望强制OleDbDataReader仅使用字符串作为列数据类型。
系统查看前8行数据并推断数据类型为Double。问题是，第9行中有一个字符串，并且OleDbDataReader返回空值，因为它无法转换为Double。
我已经使用了以下连接字符串：
Provider=Microsoft.ACE.OLEDB.12.0;Data Source="ExcelFile.xlsx";Persist Security Info=False;Extended Properties="Excel 12.0;IMEX=1;HDR=No"
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="ExcelFile.xlsx";Persist Security Info=False;Extended Properties="Excel 8.0;HDR=No;IMEX=1"
查看reader.GetSchemaTable().Rows[7].ItemArray[5]，它的数据类型为Double。
是否可以为读取器创建自定义TableSchema，以便在访问Excel文件时将所有单元格视为文本，而不是让系统尝试推断数据类型？

我在这个页面找到了一些有用的信息：Tips for reading Excel spreadsheets using ADO.NET
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);
            }
        }
    }
}

- Keith Sirmons

你能贴出从电子表格中读取数据的代码吗？ - rlb.usa

谢谢你提供的代码。在我看来，它看起来很好。我自己也做过这个ADO Excel读取器，没有遇到任何问题。当你明确告诉它使用数据值作为字符串时，它是否会给你任何错误？celldata = reader[i][i].ToString() - rlb.usa

或者，如果您正在使用GridView或Repeater（我怀疑），则显式转换并将数据用作字符串？ - rlb.usa

数据读取器本身将问题单元格返回为dbNull。请查看问题中间的链接，它很好地解释了这个问题。 - Keith Sirmons

4个回答

2

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

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

- Simon_Weaver

然后将TypeGuessRows设置为0。 - Simon_Weaver

1

请查看此页面上的最终答案。

刚刚注意到你所提到的页面上也说了同样的事情...

更新：

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

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

如果自动化选项太慢，那么可能只需使用它以不同的格式保存电子表格，这样更容易处理。

- Asher

是的。这是我提供的链接相同的信息，但对于我的情况不起作用。谢谢，Keith - Keith Sirmons

0

我曾经遇到过同样的问题，并确定这是许多人常见的情况。以下是一些已被建议的解决方案，其中许多我已尝试实施：

将以下内容添加到您的连接字符串中(Source)：

TypeGuessRows=0;ImportMixedTypes=Text

将以下内容添加到您的连接字符串中(Source, More Discussion, Even More)：

IMEX=1;HDR=NO;

编辑以下注册表设置，禁用 "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

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

EPPlus
ExcelDataReader（也被@Thomas建议）
OpenXml

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

在导入文件之前使用Schema.ini文件定义数据类型，我发现这与直接使用“Jet.OleDb”有关，可能需要修改连接字符串。这可能仅适用于CSV，我尚未尝试此方法（Source，Related Post）

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

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

"目标数据类型为varchar，但假定的“double”数据类型使得任何不符合条件的数据无效。"(来源)
"但问题实际上出在OLEDBDataReader上。问题在于，如果它在一列中看到大多数数字，它会假定所有东西都是数字 - 如果正在读取的行项目不是数字，则简单地将其设置为null！疼！"(来源)
"问题似乎出在JET引擎本身而不是ADO上。一旦JET决定了类型，它就会坚持这样做。"(@Asher)

虽然我没有在官方文档中找到任何相关内容，但我认为这是一个有意的设计决策，也是Jet Database Library的工作方式。我不敢说这个库完全没用，因为我认为对于许多人来说，其中一些解决方案确实有效，但就我的项目而言，我得出的结论是这个库无法读取单个列中的多种数据类型，且不适合进行通用数据检索。

- David Rogers

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Thomas · Accepted Answer

正如您所发现的那样，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();
    }
}