导入Excel混合数据列数据丢失分析与解决

【转载，原文出处http://www.douban.com/note/18510346/】 
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中，简化的代码如下： 
　　String ConnStr = " Provider = Microsoft.Jet.OLEDB.4.0; DataSource=c:/auto.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相应的字段中。 
　　实现的简化代码如下： 
　　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(); 
　　 
5 结语 
　　在应用程序的设计中，需要访问Excel数据的情况非常普遍，本文以在.NET中对访问含有混合类型数据的Excel表格拟采取的方法进行探讨。当然，如果不存在混合类型的数据使用Microsoft.Jet.OLEDB为较佳方案。对于不是使用.NET开发的情况，本论文的分析和所提供的方法亦可参考。

 

 

 

 

 

摘要：研究了ISAM驱动限制，分析了在.NET中采用Microsoft.Jet.OLEDB读取Excel文件产生数据丢失的原因，给出了解决此问题的方法。 
　　关键词：Excel;数据访问;Ole DB;ISAM 
　　中图分类号：TP 311.1文献标识码：A 文章编号：1009-3044(2007)04-10937-02 
　　 
　　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中，简化的代码如下： 
　　String ConnStr = " Provider = Microsoft.Jet.OLEDB.4.0; DataSource=c:/auto.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相应的字段中。 
　　实现的简化代码如下： 
　　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(); 
　　 
　　5 结语 
　　在应用程序的设计中，需要访问Excel数据的情况非常普遍，本文以在.NET中对访问含有混合类型数据的Excel表格拟采取的方法进行探讨。当然，如果不存在混合类型的数据使用Microsoft.Jet.OLEDB为较佳方案。对于不是使用.NET开发的情况，本论文的分析和所提供的方法亦可参考。 
　　参考文献： 
　　[1]Linuxmine.利用ASP.NET来访问Excel文档[EB/OL]. http://www.linuxmine.com/77726.html,2007-1-22. 
　　[2]刘洪成.C#高级编程[M]. 北京:清华大学出版社,2003. 187-200. 
　　[3]肖正宏,曹元大,韩秋风.数据访问技术——DAO、ADO、RDO的比较[J].电脑与信息技术,2001,(1):31-32. 
　　[4]PBR.Excel使用DAO OpenRecordset NULL作为返回值[EB/OL].http://support.microsoft.com/kb/194124,2004-6-24. 
　　本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。 

评论: 
#1楼 [楼主] 2008-07-16 09:29 | 巍巍边疆      

Ado.Net读取Excel常见问题总结分类:.NET技术点滴 
经常需要在数据库与Execl之间互导数据。net时代，ADO.NET可以使用使用Microsoft.Jet.OleDb访问访问Excel，网上已经有很多类似的资源，最典型也是最简单的可能如下：（asp.net环境） 

// 连接字符串 
string xlsPath = Server.MapPath("~/app_data/somefile.xls"); // 绝对物理路径 
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" 
"Extended Properties=Excel 8.0;" 
"data source=" xlsPath; 
// 查询语句 
string sql = "SELECT * FROM [Sheet1＄]"; 

DataSet ds = new DataSet(); 
OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr); 
da.Fill(ds); // 填充DataSet 

// 在这里对DataSet中的数据进行操作 

// 输出，绑定数据 
GridView1.DataSource = ds.Tables[0]; 
GridView1.DataBind(); 
很简单吧？！一切就像操作数据库一样，只是需要注意的是： 
1。数据提供程序使用Jet，同时需要指定Extended Properties 关键字设置 Excel 特定的属性，不同版本的Excel对应不同的属性值： 用于 Extended Properties 值的有效 Excel 版本。 
对于 Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 工作簿，请使用 Excel 8.0。 

对于 Microsoft Excel 5.0 和 7.0 (95) 工作簿，请使用 Excel 5.0。 

对于 Microsoft Excel 4.0 工作簿，请使用 Excel 4.0。 

对于 Microsoft Excel 3.0 工作簿，请使用 Excel 3.0。 

ref：http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp" target="_new">http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp 

2。数据源路径使用物理绝对路径（同Access） 

