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=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相应的字段中。
实现的简化代码如下:
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,程序就会默认行数为最大
分享到:
相关推荐
汽车产业链平台的开发使用的开发工具为VS.NET,使用的语言是C#,在开发的过程中发现使用Microsoft.Jet.OLEDB.4.0读取数据会出现当某一字段内分别含有文本和数字的混合数据时,某一类型的数据会产生丢失。本文就对此...
C# WinFORM 窗体小程序。采用oledb技术读取EXCEL表格内容,并使用DataGridView控件进行展示,含全部源代码,供有需要的人士下载学习使用。
以ole方式读取excel文件 ,以ole方式读取excel文件 ,以ole方式读取excel文件
C# 示例源码 快速读取Excel,把Excel当成数据来源(Microsoft.Jet.OLEDB.4.0),可读取多张表,
* 4、对注册表的操作,解决读取Excel表格数据位数的限制【OperateRegedit】。 * 5、删除磁盘上指定的文件【ClearExcelFile】。 * 6、根据传入的Datatable数据源,生成Excel数据表...
C++读取Excel数据 ole 模式 VS2003 Excel 2003
使用oledb读写excel出使用oledb读写excel出使用oledb读写excel出使用oledb读写excel出
解决用字符串连接数据库测试时提示指定的初始化字符串不符合OLEDB规定
功能描述: ...2、选取Excel中某个sheet中的某几列读入到Dataset里面; 3、从Dataset导出到Excel表格 注:此代码运行是需有office组建作为支持,即运行该程序的电脑上必须安装office软件;程序由VS2012开发。
oledb连接字符串生成器(Web中开发中直接配置),通过JavaScript调用,在其他应用程序中也可以参考。
C#操作Excel(OLEDB)的实例,你可以参考这个代码发挥一下,写出属于自己的作品
本文实例讲述了C#使用oledb读取excel表格内容到datatable的方法。分享给大家供大家参考。具体分析如下: 首先看一段实例代码 代码如下:string strCon = @”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + ...
【Microsoft.ACE.OLEDB.12.0-提供程序】本资源是C#程序使用OleDb读取Excel时必备的驱动程序——Microsoft.ACE.OLEDB.12.0的提供程序。OleDb是一个数据库驱动接口,能够通过标准的 SQL 语句访问多种数据库,包括 ...
读取excel内容到数据,所有相关素材。
C# 通过OLEDB向Excel新增和修改记录,不加载Excel类库,方便调用,使用Excel2007
解决microsoft.ace.oledb.12.0连接失败所需要的驱动 完整版.zip 解决microsoft.ace.oledb.12.0连接失败所需要的驱动 完整版.zip 解决microsoft.ace.oledb.12.0连接失败所需要的驱动 完整版.zip 解决microsoft.ace....
根据excel文件的完整存放路径,读取该excel文件所有的sheet的名字,默认的一般是sheet1,sheet2等,也可以自定义名字,得到的是一个DataTable,是sheet的名字集合。
c# 在excel导入的时候报错 Microsoft.ACE.OLEDB.12.0,安装对应系统版本的AccessDatabaseEngine即可解决该问题
两种方式 一种oledb 一种 excel.application excel 转为 datatable
一个自己写的对Excel操作工具,用OleDbConnection连接,主要是查询 新增快,如果数据量大不建议用这个做修改,因为Excel没索引,修改会比较慢,查询和新增还是很快的,主要是操作简单,就是写SQL语句