• Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

Coding Still

  • Home
  • About

Export GridView or Repeater to Excel

November 20, 2012 By _tasos Leave a Comment

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=" &amp; ExcelFileName &amp; ".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=" &amp; ExcelFileName &amp; ".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

Filed Under: ASP.NET Tagged With: ASP.NET Web Forms, Data backup

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Categories

  • .NET Development
  • ASP.NET
  • Databases
  • Fun
  • IIS
  • JavaScript
  • Web Development

Tags

.NET Core Android ANTLR ASP.NET Ajax ASP.NET Core ASP.NET MVC ASP.NET Web Forms AWS Bouncy Castle Chartjs cli Client info detection Comic Continuous integration CSS Data backup Date handling Firebase Firefox addons Github HigLabo HTML5 Image manipulation jQuery JWT MySQL Nodejs Nuget OAuth Objectionjs OOP openssl Oracle ORM PHP Regular expressions SEO Social media SQL SQL Server UI/UX Url rewriting Videos Visual Studio Web design

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Secondary Sidebar

Archives

  • July 2020
  • March 2020
  • August 2019
  • December 2018
  • November 2018
  • February 2018
  • August 2016
  • June 2016
  • May 2016
  • February 2016
  • January 2016
  • August 2015
  • July 2015
  • October 2014
  • July 2014
  • November 2013
  • April 2013
  • February 2013
  • January 2013
  • December 2012
  • November 2012
  • August 2012
  • May 2012
  • February 2012
  • December 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • May 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • October 2010
  • September 2010
  • August 2010
  • July 2010

Footer

Recent Posts

  • Anatomy of an Objection.js model
  • Check your RSA private and public keys
  • Round functions on the Nth digit
  • Send FCM Notifications in C#
  • Jwt Manager
  • Things around the web #5
  • Query JSON data as relational in MySQL
  • Create and sign JWT token with RS256 using the private key
  • Drop all database objects in Oracle
  • Create and deploy a Nuget package

Latest tweets

  • Geekiness Intensifies.. NASA used Three.js to render a real-time simulation of this week's NASA rover landing on M… https://t.co/orgkXnYj9O February 19, 2021 18:12
  • Things I Wished More Developers Knew About Databases https://t.co/h4gfq6NJgo #softwaredevelopment #databases May 3, 2020 12:52
  • How a Few Lines of Code Broke Lots of Packages https://t.co/p7ZSiLY5ca #javascript May 3, 2020 12:48
  • Can someone steal my IP address and use it as their own? https://t.co/HoQ7Z3BG69 January 24, 2020 13:27
  • Organizational complexity is the best predictor of bugs in a software module https://t.co/aUYn9hD4oa #softwaredevelopment January 13, 2020 08:24
  • http://twitter.com/codingstill

Misc Links

  • George Liatsos Blog
  • Plethora Themes
  • C# / VB Converter
  • Higlabo: .NET library for mail, DropBox, Twitter & more

Connect with me

  • GitHub
  • LinkedIn
  • RSS
  • Twitter
  • Stack Overflow

Copyright © 2021 · eleven40 Pro on Genesis Framework · WordPress · Log in