通过注册表解决 OLEDB 方式读取 Excel 数字文本混合列数据丢失的问题

文章评分 3 次，平均分 4.7 ：

在 C# 中，读取 EXCEL 文件一般有四种方法：

OleDB 方式：Microsoft.Jet.OleDb.4.0，或者 Microsoft.Ace.OleDb.12.0
COM 组件：Microsoft.Office.Interop.Excel.dll
文件流读取，需要预先转化成 CSV（逗号分隔）文件
第三方组件：比如 NPOI 等

小编在 C# 程序开发的时候，常用的是第一种方法，即 OleDB 方式。该方法的代码简短，执行效率也能接受。不过，在使用 OleDB 方式读取 Excel 时，遇到数字文本等混合数据列很容易发生值丢失的现象。

产生这种问题的根源，在于 Excel ISAM（Indexed Sequential Access Method，即索引顺序存取方法）驱动程序的数据类型识别机制。Excel ISAM 驱动程序是通过检查 Excel 表中前 N（默认情况下为 8）行的实际值，来选择能够代表其样本中大部分值的数据类型作为该列的数据类型。例如，如果数字占多，那么其它含有字母等文本的数据项就会置空；相反如果文本居多，纯数字的数据项就会被置空。

解决方法是：连接字符串中的 Extended Properties 项中的IMEX设置为 1，即导入模式，将混合数据强制转换为文本。

但是，但是后面一般都很重要。这种设置是不可靠的！因为只有当前N行中出现文本时，强制转换才会生效。当前N行中没有出现文本，比如都是数字格式，那么这一列的数据类型仍会被设置为数字。从第N+1行开始出现的中文、字母等非数字的值，仍会丢失。

针对上面这种情况，我们可以通过修改注册表，尽可能增大N的值（比如 1000），进一步减小格式误判的可能性。

1.Microsoft.Jet.OleDb.4.0（.xls文件）

32 位系统：[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] ，对应字段 TypeGuessRows;

64 位系统：[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Excel]，对应字段 TypeGuessRows。

2.Microsoft.Ace.OleDb.12.0（.xlsx文件）

32 位系统：[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel]，对应字段 TypeGuessRows；

64 位系统：[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel]，对应字段 TypeGuessRows。

但是需要注意的是，通过设置 IMEX=1 和增大 TypeGuessRows 仅能减少格式误判的可能性，而没有办法彻底解决这一问题。在某些极端情况下，比如前 1000 行都没有出现混合数据格式，那么 1001 行开始出现的文本值仍然会丢失。但是，也有网友测试说，把 TypeGuessRows 设为 0 可以解决误判问题，感兴趣的朋友可以尝试一下。

当然不管怎么样，最保险的还是在读取 Excel 数据之前，首先确保 Excel 表字段的前 N 行中出现数字、中文、字母的混合数据格式。

打赏