2015-12-2

使用 ADO 讀取 Excel

ADO 除了可以用來讀取資料庫之外，還可以讀取 Excel 的資料，此篇文章會說明如何在 ASP 使用 ADO 來讀取 Excel 的資料。

Excel 範例檔案

這邊所使用的 Excel 範例是一個郵遞區號列表，共有三個欄位，第一列是標題，工作表名稱為郵遞區號一覽表。

完整的程式碼

'欲讀取的 excel 檔案路徑
Dim sourceFilePath
sourceFilePath = Server.MapPath("data_source.xls")

'開啟 excel
Dim excelConn : Set excelConn = Server.CreateObject("ADODB.Connection")
excelConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
               "Data Source=" & sourceFilePath & ";" &_
               "Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"

'使用 ADOX.Catalog 取得工作表名稱
Dim catalog : Set catalog = Server.CreateObject("ADOX.Catalog")
catalog.ActiveConnection = excelConn

'列舉所有工作表的名稱
Dim table
For Each table In catalog.Tables
    If (table.Type = "TABLE") Then
        Response.Write table.Name & "<br>"
    End If
Next

'取得第一個工作表的名稱
Dim sheetName
sheetName = catalog.Tables(0).Name
Set catalog = Nothing

'讀取第一個工作表的資料
Dim excelRs : Set excelRs = Server.CreateObject("ADODB.RecordSet")
Dim query, field
query = "SELECT * FROM [" & sheetName & "]"
excelRs.Open query,excelConn,1,3
Do Until excelRs.EOF
    For Each field In excelRs.Fields
        Response.Write field.Value & " "
    Next
    Response.Write "<br>"
    excelRs.MoveNext()
Loop
excelRs.Close
Set excelRs = Nothing

excelConn.Close
Set excelConn = Nothing

程式碼說明

excelConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
               "Data Source=" & sourceFilePath & ";" &_
               "Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"

首先設定連線參數，Data Source是 excel 檔案的路徑，Extended Properties設定連接參數。
Extended Properties 的參數說明：

• Excel 8.0 : 一般存成 Excel 97-2003 的檔案都可以讀的到。
• HDR=YES : 是否有標題列，YES 表示該 excel 有標題列，否則請設為 NO。
• IMEX=1 : 0 為匯出模式，1 為匯入模式。

Dim catalog : Set catalog = Server.CreateObject("ADOX.Catalog")
catalog.ActiveConnection = excelConn
Dim sheetName
sheetName = catalog.Tables(0).Name
Set catalog = Nothing

這邊使用了 ADOX.Catalog 來取得工作表的名稱，這樣就不怕使用者亂改名稱而取不到資料。

Dim excelRs : Set excelRs = Server.CreateObject("ADODB.RecordSet")
Dim query, field
query = "SELECT * FROM [" & sheetName & "]"
excelRs.Open query,excelConn,1,3

使用 SQL 來查詢資料，就跟平常連接到資料庫一樣。 如果你想手動指定要讀取的工作表，須將工作表名稱以 [] 包起來，並在裡面的最後方加上 $ 符號，以這個範例來講就是 SELECT * FROM [郵遞區號一覽表$]。

Do Until excelRs.EOF
    For Each field In excelRs.Fields
        Response.Write field.Value & " "
    Next
    Response.Write "<br>"
    excelRs.MoveNext()
Loop

讀取每列的資料並輸出到頁面上。

Reference

Office Space：撰寫 Microsoft Office 應用程式指令碼的秘訣
https://technet.microsoft.com/zh-tw/library/ee692882.aspx
透過 OleDb 精準讀入 Excel 檔的方法
http://blog.miniasp.com/post/2008/08/05/How-to-read-Excel-file-using-OleDb-correctly.aspx

ASP