当前页面位置: » 丰搜网 » 文档中心 » 详细内容
在asp中利用ado显示excel文件内容的函数
function switchexcelinfo(xlsfilename)
dim xlsstr
dim rs
dim i,j,k
dim excelconn
dim excelfile
dim objexcelapp
dim objexcelbook
dim bgcolor
xlsstr = ""
execlfile = server.mappath(xlsfilename)
set objexcelapp = createobject("excel.application")
objexcelapp.quit
set objexcelapp = createobject("excel.application")
objexcelapp.quit
objexcelapp.displayalerts = false '不显示警告
objexcelapp.application.visible = false '不显示界面
objexcelapp.workbooks.open(execlfile)
set objexcelbook = objexcelapp.activeworkbook
redim arrsheets(objexcelbook.sheets.count)
for i=1 to objexcelbook.sheets.count
arrsheets(i) = objexcelbook.sheets(i).name
next
objexcelapp.quit
set objexeclapp=nothing
'#################
'power by www.knowsky.com
'#################
set excelconn = server.createobject("adodb.connection")
exceldriver = "driver={microsoft excel driver (*.xls)};dbq=" & execlfile
excelconn.open exceldriver
set rs = server.createobject("adodb.recordset")
for i = 1 to ubound(arrsheets)
sql = "select * from ["& arrsheets(i) &"$] "
''显示各工作表的名称
''xlsstr = xlsstr & "<br>"& arrsheets(i) &"<br>"
xlsstr = xlsstr & "<table cellpadding=1 width=""100%"" cellspacing=1 border=1 bordercolor='#000000' style='border-collapse:collapse;border:2px solid #000000'>"
rs.open sql,excelconn,1,1
k = 1
while not rs.eof
if k mod 2 <> 0 then bgcolor = "bgcolor=#e0e0e0" else bgcolor = ""
xlsstr = xlsstr & "<tr "& bgcolor &">"
xlsstr = xlsstr & "<td>"& rs(0) &"</td>"
for j = 0 to rs.fields.count-1
xlsstr = xlsstr & "<td>"& rs(j) &"</td>"
next
xlsstr = xlsstr & "</tr>"
rs.movenext
k = k + 1
wend
xlsstr = xlsstr & "</table><br>"
rs.close
next
excelconn.close
set excelconn = nothing
switchexcelinfo = xlsstr
end function
