Search code examples
excelvb.netdatagridviewiterationexport-to-excel

Excel Export exporting same row multiple times

I've written an export procedure for my vb.net application, which exports data displayed in a DataGridViewinto 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 Loopthat 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

Solution

  • The original problem of duplicate rows is from this:

    For i = 0 To dgvExport.RowCount - 1
    For j = 0 To dgvExport.ColumnCount - 1
       For k As Integer = 1 To dgvExport.Columns.Count

    The cells are being iterated too many times. Use one loop to export the HeaderText, then another for the data.

    Object reference not set to an instance of an object

    AKA NullReferenceException

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