在ASP中利用ADO显示Excel文件内容的函数[原创]

王朝asp·作者佚名 2006-01-09

宽屏版字体: 小|中|大|超大

Function SwitchExcelInfo(xlsFileName)

'#################

'Power By Tangn.COM

'#################

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

objExcelApp.Quit

Set objExeclApp=nothing

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>"

xlsStr = xlsStr & "</tr>"

rs.movenext

k = k + 1

Wend

xlsStr = xlsStr & "</table><br>"

rs.Close

ExcelConn.Close

Set ExcelConn = Nothing

SwitchExcelInfo = xlsStr

End Function