使用adonet读取excel的2个函数

'获取某单元格数据51Testing软件测试网-WEv1L;Z$i(Q {?

Function get_excel_value(rowindex,cellindex)

On Error Resume NEXT

Dim myarray()

Set con=createobject("adodb.connection")51Testing软件测试网\1RP5L&K+l

 conset="DSN=test;DBQ=D:\test\test.xls;DefaultDir=D:\test;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;"

con.open conset51Testing软件测试网r#H)BA!met`&m

Set rs=createobject("adodb.recordset")

sql="select count(*) from [Sheet1$]"51Testing软件测试网 k.K1\N)l

rs.open sql,con

rows=rs.Fields(0)

  rs.Close51Testing软件测试网M'^:y&^.g0x

 sql="select * from [Sheet1$]"

  rs.open sql,con

   If rowindex >rows or cellindex>rs.Fields.Count Then

 51Testing软件测试网@WR$MY A

     ‘MsgBox "该单元格无数据"

     get_excel_value =-1

     Exit function51Testing软件测试网 C;w7zj|

   Else

     rs.Move rowindex-1

   get_excel_value =rs.Fields(cellindex-1).Value51Testing软件测试网K(_T*De%m1b5J

   End If51Testing软件测试网 U(x#e
z(t0o&|

 con.Close

 Set con=Nothing51Testing软件测试网r
pn/fW+{ h

On Error Goto 051Testing软件测试网6snHz.}OF

End Function

 51Testing软件测试网qhLM:}0F0dE

'get_excel_cell:获取excel某列的数据，返回数组

'cellindex:获取列的序号或列名51Testing软件测试网M"Y1xxP;r'n;A

Function get_excel_cell(cellindex)51Testing软件测试网n,q/S@+O3jn(l

On Error Resume NEXT

Dim myarray()

Set con=createobject("adodb.connection")51Testing软件测试网"snZ~+|9zA
i;M

conset="DSN=test;DBQ=D:\test\test.xls;DefaultDir=D:\test;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;"

con.open conset51Testing软件测试网xw?W7jQ:j:]{N

Set rs=createobject("adodb.recordset")

sql="Select count(*) from [Sheet1$]"51Testing软件测试网:cG@+x.{TfE%B

rs.Open sql,con

'记录的总行数51Testing软件测试网%a_:cF-B6L

rows=rs.Fields(0).Value51Testing软件测试网3Ea3N"J,mhc3j.vM0G

rs.Close

 sql="select * from [Sheet1$]"

  rs.open sql,con51Testing软件测试网N iI'ZpGn
XM'v

  ReDim myarray(rows-1)

  For 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

 get_excel_cell=myarray51Testing软件测试网D0D@6xW
[ W"x

  con.Close51Testing软件测试网-eo#QNz1V)uka

 Set con=Nothing51Testing软件测试网sa/? sR q/b

On Error Goto 0

End Function