none
Exporting multiple datagrid views to excel RRS feed

  • Question

  • i am trying to export multiple datagrid views from different windows forms to a single excel workbook but to diff sheets of the same excel. until now i can only export each datagrid views to each excel file. any suggestions pls? the below didnt work !!!!

    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
                excel.Worksheets(2).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
    End Class

    Friday, February 16, 2018 3:03 PM

All replies

  • Where does the data for theDataGridView come from? You are more likely to be successful if you get the data from there.

    The following are in different places:

    worksheet.Name = "Anim_Check List_"
    .
    .
    .
    worksheet.Name = "Edits 1-5_"

    You are not giving the worksheets unique names, right?



    Sam Hobbs
    SimpleSamples.Info


    Friday, February 16, 2018 8:56 PM
  • Check how you are using "sheetnumber"; you might not be incrementing and using it in the right places.

    Also I think the following is executed only once and only if "CheckedListBox1" is checked.

    worksheet = workbook.Sheets(sheetnumber)



    Sam Hobbs
    SimpleSamples.Info

    Friday, February 16, 2018 9:04 PM
  • yes am trying to give worksheets unique names of the same excel file , and the datagrid views are in diff forms, which are getting values from the form itself.
    Saturday, February 17, 2018 5:56 AM
  • this is what i intent to do, if the corresponding checklistbox is checked, export the data available in the corresponding form to a separate sheet, so if multiple items are checked it exports excel file with multiple sheets.....
    Saturday, February 17, 2018 6:00 AM
  • Hello shenoyaries,

    >> export the data available in the corresponding form to a separate sheet, so if multiple items are checked it exports excel file with multiple sheets.....

    So once your select three items in the CheckedListBox, then you need selected data from three forms and then use three sheets to get the data one by one, right?

    Please check if below code could work for you.

     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
            Dim objdataGridView As DataGridView
            Me.Cursor = Cursors.WaitCursor
    
            For i = 0 To CheckedListBox1.Items.Count - 1
                If CheckedListBox1.GetItemChecked(i) = True Then
                    worksheet = workbook.Sheets.Add
                    worksheet.Name = CheckedListBox1.Items(i).ToString()
                    Select Case i
                        Case 1 : objdataGridView = DataGridView1
                        Case 2 : objdataGridView = DataGridView2
                        Case 3 : objdataGridView = DataGridView3
                    End Select
                    DataGridToWorkSheet(worksheet, objdataGridView)
                End If
            Next
    
            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 DataGridToWorkSheet(worksheet As Microsoft.Office.Interop.Excel.Worksheet, dataGridView1 As DataGridView)
            Dim cellRowIndex As Integer = 1
            Dim cellColumnIndex As Integer = 1
            For i As Integer = 0 To dataGridView1.Rows.Count - 1
                For j As Integer = 0 To dataGridView1.Columns.Count - 1
                    If cellRowIndex = 1 Then
                        worksheet.Cells(cellRowIndex, cellColumnIndex) = dataGridView1.Columns(j).HeaderText
                    Else
                        worksheet.Cells(cellRowIndex, cellColumnIndex) = dataGridView1.Rows(i).Cells(j).Value.ToString()
                    End If
                    cellColumnIndex += 1
                Next
                cellColumnIndex = 1
                cellRowIndex += 1
            Next

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 22, 2018 10:13 AM