有没有更好的方法来指示＆QUOT;零＆QUOT;在Excel中值？

问题描述

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

通过一些试验，我已经成功地找到两种不同的方式来表示一个单元应该由ADO.NET视为空：

1. 细胞是完全空白。
2. 在该单元格中包含＃N / A 。

不幸的是，这两种是有问题的：

1. 大多数在Excel中的数据我列通过公式生成，但它无法在Excel中生成一个公式，结果在一个完全空白单元格。而且，只有一个完全空白单元格将被视为无效（空字符串将无法正常工作）。

2. 任何公式计算结果为＃N / A （由于实际查找错误，或者因为 NA（）函数使用）的将会的被视为无效。这似乎是理想的解决方案，直到我发现， Excel工作簿必须打开这个工作。当您关闭工作簿，OLEDB突然开始看到那些＃N / A 取值为字符串。这会导致类似以下例外情况在填充时抛出的数据表：

问：我怎么能指示通过不Excel公式的空值的必有打开工作簿时，我填的是数据表？或者有什么可以做，使＃N / A 的值被视为无效，即使关闭工作簿？

在情况下，它是重要的，我的连接字符串使用以下方法建立的：

  VAR建设者=新OleDbConnectionStringBuilder
{
    供应商=Microsoft.ACE.OLEDB.12.0
    数据源= _workbookPath
};
builder.Add（扩展属性，创先争优12.0的Xml; HDR =是; IMEX = 0）;
返回builder.ConnectionString;
 

（ _workbookPath 是完整路径到工作簿）。

我都试过 IMEX = 0 和 IMEX = 1 ，但它没有什么区别。

解决方案

你打了Excel许多非常沮丧用户所遇到的砖墙。不幸的是Excel作为一个公司的工具是wides $ P $垫，似乎相当强劲，可惜的是，因为每一个细胞/行/列有它使一个噩梦来处理与其他工具，如MySQL，SQL Server中，R，RapidMiner变体的数据类型，SPSS和这样的例子不胜枚举。看来，Excel的2007/2010不是很好的支持，更要考虑32/64位版本的考虑，这是可耻的这个时代。当

的主要问题是，当ACE /射流访问在Excel每个字段它们使用注册表设置'TypeGuessRows'，以确定有多少行使用评估数据类型。默认的行以扫描是8行。注册表设置TypeGuessRows可以指定一（1）至十六（16）行的整数值，也可以指定零（0），扫描所有现有行。如果您不能更改注册表设置（如90％的办公环境），它使生活困难的猜测行仅限于第8。

例如，没有更改注册表如果＃N / A中第一次出现是在第8行内，则IMEX = 1将返回错误作为一个字符串＃N / A。如果IMEX = 0那么＃N / A将返回'零'。

如果对＃N / A中第一次出现是超越了前8行则两个IMEX = 0＆放大器; IMEX = 1都返回'零'（假定所需的数据类型是数字）。

通过更改注册表（TypeGuessRows = 0），那么都应该罚款。

也许有4个选项：

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

2. 列表中的第8行作为伪数据所有可能的类型的变化（例如备注字段/ NCHAR（最大值）/错误＃N / A等）

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

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

编辑：只需要把引导的:)另外两件事情真的激怒了我的;如果在一个表中的前场已经结束的前8行空，您不能编辑注册表设置，然后将整个工作表返回为空白（许多有趣的谈话，告诉经理人他们是傻瓜的合并单元格的！）。另外，如果在Excel 2007/2010你有一个部门与返回> 255列的表/字段，那么你有很大的问题，如果你需要非连续输入（例如：钥匙在第1栏和数据COLS 255+）

I have an Excel 2007 workbook that contains tables of data that I'm importing into DataTable objects using ADO.NET.

Through some experimentation, I've managed to find two different ways to indicate that a cell should be treated as "null" by ADO.NET:

1. The cell is completely blank.
2. The cell contains #N/A.

Unfortunately, both of these are problematic:

1. Most of my columns of data in Excel are generated via formulas, but it's not possible in Excel to generate a formula that results in a completely blank cell. And only a completely blank cell will be considered null (an empty string will not work).

2. Any formula that evaluates to #N/A (either due to an actual lookup error or because the NA() function was used) will be considered null. This seemed like the ideal solution until I discovered that the Excel workbook must be open for this to work. As soon as you close the workbook, OLEDB suddenly starts seeing all those #N/As as strings. This causes exceptions like the following to be thrown when filling the DataTable:

Question: How can I indicate a null value via an Excel formula without having to have the workbook open when I fill the DataTable? Or what can be done to make #N/A values be considered null even when the workbook is closed?

In case it's important, my connection string is built using the following method:

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 is the full path to the workbook).

I've tried both IMEX=0 and IMEX=1 but it makes no difference.

解决方案

You're hitting the brickwall that many very frustrated users of Excel are experiencing. Unfortunately Excel as a company tool is widespread and seems quite robust, unfortunately because each cell/column/row has a variant data type it makes it a nightmare to handle with other tools such as MySQL, SQL Server, R, RapidMiner, SPSS and the list goes on. It seems that Excel 2007/2010 is not very well supported and even more so when taking 32/64 bit versions into account, which is scandalous in this day and age.

The main problem is that when ACE/Jet access each field in Excel they use a registry setting 'TypeGuessRows' to determine how many rows to use to assess the datatype. The default for "Rows to Scan" is 8 rows. The registry setting 'TypeGuessRows' can specify an integer value from one (1) to sixteen (16) rows, or you can specify zero (0) to scan all existing rows. If you can't change the registry setting (such as in 90% of office environments) it makes life difficult as the rows to guess are limited to the first 8.

For example, without the registry changeIf the first occurrence of #N/A is within the first 8 rows then IMEX = 1 will return the error as a string "#N/A". If IMEX = 0 then an #N/A will return 'Null'.

If the first occurrence of #N/A is beyond the first 8 rows then both IMEX = 0 & IMEX = 1 both return 'Null' (assuming required data type is numeric).

With the registry change (TypeGuessRows = 0) then all should be fine.

Perhaps there are 4 options:

1. Change the registry setting TypeGuessRows = 0

2. List all possible type variations in the first 8 rows as 'dummy data' (eg memo fields/nchar(max)/ errors #N/A etc)

3. Correct ALL data type anomalies in Excel

4. Don't use Excel - Seriously worth considering!

Edit:Just to put the boot in :) another 2 things that really annoy me are; if the first field on a sheet is blank over the first 8 rows and you can't edit the registry setting then the whole sheet is returned as blank (Many fun conversations telling managers they're fools for merging cells!). Also, if in Excel 2007/2010 you have a department return a sheet with >255 columns/fields then you have huge problems if you need non-contiguous import (eg key in col 1 and data in cols 255+)

这篇关于有没有更好的方法来指示＆QUOT;零＆QUOT;在Excel中值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！