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

-Eg Ly.Q0在进行自动化测试的脚本开发或者框架开发的过程中，经常会使用到excel操作技术，对excel的操作，主要有两个方法：1）通过CreateObject("Excel.Application")的方式51Testing软件测试网y+Q1nnk(g2j-W

U8E\)d~Rt02）通过ADO访问excel数据源，这篇文章主要是描述一个ADO操作excel时，遇到数据丢失，数据错误或数据乱码的一个问题。51Testing软件测试网Jdc:S| ]ci{4t*W
Z;o|

/_/YI
fxHQX0一个简单的使用ADO函数：51Testing软件测试网2x,g7] hK+MT"]kH

' *********************************************************************************************51Testing软件测试网zh`%~,k)^8WJ;n
' 函数说明：通过传入的文件路径、名称、字段列、表单名以及查询条件取得Excel表格中的数据；
Yj~p3`/~E0' 参数说明：
9StHYf&P%H0'          (1)strFilePath:文件路径及文件名;
!D&XbiUOn+s0'          (2)strFields:要查找的字段列，符合基本SQL语法字段;
B!T9R~9S0'          (3)strSheetName:表单名;51Testing软件测试网~SR~6ql{l
'          (4)strWhere:查询条件，不需要带SQL中的Where关键字;
#Od{Y,a,U)?0' 返回结果：51Testing软件测试网/K?#e~j_Bv
'          (1)返回ADODB.RECORDSET对象
J-eS,vQ0' 调用方法:
y3i(_lE0'           res= Xls_ADOGetDataFromSheet( strFilePath,strFields,strSheetName,strWhere)51Testing软件测试网 gc%HZ4X;cwB!y
' *********************************************************************************************
0Ll
bXV1S0Function Xls_ADOGetDataFromSheet(strFilePath,strFields,strSheetName,strWhere)51Testing软件测试网lv:PQLU
 Dim conn
zP6U)G@[p
@0 Dim Rs
t(b_Dbh2V0 Dim strSQL51Testing软件测试网:xp&KHx,[$?)Yi
 If strWhere<>"" Then51Testing软件测试网m/^_YOG7~P4q
  strWhere =" Where " & strWhere51Testing软件测试网p0m)f\8fa
  else
*ai|8k N(m6jML/{o0  strWhere =""51Testing软件测试网f-obe9JA
 End If
uj,J8s)p#P*c0 strSQL = "select " & strFields & " From [" & strSheetName & "$] " & strWhere
;d'G3?/s^n,W4L[0 Set conn = Xls_OpenADOConnectionOfExcel(strFilePath)51Testing软件测试网_C*G+F;Q^ X9M.M%d
 Set Rs = Xls_ADOExecuteSql(strSQL,conn)51Testing软件测试网s] W5w9y&M
 'msgbox(Rs.RecordCount)
/a dzA4wF$?4Hr0 Set Xls_ADOGetDataFromSheet = Rs51Testing软件测试网9KzO+T_*r
End Function

在调用此函数传入一个SQL语句读取excel中的内容时：51Testing软件测试网VWm*q VP-_'C+gs

+Tj-UR\Q'qYK0本人遇到的一个错误描述：51Testing软件测试网 c0p:GodZ$S

7DX@Ln{?DO0读取Excel表格后，会把数据传到另一个recordset再进行处理的。在给另一个recordset付值时出错。根据错误信息，得知如果把一个超长的或者错误的数据付给一个字段时会出现这样的错误。但在recordset中，我们设定了对于的字段的长度是1000的，而要赋值的数据长度是628，为什么还会出错呢。这个字段出来的值后面也带有很多的乱码。有可能，从Excel中读出来的数据已经是乱码了。
KQ f$lv4r2{*Q0
!}7n\%Q[8MV0w0      把怀疑产生乱码的所有空格去掉，结果错误到了下一行。再把正常的数据复制变成很长，结果也会产生错误。再尝试把前面的正常行去掉，居然错误就不见了。51Testing软件测试网a [i\4z~9Ng$}
        把断点设在错误的那行，查数据的长度，是628, 再看rs.fileds(11).defineSize, 发现只有255. 那就说明，它实际字段是认为长255的，但把数据都读出来了，结果是乱码，再把这些乱码付值给另一个recordset时才报错。
$j
^9Gx(l0        把产生错误的数据放在第一行时，是不会有这个错误的，查看它的defineSize, 发现不再是255了，而是很大的数字。51Testing软件测试网_$mzlP[n?$X
        再尝试把错误的数据放在1－8行，都不会产生问题，在9行以上就会产生。51Testing软件测试网R^Xo{ Hw/nU

'O[ R1VE*l0        原来，ODBC在读取Excel时，会有个值设置TypeGuessRows=8， 意思是ODBC会先在前8行的数据中去比较，如果所有的数据都在255或以下，那这个字段长度就是varchar(255),否则，这个字段将是text类型。51Testing软件测试网Oy t;lIud)~E

q~y2PUiC,L D5y0      于是，我们加上TypeGuessRow=100，再试，结果错误依然存在。
3Sq7zfx051Testing软件测试网;xbZ5pc!x%M;n
      原来，虽然可以设置这个属性，但ODBC是不会去用它的，要使这个属性有效，必须修改注册表。对于注册表键是51Testing软件测试网6sx;aW#Y,Aj,nC BoGC

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

Or/P{ sLf0

?YHq:Y$s O/`1q051Testing软件测试网? DQP;^(@o Q g
J
修改了里面的TypeGuessRow后，这个问题暂时解决（修改完注册表立即生效，不需要重启）。
`"p;{X]b051Testing软件测试网 tV.W"aE Xs
Z
但是，如果大的数据在表的后面位置的话，这个问题还是有可能发生的。所以要彻底的解决的话，在打开一个recordset前，就要先定义好它的字段长度了。

希望大家在应用ADO操作Excel的时候要特别注意这个知识点，使用CreateObject("Excel.Application")的方式读取excel都是正常的，目前尚未出现过这类问题。
4UOa'ox~ D"]8l051Testing软件测试网yvlU5rl