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 活頁簿讀取和寫入資料的方法