透過 Microsoft Jet OLE DB Provider 將 Excel 檔案視為資料庫,使用 ADO 讀取工作表數據並輸出 HTML 表格。支援多工作表、自動獲取欄位名、斑馬紋顯示。
<%
Function SwitchExcelInfo(xlsFileName)
Dim xlsStr, rs, i, j, k, ExcelConn, ExcelFile
Dim objExcelApp, objExcelBook, bgColor, arrSheets
xlsStr = ""
ExcelFile = Server.MapPath(xlsFileName)
' 先用 Excel COM 物件獲取工作表名稱 (ADO 無法直接列舉)
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.DisplayAlerts = False
objExcelApp.Visible = False
objExcelApp.WorkBooks.Open ExcelFile
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 objExcelApp = Nothing
' ADO 連接 Excel
Set ExcelConn = Server.CreateObject("ADODB.Connection")
ExcelDriver = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & ExcelFile
ExcelConn.Open ExcelDriver
Set rs = Server.CreateObject("ADODB.Recordset")
For i = 1 To UBound(arrSheets)
' 工作表名需加 $ 符號並用方括號
Sql = "SELECT * FROM [" & arrSheets(i) & "$]"
xlsStr = xlsStr & "<h3>" & arrSheets(i) & "</h3>"
xlsStr = xlsStr & "<table border=1 cellspacing=0 cellpadding=3>"
rs.Open Sql, ExcelConn, 1, 1
k = 1
' 輸出表頭
xlsStr = xlsStr & "<tr style='background:#ccc'>"
For j = 0 To rs.Fields.Count - 1
xlsStr = xlsStr & "<th>" & rs.Fields(j).Name & "</th>"
Next
xlsStr = xlsStr & "</tr>"
' 輸出數據行
While Not rs.EOF
If k Mod 2 = 1 Then bgColor = "bgcolor='#E0E0E0'" Else bgColor = ""
xlsStr = xlsStr & "<tr " & bgColor & ">"
For j = 0 To rs.Fields.Count - 1
xlsStr = xlsStr & "<td>" & rs.Fields(j).Value & "</td>"
Next
xlsStr = xlsStr & "</tr>"
rs.MoveNext
k = k + 1
Wend
rs.Close
xlsStr = xlsStr & "</table><br>"
Next
ExcelConn.Close
Set ExcelConn = Nothing
SwitchExcelInfo = xlsStr
End Function
%>
<%
Response.Write SwitchExcelInfo("data/report.xls")
%>
Driver={Microsoft Excel Driver (*.xls)};DBQ=路徑 (適用 .xls);.xlsx 需 Microsoft.ACE.OLEDB.12.0[SheetName$] 格式,末尾必須有 $Quit Excel、Close 連接、Set Nothing,防止 Excel 進程殘留| 問題 | 解決 |
|---|---|
| 找不到 ISAM | 安裝 Access Database Engine (ACE) 驅動 |
| 欄位名為 F1, F2... | Excel 第一行無標題,加 HDR=NO 或手動指定 |
| 數字顯示為科學記號 | Excel 儲存格格式設為「文字」或讀取後格式化 |
| 大文件效能差 | 改用 OpenXML SDK / EPPlus 等純 .NET 方案 |
此函數適合中小型 .xls 檔案的 Web 顯示需求;大數據量或 .xlsx 請遷移至 .NET 方案。