(原創) 如何使用VB讀寫Excel檔? (Visual BASIC) (Excel)

Abstract
一般若想用程式語言去讀寫excel檔，直覺會使用Excel這個COM物件，事實上也可使用資料庫ADO的方式，而且速度更快。

Introduction
使用環境：Visual BASIC 6.0 SP6 + Excel 2003

VB讀取Excel

這裡示範三種方式用VB讀取excel檔：
1.使用Excel COM的方式讀取excel檔
2.使用ADO的方式讀取excel檔
3.使用ADO的方式讀取excel檔，並使用databinding

1.使用Excel COM的方式讀取excel檔

1  Private   Sub ReadExlByLoopCOM_Click()
2    Dim exl As Excel.Application
3    Dim wb As Excel.Workbook
4    Dim sht As Excel.Worksheet
5    Dim i, j As   Integer
6    Dim t1, t2, t As   Date
7   
8    t1 = Now
9   
10    Set exl =   CreateObject ( " Excel.Application " )
11    Set wb = exl.Workbooks.Open(App.Path &   " \sample.xls " )
12    Set sht = wb.Worksheets( 1 )
13   
14    For i =   2   To   10004
15      For j =   1   To   3
16        Combo1.AddItem sht.Cells(i, j)
17      Next j
18    Next i
19   
20    exl.Quit
21   
22    t2 = Now
23   
24    t = t2 - t1
25   
26    MsgBox   Second (t)
27  End Sub

使用Excel.Application將Excel COM物件叫進來，然後使用其method去讀取，若你熟析VBA，對這種方式就會覺得很親切。

2.使用ADO的方式讀取excel檔

1  Private   Sub cmdReadExlByLoopADO_Click()
2    Dim cn As   New ADODB.Connection
3    Dim rs As   New ADODB.Recordset
4    Dim i As   Integer
5    Dim t1, t2, t As   Date
6   
7    t1 = Now
8   
9    cn.Open " Provider=Microsoft.Jet.OLEDB.4.0; "   & _
10            " Data Source= "   & App.Path &   " \sample.xls; "   & _
11            " Extended Properties=""Excel 8.0;HDR=YES;"" "
12         
13    rs.Open " SELECT * FROM [Sheet1$A1:C10002] " , cn, adOpenStatic
14 
15    Do   While   Not rs.EOF
16      For i =   0   To   2
17        Combo2.AddItem rs(i)
18      Next i
19     
20      rs.MoveNext
21    Loop
22   
23    rs.Close
24    Set rs =   Nothing
25    cn.Close
26    Set cn =   Nothing
27   
28    t2 = Now
29   
30    t = t2 - t1
31   
32    MsgBox   Second (t)
33  End Sub

使用ADO的方式，透過OLEDB資料庫引擎去讀取excel檔，connection string的HDR代表是否要將第一行當成header處理。

這種方式的特色，是可以使用SQL語言去做對excel做塞選，而且可以自己指定Sheet與cell範圍，資料抓進VB後就變成recordset，剩下的就是依照ADO的方式去處理。

若你熟析SQL與ADO，對這種方式就會覺得很親切。

3.使用ADO的方式讀取excel檔，並使用databinding

1  Private   Sub cmdReadExlbyDataBinding_Click()
2    Dim cn As   New ADODB.Connection
3    Dim rs As   New ADODB.Recordset
4    Dim t1, t2, t As   Date
5   
6    t1 = Now
7   
8    cn.Open " Provider=Microsoft.Jet.OLEDB.4.0; "   & _
9            " Data Source= "   & App.Path &   " \sample.xls; "   & _
10            " Extended Properties=""Excel 8.0;HDR=YES;"" "
11    cn.CursorLocation = adUseClient
12         
13    rs.Open " SELECT * FROM [Sheet1$A1:C10002] " , cn, adOpenStatic
14 
15    Set DataGrid1.DataSource = rs
16    DataGrid1.Refresh
17   
18    t2 = Now
19   
20    t = t2 - t1
21   
22    MsgBox   Second (t)
23  End Sub

之所以會特別講這種方式，是因為在很多應用上，讀進來的excel檔就是要顯示在DataGrid上，若你只會Excel COM那種方式讀取excel檔，最後還是要湊成RecordSet才能跟DataGrid做DataBinding，既然如此，就直接使用ADO的方式將excel資料讀進RecordSet就好，但要注意的是，必須使用client side的RecordSet，且不能去關閉connection與RecordSet，否則DataGrid的資料會不見。

Summary
這三種方式哪個最好呢?在我的NB上測試，10000筆的excel檔資料，使用Excel COM需要20秒，使用ADO的方式只要2秒，而DataBinding的不到1秒，再次證明使用Excel COM是最沒有效率的方式，建議使用ADO的方式讀取Excel檔。

VB寫入Excel

這裡示範三種方式用VB寫入excel檔：
1.使用Excel COM的方式寫入excel檔
2.使用ADO的方式寫入excel檔，搭配RecordSet的AddNew
3.使用ADO的方式寫入excel檔，搭配SQL語法

1.使用Excel COM的方式寫入excel檔

