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?

score 2 · Answer 1 · answered Feb 20 '17 at 14:35

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

Have you traced your answer? That third loop ‘k’ loop looks very strange. A simple trace appears to show that the code is writing the same data over and over...once for column 1, twice for column 2, three time for column 3 and so on. You may want to check this. — JohnG, Feb 21 '17 at 05:03
Hi John, I traced the code. Actually we are copying all the Grid values cell by cell and row wise so it repeats loop 'k' but its not writing the same data over and over. — Sandy, Feb 23 '17 at 18:53
@Sandy, I hate to disagree, but I traced your code and it writes the same data more than once. Once for column 1,twice for column 2 and 3 times for column 3. Please step through the code and you will see this is the case. Try removing some data after you output it, and it will reappear in the next iteration. Also two loops are all that is needed. — JohnG, Mar 01 '17 at 00:15

JohnG · Accepted Answer · 2017-02-21T05:37:01.727

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.

Wow, this must be the most comprehensive and well-explained answer I've seen so far on Stackoverflow. Thank you! I even begin to understand what I'm doing here :) You mention two foreach loops; should i place your loop before the existing loop? — LarsS, Feb 21 '17 at 07:03
Sorry, my bad. I see that there are two loops in your code. Too triggerhappy ... ;) — LarsS, Feb 21 '17 at 07:08
The last bit of code should do the trick. First loop for headers, the next for rows. — JohnG, Feb 21 '17 at 07:10
It did. Now I only have a problem with my colouring part, which offsets the colouring somehow. I'll try to find out for myself before asking here, though :) Thanks a lot for the massive help you've given me on this thing. — LarsS, Feb 21 '17 at 07:16

Export to Excel with header text

2 Answers2