3。如何引用表名？ 
对 Excel 工作簿中表（或范围）的有效引用。 
若要引用完全使用的工作表的范围，请指定后面跟有美元符号的工作表名称。例如： 

select * from [Sheet1＄] 
若要引用工作表上的特定地址范围，请指定后面跟有美元符号和该范围的工作表名称。例如： 

select * from [Sheet1＄A1:B10] 
若要引用指定的范围，请使用该范围的名称。例如： 

select * from [MyNamedRange] 
ref：http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp" target="_new">http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp 
说明： 
可以引用Excel 工作簿中的三种对象： 
• 整张工作表：[Sheet1＄] ，Sheet1 就是工作表的名称 
• 工作表上的命名单元格区域：[MyNamedRange] （不需要指定工作表，因为整个xls中命名区域只能唯一）
XLS命名方法：选中单元格范围》插入》名称》定义 
• 工作表上的未命名单元格区域 ：[Sheet1＄A1:B10] 
（在关系数据库提供的各种对象中（表、视图、存储过程等），Excel 数据源仅提供相当于表的对象，它由指定工作簿中的工作表和定义的命名区域组成。命名区域被视为“表”，而工作表被视为“系统表”） 

注意： 
•必须使用[]（方括号），否将报： 
FROM 子句语法错误 
•必须跟＄（美元符号），否则报： 
Microsoft Jet 数据库引擎找不到对象'Sheet2'。请确定对象是否存在，并正确地写出它的名称和路径。 
•如果工作表名称不对，或者不存在，将报： 
'Sheet2＄' 不是一个有效名称。请确认它不包含无效的字符或标点，且名称不太长。 
•在 如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据 中提到可以使用 
~ 和 '(波浪线和单引号）代替[]，使用ADO。NET测试没有成功，报： 
FROM 子句语法错误 
•当引用工作表明名（[Sheet1＄]）时，数据提供程序认为数据表从指定工作表上最左上方的非空单元格开始。比如，工作表从第 3 行，C 列开始，第3行，C列之前以及第1、2行全为空，则只会显示从第3行，C列开始的数据；以最后表最大范围内的非空单元结束； 
•因此，如需要精确读取范围，应该使用命名区域 [NamedRange]，或者指定地址：[Sheet1＄A1:C10] 

4。如何引用列名？ 
•根据默认连接字符串中，数据提供程序会将有效区域内的第一行作为列名，如果此行某单元格为空则用F1、F2表示，其中序数，跟单元格的位置一致，从1开始； 
•如果希望第一行作为数据显示，而非列名，可以在连接串的 Extended Properties 属性指定：HDR=NO 
默认值为：HDR=NO 格式如下： 

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" 
"Extended Properties=""Excel 8.0;HDR=NO"";" 
"data source=" xlsPath; 
注意： Excel 8.0;HDR=NO 需要使用双引号（这里的反斜扛，是C#中的转义） 

ref： ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.chs/WD_ADONET/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm 中 《连接Excel》节（说明：在我自己的MSDN中，它的例子使用了两个双引号是错的，测试没有通过，原文这样说的： 

注意，Extended Properties 所需的双引号必须还要加双引号。 
） 

在这种情况下，所有的列名都是以F开头，然后跟索引，从F1开始，F2，F3。。。。。。。 

5。为什么有效单元格数据不显示出来？ 
出现这种情况的可能原因是，默认连接中，数据提供程序根据前面单元格推断后续单元个的数据类型。 
可以通过 Extended Properties 中指定 IMEX=1 

“IMEX=1;”通知驱动程序始终将“互混”数据列作为文本读取 
ref：同4 

PS：在baidu这个问题的时候，有网友说，将每个单元都加上引号，这固然是格方案，但是工作量何其大啊，又不零活，庆幸自己找到”治本药方“ 

more ref： 
如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据 
http://dotnet.aspx.cc/ShowDetail.aspx? 

注意： 
在网上有很多同志说是加上HDR或IMEX这两个属性值后会报“找不到可安装的ISAM ”的错误。这时解决方法很简单： 
就是把连接串写成如下形式： 

Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=1; IMEX=1;' (注意红色标注的单引号) 

而不要写成这样的形式： 

Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended Properties=Excel 8.0;HDR=1; IMEX=1;