472,805 Members | 1,008 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

Code in ASP to export to Excel - works & doesn't work??? Help!

The following code:

Private Sub ClearControls(ByVal ctrl As Control)

Dim i As Int32

For i = ctrl.Controls.Count - 1 To 0 Step -1

ClearControls(ctrl.Controls(i))

Next

If ctrl.GetType().ToString() <> "TableCell" Then

If Not ctrl.GetType().GetProperty("SelectedItem") Is Nothing Then

Dim literal As LiteralControl = New LiteralControl()

ctrl.Parent.Controls.Add(literal)

Try

literal.Text =
CType(ctrl.Controls.GetType().GetProperty("Selecte dItem").GetValue(ctrl,
Nothing), System.String)

Catch

End Try

ctrl.Parent.Controls.Remove(ctrl)

End If

Else

Dim literal As LiteralControl = New LiteralControl()

ctrl.Parent.Controls.Add(literal)

literal.Text =
CType(ctrl.Controls.GetType().GetProperty("Text"). GetValue(ctrl, Nothing),
System.String)

ctrl.Parent.Controls.Remove(ctrl)

End If

Return

End Sub

Private Sub RbtnExport_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles RbtnExport.SelectedIndexChanged

Dim sFile As String = Session("User") & "-Customer List-" & Today()

sFile = sFile.Replace("/", "-")

ClearControls(DataGrid1)

Select Case RbtnExport.SelectedItem.Value

Case "Excel"

Response.ContentType = "application/vnd.ms-excel"

Response.AppendHeader("content-disposition", "attachment; filename=" & sFile
& ".xls")

Case "Word"

Response.ContentType = "application/vnd.ms-word"

Response.AppendHeader("content-disposition", "attachment; filename=" & sFile
& ".doc")

End Select

' Remove the charset from the Content-Type header.

Response.Charset = ""

' Turn off the view state.

Me.EnableViewState = False

Dim tw As New System.IO.StringWriter()

Dim hw As New System.Web.UI.HtmlTextWriter(tw)

' Get the HTML for the control.

DataGrid1.RenderControl(hw)

' Write the HTML back to the browser.

Response.Write(tw.ToString())

' End the response.

Response.End()

End Sub

I have as you see above EXACTLY in 2 separate aspx pages. One provides a
list of products and inventories, the other displays a list of customers.
The code above is copied from one to the other. The one that displays the
products & inventories works - I get an Excel book I can read - the other
does not - it returns a 0 byte file that produces the "Unable to read file"
message when I try to open it. On each page, I am taking a web page with a
datagrid on it to an excel file.

There are some differences between the 2 pages.

One, the product page does not have sortable columns, while the customer
list did (I took this out of the customer list with no effect, so I put it
back in).

Two, the product listing page is loaded as the result of an If Not
page.ispostback condition in the Page_load event, and does not require any
clicks of boxes or buttons. The Customer listing allows you to make some
selections before the datagrid is loaded (such as by a distribution
channel), and the also allows you to filter the request (so you can pick out
just shipto locations, or soldto information only). Therefore, the customer
list datagrid is generated based on one of 2 things - whether a radio button
that indicates you want all distribution channels displayed, or whether the
index has changed on a combo box. That builds your sql based on your
selections you've made and once you've selected either the All button or the
appropriate combo box item, it'll run the sql and load your datagrid based
on those selections.

You'll then click a radio button to bring it to excel. Both pages function
similarly, except the one where the datagrid is loaded in page_load works,
and the other one returns a zero byte page.

Any ideas as to what's wrong with this puppy?

SC


Nov 18 '05 #1
1 2081
Does the bindgrid & the render control have to be done in a new page?

i.e. - in my buttonclick event, I'd transfer control to page2.aspx (via a
response.redirect), and in page2.aspx, I'd have a bindgrid & a render
control routine (similar to what I have in the selectindexchanged sub
below). But that would be all I'd have in my page2.aspx.

