Exporting to Excel from DataGridView terminates at null cell entry
I noticed that when I export to Excel from a DGV, the export leaves out cells to the right and below an empty cell (with Nothing or Null in it), i.e., terminates at that cell. I am using a dbNull trap, so is there something I am missing?
Code:
Dim dgv As DataGridView = CType(TabControl2.TabPages(1).Controls("dgv1"), DataGridView)
Dim dt As New DataTable()
'Adding the Columns
For Each column As DataGridViewColumn In dgv.Columns
dt.Columns.Add(column.HeaderText, column.ValueType)
Next
'Adding the Rows
Try
For Each row As DataGridViewRow In dgv.Rows
dt.Rows.Add()
For Each cell As DataGridViewCell In row.Cells
If cell.Value IsNot Nothing Then
dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value.ToString()
End If
If IsDBNull(cell.Value) Then
dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = ""
End If
Next
Next
Catch
End Try
'Exporting to Excel
Try
Using wb As New XLWorkbook()
wb.Worksheets.Add(dt, RunName)
wb.SaveAs(SaveFilename)
wb.Dispose()
End Using
dt.Dispose()
Catch
System.Windows.Forms.MessageBox.Show("You have the the export file open in Excel. Please close the file, and then retry exporting.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Exit Sub
End Try
Re: Exporting to Excel from DataGridView terminates at null cell entry
Maybe you should start by not having an empty Catch block. There is probably an exception being thrown but you're just ignoring it. Either get rid of the exception handler for the time being until you get things working properly or put some logging code or a breakpoint in or on the Catch block and see what's actually happening. The system is almost certainly trying to tell you what's wrong so you need to listen. You need to ALWAYS have debugged your code thoroughly BEFORE posting here, so you can already tell us EXACTLY what the code is doing and EXACTLY how that differs from your expectations. If you do that then you'll find that you don't even have to ask about many of your problems because you'll be able to solve them yourself. We'll still be here for the others but at least you can provide all the relevant information.