excel读取错误之分析篇------格式错误

今天在我发布出去的checktool反馈中，一个同事反映我的工具在读取excel的时候有个sheet无法读取。

我自己查看了日志：

[2011-04-06 15:46:30]<2>System.ArgumentException: 不是合法的 OleAut 日期。   在 System.DateTime.DoubleDateToTicks(Double value)   在 System.Data.OleDb.ColumnBinding.Value_DATE()   在 System.Data.OleDb.ColumnBinding.Value()   在 System.Data.OleDb.OleDbDataReader.GetValues(Object[] values)   在 System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)   在 System.Data.ProviderBase.SchemaMapping.LoadDataRow()   在 System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)   在 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)   在 System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)   在 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)   在 System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)   在 System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)   在 CValidCheck.DataAccess.Query(String pSQLStr) 位置 D:\AIS Code\压缩包检测工具\CheckToolForAIS\CValidCheck\DataAccess.cs:行号 53[2011-04-06 15:46:30]<1>select * from [PDB$]

出现这个问题我真的很诧异，因为我的连接字符串中已经做了忽略excel数据格式的处理了：

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"

为什么还会出问题呢？

查找了资料后发现，一切都在于IMEX=1。

当我们设置IMEX＝1时将强制混合数据转换为文本，但仅仅这种设置并不可靠，IMEX＝1只确保在某列前8行数据至少有一个是文本项的时候才起作用，它只是把查找前8行数据中数据类型占优选择的行为作了略微的改变。例如某列前8行数据全为纯数字，那么它仍然以数字类型作为该列的数据类型，随后行里的含有文本的数据仍然变空。 另一个改进的措施是IMEX＝1与注册表值TypeGuessRows配合使用，TypeGuessRows 值决定了ISAM 驱动程序从前几条数据采样确定数据类型，默认为“8”。可以通过修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的该注册表值来更改采样行数。但是这种改进还是没有根本上解决问题，即使我们把IMEX设为“1”，TypeGuessRows设得再大，例如1000，假设数据表有1001行，某列前1000行全为纯数字，该列的第1001行又是一个文本，ISAM驱动的这种机制还是让这列的数据变成空。 
产生这种问题的根源与Excel ISAM[3]（Indexed Sequential Access Method，即索引顺序存取方法）驱动程序的限制有关，Excel ISAM 驱动程序通过检查前几行中实际值确定一个 Excel 列的类型，然后选择能够代表其样本中大部分值的数据类型[4]。也即Excel ISAM查找某列前几行（默认情况下是8行），把占多的类型作为其处理类型。例如如果数字占多，那么其它含有字母等文本的数据项就会置空；相反如果文本居多，纯数字的数据项就会被置空。 　　现具体分析在第1节程序代码Extended Properties项中的HDR和IMEX所代表的含义。HDR用来设置是否将Excel表中第一行作为字段名，“YES”代表是，“NO”代表不是即也为数据内容；IMEX是用来告诉驱动程序使用Excel文件的模式，其值有0、1、2三种，分别代表导出、导入、混合模式解决方法 　　从以上的分析中可以得知，当某列数据中含有混合类型时，在.NET中使用Microsoft.Jet.OLEDB.4.0来读取Excel文件造成数据丢失是不可避免的，要解决这个问题只能考虑采用其它数据读取方法。 　　在.NET中读取Excel文件的另外一种方法是回到使用传统COM组件，这种方法在很多技术文章或论文中都有涉及，本文不作赘述。需要指出的是，使用COM组件来读取Excel文件数据的效率较低，在作释放的时候有可能碰到不可预知的错误，特别开发Web应用的程序应该慎重使用。  本文提出另外一种利用读取CSV纯文本格式解决此问题的方法。 　　

（1）在读取Excel的.xls类型的文本数据之前，先将其转换为.csv格式，在Excel中直接另存为这种格式就可以达到转换的目的。CSV文件又称为逗号分隔的文件，是一种纯文本文件，它以“,”分隔数据列，本文表1的数据表用CSV格式存储后用纯文本编辑器打开的表现形式如表3所示。 　　表3 采用CSV格式保存的表1数据 　　需要指出的是，CSV文件也可以用Ole DB或ODBC的方式读取，但是如果采用这些方式读取其数据又会回到丢失数据的老路上，ISAM机制同样会发挥作用。 　　

（2）采用普通的读取文本文件的方法打开文件，读取第一行，用“,”作为分隔符获得各字段名，在DataTable中创建对应的各字段，字段的类型可以统一创建成“String”。