Export Gridview Data to Excel

'on button's Click
'Prepare gridview and all controls that exists in gridview for exporting to excel

Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcel.Click
If GV.Rows.Count > 0 Then ' Here GV is gridview
PrepareControlForExport(GV)
Export("FileName.xls", GV)
End If
End sub


Private Shared Sub PrepareControlForExport(ByVal control As Control)
For i As Integer = 0 To control.Controls.Count - 1
Dim current As Control = control.Controls(i)
If TypeOf current Is LinkButton Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(TryCast(current, LinkButton).Text))
ElseIf TypeOf current Is ImageButton Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(TryCast(current, ImageButton).AlternateText))
ElseIf TypeOf current Is HyperLink Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(TryCast(current, HyperLink).Text))
ElseIf TypeOf current Is DropDownList Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(TryCast(current, DropDownList).SelectedItem.Text))
ElseIf TypeOf current Is CheckBox Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(IIf(TryCast(current, CheckBox).Checked, "True", "False")))
End If
If current.HasControls() Then
PrepareControlForExport(current)
End If
Next
End Sub


''Finaly


Public Shared Sub Export(ByVal fileName As String, ByVal gv As GridView)
'HttpContext.Current.Response.Clear()
HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))
HttpContext.Current.Response.ContentType = "application/ms-excel"
Using sw As New StringWriter()
Using htw As New HtmlTextWriter(sw)
' Create a form to contain the grid
Dim table As New System.Web.UI.WebControls.Table()
' add the header row to the table
If gv.HeaderRow IsNot Nothing Then
PrepareControlForExport(gv.HeaderRow)
table.Rows.Add(gv.HeaderRow)
End If
' add each of the data rows to the table
For Each row As GridViewRow In gv.Rows
PrepareControlForExport(row)
table.Rows.Add(row)
Next
' add the footer row to the table
If gv.FooterRow IsNot Nothing Then
PrepareControlForExport(gv.FooterRow)
table.Rows.Add(gv.FooterRow)
End If
' render the table into the htmlwriter
table.RenderControl(htw)
' render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString())
HttpContext.Current.Response.[End]()
gv.AllowPaging = True
gv.DataBind()
End Using
End Using
gv = Nothing
End Sub