Datagrid to Excel problem (ASP dot net)

Discussion in 'ASP .Net' started by Steve Chatham, Feb 20, 2004.

  1. I use the following code:

    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("/", "")
    RbtnExport.Visible = False
    Select Case RbtnExport.SelectedItem.Value
    Case "Excel"
    Response.ContentType = "application/x-msexcel"
    Case "Word"
    Response.ContentType = "application/msword"
    End Select
    Response.AddHeader("Content-Disposition", "Attachment; filename=" & sFile)
    End Sub

    To allow a user to save a web page with a datagrid on it into an Excel
    sheet.

    They'll run a page using a Web check box, which renders it to an ASPX page,
    and then when they have the final product on the screen, they click the
    radio button for Excel, and it prompts them to Open or to Save the file.

    The problem occurs when you save or open the file.

    The datagrid has 9 columns in it, a product code, description, unit of
    measure, price, product hierarchy number, weight and UPC code, etc.

    The data grid has a header & a footer on it, and each column in the datagrid
    is sortable.

    If I have a short list, one of maybe less than 20 records, I can click the
    Excel button, it prompts me to save it, and it'll save as a document with a
    ..xls extension. You can then open it up without incident.

    If the list is longer - say maybe over 40-50 rows in length (up to the
    longest list, with up to 500 or more items in it), it will save it with a
    ..xls extension, but you cannot open it.

    Excel attempts to open it, but it immediately gives you an OK box that says
    "Unable to read file". It works fine if the number of rows is small (under
    25 or so), but not if it's much more than that. Thus far, I have not seen a
    pattern in it. The file that will open is 20K in size, the one that will
    not is 400K in size. I have figured that the difference is between 30 and 50
    records before it gives me this problem. I've noticed that each line
    appears to add about 1K in file size to it the excel file.

    It doesn't appear to be a memory issue, as excel works fine any other time,
    and will open spreadsheets much larger than this. I've confirmed that it's
    not version specific in Excel, as it barfs on every version of excel it
    tries to open it in.

    Any idea as to what is going on here on this problem? Is there any way I
    can step thru the open of the file in Excel, to see the specific problem
    Excel has with opening the file?

    Any help/advice appreciated.

    Thanks,

    SC
    Steve Chatham, Feb 20, 2004
    #1
    1. Advertising

  2. Steve,
    If there are no formulas to be involved, you might want to look at using the
    RenderControl method of the DataGrid (and most other ASP.NET controls)
    This will spit out the HTML representation of the DataGrid.

    You can find plenty of examples of doing this on the .NET.
    --Peter

    "Steve Chatham" <> wrote in message
    news:OhDJd1$...
    >I use the following code:
    >
    > 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("/", "")
    > RbtnExport.Visible = False
    > Select Case RbtnExport.SelectedItem.Value
    > Case "Excel"
    > Response.ContentType = "application/x-msexcel"
    > Case "Word"
    > Response.ContentType = "application/msword"
    > End Select
    > Response.AddHeader("Content-Disposition", "Attachment; filename=" & sFile)
    > End Sub
    >
    > To allow a user to save a web page with a datagrid on it into an Excel
    > sheet.
    >
    > They'll run a page using a Web check box, which renders it to an ASPX
    > page,
    > and then when they have the final product on the screen, they click the
    > radio button for Excel, and it prompts them to Open or to Save the file.
    >
    > The problem occurs when you save or open the file.
    >
    > The datagrid has 9 columns in it, a product code, description, unit of
    > measure, price, product hierarchy number, weight and UPC code, etc.
    >
    > The data grid has a header & a footer on it, and each column in the
    > datagrid
    > is sortable.
    >
    > If I have a short list, one of maybe less than 20 records, I can click the
    > Excel button, it prompts me to save it, and it'll save as a document with
    > a
    > .xls extension. You can then open it up without incident.
    >
    > If the list is longer - say maybe over 40-50 rows in length (up to the
    > longest list, with up to 500 or more items in it), it will save it with a
    > .xls extension, but you cannot open it.
    >
    > Excel attempts to open it, but it immediately gives you an OK box that
    > says
    > "Unable to read file". It works fine if the number of rows is small
    > (under
    > 25 or so), but not if it's much more than that. Thus far, I have not seen
    > a
    > pattern in it. The file that will open is 20K in size, the one that will
    > not is 400K in size. I have figured that the difference is between 30 and
    > 50
    > records before it gives me this problem. I've noticed that each line
    > appears to add about 1K in file size to it the excel file.
    >
    > It doesn't appear to be a memory issue, as excel works fine any other
    > time,
    > and will open spreadsheets much larger than this. I've confirmed that
    > it's
    > not version specific in Excel, as it barfs on every version of excel it
    > tries to open it in.
    >
    > Any idea as to what is going on here on this problem? Is there any way I
    > can step thru the open of the file in Excel, to see the specific problem
    > Excel has with opening the file?
    >
    > Any help/advice appreciated.
    >
    > Thanks,
    >
    > SC
    >
    >
    Peter Bromberg [C# MVP], Feb 21, 2004
    #2
    1. Advertising

  3. Peter:

    Before I get too far into looking into this option (RenderControl) - a quick
    question:

    On our web pages, the datagrids may have a number of columns - like for
    complete shipments, you'd have $$ ordered & shipped, and for incomplete
    ones, you'd have those 2 colums + an open $$ and qty - meaning, that we'd
    have 6 columns in some datagrids, and 10 in others. Too, the columns would
    be named differently in each of the datagrids, as they're built from a SQL
    command.

    Does the Rendercontrol command force you to write routines to render the
    datagrid based on the number of columns you have?

    To answer your question, there are formulas involved, but they're in sql -
    i.e. ordered-shipped = open etc.

    SC




    "Peter Bromberg [C# MVP]" <> wrote in message
    news:eA6rZbC%...
    > Steve,
    > If there are no formulas to be involved, you might want to look at using

    the
    > RenderControl method of the DataGrid (and most other ASP.NET controls)
    > This will spit out the HTML representation of the DataGrid.
    >
    > You can find plenty of examples of doing this on the .NET.
    > --Peter
    >
    > "Steve Chatham" <> wrote in message
    > news:OhDJd1$...
    > >I use the following code:
    > >
    > > 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("/", "")
    > > RbtnExport.Visible = False
    > > Select Case RbtnExport.SelectedItem.Value
    > > Case "Excel"
    > > Response.ContentType = "application/x-msexcel"
    > > Case "Word"
    > > Response.ContentType = "application/msword"
    > > End Select
    > > Response.AddHeader("Content-Disposition", "Attachment; filename=" &

    sFile)
    > > End Sub
    > >
    > > To allow a user to save a web page with a datagrid on it into an Excel
    > > sheet.
    > >
    > > They'll run a page using a Web check box, which renders it to an ASPX
    > > page,
    > > and then when they have the final product on the screen, they click the
    > > radio button for Excel, and it prompts them to Open or to Save the file.
    > >
    > > The problem occurs when you save or open the file.
    > >
    > > The datagrid has 9 columns in it, a product code, description, unit of
    > > measure, price, product hierarchy number, weight and UPC code, etc.
    > >
    > > The data grid has a header & a footer on it, and each column in the
    > > datagrid
    > > is sortable.
    > >
    > > If I have a short list, one of maybe less than 20 records, I can click

    the
    > > Excel button, it prompts me to save it, and it'll save as a document

    with
    > > a
    > > .xls extension. You can then open it up without incident.
    > >
    > > If the list is longer - say maybe over 40-50 rows in length (up to the
    > > longest list, with up to 500 or more items in it), it will save it with

    a
    > > .xls extension, but you cannot open it.
    > >
    > > Excel attempts to open it, but it immediately gives you an OK box that
    > > says
    > > "Unable to read file". It works fine if the number of rows is small
    > > (under
    > > 25 or so), but not if it's much more than that. Thus far, I have not

    seen
    > > a
    > > pattern in it. The file that will open is 20K in size, the one that

    will
    > > not is 400K in size. I have figured that the difference is between 30

    and
    > > 50
    > > records before it gives me this problem. I've noticed that each line
    > > appears to add about 1K in file size to it the excel file.
    > >
    > > It doesn't appear to be a memory issue, as excel works fine any other
    > > time,
    > > and will open spreadsheets much larger than this. I've confirmed that
    > > it's
    > > not version specific in Excel, as it barfs on every version of excel it
    > > tries to open it in.
    > >
    > > Any idea as to what is going on here on this problem? Is there any way

    I
    > > can step thru the open of the file in Excel, to see the specific problem
    > > Excel has with opening the file?
    > >
    > > Any help/advice appreciated.
    > >
    > > Thanks,
    > >
    > > SC
    > >
    > >

    >
    >
    Steve Chatham, Feb 23, 2004
    #3
  4. A follow-up -

    I have found some code:

    Sub Page_Load(sender as Object, e as EventArgs)
    '1. Create a connection
    Dim myConnection as New
    SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

    '2. Create the command object, passing in the SQL string
    Const strSQL as String = "sp_Popularity"
    Dim myCommand as New SqlCommand(strSQL, myConnection)

    'Set the datagrid's datasource to the datareader and databind
    myConnection.Open()
    dgPopularFAQs.DataSource =
    myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    dgPopularFAQs.DataBind()


    'Get the rendered HTML
    Dim SB as New StringBuilder()
    Dim SW as New StringWriter(SB)
    Dim htmlTW as New HtmlTextWriter(SW)
    dgPopularFAQs.RenderControl(htmlTW)

    Dim dataGridHTML as String = SB.ToString()

    ltlHTMLOutput.Text = Server.HtmlEncode(dataGridHTML)
    End Sub

    which I think will render in HTML, only question is - how does this work
    with Excel? Would I still use the same method I did before?

    Case "Excel"
    Response.ContentType = "application/x-msexcel"
    Case "Word"
    Response.ContentType = "application/msword"
    End Select
    Response.AddHeader("Content-Disposition", "Attachment; filename=" & sFile)

    (where I am using the response.contenttype property)?

    SC



    "Steve Chatham" <> wrote in message
    news:%23tF1Ubh%...
    > Peter:
    >
    > Before I get too far into looking into this option (RenderControl) - a

    quick
    > question:
    >
    > On our web pages, the datagrids may have a number of columns - like for
    > complete shipments, you'd have $$ ordered & shipped, and for incomplete
    > ones, you'd have those 2 colums + an open $$ and qty - meaning, that we'd
    > have 6 columns in some datagrids, and 10 in others. Too, the columns

    would
    > be named differently in each of the datagrids, as they're built from a SQL
    > command.
    >
    > Does the Rendercontrol command force you to write routines to render the
    > datagrid based on the number of columns you have?
    >
    > To answer your question, there are formulas involved, but they're in sql -
    > i.e. ordered-shipped = open etc.
    >
    > SC
    >
    >
    >
    >
    > "Peter Bromberg [C# MVP]" <> wrote in message
    > news:eA6rZbC%...
    > > Steve,
    > > If there are no formulas to be involved, you might want to look at using

    > the
    > > RenderControl method of the DataGrid (and most other ASP.NET controls)
    > > This will spit out the HTML representation of the DataGrid.
    > >
    > > You can find plenty of examples of doing this on the .NET.
    > > --Peter
    > >
    > > "Steve Chatham" <> wrote in message
    > > news:OhDJd1$...
    > > >I use the following code:
    > > >
    > > > 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("/", "")
    > > > RbtnExport.Visible = False
    > > > Select Case RbtnExport.SelectedItem.Value
    > > > Case "Excel"
    > > > Response.ContentType = "application/x-msexcel"
    > > > Case "Word"
    > > > Response.ContentType = "application/msword"
    > > > End Select
    > > > Response.AddHeader("Content-Disposition", "Attachment; filename=" &

    > sFile)
    > > > End Sub
    > > >
    > > > To allow a user to save a web page with a datagrid on it into an Excel
    > > > sheet.
    > > >
    > > > They'll run a page using a Web check box, which renders it to an ASPX
    > > > page,
    > > > and then when they have the final product on the screen, they click

    the
    > > > radio button for Excel, and it prompts them to Open or to Save the

    file.
    > > >
    > > > The problem occurs when you save or open the file.
    > > >
    > > > The datagrid has 9 columns in it, a product code, description, unit of
    > > > measure, price, product hierarchy number, weight and UPC code, etc.
    > > >
    > > > The data grid has a header & a footer on it, and each column in the
    > > > datagrid
    > > > is sortable.
    > > >
    > > > If I have a short list, one of maybe less than 20 records, I can click

    > the
    > > > Excel button, it prompts me to save it, and it'll save as a document

    > with
    > > > a
    > > > .xls extension. You can then open it up without incident.
    > > >
    > > > If the list is longer - say maybe over 40-50 rows in length (up to the
    > > > longest list, with up to 500 or more items in it), it will save it

    with
    > a
    > > > .xls extension, but you cannot open it.
    > > >
    > > > Excel attempts to open it, but it immediately gives you an OK box that
    > > > says
    > > > "Unable to read file". It works fine if the number of rows is small
    > > > (under
    > > > 25 or so), but not if it's much more than that. Thus far, I have not

    > seen
    > > > a
    > > > pattern in it. The file that will open is 20K in size, the one that

    > will
    > > > not is 400K in size. I have figured that the difference is between 30

    > and
    > > > 50
    > > > records before it gives me this problem. I've noticed that each line
    > > > appears to add about 1K in file size to it the excel file.
    > > >
    > > > It doesn't appear to be a memory issue, as excel works fine any other
    > > > time,
    > > > and will open spreadsheets much larger than this. I've confirmed that
    > > > it's
    > > > not version specific in Excel, as it barfs on every version of excel

    it
    > > > tries to open it in.
    > > >
    > > > Any idea as to what is going on here on this problem? Is there any

    way
    > I
    > > > can step thru the open of the file in Excel, to see the specific

    problem
    > > > Excel has with opening the file?
    > > >
    > > > Any help/advice appreciated.
    > > >
    > > > Thanks,
    > > >
    > > > SC
    > > >
    > > >

    > >
    > >

    >
    >
    Steve Chatham, Feb 23, 2004
    #4
    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. Samuël van Laere

    To dot or not to dot?

    Samuël van Laere, Oct 16, 2003, in forum: HTML
    Replies:
    8
    Views:
    416
    Samuël van Laere
    Oct 16, 2003
  2. Christopher M. Lusardi

    volatile struct in dot h vs dot c

    Christopher M. Lusardi, May 11, 2004, in forum: C Programming
    Replies:
    3
    Views:
    465
    Peter Shaggy Haywood
    May 15, 2004
  3. Nathan Sokalski
    Replies:
    11
    Views:
    690
    AAaron123
    Aug 14, 2009
  4. krishnan

    Dot Net Project Execution without Dot Net and Framework....

    krishnan, Jan 7, 2006, in forum: ASP .Net Building Controls
    Replies:
    0
    Views:
    175
    krishnan
    Jan 7, 2006
  5. Replies:
    6
    Views:
    231
    Thomas 'PointedEars' Lahn
    Dec 12, 2005
Loading...

Share This Page