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

un-ok 2012-01-17

展開全文

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

Introduction
使用環(huán)境：Visual BASIC 6.0 SP6 + Excel 2003

VB讀取Excel

vbado001

這裡示範三種方式用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，且不能去關(guān)閉connection與RecordSet，否則DataGrid的資料會不見。

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

VB寫入Excel

vbado002

這裡示範三種方式用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