1  Private   Sub cmdWriteExlByLoopCOM_Click()
2    Dim exl As Excel.Application
3    Dim wb As Excel.Workbook
4    Dim sht As Excel.Worksheet
5    Dim i, j, cnt As   Integer
6    Dim sData As   String
7    Dim t1, t2, t As   Date
8   
9    t1 = Now
10   
11    Set exl =   CreateObject ( " Excel.Application " )
12    Set wb = exl.Workbooks.Add
13    Set sht = wb.ActiveSheet
14   
15    sht.Cells( 1 , 1 ) =   " R "
16    sht.Cells( 1 , 2 ) =   " G "
17    sht.Cells( 1 , 3 ) =   " B "
18   
19    cnt =   0
20   
21    For i =   2   To   10004
22      For j =   1   To   3
23        sht.Cells(i, j) = cnt
24        cnt = cnt +   1
25      Next j
26    Next i
27   
28    exl.ActiveWorkbook.SaveAs (App.Path &   " \sample1.xls " )
29    exl.ActiveWorkbook.Close
30    exl.Quit
31   
32    t2 = Now
33   
34    t = t2 - t1
35   
36    MsgBox   Second (t)
37  End Sub

這種方式也不必多說了，基本上就跟VBA完全一樣，就是一個cell一個cell的去控制。

2.使用ADO的方式寫入excel檔，搭配RecordSet的AddNew

1  Private   Sub cmdWriteExlByLoopADO_AddNew_Click()
2    Dim rs As   New ADODB.Recordset
3    Dim i, j, cnt As   Integer
4    Dim exl As Excel.Application
5    Dim wb As Excel.Workbook
6    Dim sht As Excel.Worksheet
7    Dim cn As   New ADODB.Connection
8    Dim t1, t2, t As   Date
9   
10    t1 = Now
11   
12    rs.Fields.Append " R " , adInteger
13    rs.Fields.Append " G " , adInteger
14    rs.Fields.Append " B " , adInteger
15   
16    Set exl =   CreateObject ( " Excel.Application " )
17    Set wb = exl.Workbooks.Add
18    Set sht = wb.ActiveSheet
19   
20    sht.Cells( 1 , 1 ) =   " R "
21    sht.Cells( 1 , 2 ) =   " G "
22    sht.Cells( 1 , 3 ) =   " B "
23   
24    cnt =   0
25    rs.Open
26    For i =   0   To   10002
27      rs.AddNew
28      For j =   0   To   2
29        rs(j) = cnt
30        cnt = cnt +   1
31      Next j
32      rs.Update
33    Next i
34   
35    sht.Cells( 2 , 1 ).CopyFromRecordset rs
36   
37    rs.Close
38    Set rs =   Nothing
39   
40    exl.ActiveWorkbook.SaveAs App.Path &   " \sample2.xls "
41    exl.ActiveWorkbook.Close
42    exl.Quit
43   
44    t2 = Now
45   
46    t = t2 - t1
47   
48    MsgBox   Second (t)
49  End Sub

這種方式的特色是所有資料先在RecordSet處理，最後再利用Excel COM的CopyFromRecordset一次將RecordSet搬到Excel，而資料新增靠的是RecordSet的AddNew，剩下的都是ADO的控制。

3.使用ADO的方式寫入excel檔，搭配SQL語法

1  Private   Sub cmdWriteExlByADO_INSERT_Click()
2    Dim exl As Excel.Application
3    Dim wb As Excel.Workbook
4    Dim i, j, cnt As   Integer
5    Dim cn As   New ADODB.Connection
6    Dim t1, t2, t As   Date
7   
8    t1 = Now
9   
10    Set exl =   CreateObject ( " Excel.Application " )
11    Set wb = exl.Workbooks.Add
12   
13    exl.ActiveWorkbook.SaveAs (App.Path &   " \sample3.xls " )
14    exl.ActiveWorkbook.Close
15    exl.Quit
16   
17    cn.Open " Provider=Microsoft.Jet.OLEDB.4.0; "   & _
18            " Data Source= "   & App.Path &   " \sample3.xls; "   & _
19            " Extended Properties=""Excel 8.0;HDR=YES;"" "
20           
21    cn.Execute " CREATE TABLE [Sheet1$] (R INT, G INT, B INT) "
22    cn.Execute " UPDATE [Sheet1$] SET R = 0 "
23    cn.Execute " UPDATE [Sheet1$] SET G = 1 "
24    cn.Execute " UPDATE [Sheet1$] SET B = 2 "
25   
26    cnt =   3
27    For i =   1   To   10000
28      cn.Execute " INSERT INTO [Sheet1$] (R,G,B) VALUES ( "   & cnt &   " , "   & cnt +   1   &   " , "   & cnt +   2   &   " ) "
29      cnt = cnt +   3
30    Next i
31   
32    cn.Close
33    Set cn =   Nothing
34    t2 = Now
35   
36    t = t2 - t1
37   
38    MsgBox   Second (t)
39  End Sub

我承認我當初看到這種寫法時真的是嚇了一大跳，沒想到可以直接對excel檔下SQL語法，目前僅有DELETE-SQL不能下，其他的SQL語法皆有支援。

Sumary
這三種方式哪個最好呢?在我的NB上測試，10000筆資料寫入，使用Excel COM要14秒，使用ADO的RecordSet.AddNew要2秒，而使用SQL需要5秒，我認為SQL之所以較慢，並不是SQL語言的問題，而是每筆資料必須透過Connection.Excute一次，但是AddNew則是用CopyFromRecordSet一次搬過去，所以速度最快。

完整程式碼下載
ADOReadExcel.7z
ADOWriteExcel.7z

Reference
HOW TO：使用 ADO.NET 擷取與修改利用 Visual Basic .NET 之 Excel 活頁簿中的記錄
ExcelADO 示範使用 ADO 在 Excel 活頁簿讀取和寫入資料的方法