(原創) 如何使用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檔
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檔
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
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檔
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
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語法
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 活頁簿讀取和寫入資料的方法
