OLDB读取excel的数据类型不配合的解决方案(ZT)

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开发的情况,本论文的解析以及所提供的主意会参考。

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、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驱动之这种机制还是于这排的数量变成空。

相关文章