asp .net / sql database / datagrid / export to excel

[mattdaddym]

Hi all,

I have been using the following code to export my datagrids to excel
for quite some time. Very simple and very effective.

<code>
Sub btnExcelExport_Click ( s As Object, e As EventArgs )
Response.Clear()
Response.AddHeader("content-disposition",
"attachment;filename=FileName.xls")
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"
Dim stringWrite As System.IO.StringWriter = New
System.IO.StringWriter()
Dim htmlWrite As System.Web.UI.HtmlTextWriter = New
HtmlTextWriter(stringWrite)
dgrdComplexSearchHidden.RenderControl(htmlWrite)
Response.Write(stringWrite.ToString())
Response.End()
End Sub
</code>

The problem is when a user tries to export a very large datagrid--in
this case a query that is returning approx 1600 rows-- the instant the
export button is pressed (no delay) I get:

<error>The page cannot be displayed
The page you are looking for is currently unavailable. The Web site
might be experiencing technical difficulties, or you may need to adjust
your browser settings. </error>

I have seen a reference or two to this online but no solution as of
yet. My server is 2003 advanced server / iis 6.0/ asp .net 1.1 / sql
server 2000. I appreciate any and all help. This is a big deal to the
bosses who are addicted to excel....

 

[Steve C. Orr [MVP, MCSD]]

There are a variety of ways to export ASP.NET content to Excel.
Perhaps one of these alternate techniques will work out better for you:

http://SteveOrr.net/articles/ExcelExport.aspx
http://SteveOrr.net/articles/ExportPanel.aspx

 

[CodeMeister]

I've used a similar pattern to create an Excel export. Rather than writing
to the response I override the Render method of the page. In the button
event handler I set a boolean flag to indicate that it should render to
Excel. I have not had any issues with rendering this way. The error may be
caused by the Response.End(). Here's the code patten I use translated from
C# (forgive me if I've made any syntax errors).

Dim sendAsExcel As Boolean = False

Sub btnExcelExport_Click ( s As Object, e As EventArgs )
sendAsExcel = True
End Sub

Sub Overrides Render(HtmlWriter writer)
If sendAsExcel Then
Response.AddHeader("content-disposition","attachment;filename=FileName.xls")
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"
dgrdComplexSearchHidden.RenderControl(writer)
Else
MyBase.Render(writer)
End If
End Sub

IHTH

Jon

 

[mattdaddym]

Thank you all. The answer was right under my nose and something
unrelated. I need to add this line to my web.config file.

<httpRuntime maxRequestLength = "20480"/>

The default was 4mb and I was surpassing it. The Excel code was fine.
Thanks again.