What I don't know is (1) if this would fix my problem, and (2) how do you
get the data results (in my case probably a meg's worth of data) into the
new page?

The code I'm looking at is this:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
BindGrid()
RenderGrid()
End Sub

Private Sub BindGrid()
Dim dtResults As DataTable = CType(Session("DynamicFormResults"),
DataTable)
If Not dtResults Is Nothing Then
DataGrid1.DataSource = dtResults.DefaultView
DataGrid1.DataBind()
End If
End Sub

Private Sub RenderGrid()
Response.ContentType = "application/vnd.ms-excel"
' Remove the charset from the Content-Type header.
Response.Charset = ""
' Turn off the view state.
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
' Get the HTML for the control.
DataGrid1.RenderControl(hw)
' Write the HTML back to the browser.
Response.Write(tw.ToString())
' End the response.
Response.End()
End Sub

which is located here:
http://weblogs.asp.net/dneimke/archi.../27/63348.aspx

Anyone have any ideas?

SC

<me@privacy.net> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
The following code:

Private Sub ClearControls(ByVal ctrl As Control)

Dim i As Int32

For i = ctrl.Controls.Count - 1 To 0 Step -1

ClearControls(ctrl.Controls(i))

Next

If ctrl.GetType().ToString() <> "TableCell" Then

If Not ctrl.GetType().GetProperty("SelectedItem") Is Nothing Then

Dim literal As LiteralControl = New LiteralControl()

ctrl.Parent.Controls.Add(literal)

Try

literal.Text =
CType(ctrl.Controls.GetType().GetProperty("Selecte dItem").GetValue(ctrl,
Nothing), System.String)

Catch

End Try

ctrl.Parent.Controls.Remove(ctrl)

End If

Else

Dim literal As LiteralControl = New LiteralControl()

ctrl.Parent.Controls.Add(literal)

literal.Text =
CType(ctrl.Controls.GetType().GetProperty("Text"). GetValue(ctrl, Nothing),
System.String)

ctrl.Parent.Controls.Remove(ctrl)

End If

Return

End Sub

Private Sub RbtnExport_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles RbtnExport.SelectedIndexChanged

Dim sFile As String = Session("User") & "-Customer List-" & Today()

sFile = sFile.Replace("/", "-")

ClearControls(DataGrid1)

Select Case RbtnExport.SelectedItem.Value

Case "Excel"

Response.ContentType = "application/vnd.ms-excel"

Response.AppendHeader("content-disposition", "attachment; filename=" & sFile & ".xls")

Case "Word"

Response.ContentType = "application/vnd.ms-word"

Response.AppendHeader("content-disposition", "attachment; filename=" & sFile & ".doc")

End Select

' Remove the charset from the Content-Type header.

Response.Charset = ""

' Turn off the view state.

Me.EnableViewState = False

Dim tw As New System.IO.StringWriter()

Dim hw As New System.Web.UI.HtmlTextWriter(tw)

' Get the HTML for the control.

DataGrid1.RenderControl(hw)

' Write the HTML back to the browser.

Response.Write(tw.ToString())

' End the response.

Response.End()

End Sub

I have as you see above EXACTLY in 2 separate aspx pages. One provides a
list of products and inventories, the other displays a list of customers.
The code above is copied from one to the other. The one that displays the
products & inventories works - I get an Excel book I can read - the other
does not - it returns a 0 byte file that produces the "Unable to read file" message when I try to open it. On each page, I am taking a web page with a datagrid on it to an excel file.

There are some differences between the 2 pages.

One, the product page does not have sortable columns, while the customer
list did (I took this out of the customer list with no effect, so I put it
back in).

Two, the product listing page is loaded as the result of an If Not
page.ispostback condition in the Page_load event, and does not require any
clicks of boxes or buttons. The Customer listing allows you to make some
selections before the datagrid is loaded (such as by a distribution
channel), and the also allows you to filter the request (so you can pick out just shipto locations, or soldto information only). Therefore, the customer list datagrid is generated based on one of 2 things - whether a radio button that indicates you want all distribution channels displayed, or whether the index has changed on a combo box. That builds your sql based on your
selections you've made and once you've selected either the All button or the appropriate combo box item, it'll run the sql and load your datagrid based
on those selections.

You'll then click a radio button to bring it to excel. Both pages function similarly, except the one where the datagrid is loaded in page_load works,
and the other one returns a zero byte page.

Any ideas as to what's wrong with this puppy?

SC

Nov 18 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Carl Corcoran | last post by:
I'm developing a website for work that exports some reports to excel. I am using the... Response.AddHeader "Content-Disposition","attachment;filename=report.xls" Response.ContentType =...
4
by: Gary Wright | last post by:
I have an Access 2K database split into front and back. Quite often the users want to do some data analysis that I have not created a report for so they want to export some subset of the data into...
3
by: John | last post by:
Is there a way to code the button that's available in the query window--microsoft excel icon that exports to excel. I know transferspreadsheet will do this---but I want the query, which is in a...
5
by: Igor | last post by:
Hi everyone! I have a question about exporting data to Microsoft Excel. I am writing program in C# and have to export some data to excel workbook. It works very good using OLE technology, but...
0
by: Max Mayer | last post by:
Hello everybody, I have implemented a windows form in C# .NET to export data from a listbox to Excel. I work on a Windows XP machine with Office 97 and .NET framework 1.1 SDK installed. I use...
0
by: Max Mayer | last post by:
Hello everybody, I have implemented a windows form in C# .NET to export data from a listbox to Excel. I work on a Windows XP machine with Office 97 and .NET framework 1.1 SDK installed. I use...
8
by: Taffman | last post by:
I've searched this goup for an answer to this, there are many discussions that come close but non that I can find that actually addresses this particular problem. I'm exporting queries to Excel....
4
NeoPa
by: NeoPa | last post by:
Introduction: Macro Security Levels in MS Office applications are recommended to be set to High. This stops any VBA code associated with a project from running, unless it is signed (with a...
4
by: petro | last post by:
Hello, I have some trouble to export this code which works fine to VB 2008 This code is intented to work with datalogger. Declare Function pl1000CloseUnit Lib "pl1000.dll" (ByVal handle As...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.