Export datagrid to excel

[Guest]

Hi,

I copied the following codes from Internet to export datagrid to Excel, it
works with datagrid not supporting sorting or paging.

Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Response.AddHeader("Content-Disposition", "attachment;filename=Statement.xls")
Me.EnableViewState = False

Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
DataGrid1.RenderControl(hw)
Response.Write("<table><tr><td><b>" + lblMessage.Text +
"</b></td></tr><tr><td>" + tw.ToString() + "</td></tr></table>")
Response.End()

Looks like if datagrid supports sorting or paging, ASP.Net will render HTML
with Javascript inside, and the codes here do not like that. Is there a
workaround?

TIA

 

[Steve C. Orr [MVP, MCSD]]

You should disable datagrid paging when exporting to Excel. In that case
just write out all the records in one long page because this is how Excel
likes to deal with data. Excel doesn't do JavaScript.

Here's more info:
http://www.SteveOrr.net/articles/ExcelExport.aspx
http://www.SteveOrr.net/articles/ExportPanel.aspx