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

Discussion in 'ASP .Net Web Controls' started by Guest, Mar 4, 2004.

  1. Guest

    Guest Guest

    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("SelectedItem").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
    Guest, Mar 4, 2004
    #1
    1. Advertising

  2. Guest

    Guest Guest

    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/archive/2004/01/27/63348.aspx

    Anyone have any ideas?

    SC



    <> wrote in message
    news:%...
    > 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("SelectedItem").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
    >
    >
    >
    >
    Guest, Mar 4, 2004
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    1
    Views:
    593
    Guest
    Mar 4, 2004
  2. =?Utf-8?B?U3JpZGhhcg==?=

    using Microsoft Excel image for Export to Excel button

    =?Utf-8?B?U3JpZGhhcg==?=, Dec 9, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    3,085
    =?Utf-8?B?U3JpZGhhcg==?=
    Dec 9, 2005
  3. =?Utf-8?B?SGVtYW50IFNpcGFoaW1hbGFuaQ==?=

    Export to Excel (Default File Type - Excel)

    =?Utf-8?B?SGVtYW50IFNpcGFoaW1hbGFuaQ==?=, Apr 24, 2006, in forum: ASP .Net
    Replies:
    15
    Views:
    17,474
    syed hassan
    May 21, 2009
  4. Grey
    Replies:
    4
    Views:
    1,947
    Mark Rae [MVP]
    Oct 17, 2007
  5. Guest
    Replies:
    1
    Views:
    134
    Guest
    Mar 4, 2004
Loading...

Share This Page