使用 Microsoft.Jet.OLEDB.4.0 讀取 Excel

使用 Microsoft.Jet.OLEDB.4.0 來讀取 Excel 文件,確定已安裝 Microsoft Jet OLEDB 4.0 驅動程式。當處於伺服器的 OLEDB.4.0 支持問題,讀取 Excel .xls 的檔案,且使用 .ASPX 的情況下嘗試一步步解析。

讀取 Excel
Server.MapPath("Files/Book.xls")

C:\Inetpub\wwwroot\Files\Book.xls

確定 Excel 的檔案位置,取得檔案虛擬位置路徑。使用 .ASPX 需要匯入命名空間。

<%@ Import Namespace="System.Data.OleDb" %>
Dim myConnection, OleMdbCommand, strSql
Dim rs As OleDbDataReader
myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("./Files/Book.xls") & ";Extended Properties=Excel 8.0")
myConnection.Open()

使用 TypeName(myConnection) = OleDbConnection 表示連接沒問題。

Dim sheetName As String = "工作表1$"
strSql = "SELECT * FROM [" & sheetName & "]"
OleMdbCommand = New OleDbCommand(strSql, myConnection)
rs = OleMdbCommand.ExecuteReader()

使用 TypeName(rs) = OleDbDataReader 表示讀取沒問題,GetName() 欄位名稱。

For i = 0 To rs.FieldCount - 1
  Response.Write( rs.GetName(i) & ", ")
Next

倉庫名稱, 庫存量, 單據日期, 安全存量,

列出相關記錄

Response.Write ("<table>")
Do While (rs.Read())
  Response.Write ("<tr>")
  For i = 0 To rs.FieldCount - 1
    Response.Write ("<td>" & rs.Item(i) & "</td>")
  Next
  Response.Write ("</tr>")
Loop
Response.Write ("</table>")
Los Angeles4602016/5/14400
San Diego2802016/4/23300
Boston1602016/7/20200
New Jersey3202016/3/18300

關閉 Excel 檔案的讀取。

rs.Close()
myConnection.Close()

以上只是比較適合在載入已經編輯的 Excel 檔案做格式轉換,切換經典 Class ASP 改用 ASPX 時的應用。





Class ASP 使用 ADO 讀取 Excel (97-2003)

使用 ADOExcel 活頁簿讀取資料的方法

Dim sourceFilePath, strConnString
sourceFilePath = Server.MapPath("Book1.xls") REM 讀取的 Excel 檔案路徑
REM 開啟 Excel
Dim excelConnection : Set excelConnection = Server.CreateObject("ADODB.Connection")
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourceFilePath & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
excelConnection.Open strConnString

參數 Excel 8.0:對于 Excel 97 以上版本 (Excel 2003) .xls 使用 Excel 8.0 .xlsx 似乎不行。

參數 HDRHDR=YES 這代表第一行是標題,不做為數據使用。
如果用 HDR=NO 則表示第一行不是標題,做為數據來使用。系統默認值是 YES

  • IMEX (IMport EXport Mode):IMEX 有三種模式設置:不同的模式代表著不同的讀寫行為
  • IMEX=0Export Mode 匯出模式,開啓的 Excel 檔案只能用來做「寫入」用途。
  • IMEX=1Import Mode 匯入模式,開啓的 Excel 檔案只能用來做「讀取」用途。
  • IMEX=2Linked Mode 連結模式,開啓的 Excel 檔案可同時支援「讀取」與「寫入」用途。

使用 ADOX.Catalog 取得工作表名稱

建立 Server.CreateObject 然後 ADOX.Catalog 主動 Excel Connection 連接物件。接著使用 For..Next 迴圈取得所有的表格,table.Type 如果表格的類型為 TABLE 就代表它是工作表,可以取得工作表名稱、最後程式碼會 Close 關閉 ADO 連線。

Dim catalogSheet : Set catalogSheet = Server.CreateObject("ADOX.Catalog")
catalogSheet.ActiveConnection = excelConnection
Dim table
For Each table In catalogSheet.Tables
  If (table.Type = "TABLE") Then
    Response.Write table.Name & "<br />"
  End If
Next
  • 工作表1$
  • 工作表2$
  • 工作表3$

取得第一個工作表的名稱

Dim sheetName
sheetName = catalogSheet.Tables(0).Name
Set catalogSheet = Nothing
  • sheetName = 工作表1$

讀取第一個工作表的資料

Dim excelRs : Set excelRs = Server.CreateObject("ADODB.RecordSet")
Dim strSql, field
strSql = "SELECT * FROM [" & sheetName & "]"
excelRs.Open strSql, excelConnection, 1, 3
REM 輸出到頁面
Response.Write "<table>"
Do Until excelRs.EOF
Response.Write "<tr>"
  For Each field In excelRs.Fields
    Response.Write "<td>" & field.Value & "</td>"
  Next
  Response.Write "</tr>"
  excelRs.MoveNext()
Loop
Response.Write "</table>"

關閉 ADO 連線

excelRs.Close
Set excelRs = Nothing
excelConnection.Close
Set excelConnection = Nothing

讀取 Excel

讀取結果

倉庫名稱庫存量單據日期安全存量
Los Angeles4602016/5/14400
San Diego2802016/4/23300
Boston1602016/7/20200
New Jersey3202016/3/18300

ASP 使用 ADO 讀取 Excel 程式碼

Dim sourceFilePath, strConnString
sourceFilePath = Server.MapPath("Book1.xls") REM 讀取的 Excel 檔案路徑
REM 開啟 Excel
Dim excelConnection : Set excelConnection = Server.CreateObject("ADODB.Connection")
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourceFilePath & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
excelConnection.Open strConnString

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

REM 讀取第一個工作表的資料
Dim excelRs : Set excelRs = Server.CreateObject("ADODB.RecordSet")
Dim strSql, field
strSql = "SELECT * FROM [" & sheetName & "]"
excelRs.Open strSql, excelConnection, 1, 3

REM 輸出到頁面
Response.Write "<table>"
Do Until excelRs.EOF
Response.Write "<tr>"
  For Each field In excelRs.Fields
    Response.Write "<td>" & field.Value & "</td>"
  Next
  Response.Write "</tr>"
  excelRs.MoveNext()
Loop
Response.Write "</table>"

excelRs.Close
Set excelRs = Nothing
excelConnection.Close
Set excelConnection = Nothing