Fill the Excel multi-cell area with VB. NET

To fill a multi-cell area, instead of filling one cell at a time, you canRangeObjectValueSet the attribute to a two-dimensional array. Similarly, you can useValueAttribute to retrieve a two-dimensional array of values of multiple cells at a time. The following steps describe how to set and retrieve data using a two-dimensional array.

Generate an automatic client for Microsoft Excel

1. Start Microsoft Visual Studio. NET.
2. InFileClickNewAnd then clickProject. Select from Visual Basic project typeWindows Applications. By default, form1 is created.
3. AddMicrosoft Excel Object Library. To do this, follow these steps:
   1. InProjectClickAdd reference.
   2. InComTab, findMicrosoft Excel Object LibraryAnd then clickSelect.
4. InViewChooseToolboxTo display the toolbox. Add two buttons and a check box to form1.
5. SetNameSet the property to fillwithstrings.
6. Double-clickButton1. The code window of the form appears.
7. Add the following code to the top of form1.vb:

   Imports Microsoft.Office.Interop

8. In the code window

       Private Sub Button1_Click(ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles Button1.Click    End Sub

   Replace:

       '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. Return to the design view of form1 and double-clickButton2.

10.     Private Sub Button2_Click(ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles Button2.Click    End Sub

    Replace:

        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

Test the automated client

1. Press F5 to generate and run the sample program.
2. ClickButton1. Microsoft Excel starts and opens a new Workbook. The cell A1: E5 of the first worksheet is filled with numerical data from an array.
3. ClickButton2. This program will retrieve the data in cell A1: E5 and fill it in a new array, and then display the result in a message box.
4. SelectFillwithstringsAnd then clickButton1Fill cell A1: E5 with string data.