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.AddHea der("content-disposition",
"attachment;fil ename=FileName. xls")
Response.Charse t = ""
Response.Cache. SetCacheability (HttpCacheabili ty.NoCache)
Response.Conten tType = "applicatio n/vnd.xls"
Dim stringWrite As System.IO.Strin gWriter = New
System.IO.Strin gWriter()
Dim htmlWrite As System.Web.UI.H tmlTextWriter = New
HtmlTextWriter( stringWrite)
dgrdComplexSear chHidden.Render Control(htmlWri te)
Response.Write( stringWrite.ToS tring())
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

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
<ma********@gma il.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

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.AddHea der("content-disposition",
"attachment;fil ename=FileName. xls")
Response.Charse t = ""
Response.Cache. SetCacheability (HttpCacheabili ty.NoCache)
Response.Conten tType = "applicatio n/vnd.xls"
Dim stringWrite As System.IO.Strin gWriter = New
System.IO.Strin gWriter()
Dim htmlWrite As System.Web.UI.H tmlTextWriter = New
HtmlTextWriter( stringWrite)
dgrdComplexSear chHidden.Render Control(htmlWri te)
Response.Write( stringWrite.ToS tring())
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....

[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(HtmlWrit er writer)
If sendAsExcel Then
Response.AddHea der("content-disposition","a ttachment;filen ame=FileName.xl s")
Response.Cache. SetCacheability (HttpCacheabili ty.NoCache)
Response.Conten tType = "applicatio n/vnd.xls"
dgrdComplexSear chHidden.Render Control(writer)
Else
MyBase.Render(w riter)
End If
End Sub

IHTH

Jon
<ma********@gma il.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

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.AddHea der("content-disposition",
"attachment;fil ename=FileName. xls")
Response.Charse t = ""
Response.Cache. SetCacheability (HttpCacheabili ty.NoCache)
Response.Conten tType = "applicatio n/vnd.xls"
Dim stringWrite As System.IO.Strin gWriter = New
System.IO.Strin gWriter()
Dim htmlWrite As System.Web.UI.H tmlTextWriter = New
HtmlTextWriter( stringWrite)
dgrdComplexSear chHidden.Render Control(htmlWri te)
Response.Write( stringWrite.ToS tring())
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....

[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 maxRequestLengt h = "20480"/>

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