David David - 11 months ago 52
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 Source

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