[ADO.NET]透過 Jet.OLEDB 讀取 Excel 2003裡面的資料 (把Excel當成資料庫,透過SQL指令 - Select來讀取)

透過 Jet.OLEDB 讀取 Excel裡面的資料
(把Excel當成資料庫,透過SQL指令 - Select來讀取)

本文是參考自微軟的文章,把他的ASP.NET 1.1版修改成2.0/3.5適用的版本(要手動加入參考!)
並且改成VB語法。






 

 

 

 

本範例僅搭配 舊版 Excel(2003 或更早的版本)

新版本Excel (2007/2010/2013)請改用 ACE引擎來取代本文描述的 Jet引擎(文章最後有說明)

 

我的新書裡面(ASP.NET專題實務(文魁出版)/ ASP.NET 4.0專題實務(松崗出版)),

介紹了幾個範例,跟Excel 2003有關。

分別是 GridView轉成Excel、XML轉成Excel....等等。

 

這次找到相關的資料給大家參考。

         第二篇文章,是C#的範例,是舊的ASP.NET 1.1版。本文以此為例,需稍作修改。

 

首先,參考第一篇文章的作法。  

圖片 四 (Fig 4. Adding Microsoft Excel 11.0 Object Library as Reference)

     

先在VS 2005/2008/2010中,加入參考 ==> COM >>選擇「Microsoft Excel 11.0 Object Library

然後就簡單了。

 

把第二篇文章的 C#改成VB語法就行了。   

      寫 .NET程式的人,如果無法自己轉換 VB與 C#語法,那真的很糟糕了。

      請看----VB與C#語法的轉換,真的很簡單,別怕好嗎??

 

 

後置程式碼的最上方,別忘了加上這兩個 NameSpace(命名空間)

Imports System.Data
Imports System.Data.OleDb

 

然後...............下面的 Excel檔名,請自己修改一下

最討厭的是,T-SQL指令裡面,要抓取 Excel的工作表,務必要寫成這個樣子。後面要加上 $符號!!

 

      SELECT * FROM [Sheet1$]

微軟msdn的文件 --

 HOW TO:使用 ADO.NET 擷取與修改利用 Visual Basic .NET 之 Excel 活頁簿中的記錄

 
1. 如何將 Microsoft Excel 活頁簿與 Jet OLE DB Provider 搭配使用.
2. 如何將 Excel 活頁簿當做 ADO.NET 資料來源來使用
 
文章裡面提到:
在 Excel 活頁簿中有幾種參照表格 (或範圍) 的方法:
使用其後接著錢幣符號的工作表名稱 (例如,[Sheet1$] 或 [My Worksheet$])。 以此方法參照的活頁簿表格會包括工作表中所使用的全部範圍。
Select * from [Sheet1$]
 
使用定義名稱的範圍 (例如,[MyNamedRange]):
Select * from [MyNamedRange]
 
使用特定位址的範圍 (例如,[Sheet1$A1:B10]):
Select * from [Sheet1$A1:B10]
 

       透過GetOleDbSchemaTable來取到Excel的Sheet Name

 

 

照著原文(上面第二篇文章)來作,一定會錯

HOW TO:使用 ASP.NET、ADO.NET 及 Visual C# .NET 查詢及顯示 Excel 資料  http://support.microsoft.com/kb/306572/zh-tw

01     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
02         Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("GridView_Excel_DataSource.xls") + ";Extended Properties=Excel 8.0;"
03 
04         Dim objConn As OleDbConnection = New OleDbConnection(sConnectionString)
05 
06         ' Create new OleDbCommand to return data from worksheet.
07         '重點在於  Sheet1 的寫法!!
08         Dim objCmdSelect As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", objConn)
09 
10         ' Create new OleDbDataAdapter that is used to build a DataSet
11         ' based on the preceding SQL SELECT statement.
12         Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter()
13 
14         'Pass the Select command to the adapter.
15         objAdapter1.SelectCommand = objCmdSelect
16 
17         ' Create new DataSet to hold information from the worksheet.
18         Dim objDataset1 As DataSet = New DataSet()
19 
20         ' Fill the DataSet with the information from the worksheet.
21         objAdapter1.Fill(objDataset1, "XLData")
22 
23         ' Bind data to DataGrid control.
24         GridView1.DataSource = objDataset1.Tables(0).DefaultView
25         GridView1.DataBind()
 
26     End Sub

 

完成了,最後補充一下:

1 . 因為DataAdapter與DataSet,會自動開啟、關閉「資料庫的連線」,所以我刪去這部份程式碼。跟微軟的原文不太一樣。

