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

dddggg [原作]

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

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

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

"& arrSheets(i) &"

"

xlsStr = xlsStr & "

"

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

xlsStr = xlsStr & ""

For j = 0 to rs.Fields.Count-1

xlsStr = xlsStr & ""

Next

xlsStr = xlsStr & ""

rs.movenext

k = k + 1

Wend

xlsStr = xlsStr & "

"& rs(0) &""& rs(j) &"

"

rs.Close

Next

ExcelConn.Close

Set ExcelConn = Nothing

SwitchExcelInfo = xlsStr

End Function

更多信息请查看IT技术专栏