用vbs读取Excel文件的函数代码

核心代码

function readexcel( myxlsfile, mysheet, my1stcell, mylastcell, blnheader )
' function : readexcel
' version : 2.00
' this function reads data from an excel sheet without using ms-office
'
' arguments:
' myxlsfile [string] the path and file name of the excel file
' mysheet [string] the name of the worksheet used (e.g. "sheet1")
' my1stcell [string] the index of the first cell to be read (e.g. "a1")
' mylastcell [string] the index of the last cell to be read (e.g. "d100")
' blnheader [boolean] true if the first row in the sheet is a header
'
' returns:
' the values read from the excel sheet are returned in a two-dimensional
' array; the first dimension holds the columns, the second dimension holds
' the rows read from the excel sheet.
'
' written by rob van der woude
' http://www.robvanderwoude.com
dim arrdata( ), i, j
dim objexcel, objrs
dim strheader, strrange

const adopenforwardonly = 0
const adopenkeyset = 1
const adopendynamic = 2
const adopenstatic = 3

' define header parameter string for excel object
if blnheader then
strheader = "hdr=yes;"
else
strheader = "hdr=no;"
end if

' open the object for the excel file
set objexcel = createobject( "adodb.connection" )
' imex=1 includes cell content of any format; tip by thomas willig
objexcel.open "provider=microsoft.jet.oledb.4.0;data source=" & _
myxlsfile & ";extended properties=""excel 8.0;imex=1;" & _
strheader & """"

' open a recordset object for the sheet and range
set objrs = createobject( "adodb.recordset" )
strrange = mysheet & "$" & my1stcell & ":" & mylastcell
objrs.open "select * from [" & strrange & "]", objexcel, adopenstatic

' read the data from the excel sheet
i = 0
do until objrs.eof
' stop reading when an empty row is encountered in the excel sheet
if isnull( objrs.fields(0).value ) or trim( objrs.fields(0).value ) = "" then exit do
' add a new row to the output array
redim preserve arrdata( objrs.fields.count - 1, i )
' copy the excel sheet's row values to the array "row"
' isnull test credits: adriaan westra
for j = 0 to objrs.fields.count - 1
if isnull( objrs.fields(j).value ) then
arrdata( j, i ) = ""
else
arrdata( j, i ) = trim( objrs.fields(j).value )
end if
next
' move to the next row
objrs.movenext
' increment the array "row" number
i = i + 1
loop

' close the file and release the objects
objrs.close
objexcel.close
set objrs = nothing
set objexcel = nothing

' return the results
readexcel = arrdata
end function

使用方法:

option explicit

dim arrsheet, intcount

' read and display columns a,b, rows 2..6 of "readexceltest.xls"
arrsheet = readexcel( "readexceltest.xls", "sheet1", "a1", "b6", true )
for intcount = 0 to ubound( arrsheet, 2 )
wscript.echo arrsheet( 0, intcount ) & vbtab & arrsheet( 1, intcount )
next

wscript.echo "==============="

' an alternative way to get the same results
arrsheet = readexcel( "readexceltest.xls", "sheet1", "a2", "b6", false )
for intcount = 0 to ubound( arrsheet, 2 )
wscript.echo arrsheet( 0, intcount ) & vbtab & arrsheet( 1, intcount )
next