OLDB读取excel的数据类型不般配的消除方案(ZT)

1
引言 
  在应用程序的统一筹划中,平日须要读取Excel数据或将Excel数据导入转换成别的数据载体中,例如将Excel数据通过应用程序导入SQL
Sever等数据库中以备使用。作者在支付“小车产业链ASP协同商务平台”中相遇了类似供给。某小车整车生产公司急需将其车辆发车消息发布到小车产业链平台上去,其数量为内部ELX570P系统生成的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]。现以读取3个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;HDLX570=NO;IMEX=1’,认为能够缓解此难点。由于在开发“汽车产业链联手商务平台”中遇见过类似题材,作了大气的测试后发现,添加IMEX=1后尚未实质上缓解此题材。表现为:若是某字段前8条记下中全部为纯数字来说,那么在该字段随后的记录中包括字母或汉字的项将依然变为空,不过一旦该字段前8条记下中有一条不为纯数字,将能收获预期想要的结果。 
   
3
难题浅析 
  发生那种难题的来源于与Excel ISAM[3](Indexed Sequential Access
Method,即索引顺序存取方法)驱动程序的限制有关,Excel ISAM
驱动程序通过检查前几行中实际值分明三个 Excel
列的系列,然后采取能够代表其样本中山大学部分分值的数据类型[4]。也即Excel
ISAM查找某列前几行(私下认可情况下是8行),把占多的花色作为其拍卖项目。例如假如数字占多,那么别的富含字母等公事的多少项就会置空;相反假设文本居多,纯数字的数据项就会被置空。 
  现具体分析在第贰节程序代码Extended
Properties项中的HD景逸SUV和IMEX所代表的意义。HDOdyssey用来安装是或不是将Excel表中率先行作为字段名,“YES”代表是,“NO”代表不是即也为数量内容;IMEX是用来告诉驱动程序使用Excel文件的格局,其值有0、壹 、2三种,分别表示导出、导入、混合方式。当大家设置IMEX=1时将强制混合数据转换为文本,但单纯那种装置并不牢靠,IMEX=2头保险在某列前8行数据至少有2个是文本项的时候才起功用,它只是把查找前8行数据中数据类型占优选用的作为作了略微的更动。例如某列前8行数据全为纯数字,那么它照旧以数字类型作为该列的数据类型,随后行里的带有文本的数目还是变空。 
  另一个修正的格局是IMEX=1与注册表值TypeGuessRows合营使用,TypeGuessRows
值决定了ISAM
驱动程序在此以前几条数据采集样品鲜明数据类型,暗中同意为“8”。能够经过改动“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的该注册表值来改变采集样品行数。不过那种创新要么尚未一直上缓解难题,就算我们把IMEX设为“1”,
TypeGuessRows设得再大,例如一千,倘若数据表有1001行,某列前一千行全为纯数字,该列的第⑦01行又是贰个文本,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文件也能够用OleDB或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开发的动静,本随想的辨析和所提供的章程能够参考。

OLEDB 连接EXCEL的接连字符串 IMEX的标题

       今日遇上四个标题亟需想EXCEL表中写多少,折腾了漫漫才发觉是IMEX惹得祸,所以记录下提醒自身,也希望大家不要出一致的错。 

境遇难题:使用语句 “insert into [Sheet1$] (大类) values (‘test’)”
不能够插入 。 

原因:Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=’2008-08.xls’; Extended Properties=’Excel 8.0;HDR=Yes;IMEX=1′ 

解决措施:
去掉IMEX=1 

补充: 
       向EXCEL插入数据时 数据类型是由前8行数据中数据类型占优选用例如:分数一列前前8行为空值 插入5为字符串格式,假使前8作为数字格式
插入5为数字格式关于IMEX的材质: 

       IMEX是用来报告驱动程序使用Excel文件的格局,其值有0、一 、2三种,分别代表导出、导入、混合方式。当大家设置IMEX=1时将威吓混合数据转换为文本,但单纯那种设置并不可信,IMEX=1头保证在某列前8行数据至少有2个是文本项的时候才起效果,它只是把查找前8行数据中数据类型占优选拔的行为作了略微的变更。例如某列前8行数据全为纯数字,那么它依然以数字类型作为该列的数据类型,随后行里的隐含文本的多少仍然变空。 

  另贰个创新的形式是IMEX=1与注册表值TypeGuessRows合作使用,TypeGuessRows
值决定了ISAM
驱动程序在此从前几条数据采集样品鲜明数据类型,暗中认可为“8”。能够因此改动“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft
\Jet\4.0\Engines\C++,Excel”下的该注册表值来改变采集样品行数。可是那种立异要么没有平素上消除难点,即便大家把IMEX设为“1”,
TypeGuessRows设得再大,例如一千,借使数据表有1001行,某列前一千行全为纯数字,该列的第⑩01行又是3个文件,ISAM驱动的那种体制照旧让那列的数码变成空。