学步园

要填充一个多单元格区域而又不是一次一个单元格地进行填充，可以将 Range 对象的 Value 属性设置为二维数组。同样，可通过使用 Value 属性一次检索多个单元格的值的二维数组。下面的步骤阐述了这一使用二维数组设置和检索数据的过程。

为 Microsoft Excel 生成自动化客户端

1. 启动 Microsoft Visual Studio .NET。
2. 在文件菜单上，单击新建，然后单击项目。从 Visual Basic 项目类型中选择 Windows 应用程序。默认情况下会创建 Form1。
3. 添加对 Microsoft Excel 对象库的引用。为此，请按照下列步骤操作：
   1. 在项目菜单上，单击添加引用。
   2. 在 COM 选项卡上，找到 Microsoft Excel 对象库，然后单击选择。
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。