解决ADO读取Excel，数据丢失、数据错误、数据乱码问题

最新推荐文章于 2021-08-08 11:14:06 发布

iteye_17686

最新推荐文章于 2021-08-08 11:14:06 发布

阅读量363

点赞数

文章标签：数据库

在进行自动化测试的脚本开发或者框架开发的过程中，经常会使用到excel操作技术，对excel的操作，主要有两个方法：1）通过CreateObject("Excel.Application")的方式

2）通过ADO访问excel数据源，这篇文章主要是描述一个ADO操作excel时，遇到数据丢失，数据错误或数据乱码的一个问题。

一个简单的使用ADO函数：

' *********************************************************************************************
' 函数说明：通过传入的文件路径、名称、字段列、表单名以及查询条件取得Excel表格中的数据；
' 参数说明：
' (1)strFilePath:文件路径及文件名;
' (2)strFields:要查找的字段列，符合基本SQL语法字段;
' (3)strSheetName:表单名;
' (4)strWhere:查询条件，不需要带SQL中的Where关键字;
' 返回结果：
' (1)返回ADODB.RECORDSET对象
' 调用方法:
' res= Xls_ADOGetDataFromSheet( strFilePath,strFields,strSheetName,strWhere)
' *********************************************************************************************
Function Xls_ADOGetDataFromSheet(strFilePath,strFields,strSheetName,strWhere)
Dim conn
Dim Rs
Dim strSQL
If strWhere<>"" Then
strWhere =" Where " & strWhere
else
strWhere =""
End If
strSQL = "select " & strFields & " From [" & strSheetName & "$] " & strWhere
Set conn = Xls_OpenADOConnectionOfExcel(strFilePath)
Set Rs = Xls_ADOExecuteSql(strSQL,conn)
'msgbox(Rs.RecordCount)
Set Xls_ADOGetDataFromSheet = Rs
End Function

在调用此函数传入一个SQL语句读取excel中的内容时：

本人遇到的一个错误描述：

读取Excel表格后，会把数据传到另一个recordset再进行处理的。在给另一个recordset付值时出错。根据错误信息，得知如果把一个超长的或者错误的数据付给一个字段时会出现这样的错误。但在recordset中，我们设定了对于的字段的长度是1000的，而要赋值的数据长度是628，为什么还会出错呢。这个字段出来的值后面也带有很多的乱码。有可能，从Excel中读出来的数据已经是乱码了。

把怀疑产生乱码的所有空格去掉，结果错误到了下一行。再把正常的数据复制变成很长，结果也会产生错误。再尝试把前面的正常行去掉，居然错误就不见了。
把断点设在错误的那行，查数据的长度，是628,再看rs.fileds(11).defineSize, 发现只有255. 那就说明，它实际字段是认为长255的，但把数据都读出来了，结果是乱码，再把这些乱码付值给另一个recordset时才报错。
把产生错误的数据放在第一行时，是不会有这个错误的，查看它的defineSize, 发现不再是255了，而是很大的数字。
再尝试把错误的数据放在1－8行，都不会产生问题，在9行以上就会产生。

原来，ODBC在读取Excel时，会有个值设置TypeGuessRows=8，意思是ODBC会先在前8行的数据中去比较，如果所有的数据都在255或以下，那这个字段长度就是varchar(255),否则，这个字段将是text类型。

于是，我们加上TypeGuessRow=100，再试，结果错误依然存在。

原来，虽然可以设置这个属性，但ODBC是不会去用它的，要使这个属性有效，必须修改注册表。对于注册表键是

对于excel2000以上和以下的版本，在注册表中有分别的设置

•

Excel 97

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

Excel 2000 and later versions

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

修改了里面的TypeGuessRow后，这个问题暂时解决（修改完注册表立即生效，不需要重启）。

但是，如果大的数据在表的后面位置的话，这个问题还是有可能发生的。所以要彻底的解决的话，在打开一个recordset前，就要先定义好它的字段长度了。

希望大家在应用ADO操作Excel的时候要特别注意这个知识点，使用CreateObject("Excel.Application")的方式读取excel都是正常的，目前尚未出现过这类问题。