Export to Excel with header text

Question

I have code that is supposed to include header text when exporting to Excel.

For i As Integer = 0 To DataGridView2.Rows.Count - 2
    For j As Integer = 0 To DataGridView2.Columns.Count - 1
        ' Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
        If cellRowIndex = 1 Then
            worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView2.Columns(j).HeaderText
        Else
            worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView2.Rows(i).Cells(j).Value
        End If
        cellColumnIndex += 1
    Next
    cellColumnIndex = 1
    cellRowIndex += 1
Next

However, this code replaces the first data row with the header text instead of inserting it above. If I remove the If statement which extracts the header text, I get all rows out, but I don't get header text.

For i As Integer = 0 To DataGridView2.Rows.Count - 2
    For j As Integer = 0 To DataGridView2.Columns.Count - 1
        worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView2.Rows(i).Cells(j).Value
        cellColumnIndex += 1
    Next
    cellColumnIndex = 1
    cellRowIndex += 1
Next

Any ideas on how to solve this?

Answer 1

After tracing your code it is clear you are having an indexing problem in the two for loops. It appears the code you supplied is missing the first row of data.

As you commented:

this code replaces the first data row with the header text instead of inserting it above...

This is not correct, it is not replacing the row it is simply skipping the first row of data in the DataGridView. Below is your code to explain.

For i As Integer = 0 To DataGridView1.Rows.Count - 2
  For j As Integer = 0 To DataGridView1.Columns.Count - 1
    If cellRowIndex = 1 Then
      worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView1.Columns(j).HeaderText
    Else
      worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView1.Rows(i).Cells(j).Value
    End If
    cellColumnIndex += 1
  Next
  cellColumnIndex = 1
  cellRowIndex += 1
Next

Basically this loops through the rows then the columns. The problem is in the If statement and the index i. In this If statement you check to see if this is the first time around to get the headers. If it is the first time around you write the headers to excel and proceed. This is going to skip the first row of data because the loop variable i is used as an index into the DataGridView rows with the assignment:

worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView1.Rows(i).Cells(j).Value

When entering the j loop the first time around i is zero (0). A check is made with cellRowIndex to determine if the headers need to be output. In this case they do… the headers are output then exit this if and loop back up to the next header. When all headers are output you exit the j loop and loop back up to the i loop. This will increment i to 1 and enter the j loop… Since i has already been 0 when the headers were output we will skip/miss row 0 in the DataGridView. I hope this makes sense.

A simple solution for what you have would be to simply start i at -1 with:

For i As Integer = -1 To DataGridView1.Rows.Count - 2

This will solve the problem you are having however the code is not easy to follow. I recommend using a foreach loop for looping through the DataGridView rows and separating the column output from the rows output. This does create two loops but the first loop will only loop once to add the headers. The next loop goes through all the rows. This will make indexing easier to handle and easier to read in the future.

For Each column In DataGridView1.Columns
  worksheet.Cells(1, column.Index + 1).Value = column.Name
Next

Dim rowIndex = 2
For Each row As DataGridViewRow In DataGridView1.Rows
  If Not row.IsNewRow Then
    For colIndex As Integer = 0 To DataGridView1.Columns.Count - 1
      worksheet.Cells(rowIndex, colIndex + 1).Value = row.Cells(colIndex).Value.ToString
    Next
  End If
  rowIndex += 1
Next

Hope this helps.

Answer 2

Below code creates an Excel file with header from DataGridView. I have tested it in Visual Studio 2010. First you have to add the reference of Microsoft Office assembly.

• Microsoft.Office.Interop.Excel (Version - 12.0.0.0)

  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  Dim xlApp As Microsoft.Office.Interop.Excel.Application
  Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
  Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
  Dim misValue As Object = System.Reflection.Missing.Value
  Dim i As Integer
  Dim j As Integer
  
  xlApp = New Microsoft.Office.Interop.Excel.Application
  xlWorkBook = xlApp.Workbooks.Add(misValue)
  xlWorkSheet = xlWorkBook.Sheets("sheet1")
  
  For i = 0 To DataGridView1.RowCount - 2
      For j = 0 To DataGridView1.ColumnCount - 1
          For k As Integer = 1 To DataGridView1.Columns.Count
              xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
              xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
          Next
      Next
  Next
  
  xlWorkSheet.SaveAs("C:\vbToexcel.xlsx")
  xlWorkBook.Close()
  xlApp.Quit()
  
  releaseObject(xlApp)
  releaseObject(xlWorkBook)
  releaseObject(xlWorkSheet)
  
  MsgBox("File successfully created - C:\vbToexcel.xlsx")   End Sub
  
  Private Sub releaseObject(ByVal obj As Object)
  Try
      System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
      obj = Nothing
  Catch ex As Exception
      obj = Nothing
  Finally
      GC.Collect()
  End Try   End Sub