有没有更好的方式来表示& null"值在Excel中

本文关键字：quot 值在 Excel null 有没有 方式 表示 更好 | 更新日期: 2023-09-27 17:50:38

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

通过一些实验，我设法找到了两种不同的方法来表明一个单元格应该被ADO视为"null"。净:

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

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

  1. 我在Excel中的大多数数据列都是通过公式生成的，但在Excel中不可能生成导致完全空白单元格的公式。只有完全空白的单元格才会被认为是空的(空字符串不起作用)。

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

    输入字符串格式不正确。无法在值列中存储<#N/A>。期望类型为Int32

问题:我如何通过Excel公式指示空值，而不必须在我填写DataTable时打开工作簿?或者可以做些什么来使#N/A值被认为是空的，即使工作簿是关闭的?

如果它很重要，我的连接字符串是使用以下方法构建的:

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，但没有区别。

有没有更好的方式来表示& null"值在Excel中

你遇到了许多非常沮丧的Excel用户正在经历的瓶颈。不幸的是，Excel作为一个公司工具是广泛的，似乎相当强大，不幸的是，因为每个单元格/列/行都有不同的数据类型，这使得它与其他工具(如MySQL, SQL Server, R, RapidMiner, SPSS等)一起处理成为一场噩梦。似乎Excel 2007/2010并没有得到很好的支持，尤其是当考虑到32/64位版本时，这在当今这个时代是可耻的。

主要问题是，当ACE/Jet访问Excel中的每个字段时，他们使用注册表设置'TypeGuessRows'来确定使用多少行来评估数据类型。"要扫描的行"的默认值是8行。注册表设置'TypeGuessRows'可以指定从1(1)到16(16)行的整数值，或者您可以指定0(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行中所有可能的类型变化为"虚拟数据"(例如memo字段/nchar(max)/errors #N/A等)

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

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

编辑:

只是把靴子放进去:)另外两件真的让我恼火的事情是;如果工作表的第一个字段在前8行中是空白的，并且您不能编辑注册表设置，那么整个工作表将返回为空白(许多有趣的对话告诉经理他们合并单元格是愚蠢的!)。此外，如果在Excel 2007/2010中，您有一个部门返回一个具有>255列/字段的工作表，那么如果您需要非连续导入(例如键在cols 1中，数据在cols 255+中)，您将遇到巨大的问题

相关文章：