http://support.microsoft.com/kb/306022
<script type=text/javascript>function loadTOCNode(){}</script>文章编号 | : | 306022 |
最后修改 | : | 2007年1月17日 |
修订 | : | 6.1 |
有关本文的 Microsoft Visual C# .NET 版本,请参阅
306023
(http://support.microsoft.com/kb/306023/)。
本页
概要
<script type=text/javascript>loadTOCNode(1, 'summary');</script>
本文逐步介绍了多种从 Visual Basic .NET 程序向 Excel 2002 传输数据的方法。本文还提供了每种方法的优点和缺点,以便您可以选择最适合您的情况的解决方案。
利用“自动化”,您可以使用多种方法来传输数据:
还可以使用多种未必需要利用“自动化”来向 Excel 传输数据的方法。如果您正在运行服务器端程序,这可以是一种将批量数据处理从客户端移走的好方法。
在不利用“自动化”的情况下,可以使用下列方法来传输数据:
此外,Microsoft Windows 95、Microsoft Windows 98 以及 Microsoft Windows Millennium Edition (Me) 都对接口请求有 64 KB 的限制。如果您具有 64 KB 或更多的接口请求,则“自动化”服务器 (Excel) 可能会停止响应,或者您可能会收到指出内存不足的错误消息。 有关更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
有关如何利用 Visual Basic .NET 实现 Excel 自动化的更多信息以及示例,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
下面的代码演示了如何自动运行 Excel 2000 或 2002,以使用 Northwind 示例数据库中的数据在 Excel 工作表中创建新的
QueryTable。
下面的代码说明了如何从利用 ADO.NET 读取的数据生成制表符分隔的文本文件:
下面的代码向 Book7.xls 中的表添加了两个新记录。在此情况下,该表是 Sheet1。
请注意,当一个记录被添加到工作表中的一个或多个单元格时,它会覆盖那些单元格以前包含的任何数据。换句话说,当添加新记录时,工作表中的行不会被“下移”。如果您计划使用 ADO.NET 插入新记录,当您设计工作表上的数据的布局时,一定要记住这一点。
有关如何使用 ADO.NET 的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
有关如何将 XML 与 Excel 2002 一起使用的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
概述
<script type=text/javascript>loadTOCNode(2, 'summary');</script> 最常用于向 Excel 工作簿传输数据的技术是 自动化。利用“自动化”,您可以调用特定于 Excel 任务的方法和属性。“自动化”给您提供了指定数据在工作簿中所处位置的最大的灵活性,以及将工作簿格式化和在运行时进行各种设置的能力。利用“自动化”,您可以使用多种方法来传输数据:
• | 逐个单元格地传输数据。 |
• | 将数组中的数据传输到单元格区域。 |
• | 使用 CopyFromRecordset 方法向单元格区域传输 ADO 记录集中的数据。 |
• | 在 Excel 工作表上创建一个 QueryTable 对象,该对象包含对 ODBC 或 OLEDB 数据源进行查询的结果。 |
• | 将数据传输到剪贴板,然后将剪贴板内容粘贴到 Excel 工作表中。 |
在不利用“自动化”的情况下,可以使用下列方法来传输数据:
• | 将数据传输到制表符分隔的或逗号分隔的文本文件,然后 Excel 可以将该文本文件分析为工作表上的单元格。 |
• | 使用 ADO.NET 将数据传输到工作表。 |
• | 将 XML 数据传输到 Excel(仅限于 2002 版)以提供可以被格式化和排列为行和列的数据。 |
方法
<script type=text/javascript>loadTOCNode(2, 'summary');</script>使用“自动化”功能逐单元格传输数据
<script type=text/javascript>loadTOCNode(3, 'summary');</script> 利用“自动化”,您可以按如下方式逐个单元格地向工作表传输数据: Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook.
oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "Doe"
oSheet.Range("B2").Value = "John"
'Save the Workbook and quit Excel.
oBook.SaveAs(sSampleFolder & "Book1.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
如果没有大量需要传输的数据,逐个单元格地传输数据可能是一个可以接受的方法。您可以灵活地将数据放到工作簿中的任何地方,并可以在运行时根据条件对单元格进行格式设置。然而,如果您具有大量需要传输到 Excel 工作簿的数据,则不建议使用这种方法。您在运行时获取的每个 Range 对象都会产生一个接口请求。因此,以这种方式传输数据速度较慢。此外,Microsoft Windows 95、Microsoft Windows 98 以及 Microsoft Windows Millennium Edition (Me) 都对接口请求有 64 KB 的限制。如果您具有 64 KB 或更多的接口请求,则“自动化”服务器 (Excel) 可能会停止响应,或者您可能会收到指出内存不足的错误消息。 有关更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
216400
(http://support.microsoft.com/kb/216400/) PRB:交叉处理 COM 自动化会在 Win95/98 上挂起客户端应用程序
需要再次强调的是,逐个单元格地传输数据仅对少量数据而言才可以接受。如果您必须向 Excel 传输大数据集,则应考虑使用本文中讨论的其他方法之一来批量地传输数据。
有关如何利用 Visual Basic .NET 实现 Excel 自动化的更多信息以及示例,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
301982
(http://support.microsoft.com/kb/301982/) 如何在 Visual Basic .NET 中使 Microsoft Excel 自动运行
使用“自动化”功能将数据数组传输到工作表上的区域
<script type=text/javascript>loadTOCNode(3, 'summary');</script> 可以按如下方式同时向由多个单元格组成的区域传输数据数组: Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
'Create an array with 3 columns and 100 rows.
Dim DataArray(99, 2) As Object
Dim r As Integer
For r = 0 To 99
DataArray(r, 0) = "ORD" & Format(r + 1, "0000")
DataArray(r, 1) = Rnd() * 1000
DataArray(r, 2) = DataArray(r, 1) * 0.07
Next
'Add headers to the worksheet on row 1.
oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Order ID"
oSheet.Range("B1").Value = "Amount"
oSheet.Range("C1").Value = "Tax"
'Transfer the array to the worksheet starting at cell A2.
oSheet.Range("A2").Resize(100, 3).Value = DataArray
'Save the Workbook and quit Excel.
oBook.SaveAs(sSampleFolder & "Book2.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
如果您使用数组而不是逐个单元格地传输数据,则在传输大量数据时,传输性能会大大提高。请考虑前面代码中的下面一行,该行将数据传输到工作表中的 300 个单元格:
oSheet.Range("A2").Resize(100, 3).Value = DataArray
此行代表了两个接口请求:一个请求是针对 Range 方法返回的 Range 对象,另一个请求是针对 Resize 方法返回的 Range 对象。相比之下,逐个单元格地传输数据却需要对 Range 对象发出 300 个接口请求。只要有可能,您就可以从批量地传输数据以及减少所发出的接口请求的数量当中受益。
使用“自动化”功能将 ADO 记录集传输到工作表区域
<script type=text/javascript>loadTOCNode(3, 'summary');</script> Excel 2000 和 Excel 2002 的对象模型提供了 CopyFromRecordset 方法,用于向工作表上的区域传输 ADO 记录集。下面的代码说明了如何使用 CopyFromRecordset 方法使 Excel 自动运行,以传输 Northwind 示例数据库中的“订单”表的内容: 'Create a Recordset from all the records in the Orders table.
Dim sNWind As String
Dim conn As New ADODB.Connection()
Dim rs As ADODB.Recordset
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNorthwind & ";")
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)
'Create a new workbook in Excel.
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
'Transfer the field names to Row 1 of the worksheet:
'Note: CopyFromRecordset copies only the data and not the field
' names, so you can transfer the fieldnames by traversing the
' fields collection.
Dim n As Int32
For n = 1 To rs.Fields.Count
oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name
Next
'Transfer the data to Excel.
oSheet.Range("A2").CopyFromRecordset(rs)
'Save the workbook and quit Excel.
oBook.SaveAs(sSampleFolder & "Book3.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
'Close the connection
rs.Close()
conn.Close()
注意:CopyFromRecordset 只能与 ADO Recordset 对象一起使用。使用 ADO.NET 创建的 DataSet 不能与 CopyFromRecordset 方法一起使用。以下几节中的多个示例演示了如何利用 ADO.NET 向 Excel 传输数据。
使用“自动化”在工作表上创建 QueryTable 对象
<script type=text/javascript>loadTOCNode(3, 'summary');</script> QueryTable 对象代表了一个表,该表是用从外部数据源返回的数据生成的。当您自动运行 Excel 时,可以通过提供指向 OLEDB 或 ODBC 数据源的连接字符串和 SQL 字符串来创建 QueryTable。Excel 将生成记录集并将该记录集插入到工作表中您所指定的位置。使用 QueryTable 对象提供了下列优于 CopyFromRecordset 方法的优点:• | Excel 处理记录集的创建并将其放置到工作表中。 |
• | 可以利用 QueryTable 对象保存查询,以便以后可以刷新它以获取更新的记录集。 |
• | 当向工作表中添加新的 QueryTable 时,可以指定将工作表上的单元格中已经存在的数据移位,以便放置新数据(有关详细信息,请查看 RefreshStyle 属性)。 |
'Create a new workbook in Excel.
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
'Create the QueryTable object.
Dim oQryTable As Object
oQryTable = oSheet.QueryTables.Add( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNorthwind & ";", oSheet.Range("A1"), _
"Select * from Orders")
oQryTable.RefreshStyle = 2 ' xlInsertEntireRows = 2
oQryTable.Refresh(False)
'Save the workbook and quit Excel.
oBook.SaveAs(sSampleFolder & "Book4.xls")
oQryTable = Nothing
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
使用剪贴板
<script type=text/javascript>loadTOCNode(3, 'summary');</script> 可以使用剪贴板来向工作表传输数据。要将数据粘贴到工作表上的多个单元格中,可以复制具有以下格式的字符串:在该字符串中,列由制表符分隔,行由回车符分隔。下面的代码说明了 Visual Basic .NET 如何使用剪贴板来向 Excel 传输数据。 'Copy a string to the Clipboard.
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"
System.Windows.Forms.Clipboard.SetDataObject(sData)
'Create a workbook in Excel.
Dim oExcel As Object
Dim oBook As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
'Paste the data.
oBook.Worksheets(1).Range("A1").Select()
oBook.Worksheets(1).Paste()
'Save the workbook and quit Excel.
oBook.SaveAs(sSampleFolder & "Book5.xls")
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
创建可由 Excel 分析为行和列的带分隔符的文本文件
<script type=text/javascript>loadTOCNode(3, 'summary');</script> Excel 可以打开由制表符或逗号分隔的文件并正确地将数据分析到单元格中。当您希望向工作表传输大量数据而只使用少量(如果有的话)自动化功能时,可以使用此功能。这对于客户端-服务器程序而言可能是一个好方法,因为文本文件可以在服务器端生成。然后,可以在客户端根据需要使用“自动化”来打开文本文件。下面的代码说明了如何从利用 ADO.NET 读取的数据生成制表符分隔的文本文件:
'Connect to the data source.
Dim objConn As New System.Data.OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sNorthwind & ";")
objConn.Open()
'Execute a command to retrieve all records from the Employees table.
Dim objCmd As New System.Data.OleDb.OleDbCommand( _
"Select * From Employees", objConn)
Dim objReader As System.Data.OleDb.OleDbDataReader
objReader = objCmd.ExecuteReader()
'Read the records in the dataset and write select fields to the
'output file.
FileOpen(1, sSampleFolder & "Book6.txt", OpenMode.Output)
Dim i As Integer, s As String
While objReader.Read()
'Loop through first 6 fields and concatenate
'each field, separated by a tab, into s variable.
s = ""
For i = 0 To 5
If Not objReader.IsDBNull(i) Then
If i = 0 Then 'field 1 is EmployeeId
s = s & objReader.GetInt32(i).ToString
ElseIf i = 5 Then 'field 6 is BirthDate
s = s & objReader.GetDateTime(i)
Else 'field is a text field
s = s & objReader.GetString(i)
End If
End If
s = s & Microsoft.VisualBasic.ControlChars.Tab
Next
PrintLine(1, s)
End While
FileClose(1)
'Close the reader and the connection.
objReader.Close()
objConn.Close()
在前面的代码没有使用“自动化”。然而,您可以按如下方式使用最少量的“自动化”来打开文本文件,并以 Excel 工作簿格式保存该文件。
'Create a new instance of Excel.
Dim oExcel As Object
oExcel = CreateObject("Excel.Application")
'Open the text file and save it in the Excel workbook format.
oExcel.Workbooks.OpenText(sSampleFolder & "Book6.txt", _
, , , -4142, , True) 'xlTextQualifierNone=-4142
oExcel.ActiveWorkbook.SaveAs(sSampleFolder & "Book6.xls", _
-4143) 'xlWorkbookNormal = -4143
'Quit Excel.
oExcel.Quit()
oExcel = Nothing
GC.Collect()
使用 ADO.NET 将数据传输到工作表
<script type=text/javascript>loadTOCNode(3, 'summary');</script> 您可以使用 Microsoft Jet OLE DB 提供程序向现有 Excel 工作簿中的表中添加记录。Excel 中的“表”只是由单元格组成的区域;该区域可能具有规定的名称。通常,区域的第一行包含标题(或字段名),该区域中所有以后的行都包含记录。下面的代码向 Book7.xls 中的表添加了两个新记录。在此情况下,该表是 Sheet1。
'Establish a connection to the data source.
Dim sConnectionString As String
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sSampleFolder & _
"Book7.xls;Extended Properties=Excel 8.0;"
Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
objConn.Open()
'Add two records to the table.
Dim objCmd As New System.Data.OleDb.OleDbCommand()
objCmd.Connection = objConn
objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" & _
" values ('Bill', 'Brown')"
objCmd.ExecuteNonQuery()
objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" & _
" values ('Joe', 'Thomas')"
objCmd.ExecuteNonQuery()
'Close the connection.
objConn.Close()
当您按所示的方法利用 ADO.NET 添加记录时,工作簿中的格式将被保持。添加到行中的每个记录都将继承它前面的行的格式。例如,添加到列 B 中的新字段的格式为右对齐,因为单元格 B1 是右对齐的。 请注意,当一个记录被添加到工作表中的一个或多个单元格时,它会覆盖那些单元格以前包含的任何数据。换句话说,当添加新记录时,工作表中的行不会被“下移”。如果您计划使用 ADO.NET 插入新记录,当您设计工作表上的数据的布局时,一定要记住这一点。
有关如何使用 ADO.NET 的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
301075
(http://support.microsoft.com/kb/301075/) 如何使用 ADO.NET 和 Visual Basic .NET 连接到数据库并运行命令
301216
(http://support.microsoft.com/kb/301216/) 如何使用 Visual Basic .NET 从数据库填充 DataSet 对象
301248
(http://support.microsoft.com/kb/301248/) 如何通过使用 Visual Basic .NET 从 DataSet 对象中更新数据库
有关如何将 Jet OLE DB 提供程序与 Excel 数据源一起使用的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中的文章:
278973
(http://support.microsoft.com/kb/278973/) ExcelADO 演示如何使用 ADO 读写 Excel 工作簿中的数据
257819
(http://support.microsoft.com/kb/257819/) 如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据
传输 XML 数据(仅限于 Excel 2002)
<script type=text/javascript>loadTOCNode(3, 'summary');</script> Excel 2002 可以打开格式完好的任何 XML 文件。XML 文件可以直接从“文件”菜单上的“打开”命令打开,也可以使用 Workbooks 集合的 Open 或 OpenXML 方法以编程方式打开。如果您创建供在 Excel 中使用的 XML 文件,您还可以创建样式表来设置数据的格式。有关如何将 XML 与 Excel 2002 一起使用的更多信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
307021
(http://support.microsoft.com/kb/307021/) 如何使用 Visual Basic .NET 向 Microsoft Excel 2002 传输 XML 数据
288215
(http://support.microsoft.com/kb/288215/) INFO:Microsoft Excel 2002 和 XML
创建完整的 Visual Basic .NET 项目示例
<script type=text/javascript>loadTOCNode(2, 'summary');</script>1. | 创建一个新文件夹以保存该示例将为您创建的 Excel 工作簿,然后将该文件夹命名为 C:/Exceldata/。 | ||||||||
2. | 按照下列步骤创建一个新工作簿,以供该示例向其中写入数据:
| ||||||||
3. | 启动 Visual Studio .NET。在“文件”菜单上,单击“新建”,然后单击“项目”。在“Visual Basic 项目”下,选择“Windows 应用程序”。默认情况下会创建 Form1。 | ||||||||
4. | 添加对 Excel 对象库的引用。为此,请按照下列步骤操作:
| ||||||||
5. | 向 Form1 添加一个 Combo Box 控件和一个 Button 控件。 | ||||||||
6. | 将以下代码添加到 Form1。 注意:如果您没有将 Office 安装到默认文件夹 (C:/Program Files/Microsoft Office),请更改代码示例中的 sNorthwind 常数以匹配您的 Northwind.mdb 安装路径。 | ||||||||
7. | 将以下代码添加到 Form1.vb 的顶部。
| ||||||||
8. | 按 F5 生成并运行该示例。 |