最近在做一个导入Excel数据到数据库表中对应字段的功能,一开始做的时候,也没有什么困难,做的还比较顺利,但是在接下去测试过程中,却遇到了一些很棘手的问题,疯狂的试验,在网上不停的搜索,有不少人也有相同的问题,但却没有一个把问题解决清楚了,即然自己解决了,就把解决问题的过程和原因记下来,与大家一同分享.
导入EXCEL的思路来自己客户的需求,客户需要从EXCEL中导入一些基础资料,来初始化系统中一些基础数据,而这些基础数据表中每个表都与几个不同的表相关联,即要保证数据的有效性,又要把相关性也导入,还真是把我们难倒了.如果不用程序导入,基础资料怎么导入呢,反过来一想,那就用SQL的方式导入方式吧.
首先不管EXCEL表中的数据是什么样的,创建一个临时用的物理表,把EXCEL中的数据导入;然后用SQL语句对这个临时表和相关的表进行SQL语句开发,这样确实简单灵活了,以后客户再有什么其它变态需求时,大不了给他写一个存储过程,要多复杂就可以多复杂,一切问题解决,真爽,呵呵.
做完后测试发现几个棘手的问题.
问题:数据驱动问题,Excel中有一些列是数值,虽然把这个列改为文本存储方式或其它方式,用这种方式读取就是读取不到这些列的数据(string strCon = @" Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;";),真是奇怪了.试验过修改EXCEL存储方式,格式,然后修改连接串,最后发现原因是数据驱动有问题,改为string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + Path + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'"; 就正确了,这里需要大家注意的一个问题'Excel 8.0;HDR=NO;IMEX=1',与Excel 8.0;HDR=NO;IMEX=1的区别,没有'号会一直出现找不到可安装的 ISAM错误!
参数说明:
Microsoft Jet 提供程序用于连接到 Excel 工作簿。在以下连接字符串中，Extended Properties 关键
字设置 Excel 特定的属性。“HDR=Yes;”指示第一行中包含列名，而不是数据，“IMEX=1;”通知驱动程
序始终将“互混”数据列作为文本读取。注意
http://msdn2.microsoft.com/zh-cn/library/ms254978.aspx
默认情况下，系统认为 Excel 数据源的第一行包含可用作字段名的列标题。如果不是这种情况，则必须
将该设置关闭，否则，第一行数据将会“消失”，而被用作字段名称。这可通过向连接字符串的扩展属性
添加可选的 HDR= 设置来完成。默认情况下（无需指定）是 HDR=Yes。如果没有列标题，则需要指定
HDR=No；提供程序将字段命名为 F1、F2 等等。因为扩展属性字符串现在包含了多个值，所以必须用引号
单独包起来，如下例所示（为便于看清楚，添加了额外的空格）。
如第1个中hdr=no的话，where时就会报错
ConnStr ="Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\Inetpub\wwwroot\test.xls;
Extended Properties='Excel 8.0;HDR=No;IMEX=1'"
Extended Properties参数属性这样写 Excel 5.0;HDR=YES;IMEX=1
IMEX=1就是指混合型转换为文本
select * from
OPENROWSET(’MICROSOFT.JET.OLEDB.4.0′
,’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\book1.xls’,[sheet1$])
1)hdr=yes时可以把xls的第1行作为字段看待，如第1个中hdr=no的话，where时就会报错
2)[]和美圆$必须要，否则M$可不认这个账
希望对做导入EXCEL功能的人有所帮助!