使用adonet读取excel的2个函数
上一篇 / 下一篇 2011-10-18 14:46:51 / 个人分类:VBScript
'获取某单元格数据51Testing软件测试网-WEv1L;Z$i(Q {?
Function get_excel_value(rowindex,cellindex)
6^a(mzqq0On Error Resume NEXT
1B?v/F9o0Dim myarray()
XN/WpB8p m)g2Y4J0Set con=createobject("adodb.connection")51Testing软件测试网\1RP5L&K+l
con.open conset51Testing软件测试网r#H)BA!met`&m
Set rs=createobject("adodb.recordset")
&t3__2[$wyH0sql="select count(*) from [Sheet1$]"51Testing软件测试网 k.K1\N)l
rs.open sql,con
F d qSC?,[~O0rows=rs.Fields(0)
M-R:b$H3A$|5[0rs.Close51Testing软件测试网M'^:y&^.g0x
sql="select * from [Sheet1$]"
{M6A c[4d)n0rs.open sql,con
4`*[a@'{.I0If rowindex >rows or cellindex>rs.Fields.Count Then
|z5g4ll |O Td6N\2g,z051Testing软件测试网@WR$MY A
‘MsgBox "该单元格无数据"
!@:_ mz$Xo#h0get_excel_value =-1
-nV @u)I!?0Exit function51Testing软件测试网 C;w7zj|
Else
1F3j YA z*v0rs.Move rowindex-1
ACg5um$E8y$s0get_excel_value =rs.Fields(cellindex-1).Value51Testing软件测试网K(_T*De%m1b5J
TD7Z,]`R0
End If51Testing软件测试网 U(x#ez(t0o&|
con.Close
]~(Y8d1NXk*zJ0Set con=Nothing51Testing软件测试网rpn/fW+{ h
On Error Goto 051Testing软件测试网6snHz.}OF
End Function
@9dn dw,oW0'get_excel_cell:获取excel某列的数据,返回数组
:n G!c.QV w:RG&i0'cellindex:获取列的序号或列名51Testing软件测试网M"Y1xxP;r'n;A
Function get_excel_cell(cellindex)51Testing软件测试网n,q/S@+O3jn(l
On Error Resume NEXT
PG6PZ{'?,zO){0Dim myarray()
6[z3j0P5IV5O0Set con=createobject("adodb.connection")51Testing软件测试网"snZ~+|9zAi;M
conset="DSN=test;DBQ=D:\test\test.xls;DefaultDir=D:\test;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;"
{Z)X8LY0con.open conset51Testing软件测试网xw?W7jQ:j:]{N
Set rs=createobject("adodb.recordset")
U[u"\A k$\U0sql="Select count(*) from [Sheet1$]"51Testing软件测试网:c G@+x.{TfE%B
rs.Open sql,con
My ~(m6fH8I0'记录的总行数51Testing软件测试网%a_:cF-B6L
rows=rs.Fields(0).Value51Testing软件测试网3Ea3N"J,mhc3j.vM0G
rs.Close
-Bc2A3K@ b f[ b{0sql="select * from [Sheet1$]"
M;A3g5c V/F+e0rs.open sql,con51Testing软件测试网N iI'ZpGnXM'v
ReDim myarray(rows-1)
X Y:N"~}+Jv-mWp s0For i=0 To rows-151Testing软件测试网!R']Xy bS0R^
'将i行cellindex列的值赋值给数组51Testing软件测试网Kj l5X#K]
myarray(i)=rs.Fields(cellindex).Value51Testing软件测试网)_*eSr*I
rs.MoveNext51Testing软件测试网4p0WOB)n6vYNBm
next
&_-O(XS6xe0get_excel_cell=myarray51Testing软件测试网D0D@6xW[ W"x
con.Close51Testing软件测试网-e o#QNz1V)uka
Set con=Nothing51Testing软件测试网sa/? sR q/b
On Error Goto 0
:m/D:N^P0m7Ab0End Function