Exporting a GridView to Excel

[burak]

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

[phoenixfire425]

I cant explain it but i cant get it to work.

[phoenixfire425]

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

   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();

Edited March 24, 2008 by PlausiblyDamp

[PlausiblyDamp]

When does the error get raised? If you step through in a debugger is it happening on a particular line?

[phoenixfire425]

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

[phoenixfire425]

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

[phoenixfire425]

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