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

M

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....
 
C

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
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,787
Messages
2,569,630
Members
45,338
Latest member
41Pearline46

Latest Threads

Top