欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

数据处理之以OLEDB方式读取Excel数据丢失的原因及解决方法

程序员文章站 2022-03-20 14:38:03
在应用程序的设计中,经常需要读取Excel数据或将Excel数据导入转换到其他数据载体中,但是通过OLEDB方式读取时,由于Excel ISAM驱动程序的限制,偶尔会出现部分数据丢失的情况,本文用于介绍丢失的原因及解决方法。 ......

1.引言

在应用程序的设计中,经常需要读取excel数据或将excel数据导入转换到其他数据载体中,c#读取excel的方式有两种,一种是通过oledb方式读取,另一种为通过com组件方式读取。近段时间有客户反映,读取到程序中的excel表,出现部分数据丢失的情况,笔者在此采用的是第一种方式读取(第二种读取比较慢,且不易控制),于是检查代码还有各种找资料,终于解决了该问题,在此记录导致丢失的原因及解决方法。

2.丢失原因

问题的根源与excel isam(indexed sequential access method,即索引顺序存取方法)驱动程序的限制有关,excel isam 驱动程序通过检查前几行中实际值确定一个 excel 列的类型,然后选择能够代表其样本中大部分值的数据类型。也即excel isam查找某列前几行(默认情况下是8行),把占多的类型作为其处理类型。例如如果数字占多,那么其它含有字母等文本的数据项就会置空;相反如果文本居多,纯数字的数据项就会被置空。 

3.解决方式

(1)oledb连接字串

若excel为excel997-2003版本(后缀为“.xls”),读取的驱动为jet,连接语句如下:

"provider=microsoft.jet.oledb.4.0;data source={0};extended properties='excel 8.0;hdr={1};imex={2}'"

 若excel为excel 2007及之后版本(后缀为“.xlsx”),读取的驱动为ace,连接语句如下:

“provider=microsoft.ace.oledb.12.0;data source={0};extended properties='excel 12.0;hdr={1};imex={2}'”

其中:

当 imex=0 时为“汇出模式”,这个模式开启的 excel 档案只能用来做“写入”用途;

当 imex=1 时为“汇入模式”,这个模式开启的 excel 档案只能用来做“读取”用途;

当 imex=2 时为“链接模式”,这个模式开启的 excel 档案可同时支援“读取”与“写入”用途;

当 hdr=yes,这代表第一行是标题;

当 hdr=no,第一行作为数据内容。

(2)解决一:修改源数据

当我们设置imex=1时将强制混合数据转换为文本,但仅仅这种设置并不可靠,imex=1只确保在某列前8行数据至少有一个是文本项的时候才起作用,它只是把查找前8行数据中数据类型占优选择的行为作了略微的改变。例如某列前8行数据全为纯数字,那么它仍然以数字类型作为该列的数据类型,随后行里的含有文本的数据仍然变空。 

(3)解决二:改动注册表

设置imex=1,修改注册表值typeguessrows(typeguessrows 值决定了isam 驱动程序从前几条数据采样确定数据类型,默认为“8”)为0,程序就会默认行数为最大。

对于修改注册表不熟悉的读者,具体步骤如下:

开始菜单,输入“regedit”,打开注册表,找到“hkey_local_machine\software\microsoft\”项,按下“ctrl+f”键,输入“typeguessrows”选择“值”项,如下图所示。

数据处理之以OLEDB方式读取Excel数据丢失的原因及解决方法

点击【查找下一个】按钮,查到找结果如下图所示,笔者office版本为office 2016 64bit。

数据处理之以OLEDB方式读取Excel数据丢失的原因及解决方法

右键该项,修改“typeguessrows”的值为“0”即可,如下图所示。

数据处理之以OLEDB方式读取Excel数据丢失的原因及解决方法

 

4.c#读取excel代码

在此贴出c#读取excel表到datatable的方法代码:

public static datatable getexceltablebyoledb(string excelpath, string tablename)
        {
            try
            {
                datatable exceltable = new datatable();     //数据表  
                dataset ds = new dataset();     //获取文件扩展名    //excel的连接
                oledbconnection objconn = new oledbconnection("provider=microsoft.ace.oledb.12.0;data source=" + excelpath + ";extended properties='excel 12.0;hdr=yes;imex=1;'");
                if (objconn == null)
                {
                    return null;
                }
                objconn.open();
                string strsql = "select * from [" + tablename + "]";//获取excel指定sheet表中的信息
                oledbdataadapter mydata = new oledbdataadapter(strsql, objconn);
                mydata.fill(ds, tablename);//填充数据     
                objconn.close();      //dtexcel即为excel文件中指定表中存储的信息 
                exceltable = ds.tables[tablename];
                return exceltable;
            }
            catch
            {
                return null;
            }
        }

5.总结

至此,不完美的解决了该问题,因为两种方法各有优缺点,受制于框架,这是无法避免的,后边有时间会写一篇通过开源库npoi读取及创建excel(不需要安装office),以此彻底解决限制问题。如果该篇博文对你有帮助,希望点个关注支持下。