Exporting Gridview with Template Fields to Excel in ASP.NET 3.5
I recently needed to export a GridView in ASP.NET with a VB.NET codebehind and was coming up very short on answers, I found a few solutions in c# but for some reason they just didn’t want to work when I translated it to VB.NET. When I finally did I found that the export came out very wrong because all my template fields which contained controls were images! I finally pieced together a solution from various sources.
First thing you’ll find when searching for how to export a GridView to an excel document is changing the content type and rending the GridView as such (note I used an image button but you can use whatever method you want):
-
Protected Sub imgExcel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgExcel.Click
-
Dim attachment As String = "attachment; filename=disposals.xls"
-
Response.ClearContent()
-
Response.AddHeader("content-disposition", attachment)
-
Response.ContentType = "application/ms-excel"
-
Dim sw As New StringWriter
-
Dim htw As New HtmlTextWriter(sw)
-
gvReports.RenderControl(htw)
-
Response.Write(sw.ToString)
-
Response.End()
-
End Sub
This will almost certainly generate a runtime error stating that:
Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server.
To fix this you must override the VerifyRenderingInServerForm sub and all you must do is this:
-
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
-
‘Gives the all clear for export, page will generate an error unless this sub is overridden
-
End Sub
This is usually where the help stops, but I got an error stating that:
RegisterForEventValidation can only be called during Render();
To fix this you must add EnableEventValidation=”false” to your <%@ Page ... %> tag at the top of your .aspx code. E.G.,
-
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="reportList.aspx.vb" Inherits="reportList" EnableEventValidation="false" %>
Now if you’re using a vanilla GridView without any template fields you should be set. However, if you are using template fields and you try to export at this point you’ll notice that your export will be… not… good. Any controls in your template field will be rendered as images and completely useless for editing in your Spreadsheet. So after much google-fu I found the solution in C# and have translated it for you.
-
Private Sub prepareGridView(ByVal gv As Control)
-
Dim txt As New Literal()
-
Dim control As Control
-
Dim i As Integer
-
For i = 0 To gv.Controls.Count – 1
-
control = gv.Controls(i)
-
If TypeOf control Is LinkButton Then
-
txt.Text = CType(control, LinkButton).Text
-
gv.Controls.Remove(control)
-
gv.Controls.AddAt(i, txt)
-
ElseIf TypeOf control Is DropDownList Then
-
txt.Text = CType(control, DropDownList).SelectedItem.Text
-
gv.Controls.Remove(control)
-
gv.Controls.AddAt(i, txt)
-
ElseIf TypeOf control Is CheckBox Then
-
txt.Text = IIf(CType(control, CheckBox).Checked, "True", "False")
-
gv.Controls.Remove(control)
-
gv.Controls.AddAt(i, txt)
-
ElseIf TypeOf control Is TextBox Then
-
txt.Text = CType(control, TextBox).Text
-
gv.Controls.Remove(control)
-
gv.Controls.AddAt(i, txt)
-
End If
-
-
If control.HasControls Then
-
prepareGridView(control)
-
End If
-
Next
-
End Sub
It’s a bit sloppy and can obviously be cleaned up but it works, a simple if/else tree to handle any controls you’ve added to your gridview, will extract the text from said controls, remove the control and replace the cell with the text. Not that it uses recursion at the end to travel through the DOM tree to find any and all controls.
Finally add the call to your original export function:
-
Protected Sub imgExcel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgExcel.Click
-
Dim attachment As String = "attachment; filename=disposals.xls"
-
prepareGridView(gvReport) ‘clean up controls
-
Response.ClearContent()
-
Response.AddHeader("content-disposition", attachment)
-
Response.ContentType = "application/ms-excel"
-
Dim sw As New StringWriter
-
Dim htw As New HtmlTextWriter(sw)
-
gvReports.RenderControl(htw)
-
Response.Write(sw.ToString)
-
Response.End()
-
End Sub
And voilà, excel exportation of your GridView with template fields.
Popularity: 6% [?]