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

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

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