使用 Microsoft.Jet.OLEDB.4.0 讀取 Excel
使用 Microsoft.Jet.OLEDB.4.0 來讀取 Excel 文件,確定已安裝 Microsoft Jet OLEDB 4.0 驅動程式。當處於伺服器的 OLEDB.4.0 支持問題,讀取 Excel .xls 的檔案,且使用 .ASPX 的情況下嘗試一步步解析。
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 Angeles | 460 | 2016/5/14 | 400 |
San Diego | 280 | 2016/4/23 | 300 |
Boston | 160 | 2016/7/20 | 200 |
New Jersey | 320 | 2016/3/18 | 300 |
關閉 Excel 檔案的讀取。
rs.Close()
myConnection.Close()
以上只是比較適合在載入已經編輯的 Excel 檔案做格式轉換,切換經典 Class ASP 改用 ASPX 時的應用。
Class ASP 使用 ADO 讀取 Excel (97-2003)
使用 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
參數 Excel 8.0:對于 Excel 97 以上版本 (Excel 2003) .xls 使用 Excel 8.0 .xlsx 似乎不行。
參數 HDR:HDR=YES 這代表第一行是標題,不做為數據使用。
如果用 HDR=NO 則表示第一行不是標題,做為數據來使用。系統默認值是 YES
- IMEX (IMport EXport Mode):IMEX 有三種模式設置:不同的模式代表著不同的讀寫行為
- IMEX=0 為 Export Mode 匯出模式,開啓的 Excel 檔案只能用來做「寫入」用途。
- IMEX=1 為 Import Mode 匯入模式,開啓的 Excel 檔案只能用來做「讀取」用途。
- IMEX=2 為 Linked 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
讀取結果
倉庫名稱 | 庫存量 | 單據日期 | 安全存量 |
Los Angeles | 460 | 2016/5/14 | 400 |
San Diego | 280 | 2016/4/23 | 300 |
Boston | 160 | 2016/7/20 | 200 |
New Jersey | 320 | 2016/3/18 | 300 |
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