2.  上面的「連結 Excel檔案」的字串,這篇文章講的很仔細,請務必看過一次-- http://blog.miniasp.com/post/2008/08/How-to-read-Excel-file-using-OleDb-correctly.aspx

3.  本範例,如果要看 C#語法,上述的第二篇(http://support.microsoft.com/kb/306572/zh-tw)就是 C#語法,請自行參考!

4.  比較頭大的問題,是在 x64(64位元)的Windows作業系統上,上述的寫法(Jet.OLEDB)問題不少。微軟已經推出 Win2003(x64)的MDAC,但Win2008呢.....還有些問題有待時間解決啦~

 

真正的好東西來了~~

(2010/1/13補充)   [轉貼]在 Server 端存取 Excel 檔案的利器:NPOI Library

 

 

==================================================================================

 

如果你對於 VB與C#語法之間的轉換很害怕的話(或是嚇到不敢嘗試),

請您一定要看看這篇文章--.NET的世界裡,VB / C#語法轉換不該是一個問題。

 

 

如果您看不懂、或是不會自己手寫 ADO.NET的程式,可以參考我另一篇文章--DataReader的標準範例 for ASP.NET 2.0 / 3.5

我的新書裡面,對ADO.NET這部份有不少補充,是市面上其他書比較少見的。

 

 

 相關文章----

 

 

上面的文章都是「讀取」Excel檔案,以下是「寫入」的作法 ----

 

 

2013/1/29補充

新版本 Access 2007/2010/2013 提供新的 ACE引擎 (ACE Provider) 來處理

詳見這兩篇文章:

使用 Access 2010 進行資料程式設計(from 微軟MSDN網站)
http://msdn.microsoft.com/zh-tw/library/office/ff965871%28v=office.14%29.aspx

 


 當您使用 AccessDataSource時,會出現警告訊息:

'Microsoft.ACE.OLEDB.12.0' 提供者並未登入於本機電腦上。

'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine


解決方法 --- [AccessDataSource] Office 2010 / 2013 與 Microsoft.ACE.OLEDB
http://www.dotblogs.com.tw/mis2000lab/archive/2013/01/29/accessdatasource_microsoft_ace_oledb.aspx

 

 

另一種透過 ADO.NET 讀取 Excel的作法,詳見:

http://code.msdn.microsoft.com/NitoExcel 只能「讀取」Excel檔

http://sridharpasham.com/tag/asp-net-2/  這篇文章有完整範例

 

 

竟然有人告訴我,我的「值班正妹」缺席好幾天!



 




這次我要下猛藥了。

 

今日值班正妹,唯川純。(日本 愛情動作片的女優,號稱「黑暗林志玲」)

 

 

 

我將思想傳授他人, 他人之所得,亦無損於我之所有;

猶如一人以我的燭火點燭,光亮與他同在,我卻不因此身處黑暗。----Thomas Jefferson

線上課程,遠距教學 (Web Form 49hr)  https://dotblogs.com.tw/mis2000lab/2016/02/01/aspnet_online_learning_distance_education_VS2015

線上課程,遠距教學 (ASP.NET MVC 75~80hr)  https://dotblogs.com.tw/mis2000lab/2018/08/14/ASPnet_MVC_Online_Learning_MIS2000Lab

ASP.NET MVC線上課程 第一天 免費看 (5.5小時) 

寫信給我,不要私訊 --  mis2000lab (at) yahoo.com.台灣  或  school (at) mis2000lab.net


ASP.NET遠距教學、線上課程(Web Form + MVC)。 第一天課程, "完整" 試聽。 

................   facebook社團   https://www.facebook.com/mis2000lab   ......................

................  YouTube (ASP.NET) 線上教學影片  https://www.youtube.com/channel/UC6IPPf6tvsNG8zX3u1LddvA/

 

Blog文章 "附的範例" 無法下載,請看 這裡 ...... https://dotblogs.com.tw/mis2000lab/2016/03/14/2008_2015_mis2000lab_sample_download

請看我們的「售後服務」範圍(嚴格認定)

...................................................................................................................................................... 

[遠距教學、教學影片] ASP.NET (Web Form) 課程 上線了!MIS2000Lab.主講

事先錄製好的影片,並非上課時側錄!   觀看影片時,有如我「一對一」跟您面對面講課

 

ASP.NET MVC 5 線上教學

         累積時數約 75~ 80小時...... 第一天(5.5小時)完整內容,"免費"讓您評估