Export GridView or Repeater to Excel

In a previous article I wrote how one could export a Datatable to an excel file. The provided function binded a DataGrid control to the Datatable and rendered it. The result was then flushed to the Response. One disadvantage of this approach is that we cannot use a GridView control, which is the control that replaced the DataGrid in .NET 2.0.

In this article we will see how we can use the GridView control for this task. This approach still limits us to use a GridView, so after that we will see how we can make a Repeater (or any other control) that renders an HTML table to an excel file. The main advantage of this approach is that we don;’t present the plain data from the DataTable, but we can utilize the HTML formatting a GridView can provide and also not having to write a complex Sql Query to replace any computations we make on the GridView.

The code to export a GridView is here.

Sub GridViewToExcel(ByVal myGridView As GridView, ByVal CurrentResponse As System.Web.HttpResponse, ByVal ExcelFileName As String)
    CurrentResponse.Clear()
    CurrentResponse.Charset = ""
    CurrentResponse.ContentType = "application/vnd.ms-excel"
    CurrentResponse.AddHeader("Content-Disposition", "attachment;filename=" & ExcelFileName & ".xls")  
 
    Dim stringWrite As New System.IO.StringWriter
    Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
    myGridView.RenderControl(htmlWrite)
    CurrentResponse.Write(stringWrite.ToString)
    CurrentResponse.End()
End Sub

With this code snippet 2 errors will occur.

One is:

You can avoid this error by adding this function to your .aspx page.

Public Overrides Sub VerifyRenderingInServerForm(control As Control)
	' Verifies that the control is rendered 
End Sub

The second is:

You can avoid this error by setting the enableEventValidation property of your page to false. This can be done by setting this globally in the Web.config file or in the @Page Directive of your .aspx file.

<%@ Page Language="VB" AutoEventWireup="true" EnableEventValidation = "false" %>

If you are don’t want to disable event validation you could put your GridView to an empty .aspx page and make the export there. Also, you could change the EnableEventValidation property via code. This can be done only in the PreInit event. There you could change its value to false.

Protected Sub Page_PreInit(sender As Object, e As EventArgs)
    If(Page.IsPostBack AndAlso Request.Form("__EVENTTARGET").Contains("ExportToExcel")) Then
        Me.Page.EnableEventValidation = False
    End If
End Sub

This is a nasty hack and also doesn’t change anything. So, we could check the control’s name that triggered the postback. Having a naming convention at least disables event validation in much less cases (still a hack though!).

The above solution doesn’t fit in cases where the data we want to export to an excel file are not rendered from a GridView control. If we have a Repeater or a combination of user controls that form an HTML table we can also create an excel file with it.
In the case of the Repeater control, we simply render our repeater to an HtmlTextWriter and flush to the response.

Sub RepeaterToExcel(ByVal myRepeater As Repeater, ByVal CurrentResponse As System.Web.HttpResponse, ByVal ExcelFileName As String)
    CurrentResponse.Clear()
    CurrentResponse.Charset = ""
    CurrentResponse.ContentType = "application/vnd.ms-excel"
    CurrentResponse.AddHeader("Content-Disposition", "attachment;filename=" & ExcelFileName & ".xls")  
 
    Dim stringWrite As New System.IO.StringWriter
    Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
    myRepeater.RenderControl(htmlWrite)
    CurrentResponse.Write(stringWrite.ToString)
    CurrentResponse.End()
End Sub

Finally, if you have a different control you can use the function below. If you have a combination of user controls that form an HTML table you wrap them in a PlaceHolder and use the same function.

Sub ControlToExcel(ByVal myControl As Control, ByVal CurrentResponse As System.Web.HttpResponse, ByVal ExcelFileName As String)
    CurrentResponse.Clear()
    CurrentResponse.Charset = ""
    CurrentResponse.ContentType = "application/vnd.ms-excel"
    CurrentResponse.AddHeader("Content-Disposition", "attachment;filename=" & ExcelFileName & ".xls")  
 
    Dim stringWrite As New System.IO.StringWriter
    Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
    myControl.RenderControl(htmlWrite)
    CurrentResponse.Write(stringWrite.ToString)
    CurrentResponse.End()
End Sub