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