|
一、引言
作为微软旗下一款优秀的RAD工具VB和作为微软的办公组件的Excel因其强大的功能，方便的操作在各单位开发和办公中得搅似毡榈挠τ谩Ｎ顺浞掷枚叩那看蠊δ埽⑷硖峁┝撕芏嘀址椒ㄊ苟哂谢慕岷掀鹄础１疚亩酝ü?/span>VB向Excel传输数据的几种常用方法进行总结，并分析每种方法的优缺点及适用环境，以便开发人员选择合适的方法，提高开发效率。
二、概述
通常我们向Excel传输数据使用自动化。自动化提供了最好的便利性并且能在运行时对单元格进行格式化和其他各项设置。对于自动化可以使用几种不同的途径来传输数据：
l 一个单元格一个单元格的传输数据；
l 传输数组中的数据到一组单元格；
l 使用CopyFromRecordset方法传输ADO recordset到一组单元格；
l 通过在Excel worksheet中定义一个Querytable，这个querytable通过ODBC或者OLEDB连接到数据源；
l 先将数据拷贝到剪切板然后将剪贴板内容粘贴到Excel worksheet中；
还有几种方法向Excel中传输数据，比如运行服务端应用程序，可以不通过客户端进行批量数据处理。以下几种方法是不通过自动化进行数据传输的方法：
l 传输数据到有tab符或者逗号分割的文本文件中，Execl可以分析这个文件将它放到worksheet中；
l 通过ADO（ActiveX Data Object）将数据传输到worksheet中；
l 通过DDE（Dynamic Data Exchange）传输数据到worksheet中。
三、几种常用方法的详细说明。
1、 使用自动化一个单元格一个单元格传输数据
可以使用自动化，一次可以将数据传输到worksheet一个单元格中，举例如下：
dim oExcel As object
Dim oBook as object
dim oSheet as object
'在EXCEL中创建一个新的worksheet
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.workbooks.Add
'在新的workbook中向第一个worksheet的单元格添加数据
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
如果要传输的数据量不是很大，一个单元一个单元传输数据是比较好的方法。可以方便的将数据放到workbook任何地方并且还可以在运行时格式化单元格。但是，当传输到workbook中数据量很大则不建议使用这种方法，因为在运行结果中的每一个range对象都需要创建接口，所以这种方法速度较慢。此外，微软的windows95和windows98接口有64k的限制，当接口需求超过64k时，自动化服务器（Excel）可能就会停止响应或者提示内存不足错误。所以如果需要向Excel中传输的数据不是很大时建议使用这种方式。
2、 使用自动化传输一个数组数据到wooksheet的range
可以一次将数组中的数据传输到weeksheet中包括多个单元格组成的range中。举例如下：
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'在Excel中创建一个新的wookbook
Set oExcel = CreateObject("excel.Application")
Set oBook=oExcel.WorkBooks.Add
'创建一个3列、100行的数组
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的第一行加上标题
Set oSheet = oBook.Worksheets(1)
oSheet,Range("A1:C1").valve = Array("定单号","数量","税金")
'将数组中的数据传输到worksheet中，数据的开始位置是A2
oSheet.Range("A2").Resize(100,3).valve=DataArray
'保存workbook，退出Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
如果通过数组而不是一个单元一个单元的传输大量的数据，则会发现性能会显著提高，将数据传输到300个worksheet的单元格中的语句如下：
osheet.range("A2").Resize(100,3)Value=DataArray
这条语句需要两个接口（由Range方法生成的Range对象需要一个接口，由resize方法生成的range对象也需要一个接口）。如果要是通过一个单元格一个单元格的传输数据则需要为range对象生成300个接口。不论什么情况，作为开发者要尽量使用批量数据传输以减少接口数量，从而提高应用系统的性能。
3、使用自动化传输ADO的recordset到wooksheet的range
Excel2000及以后版本引入了CopyFromRecordset方法，从而允许开发者将ADO的recordset传输到worksheet的range中。下面的代码演示了如何使用CopyFromRecordset将示例数据库Northwind 中orders表的数据传输到Excel中：
'生成一个包含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中创建新的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中传输数据
oSheet.Range("A1").CopyFromRecordset rs
'保存并退出Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
'关闭连接
rs.Close
conn.Close
|
4、使用剪切板
windows的剪切板也可以作为向worksheet传输数据的一种机制。可以拷贝被TAB符分割的列、回车符分割的行的字符串到worksheet的单元格中。VB通过clipboard对象传输数据到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中创建一个workbook
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'粘贴数据
oBook.Worksheets(1).Range("A1").Select
oBook.Worksheets(1).Paste
'保存并退出Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
5、生成一个带有分割符的文本文件
Excel可以打开内容被TAB符或逗号分割的文件，并且能正确的分解数据到相应的单元格中。可以利用这一点来传输大量数据，如果可能的话再使用少量的自动化。这种方式对SERVER/CLIENT应用程序是一个很好的选择，因为文本文件在服务端生成，然后在客户端使用自动化一次打开。
下面代码演示如何使用ADO recordset生成被逗号分割的文本文件。
'生成一个包含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
'关闭连接
rs.Close
conn.Close
'在Excel中打开一个新的文本文件
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
如果文本文件具有.csv扩展名，Excel直接打开文件而不会出现导入向导，并且是假设文件是被逗号分割的。与此类似，如果文件具有.txt扩展名，Excel自动以TAB符为文件的分割符来分解文件。
在前一个例子，Excel通过shell语句启动，命名文件使用带参数的命令行语句。没有使用自动化，然而，如果愿意的话，可以少量的使用自动化打开文件和存储文件。举例如下：
'创建一个新的Excel实例
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中传输数据
使用 microsoft Jet OLE DB Provider可以向已经存在的Excel workbook中的表添加数据，这里所谓的表就是已经定义了名字的range。这个定义了名字的range的第一行必须包含标题（或者叫字段名），接下来各行包含的是记录。以下演示了通过如何在workbook中创建名为MyTalbe的表：
⑴、创建一个新的workbook
⑵、在sheet1的单元格A1:B1增加如下内容
A1：FirstName B1：LastName
⑶、格式化B1为右对齐；
⑷、选中A1：B1
⑸、在插入菜单选择Names然后选择Define，输入名称MyTable然后点ok。
⑹、将新的workbook存为c:\book1.xls，退出Excel
使用ADO向MyTable增加记录的代码如下：
' 创建一个连接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中的格式是自动维护的，在上面的例子中，新增加记录的B列自动被格式化为右对齐。每一条增加的行自动借用上一行的格式。
注意，如果一条记录被增加到worksheet的单元格中，则在该单元格中的数据被覆盖掉，换句话说就是新增加的记录不是插入一行，而是覆盖原先的行。
四、小结
本文通过对VB向Excel中传输数据的几种常用方法分析和总结，提出若干建议。借鉴这些建议一定会给开发者的开发带来便利。
(作者：孙延国)
|
