I've done this many times in the past with other applications using the code below but once I moved the application into a Sharepoint environment coupled with AJAX extensions, I kept receiving the dreaded "RegisterForEventValidation can only be called during Render" error message.
Sub ExcelExport(ByVal Source As Object, ByVal E As EventArgs)
MyGrid.AllowPaging = "False"
MyGrid.AllowSorting = "False"
Dim tw As New StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim frm As HtmlForm = New HtmlForm()
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment;filename=IT.Work.Requests.xls")
Response.Charset = ""
EnableViewState = False
Controls.Add(frm)
frm.Controls.Add(MyGrid)
frm.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
MyGrid.AllowPaging = "True"
MyGrid.DataBind()
End Sub
The most common fix for this problem is to set EnableEventValidation="false" in the page directive but this wasn't working for me either.
Another common suggestion is to use the VerifyRenderingInServerForm method which ensures than a Htmlform control is rendered at the runtime.
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
End Sub
Finally, which ultimately fixed the problem was Matt Berseth's blog post on exporting data from a gridview to Excel. The VB version of the code is below.
** Default.aspx **
<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource
ID="sqldsCustomers" runat="server"
SelectCommand="select * from dbo.customers" SelectCommandType="Text"
ConnectionString="server=PCFRED\SQLEXPRESS;database=northwind;Trusted_Connection=yes;" />
<asp:GridView
id="gvCustomers" runat="server"
AllowPaging="true" AllowSorting="true" PageSize="10" DataSourceID="sqldsCustomers" />
<asp:Button
ID="btnExportGrid" runat="server"
Text="Export to Excel" OnClick="BtnExportGrid_Click" />
</div>
</form>
</body>
</html>
** Default.aspx.vb **
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Public Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub
Protected Sub BtnExportGrid_Click(ByVal sender As Object, ByVal args As EventArgs)
' pass the grid that for exporting ...
GridViewExportUtil.Export("Customers.xls", Me.gvCustomers)
End Sub
End Class
** App_Code/GridviewExportUtil.vb **
Imports System
Imports System.Data
Imports System.Configuration
Imports System.IO
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Public Class GridViewExportUtil
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"
Dim sw As StringWriter = New StringWriter
Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
' Create a form to contain the grid
Dim table As Table = New Table
table.GridLines = gv.GridLines
' add the header row to the table
If (Not (gv.HeaderRow) Is Nothing) Then
GridViewExportUtil.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
GridViewExportUtil.PrepareControlForExport(row)
table.Rows.Add(row)
Next
' add the footer row to the table
If (Not (gv.FooterRow) Is Nothing) Then
GridViewExportUtil.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()
End Sub
' Replace any of the contained controls with literals
Private Shared Sub PrepareControlForExport(ByVal control As Control)
Dim i As Integer = 0
Do While (i < control.Controls.Count)
Dim current As Control = control.Controls(i)
If (TypeOf current Is LinkButton) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, LinkButton).Text))
ElseIf (TypeOf current Is ImageButton) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, ImageButton).AlternateText))
ElseIf (TypeOf current Is HyperLink) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, HyperLink).Text))
ElseIf (TypeOf current Is DropDownList) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, DropDownList).SelectedItem.Text))
ElseIf (TypeOf current Is CheckBox) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked))
'TODO: Warning!!!, inline IF is not supported ?
End If
If current.HasControls Then
GridViewExportUtil.PrepareControlForExport(current)
End If
i = (i + 1)
Loop
End Sub
End Class