利用表内容生成文件 [在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.Close

Next

ExcelConn.Close

Set ExcelConn = Nothing

SwitchExcelInfo = xlsStr

End Function