使用adonet读取excel的2个函数

'获取某单元格数据51Testing软件测试网 V1D'p\4N

Function get_excel_value(rowindex,cellindex)51Testing软件测试网0T+]%I0wn:r^N

On Error Resume NEXT

Dim myarray()

Set con=createobject("adodb.connection")

 conset="DSN=test;DBQ=D:\test\test.xls;DefaultDir=D:\test;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;"51Testing软件测试网/tQ!e(s*?5Nn0j

con.open conset

Set rs=createobject("adodb.recordset")51Testing软件测试网f4C?0}V`%e

sql="select count(*) from [Sheet1$]"51Testing软件测试网t q,^'R'G*?'Y

rs.open sql,con

rows=rs.Fields(0)51Testing软件测试网 K#IQF1?f,aj

  rs.Close

 sql="select * from [Sheet1$]"

  rs.open sql,con

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

     ‘MsgBox "该单元格无数据"51Testing软件测试网&rm?Q/rw/|7R\(To-y

     get_excel_value =-151Testing软件测试网&r:F9M1W/Fh:o4p~

     Exit function51Testing软件测试网1JU:Dc$| Z{

   Else

     rs.Move rowindex-1

   get_excel_value =rs.Fields(cellindex-1).Value51Testing软件测试网/d @$jC!u8Cw

 51Testing软件测试网N_/_~1r:D?

   End If

 con.Close51Testing软件测试网bv?^.i'On0Z

 Set con=Nothing

On Error Goto 051Testing软件测试网m9X)m4Q-q

End Function

 51Testing软件测试网m:A@`F&oD C0Q

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

'cellindex:获取列的序号或列名

Function get_excel_cell(cellindex)51Testing软件测试网\&Kb6P N D:u*M1N

On Error Resume NEXT51Testing软件测试网]$?P;m}%_

Dim myarray()

Set con=createobject("adodb.connection")

conset="DSN=test;DBQ=D:\test\test.xls;DefaultDir=D:\test;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;"51Testing软件测试网5e g^.m6['_ZN^R\1a

con.open conset51Testing软件测试网J&h.OJ0_,i,p

Set rs=createobject("adodb.recordset")

sql="Select count(*) from [Sheet1$]"51Testing软件测试网2z[S`,ZY&?#M

rs.Open sql,con

'记录的总行数51Testing软件测试网-^`/oZ#cZ ^

rows=rs.Fields(0).Value

rs.Close51Testing软件测试网'|!H9L*w4d(yc@

 sql="select * from [Sheet1$]"51Testing软件测试网%A:WaTS]On

  rs.open sql,con

  ReDim myarray(rows-1)51Testing软件测试网U B3ZI/}S1M

  For i=0 To rows-1

  '将i行cellindex列的值赋值给数组51Testing软件测试网/y/@c}6mI

  myarray(i)=rs.Fields(cellindex).Value

  rs.MoveNext51Testing软件测试网H `ZI NO4_

  next51Testing软件测试网t;i w B3Vv

 get_excel_cell=myarray51Testing软件测试网sod"e%`o

  con.Close51Testing软件测试网Te$J]r

 Set con=Nothing51Testing软件测试网:?lT7un

On Error Goto 051Testing软件测试网#e*BR*p-wA-e

End Function51Testing软件测试网
AYUP;k