c# - 有没有更好的方法在 Excel 中指示 "null"值？

Question

我有一个 Excel 2007 工作簿，其中包含我正在使用 ADO.NET 导入到 DataTable 对象中的数据表。

通过一些实验，我设法找到了两种不同的方法来指示单元格应被 ADO.NET 视为“空”:

1. 单元格完全空白。
2. 单元格包含#N/A。

不幸的是，这两个都是有问题的:

1. 我在 Excel 中的大部分数据列都是通过公式生成的，但在 Excel 中不可能生成导致完全空白单元格的公式。并且只有完全空白的单元格才会被视为 null(空字符串无效)。

2. 任何计算结果为 #N/A 的公式(由于实际查找错误或因为使用了 NA() 函数)将 视为空值。这似乎是理想的解决方案，直到我发现必须打开 Excel 工作簿才能使其工作。一旦您关闭工作簿，OLEDB 突然开始将所有这些 #N/A 视为字符串。这会导致在填充 DataTable 时抛出如下异常:

   Input string was not in a correct format. Couldn't store <#N/A> in Value Column. Expected type is Int32.

问题:如何通过 Excel 公式指示空值，在填充 DataTable 时不必打开工作簿？或者如何使 #N/A 值即使在工作簿关闭时也被视为 null？

以防万一，我的连接字符串是使用以下方法构建的:

var builder = new OleDbConnectionStringBuilder
{
    Provider = "Microsoft.ACE.OLEDB.12.0",
    DataSource = _workbookPath
};
builder.Add("Extended Properties", "Excel 12.0 Xml;HDR=Yes;IMEX=0");
return builder.ConnectionString;

(_workbookPath 是工作簿的完整路径)。

我已经尝试了 IMEX=0 和 IMEX=1 但没有区别。

Answer 1

您遇到了许多非常沮丧的 Excel 用户正在经历的障碍。不幸的是，Excel 作为一种公司工具被广泛使用并且看起来非常健壮，不幸的是，因为每个单元格/列/行都有不同的数据类型，这使得使用其他工具(如 MySQL、SQL Server、R、RapidMiner、SPSS 和名单还在继续。似乎 Excel 2007/2010 没有得到很好的支持，考虑到 32/64 位版本时更是如此，这在当今时代是可耻的。

主要问题是，当 ACE/Jet 访问 Excel 中的每个字段时，它们使用注册表设置“TypeGuessRows”来确定使用多少行来评估数据类型。 “要扫描的行”的默认值为 8 行。注册表设置“TypeGuessRows”可以指定从一 (1) 到十六 (16) 行的整数值，或者您可以指定零 (0) 以扫描所有现有行。如果您无法更改注册表设置(例如在 90% 的办公环境中)，这将变得很困难，因为要猜测的行仅限于前 8 行。

例如，不修改注册表如果#N/A 的第一次出现在前 8 行内，则 IMEX = 1 将以字符串“#N/A”的形式返回错误。如果 IMEX = 0，则 #N/A 将返回“Null”。

如果#N/A 的第一次出现超出前 8 行，则 IMEX = 0 和 IMEX = 1 都返回“Null”(假设所需数据类型为数字)。

随着注册表的更改(TypeGuessRows = 0)，一切都应该没问题。

也许有4种选择:

1. 更改注册表设置 TypeGuessRows = 0

2. 在前 8 行中列出所有可能的类型变体作为“虚拟数据”(例如备注字段/nchar(max)/错误#N/A 等)

3. 更正 Excel 中的所有数据类型异常

4. 不要使用 Excel - 真的值得考虑!

编辑:只是为了启动 :) 另外两件真正让我烦恼的事情是；如果工作表上的第一个字段在前 8 行中是空白的，并且您无法编辑注册表设置，则整个工作表将返回为空白(许多有趣的对话告诉经理他们合并单元格是傻瓜!)。此外，如果在 Excel 2007/2010 中你有一个部门返回一个包含 >255 列/字段的工作表，那么如果你需要非连续导入(例如，第 1 列中的键和第 255 列以上的数据)，你将遇到巨大的问题