Exporting a GridView to Excel

B

burak

Centurion
Joined
Jun 17, 2003
Messages
127
Hello,

I found the code to export contents of a gridview to excel on a webpage:

Sub doExcel(Source as Object, E as EventArgs)
If Grid1.Rows.Count.ToString + 1 < 65536 Then
Grid1.AllowPaging = "False"
Grid1.DataBind()
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=Test.xls")
'Response.AddHeader("content-disposition", "attachment;filename=" & txtFile.text & ".xls")
Response.Charset = ""
EnableViewState = False
Controls.Add(frm)
frm.Controls.Add(Grid1)
frm.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
Grid1.AllowPaging = "True"
Grid1.DataBind()
Else
LblError.Text = "Too many rows - Export to Excel not possible"
End If
End Sub

this code works but the problem is I don't understand how it works.

I don't understand why we need to create a new form and add controls to it, how the data gets copied from the gridview to the excel file etc..

Could someone explain this code to me please?

Thank you,

Burak
 
Can anyone help me out with a way to export a DataGrid to Excel?

As of right now. i have this. and when i call the this it gives me and error
Visual Basic: 
    Sub doExcel(ByVal Source As Object, ByVal E As EventArgs)
        GridView1.AllowPaging = "False"
        GridView1.DataBind()
        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=CustomerSalesData.xls")
        'Response.AddHeader("content-disposition", "attachment;filename=" & txtFile.text & ".xls")
        Response.Charset = ""
        EnableViewState = False
        Controls.Add(frm)
        frm.Controls.Add(GridView1)
        frm.RenderControl(hw)
        Response.Write(tw.ToString())
        Response.End()
        GridView1.AllowPaging = "True"
        GridView1.DataBind()
    End Sub

Here is the ERROR

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();
 
Last edited by a moderator:
This error is coming from

Line 40: frm.RenderControl(hw)

With this stack trace
[InvalidOperationException: RegisterForEventValidation can only be called during Render();]
System.Web.UI.ClientScriptManager.RegisterForEventValidation(String uniqueId, String argument) +2133492
System.Web.UI.ClientScriptManager.GetPostBackEventReference(PostBackOptions options, Boolean registerForEventValidation) +49
System.Web.UI.WebControls.LinkButton.AddAttributesToRender(HtmlTextWriter writer) +256
System.Web.UI.WebControls.WebControl.RenderBeginTag(HtmlTextWriter writer) +17
System.Web.UI.WebControls.DataControlLinkButton.Render(HtmlTextWriter writer) +34
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +199
System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +20
System.Web.UI.WebControls.TableCell.RenderContents(HtmlTextWriter writer) +97
System.Web.UI.WebControls.WebControl.Render(HtmlTextWriter writer) +29
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +199
System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +20
System.Web.UI.WebControls.WebControl.RenderContents(HtmlTextWriter writer) +7
System.Web.UI.WebControls.WebControl.Render(HtmlTextWriter writer) +29
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
System.Web.UI.WebControls.Table.RenderContents(HtmlTextWriter writer) +536
System.Web.UI.WebControls.WebControl.Render(HtmlTextWriter writer) +29
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +199
System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +20
System.Web.UI.WebControls.WebControl.RenderContents(HtmlTextWriter writer) +7
System.Web.UI.WebControls.GridView.Render(HtmlTextWriter writer, Boolean renderPanel) +202
System.Web.UI.WebControls.GridView.Render(HtmlTextWriter writer) +30
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +22
System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +199
System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +20
System.Web.UI.HtmlControls.HtmlForm.RenderChildren(HtmlTextWriter writer) +59
System.Web.UI.HtmlControls.HtmlForm.Render(HtmlTextWriter output) +68
System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +25
System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +121
System.Web.UI.HtmlControls.HtmlForm.RenderControl(HtmlTextWriter writer) +37
Template_Reports_CustomerPurchasingDataSto.doExcel(Object Source, EventArgs E) in D:\Repcom\template\Reports\CustomerPurchasingDataSto.aspx.vb:40
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
 
the only way this works is if i turn sorting and paging off.
But i would like to keep atleast sorting on.
im using this code here

Sub doExcel(ByVal Source As Object, ByVal E As EventArgs)
GridView1.AllowPaging = "False"
GridView1.DataBind()
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=CustomerSalesData.xls")
'Response.AddHeader("content-disposition", "attachment;filename=" & txtFile.text & ".xls")
Response.Charset = ""
EnableViewState = False
Controls.Add(frm)
frm.Controls.Add(GridView1)
frm.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
GridView1.AllowPaging = "False"
GridView1.AllowSorting = "True"
GridView1.DataBind()
End Sub
 
Here is the Resolution!!!!
Thanks For all your help

'This eliminates the 30 secon timeout problem
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
e.Command.CommandTimeout = 5000
End Sub


'Here is the Export to EXCEL script this works with sorting and paging on
Sub doExcel(ByVal Source As Object, ByVal E As EventArgs)
GridView1.AllowSorting = "false"
GridView1.AllowPaging = "false"
GridView1.DataBind()
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=Backorders.xls")
'Response.AddHeader("content-disposition", "attachment;filename=" & txtFile.text & ".xls")
Response.Charset = ""
EnableViewState = False
Controls.Add(frm)
GridView1.CssClass = String.Empty
frm.Controls.Add(GridView1)
frm.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
GridView1.AllowPaging = "false"
GridView1.AllowSorting = "false"
GridView1.EnableViewState = "false"
GridView1.EnableSortingAndPagingCallbacks = "false"
frm.Controls.Add(frm)
GridView1.DataBind()
End Sub
 
Back
Top