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
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