Export GridView to Excel Spreadsheet

[Rox Vi]

Hi, I am having problem with my coding below when I would like to export the data from GridView to Excel Spreadsheet. Appreciate if you could share your valuable comment. Thanks

Error Message = Class 'System.Web.UI.WebControls.GridView' cannot be indexed becasue it has no default property.

Error Line = xlWorkSheet.Cells(i + 1, j + 1) = GridView1(j, i).Value

Coding

Private Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click

Dim xlApp As excel.Application
Dim xlWorkBook As excel.Workbook
Dim xlWorkSheet As excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer
Dim x As Integer
Dim iC As Integer
xlApp = New excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")

For iC = 0 To GridView1.Columns.Count - 1
xlWorkSheet.Cells(1, iC + 1).Value = GridView1.Columns(iC).HeaderText
xlWorkSheet.Cells(1, iC + 1).font.bold = True
Next iC

For i = 0 To GridView1.Rows.Count - 1
For j = 0 To GridView1.Columns.Count - 1
xlWorkSheet.Cells(i + 1, j + 1) = GridView1(j, i).Value
Next
Next

xlWorkSheet.SaveAs("C:\Excel\vbexcel.xlsx")

xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)

MsgBox("File has been saved as C:\Excel\vbexcel.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
End Class

[Reverend Jim]

Try this

Dim row As Integer = 1
Dim col As Integer = 1

For Each line As DataGridViewRow In grdToExport.Rows
    For Each cell As DataGridViewTextBoxCell In line.Cells
        sheet.Cells(row, col) = cell.Value
        col = col + 1
    Next
    row += 1
    col = 1
Next

[Rox Vi]

Try this

Dim row As Integer = 1
Dim col As Integer = 1

For Each line As DataGridViewRow In grdToExport.Rows
    For Each cell As DataGridViewTextBoxCell In line.Cells
        sheet.Cells(row, col) = cell.Value
        col = col + 1
    Next
    row += 1
    col = 1
Next

Thanks and appreciate for the replied. when I paste you coding in the error message was surfaced out - Type 'DataGridVIewRow' is not defined. Please advise. Thanks

[Reverend Jim]

I'll tell you what I think is the problem, then I'll apologize for not mentioning it explicitly, then I'll rant just a little.

Try adding the following two lines at the top

Imports Microsoft.Office.Interop
Imports System.Windows.Forms

I apologize for not mentioning that explicitly before (but I repeat myself). And I asumed you already knew to add a Reference (COM) of the form Microsoft Excel 11.0 Object Library (depending on which version of Office you have)

One thing that annoys me (even more so when I am caught doing it), is when people post a partial solution. And by that I mean a solution that would be obvious to someone who already knows what to do but not enough for the person who is asking the question. A case in point is assuming that you knew to add the correct "Imports" info. Another case (more insidious) is when people assume you know what references to add to the project to make something work. When they actually DO say to add a reference, do they mean "reference" or "service reference". If reference, do they mean from the ".NET", "COM", "Browse", etc tab? When they say "add a SQL reference", which of the 20 odd references do they mean and do they mention the actual reference name (full name) as it appears in the reference list?

There are so many details that the experts know (without having to actively think about them) that trip up the rest of us. And knowing 99.9 percent of what to do, but missing that 0.1 percent that is impossible to deduce just leads to endless frustration.

But that's just my opinion. I could be wrong ;-)

[Rox Vi]

I'll tell you what I think is the problem, then I'll apologize for not mentioning it explicitly, then I'll rant just a little.

Try adding the following two lines at the top

Imports Microsoft.Office.Interop
Imports System.Windows.Forms

I apologize for not mentioning that explicitly before (but I repeat myself). And I asumed you already knew to add a Reference (COM) of the form Microsoft Excel 11.0 Object Library (depending on which version of Office you have)

One thing that annoys me (even more so when I am caught doing it), is when people post a partial solution. And by that I mean a solution that would be obvious to someone who already knows what to do but not enough for the person who is asking the question. A case in point is assuming that you knew to add the correct "Imports" info. Another case (more insidious) is when people assume you know what references to add to the project to make something work. When they actually DO say to add a reference, do they mean "reference" or "service reference". If reference, do they mean from the ".NET", "COM", "Browse", etc tab? When they say "add a SQL reference", which of the 20 odd references do they mean and do they mention the actual reference name (full name) as it appears in the reference list?

There are so many details that the experts know (without having to actively think about them) that trip up the rest of us. And knowing 99.9 percent of what to do, but missing that 0.1 percent that is impossible to deduce just leads to endless frustration.

But that's just my opinion. I could be wrong ;-)

Thanks and really appreciate your help on this.

I tried to put in the 2 line coding below but unfortunately, new error surfaced out.

• Imports Microsoft.Office.Interop
• Imports System.Windows.Forms

New Error Message: Unable to cast object of type 'System.Web.UI.WebControls.GridViewRow' to type 'System.Windows.Forms.DataGridViewRow'.

[Reverend Jim]

If this is not a web app then try changing the GridView control to a DataGridView control. I have never done web development so I have no experience in that area.

[Rox Vi]

If this is not a web app then try changing the GridView control to a DataGridView control. I have never done web development so I have no experience in that area.

it's okay, thanks for your great help on this.

[Reverend Jim]

Any time. Please remember to mark this thread as solved.