How to read Excel file in ASP.NET?

How to read Excel file in ASP.NET?

在VB.NET有二種常用方法處理 Excel 檔案. 一種是用 Microsoft Office 元件如:

Dim oExcel As New Excel.Application()
...

但是
(Microsoft recommend not using COM Interop with Microsoft Office
products from an ASP.NET web page)

除了要在 Server 裝 Office 外, 還有權限問題要克服.
另一種是用 Microsoft Jet Engine 如 Microsoft MSDN 範例:


Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection(_
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\myData.XLS; " & _
"Extended Properties=Excel 8.0;")

' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", MyConnection)

DS = New System.Data.DataSet()
MyCommand.Fill(DS)
MyConnection.Close()


其中我遇到的小問題是,
當我不知道Excel檔案中Sheet名稱是否叫Sheet1
我必須先找出第一個Sheet Name:


MyConnection.Open()

Dim schemaTable As DataTable
schemaTable = MyConnection.GetOleDbSchemaTable(_
OleDbSchemaGuid.Tables, New Object(_
) {Nothing, Nothing, Nothing, "TABLE"})

MyConnection.Close()
Dim v_sheet1 As String = schemaTable.Rows(0).Item(_
"TABLE_NAME").ToString()

schemaTable.Clear()


之後我就可以把:


MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", MyConnection)

改成:

MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [" + v_sheet1 + "$]", MyConnection)

這樣就可以讀取第一個 Sheet 的資料到 DataSet DS 中了.