一、引言
作為微軟旗下一款優(yōu)秀的RAD工具VB和作為微軟的辦公組件的Excel因其強(qiáng)大的功能，方便的操作在各單位開發(fā)和辦公中得攪似氈櫚撓τ謾Ｎ順浞擲枚叩那看蠊δ?，微瘸堘供了很多种方法势堽者又x慕岷掀鵠礎(chǔ)１疚畝醞ü?/span>VB向Excel傳輸數(shù)據(jù)的幾種常用方法進(jìn)行總結(jié)，并分析每種方法的優(yōu)缺點(diǎn)及適用環(huán)境，以便開發(fā)人員選擇合適的方法，提高開發(fā)效率。
二、概述
通常我們向Excel傳輸數(shù)據(jù)使用自動(dòng)化。自動(dòng)化提供了最好的便利性并且能在運(yùn)行時(shí)對單元格進(jìn)行格式化和其他各項(xiàng)設(shè)置。對于自動(dòng)化可以使用幾種不同的途徑來傳輸數(shù)據(jù)：
l 一個(gè)單元格一個(gè)單元格的傳輸數(shù)據(jù)；
l 傳輸數(shù)組中的數(shù)據(jù)到一組單元格；
l 使用CopyFromRecordset方法傳輸ADO recordset到一組單元格；
l 通過在Excel worksheet中定義一個(gè)Querytable，這個(gè)querytable通過ODBC或者OLEDB連接到數(shù)據(jù)源；
l 先將數(shù)據(jù)拷貝到剪切板然后將剪貼板內(nèi)容粘貼到Excel worksheet中；
還有幾種方法向Excel中傳輸數(shù)據(jù)，比如運(yùn)行服務(wù)端應(yīng)用程序，可以不通過客戶端進(jìn)行批量數(shù)據(jù)處理。以下幾種方法是不通過自動(dòng)化進(jìn)行數(shù)據(jù)傳輸?shù)姆椒ǎ?div style="height:15px;">
l 傳輸數(shù)據(jù)到有tab符或者逗號(hào)分割的文本文件中，Execl可以分析這個(gè)文件將它放到worksheet中；
l 通過ADO（ActiveX Data Object）將數(shù)據(jù)傳輸?shù)絯orksheet中；
l 通過DDE（Dynamic Data Exchange）傳輸數(shù)據(jù)到worksheet中。
三、幾種常用方法的詳細(xì)說明。
1、 使用自動(dòng)化一個(gè)單元格一個(gè)單元格傳輸數(shù)據(jù)
可以使用自動(dòng)化，一次可以將數(shù)據(jù)傳輸?shù)絯orksheet一個(gè)單元格中，舉例如下：
dim oExcel As object
Dim oBook as object
dim oSheet as object
'在EXCEL中創(chuàng)建一個(gè)新的worksheet
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.workbooks.Add
'在新的workbook中向第一個(gè)worksheet的單元格添加數(shù)據(jù)
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value ="張三"
oSheet.Range("B1").Value="男"
oSheet.Range("A1:B1").font.Bold=true
oSheet.Range("A2").Value="李四"
oSheet.Range("B2").value="女"
'保存workbook并退出Excel
oBook.SaveAs "C:\book1.xls"
oExcel.quit
如果要傳輸?shù)臄?shù)據(jù)量不是很大，一個(gè)單元一個(gè)單元傳輸數(shù)據(jù)是比較好的方法?？梢苑奖愕膶?shù)據(jù)放到workbook任何地方并且還可以在運(yùn)行時(shí)格式化單元格。但是，當(dāng)傳輸?shù)絯orkbook中數(shù)據(jù)量很大則不建議使用這種方法，因?yàn)樵谶\(yùn)行結(jié)果中的每一個(gè)range對象都需要?jiǎng)?chuàng)建接口，所以這種方法速度較慢。此外，微軟的windows95和windows98接口有64k的限制，當(dāng)接口需求超過64k時(shí)，自動(dòng)化服務(wù)器（Excel）可能就會(huì)停止響應(yīng)或者提示內(nèi)存不足錯(cuò)誤。所以如果需要向Excel中傳輸?shù)臄?shù)據(jù)不是很大時(shí)建議使用這種方式。
2、 使用自動(dòng)化傳輸一個(gè)數(shù)組數(shù)據(jù)到wooksheet的range
可以一次將數(shù)組中的數(shù)據(jù)傳輸?shù)絯eeksheet中包括多個(gè)單元格組成的range中。舉例如下：
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'在Excel中創(chuàng)建一個(gè)新的wookbook
Set oExcel = CreateObject("excel.Application")
Set oBook=oExcel.WorkBooks.Add
'創(chuàng)建一個(gè)3列、100行的數(shù)組
Dim DataArray(1 to 100,1 to 3 ) as variant
Dim r as integer
For r = 1 to 100
DataArray(r,1) = "ORD" & Format(r,"0000")
DAtaArray(r,2)= Rnd() * 1000
DataArray(r,3) = DataArray(r,2) * 7
Next
'在worksheet的第一行加上標(biāo)題
Set oSheet = oBook.Worksheets(1)
oSheet,Range("A1:C1").valve = Array("定單號(hào)","數(shù)量","稅金")
'將數(shù)組中的數(shù)據(jù)傳輸?shù)絯orksheet中，數(shù)據(jù)的開始位置是A2
oSheet.Range("A2").Resize(100,3).valve=DataArray
'保存workbook，退出Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
如果通過數(shù)組而不是一個(gè)單元一個(gè)單元的傳輸大量的數(shù)據(jù)，則會(huì)發(fā)現(xiàn)性能會(huì)顯著提高，將數(shù)據(jù)傳輸?shù)?00個(gè)worksheet的單元格中的語句如下：
osheet.range("A2").Resize(100,3)Value=DataArray
這條語句需要兩個(gè)接口（由Range方法生成的Range對象需要一個(gè)接口，由resize方法生成的range對象也需要一個(gè)接口）。如果要是通過一個(gè)單元格一個(gè)單元格的傳輸數(shù)據(jù)則需要為range對象生成300個(gè)接口。不論什么情況，作為開發(fā)者要盡量使用批量數(shù)據(jù)傳輸以減少接口數(shù)量，從而提高應(yīng)用系統(tǒng)的性能。
3、使用自動(dòng)化傳輸ADO的recordset到wooksheet的range
Excel2000及以后版本引入了CopyFromRecordset方法，從而允許開發(fā)者將ADO的recordset傳輸?shù)絯orksheet的range中。下面的代碼演示了如何使用CopyFromRecordset將示例數(shù)據(jù)庫Northwind 中orders表的數(shù)據(jù)傳輸?shù)紼xcel中：
'生成一個(gè)包含Orders表所有記錄的 Recordset
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
sNWind = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'在Excel中創(chuàng)建新的workbook
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'向 Excel中傳輸數(shù)據(jù)
oSheet.Range("A1").CopyFromRecordset rs
'保存并退出Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
'關(guān)閉連接
rs.Close
conn.Close
4、使用剪切板
windows的剪切板也可以作為向worksheet傳輸數(shù)據(jù)的一種機(jī)制?？梢钥截惐籘AB符分割的列、回車符分割的行的字符串到worksheet的單元格中。VB通過clipboard對象傳輸數(shù)據(jù)到Excel的代碼如下：
'拷貝字符串到剪切板
Dim sData As String
sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
& "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
& "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
Clipboard.Clear
Clipboard.SetText sData
' 在 Excel中創(chuàng)建一個(gè)workbook
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'粘貼數(shù)據(jù)
oBook.Worksheets(1).Range("A1").Select
oBook.Worksheets(1).Paste
'保存并退出Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
5、生成一個(gè)帶有分割符的文本文件
Excel可以打開內(nèi)容被TAB符或逗號(hào)分割的文件，并且能正確的分解數(shù)據(jù)到相應(yīng)的單元格中。可以利用這一點(diǎn)來傳輸大量數(shù)據(jù)，如果可能的話再使用少量的自動(dòng)化。這種方式對SERVER/CLIENT應(yīng)用程序是一個(gè)很好的選擇，因?yàn)槲谋疚募诜?wù)端生成，然后在客戶端使用自動(dòng)化一次打開。
下面代碼演示如何使用ADO recordset生成被逗號(hào)分割的文本文件。
'生成一個(gè)包含Orders表所有記錄的 Recordset
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sData As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'將 recordset保存成以 tab分割符分割的文本文件
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
Open "C:\Test.txt" For Output As #1
Print #1, sData
Close #1
'關(guān)閉連接
rs.Close
conn.Close
'在Excel中打開一個(gè)新的文本文件
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
如果文本文件具有.csv擴(kuò)展名，Excel直接打開文件而不會(huì)出現(xiàn)導(dǎo)入向?qū)?，并且是假設(shè)文件是被逗號(hào)分割的。與此類似，如果文件具有.txt擴(kuò)展名，Excel自動(dòng)以TAB符為文件的分割符來分解文件。
在前一個(gè)例子，Excel通過shell語句啟動(dòng)，命名文件使用帶參數(shù)的命令行語句。沒有使用自動(dòng)化，然而，如果愿意的話，可以少量的使用自動(dòng)化打開文件和存儲(chǔ)文件。舉例如下：
'創(chuàng)建一個(gè)新的Excel實(shí)例
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
'打開文本文件
Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
'保存文件并退出
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
oExcel.Quit
6、通過ADO向worksheet中傳輸數(shù)據(jù)
使用 microsoft Jet OLE DB Provider可以向已經(jīng)存在的Excel workbook中的表添加數(shù)據(jù)，這里所謂的表就是已經(jīng)定義了名字的range。這個(gè)定義了名字的range的第一行必須包含標(biāo)題（或者叫字段名），接下來各行包含的是記錄。以下演示了通過如何在workbook中創(chuàng)建名為MyTalbe的表：
⑴、創(chuàng)建一個(gè)新的workbook
⑵、在sheet1的單元格A1:B1增加如下內(nèi)容
A1：FirstName B1：LastName
⑶、格式化B1為右對齊；
⑷、選中A1：B1
⑸、在插入菜單選擇Names然后選擇Define，輸入名稱MyTable然后點(diǎn)ok。
⑹、將新的workbook存為c:\book1.xls，退出Excel
使用ADO向MyTable增加記錄的代碼如下：
' 創(chuàng)建一個(gè)連接Book1.xls的connection對象
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Bill', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Joe', 'Thomas')"
conn.Close
果使用這種方式向表中添加記錄，workbook中的格式是自動(dòng)維護(hù)的，在上面的例子中，新增加記錄的B列自動(dòng)被格式化為右對齊。每一條增加的行自動(dòng)借用上一行的格式。
注意，如果一條記錄被增加到worksheet的單元格中，則在該單元格中的數(shù)據(jù)被覆蓋掉，換句話說就是新增加的記錄不是插入一行，而是覆蓋原先的行。
四、小結(jié)
本文通過對VB向Excel中傳輸數(shù)據(jù)的幾種常用方法分析和總結(jié)，提出若干建議。借鑒這些建議一定會(huì)給開發(fā)者的開發(fā)帶來便利。
