David David - 1 month ago 15
Vb.net Question

Excel Export exporting same row multiple times

I've written an export procedure for my vb.net application, which exports data displayed in a

DataGridView
into an Excel spreadsheet.

However, instead of copying all 23 of the rows in the
DataGridView
, it copies the same one, but 23 times. What is wrong with the below
For Loop
that means it only copies one record?

For i = 0 To dgvExport.RowCount - 1
For j = 0 To dgvExport.ColumnCount - 1
For k As Integer = 1 To dgvExport.Columns.Count
xlWorksheet.Cells(1, k) = dgvExport.Columns(k - 1).HeaderText
xlWorksheet.Cells(i + 2, j + 1) = dgvExport(j, 1).Value.ToString
Next
Next
Next


I need the field names copied, hence the
HeaderText
line and the
k
variable.

A snippet of my DGV, to show there are different records;
enter image description here

The resultant Export;

enter image description here

EDIT

As suggested, I changed my code to the following

For k As Integer = 1 To dgvExport.Columns.Count
xlWorksheet.Cells(1, k) = dgvExport.Columns(k - 1).HeaderText
Next

For i = 0 To dgvExport.RowCount - 1
For j = 0 To dgvExport.ColumnCount - 1
xlWorksheet.Cells(i + 3, j + 1) = dgvExport(j, i).Value.ToString
Next
Next


But now get an error


Object reference not set to an instance of an object


on line
xlWorksheet.Cells(i + 3, j + 1) = dgvExport(j, i).Value.ToString

Answer

If AllowUserToAddRows is true on the DGV, it means you are looping one too many rows. The cells in that row are Nothing, so you get the NullReferenceException:

 For i = 0 To dgvExport.RowCount - 1
     If dgvExport.Rows(i).IsNewRow Then Continue
     For j = 0 To dgvExport.ColumnCount - 1
         xlWorksheet.Cells(i + 3, j + 1) = dgvExport(j, i).Value.ToString
     Next
 Next

Or change the row loop:

' one less to account for the new user row
For i = 0 To dgvExport.RowCount - 2