Exporting multiple datagridviews to different tabs of excelsheet

Question

I'm a newbie to visual studio.

I want to export different datagridviews from multiple forms to an excel workbook as different sheets on the same workbook based on whether it is checked in checkedlist box.

Basically I am doing a daily checklist for our school on location basis where the user can export checklist floor wise on the corresponding form of each floor, but also can export a multiple sheet workbook containing diff floors as per its checked in checklistbox, any help please? I am stuck at checkedlistbox. currently i am doing as below: but gives me an exception at the second sheet.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

Dim excel As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()

Dim workbook As Microsoft.Office.Interop.Excel._Workbook = excel.Workbooks.Add(Type.Missing)

Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing

Me.Cursor = Cursors.WaitCursor

Dim sheetnumber As Integer = 1

If CheckedListBox1.GetItemChecked(0) = True Then

    worksheet = workbook.Sheets(sheetnumber)

    sheetnumber = sheetnumber + 1

    worksheet.Name = "Anim_Check List_"

    Dim cellRowIndex As Integer = 1

    Dim cellColumnIndex As Integer = 1

    For i As Integer = 0 To Form7.DataGridView1.Rows.Count - 1

        For j As Integer = 0 To Form7.DataGridView1.Columns.Count - 1

            If cellRowIndex = 1 Then

                worksheet.Cells(cellRowIndex, cellColumnIndex) = Form7.DataGridView1.Columns(j).HeaderText

            Else
                worksheet.Cells(cellRowIndex, cellColumnIndex) = Form7.DataGridView1.Rows(i).Cells(j).Value.ToString()

            End If

            cellColumnIndex += 1

        Next

        cellColumnIndex = 1

        cellRowIndex += 1

    Next

End If

If CheckedListBox1.GetItemChecked(1) = True Then

    workbook.Worksheets.Add(sheetnumber)

    excel.Worksheets(sheetnumber).activate

    sheetnumber = sheetnumber + 1

    worksheet.Name = "Edits 1-5_"

    Dim cellRowIndex As Integer = 1

    Dim cellColumnIndex As Integer = 1

    For i As Integer = 0 To Form8.DataGridView1.Rows.Count - 1

        For j As Integer = 0 To Form8.DataGridView1.Columns.Count - 1

            If cellRowIndex = 1 Then

                worksheet.Cells(cellRowIndex, cellColumnIndex) = Form8.DataGridView1.Columns(j).HeaderText

            Else

                worksheet.Cells(cellRowIndex, cellColumnIndex) = Form8.DataGridView1.Rows(i).Cells(j).Value.ToString()

            End If

            cellColumnIndex += 1

        Next

        cellColumnIndex = 1

        cellRowIndex += 1

    Next

End If

Dim saveDialog As New SaveFileDialog()

saveDialog.FileName = workbook.Name

saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"

saveDialog.FilterIndex = 1


If saveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

    workbook.SaveAs(saveDialog.FileName)

    MessageBox.Show("Export Successful")

End If

End Sub

JohnG JohnG · Accepted Answer · 2018-02-19T00:56:12

It is very difficult to understand what you are trying to do. From what I can decipher, there appears to be three (3) different WindowsForms open. The main active form has a Button and a CheckedListBox. The two other forms (Form7, Form8) each have a DataGridView with some data.

I assume that the CheckListBox allows the user to select which form to use when exporting to Excel. Example, if the user checks the first option, then clicks the button, then the DataGridView on Form7 will be exported to the Excel workbook. If the second option is checked then Form8’s DataGridView will be exported to the same workbook.

I hope I have this correct. To help, I would recommend you break the problem down into smaller pieces. Example, looking at the code in both “if” statements that check for a checked state in the checked list box… it is clear they are doing the same thing. The only difference is the DataGridView and the name of the worksheet.

It looks like a method, that took a workbook to add the sheet to, a DataGridView to export and finally a string for the worksheet name… might come in handy.

Private Sub AddGridToWorkbook(workbook As Workbook, dgv As DataGridView, sheetName As String)

This method would add a new worksheet to the given workbook using the given sheetName as the name for the new worksheet. Lastly the method would loop through the given DataGridView and export the values to the new worksheet.

The current posted code is trying to print the header row inside the loop through the grids rows. This will miss the first row of data. The header row for the new excel worksheet needs to be exported separately and outside the loop through the grids rows.

Lastly, I am curious what Form7 and Form8 are… If these are WindowsForm “Classes”… then the statement:…

worksheet.Cells(cellRowIndex, cellColumnIndex) = Form7.DataGridView1.Rows(i).Cells(j).Value.ToString()

This will not work as you expect… Form7 is a “Class” and you are using it like an instantiated object.

In my example below, I have two WindowsForms “Classes” called “Form7” and “Form8”. When the main form loads, I instantiate and show these two forms. Example: create the global variable forms.

Dim form7 As Form7 = New Form7()
Dim form8 As Form8 = New Form8()

Then show the forms on load.

form7.Show()
form8.Show()

Now the previous line of code will work using the instantiated “Form7” object named form7.

worksheet.Cells(cellRowIndex, cellColumnIndex) = form7.DataGridView1.Rows(i).Cells(j).Value.ToString()

Revised code to export the grids

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    excel.Visible = True
    Dim workbook = excel.Workbooks.Add()

    If CheckedListBox1.GetItemChecked(0) = True Then
        AddGridToWorkbook(workbook, form7.DataGridView1, "Anim_Check List_")
    End If

    If CheckedListBox1.GetItemChecked(1) = True Then
        AddGridToWorkbook(workbook, form8.DataGridView1, "Edits 1-5_")
    End If

    Dim saveDialog As New SaveFileDialog()
    saveDialog.FileName = workbook.Name
    saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
    saveDialog.FilterIndex = 1

    If saveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
        workbook.SaveAs(saveDialog.FileName)
        MessageBox.Show("Export Successful")
    End If
End Sub

Private Sub AddGridToWorkbook(workbook As Workbook, dgv As DataGridView, sheetName As String)
    Try
        Dim worksheet As Worksheet = workbook.Worksheets.Add()
        worksheet.Name = sheetName
        AddHeaderRow(worksheet, dgv)
        For row As Integer = 0 To dgv.Rows.Count - 1
            For col As Integer = 0 To dgv.Columns.Count - 1
                If dgv.Rows(row).Cells(col).Value IsNot Nothing Then
                    worksheet.Cells(row + 2, col + 1) = dgv.Rows(row).Cells(col).Value.ToString()
                End If
            Next
        Next
    Catch ex As Exception
        MessageBox.Show("Ex: " + ex.Message)
    End Try
End Sub

Private Sub AddHeaderRow(worksheet As Worksheet, dgv As DataGridView)
    For col As Integer = 0 To dgv.Columns.Count - 1
        worksheet.Cells(1, col + 1) = dgv.Columns(col).HeaderText
    Next
End Sub

Hope this helps

Exporting multiple datagridviews to different tabs of excelsheet

1 Answers