要填充一个多单元格区域而又不是一次一个单元格地进行填充，可以将 Range 对象的 Value 属性设置为二维数组。同样，可通过使用 Value 属性一次检索多个单元格的值的二维数组。下面的步骤阐述了这一使用二维数组设置和检索数据的过程。 为 Microsoft Excel 生成自动化客户端 1. 启动 Microsoft Visual Studio .NET。 2. 在文件菜单上，单击新建，然后单击项目。从 Visual Basic 项目类型中选择 Windows 应用程序。默认情况下会创建 Form1。 3. 添加对 Microsoft Excel 对象库的引用。为此，请按照下列步骤操作： a. 在项目菜单上，单击添加引用。 b. 在 COM 选项卡上，找到 Microsoft Excel 对象库，然后单击选择。注意：Microsoft Office 2003 包含主 Interop 程序集 (PIA)。Microsoft Office XP 不包含 PIA，但您可以下载 PIA。 有关 Office XP PIA 的其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 328912 (http://support.microsoft.com/kb/328912/EN-US/) INFO：Microsoft Office XP PIA 可供下载 c. 在添加引用对话框中单击确定以接受您的选择。如果系统提示您为选定的库生成包装，请单击是。 4. 在视图菜单上，选择工具箱以显示工具箱。向 Form1 添加两个按钮和一个复选框。 5. 将复选框的 Name 属性设置为 FillWithStrings。 6. 双击 Button1。将出现该窗体的代码窗口。 7. 将以下代码添加到 Form1.vb 的顶部：Imports Microsoft.Office.Interop 8. 在代码窗口中，将以下代码 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click End Sub 替换为： 'Keep the application object and the workbook object global, so you can 'retrieve the data in Button2_Click that was set in Button1_Click. Dim objApp As Excel.Application Dim objBook As Excel._Workbook Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim objBooks As Excel.Workbooks Dim objSheets As Excel.Sheets Dim objSheet As Excel._Worksheet Dim range As Excel.Range ' Create a new instance of Excel and start a new workbook. objApp = New Excel.Application() objBooks = objApp.Workbooks objBook = objBooks.Add objSheets = objBook.Worksheets objSheet = objSheets(1) 'Get the range where the starting cell has the address 'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols. range = objSheet.Range("A1", Reflection.Missing.Value) range = range.Resize(5, 5) If (Me.FillWithStrings.Checked = False) Then 'Create an array. Dim saRet(5, 5) As Double 'Fill the array. Dim iRow As Long Dim iCol As Long For iRow = 0 To 5 For iCol = 0 To 5 'Put a counter in the cell. saRet(iRow, iCol) = iRow * iCol Next iCol Next iRow 'Set the range value to the array. range.Value = saRet Else 'Create an array. Dim saRet(5, 5) As String 'Fill the array. Dim iRow As Long Dim iCol As Long For iRow = 0 To 5 For iCol = 0 To 5 'Put the row and column address in the cell. saRet(iRow, iCol) = iRow.ToString() + "|" + iCol.ToString() Next iCol Next iRow 'Set the range value to the array. range.Value = saRet End If 'Return control of Excel to the user. objApp.Visible = True objApp.UserControl = True 'Clean up a little. range = Nothing objSheet = Nothing objSheets = Nothing objBooks = Nothing End Sub 9. 返回到 Form1 的设计视图并双击 Button2。 10. ÔÚ´úÂë´°¿ÚÖУ¬½«ÒÔÏ´úÂë Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click End Sub 替换为： Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Dim objSheets As Excel.Sheets Dim objSheet As Excel._Worksheet Dim range As Excel.Range 'Get a reference to the first sheet of the workbook. On Error Goto ExcelNotRunning objSheets = objBook.Worksheets objSheet = objSheets(1) ExcelNotRunning: If (Not (Err.Number = 0)) Then MessageBox.Show("Cannot find the Excel workbook. Try clicking Button1 to " + _ "create an Excel workbook with data before running Button2.", _ "Missing Workbook?") 'We cannot automate Excel if we cannot find the data we created, 'so leave the subroutine. Exit Sub End If 'Get a range of data. range = objSheet.Range("A1", "E5") 'Retrieve the data from the range. Dim saRet(,) As Object saRet = range.Value 'Determine the dimensions of the array. Dim iRows As Long Dim iCols As Long iRows = saRet.GetUpperBound(0) iCols = saRet.GetUpperBound(1) 'Build a string that contains the data of the array. Dim valueString As String valueString = "Array Data" + vbCrLf Dim rowCounter As Long Dim colCounter As Long For rowCounter = 1 To iRows For colCounter = 1 To iCols 'Write the next value into the string. valueString = String.Concat(valueString, _ saRet(rowCounter, colCounter).ToString() + ", ") Next colCounter 'Write in a new line. valueString = String.Concat(valueString, vbCrLf) Next rowCounter 'Report the value of the array. MessageBox.Show(valueString, "Array Values") 'Clean up a little. range = Nothing objSheet = Nothing objSheets = Nothing End Sub 对自动化客户端进行测试 1. 按 F5 键生成并运行该示例程序。 2. 单击 Button1。Microsoft Excel 将启动并打开一个新工作簿，而且第一个工作表的单元格 A1:E5 已填充了来自某个数组的数值数据。 3. 单击 Button2。该程序将检索单元格 A1:E5 中的数据并将其填充到一个新的数组中，然后将结果显示在一个消息框中。 4. 选择 FillWithStrings，然后单击 Button1 用字符串数据填充单元格 A1:E5。

评论