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:
Server Error in ‘/ASP.Net’ Application.
Control ‘GridView1’ of type ‘GridView’ must be placed inside a form tag with runat=server.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.HttpException: Control ‘GridView1’ of type ‘GridView’ must be placed inside a form tag with runat=server.
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:
Server Error in ‘ASP.Net’ Application.
RegisterForEventValidation can only be called during Render();
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: RegisterForEventValidation can only be called during Render();
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
Leave a Reply