OLEDB方式读取Excel丢失数据、字符串截断的原因和解决方法

 

1 引言
　　在应用程序的设计中，经常需要读取Excel数据或将Excel数据导入转换到其他数据载体中，例如将Excel数据通过应用程序导入SQL Sever等数据库中以备使用。笔者在开发“汽车产业链ASP协同商务平台”中遇到了类似需求。某汽车整车生产企业需要将其车辆发车信息发布到汽车产业链平台上去，其数据为内部ERP系统生成的Excel数据表，用户首先将该数据表上传至汽车产业链平台，平台将此Excel数据读取导入到平台内部的SQL Sever数据库中，以供其它应用使用。汽车产业链平台的开发使用的开发工具为VS.NET，使用的语言是C#，在开发的过程中发现使用Microsoft.Jet.OLEDB.4.0读取数据会出现当某一字段内分别含有文本和数字的混合数据时，某一类型的数据会产生丢失。本文就对此问题产生的根源进行了分析并给出了相应的解决方法。

2 问题描述
　　Excel是Microsoft公司的电子表格处理软件，在现代办公及企业信息化的应用中使用非常广泛，正因如此，在程序设计中我们经常要通过访问Excel文件来获得数据，但Excel文件不是标准数据库[1]。
　　ASP.NET也是Microsoft公司的产品，作为.NET FrameWork框架中的一个重要组成部分，其主要用于Web设计。我们在.NET中访问读取Excel数据时一般采用Microsoft.Jet.OLEDB.4.0[2]。现以读取一个Excel文件auto.xls中sheet1工作表为例，工作表的内容如表1所示。
　　表1 sheet1表的数据内容
　　现将该表的数据内容读取并显示到到DataGrid中，简化的代码如下：
　　

C#代码  

1. String ConnStr = " Provider = Microsoft.Jet.OLEDB.4.0; DataSource=f:/test.xls;Extended Properties='Excel 8.0;HDR=YES';";   
2. 　　OleDbConnection Conn=new OleDbConnection(ConnStr);   
3. 　　Conn.Open();   
4. 　　string SQL="select * from [sheet1$]";   
5. 　　OleDbDataAdapter da=new OleDbDataAdapter(SQL,ConnStr);   
6. 　　DataSet ds=new DataSet();   
7. 　　da.Fill(ds);   
8. 　　DataGrid1.DataSource=ds;   
9. 　　DataGrid1.DataBind();   
10. 　　Conn.Close();   

String ConnStr = " Provider = Microsoft.Jet.OLEDB.4.0; DataSource=f:/test.xls;Extended Properties='Excel 8.0;HDR=YES';"; 
　　OleDbConnection Conn=new OleDbConnection(ConnStr); 
　　Conn.Open(); 
　　string SQL="select * from [sheet1$]"; 
　　OleDbDataAdapter da=new OleDbDataAdapter(SQL,ConnStr); 
　　DataSet ds=new DataSet(); 
　　da.Fill(ds); 
　　DataGrid1.DataSource=ds; 
　　DataGrid1.DataBind(); 
　　Conn.Close(); 

 　　但是运行以上代码的结果并不是期望的，它将显示为表2所示的内容。可以发现第一个字段中为“1042”的两个数据项变为空。
　　表2 DataGrid1所显示的数据内容
　　有程序设计人员将以上代码OleDbConnection连接字符串中的Extended Properties一项作了如下改动，Extended Properties='Excel 8.0;HDR=NO;IMEX=1’，认为可以解决此问题。由于在开发“汽车产业链协同商务平台”中碰到过类似问题，作了大量的测试后发现，添加IMEX=1后并未实质上解决此问题。表现为：如果某字段前8条记录中全部为纯数字的话，那么在该字段随后的记录中含有字母或汉字的项将仍然变为空，但是如果该字段前8条记录中有一条不为纯数字，将能得到预期想要的结果。

 

3 问题分析
　　产生这种问题的根源与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三种，分别代表导出、导入、混合模式。当我们设置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驱动的这种机制还是让这列的数据变成空。

4 解决方法
　　从以上的分析中可以得知，当某列数据中含有混合类型时，在.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”。
　　
本文原文
　　（3）逐行读取数据行， 用“,”作为分隔符获得某行各列的数据并填入DataTable相应的字段中。
　　实现的简化代码如下：
　　

C#代码  

1. String line;   
2. 　　String [] split = null;   
3. 　　DataTable table=new DataTable("auto");   
4. 　　DataRow row=null;   
5. 　　StreamReader sr=new StreamReader("c:/auto.csv",System.Text.Encoding.Default);   
6. 　　//创建与数据源对应的数据列   
7. 　　line = sr.ReadLine();   
8. 　　split=line.Split(',');   
9. 　　foreach(String colname in split){   
10. 　　table.Columns.Add(colname,System.Type.GetType("System.String")); }   
11. 　　//将数据填入数据表   
12. 　　int j=0;   
13. 　　while((line=sr.ReadLine())!=null){   
14. 　　 j=0;   
15. 　　 row = table.NewRow();   
16. 　　 split=line.Split(',');   
17. 　　 foreach(String colname in split){   
18. 　　 row[j]=colname;   
19. 　　 j++;}   
20. 　　 table.Rows.Add(row);}   
21. 　　 sr.Close();   
22. 　　//显示数据   
23. 　　dataGrid1.DataSource=table.DefaultView;   
24. 　　dataGrid1.DataBind();   

String line; 
　　String [] split = null; 
　　DataTable table=new DataTable("auto"); 
　　DataRow row=null; 
　　StreamReader sr=new StreamReader("c:/auto.csv",System.Text.Encoding.Default); 
　　//创建与数据源对应的数据列 
　　line = sr.ReadLine(); 
　　split=line.Split(','); 
　　foreach(String colname in split){ 
　　table.Columns.Add(colname,System.Type.GetType("System.String")); } 
　　//将数据填入数据表 
　　int j=0; 
　　while((line=sr.ReadLine())!=null){ 
　　 j=0; 
　　 row = table.NewRow(); 
　　 split=line.Split(','); 
　　 foreach(String colname in split){ 
　　 row[j]=colname; 
　　 j++;} 
　　 table.Rows.Add(row);} 
　　 sr.Close(); 
　　//显示数据 
　　dataGrid1.DataSource=table.DefaultView; 
　　dataGrid1.DataBind(); 

 

 

说明：按照上面这篇文章分析当某列数据中含有混合类型时，在.NET中使用Microsoft.Jet.OLEDB.4.0来读取Excel文件造成数据丢失是不可避免的

当IMEX＝1与注册表值TypeGuessRows配合使用，TypeGuessRows 值决定了ISAM 驱动程序从前几条数据采样确定数据类型，默认为“8”。可以通过修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的该注册表值来更改采样行数。但是这种改进还是没有根本上解决问题，即使我们把IMEX设为“1”， TypeGuessRows设得再大，例如1000，假设数据表有1001行，某列前1000行全为纯数字，该列的第1001行又是一个文本，ISAM驱动的这种机制还是让这列的数据变成空。

 

经过测试原文的对这个解决方法的分析有误。

当修改TypeGuessRows 值为0时可以彻底解决这个问题

估计TypeGuessRows =0，程序就会默认行数为最大