Response.Write writes whole page

[JimmyFo]

Hi, I am trying to write a file out to an Excel, tab-delimeted file. I do this by going through a datatable, getting the values and creating a tab-delimited string, which I then try to write out. However, when I use the response.write to spit the file out, it writes the string AS WELL AS all the code for the page, the divs/forms/buttons everything. Any ideas? Is there a better way to export excel without having it installed on the web server? Here is the code, how can I get rid of all the page's code in the output...

Try
                'execute and fill the dataset
                sqlConn.Open()
                Dim ds As DataSet = New DataSet
                da.Fill(ds, "tblPortfolio")
                sqlConn.Close()

                'export to excel
                Response.ContentType = "application/ms-excel"
                Response.AddHeader("Content-Disposition", "inline;filename=Portfolio_" & ds.Tables("tblPortfolio").Rows(0).Item(1).ToString & ".xls")
                Dim str_data As String = ""
                ConvertDtToTDF(ds.Tables("tblPortfolio"), str_data)
                Response.Write(str_data)
            Catch ex As Exception
                error_label.Text = "There was a problem exporting the portfolio."
            End Try

To generate tab-delimted string:

Private Function ConvertDtToTDF(ByVal dt As DataTable, ByRef str_data As String) As String
        Dim dr As DataRow, ary() As Object, i As Integer
        Dim iCol As Integer

        'Output Column Headers 
        For iCol = 0 To dt.Columns.Count - 1
            str_data += dt.Columns(iCol).ToString & vbTab
        Next
        str_data += vbCrLf

        'Output Data 
        For Each dr In dt.Rows
            ary = dr.ItemArray
            For i = 0 To UBound(ary)
                str_data += ary(i).ToString & vbTab
            Next
            str_data += vbCrLf
        Next
    End Function

Thanks,
James

 

[ca8msm]

You could use the RenderControl method of whatever you are displaying the data in. See here for an example.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[JimmyFo]

Thanks, that's working - I've got a few quirks to try and fix, but it seems good! Thanks,
James

 

[JimmyFo]

Alright, I'm messing something up here. What it looks like is it is not clearing something somewhere.

The "tblProject" datatable count is 3, so while it loops through three times, it does a select with the currently selected index in the datatable and writes the data out. However, it seems to accumulate all the data, i.e. the first write out works with the correct data, the second writes out with the data from the first and second, the third from all three up to that point, and so on.

Any ideas?

sqlCmd.CommandText = "uspSELECT_MILESTONE"
                sqlCmd.Parameters.Clear()
                sqlCmd.Parameters.Add("p_PROJECTID", SqlDbType.Int)
                For index = 0 To ds.Tables("tblProject").Rows.Count - 1
                    sqlCmd.Parameters.Item("p_PROJECTID").Value = ds.Tables("tblProject").Rows(index).Item(0).ToString
                    sqlConn.Open()
                    da.Fill(ds, "tblMilestone")
                    sqlConn.Close()
                    grid_export_milestone.DataSource = ds.Tables("tblMilestone")
                    grid_export_milestone.DataBind()
                    grid_export_milestone.RenderControl(hw_milestone)
                    Response.Write(sw_milestone.ToString)
                    ds.Tables("tblMilestone").Clear()
                Next index

Thanks,
James

 

[ca8msm]

Why are you looping through the rows? Did you follow the example from the link I gave above?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[JimmyFo]

Yeah, I followed that example and it works well - the thing is, this is exporting data not visible on the screen - so I declare a datagrid programmatically, bind it/format it, then run the export procedure.

For each project now in "tblProject", there are milestones for "tblMilestone" which are filled one at a time, written to the response, then emptied. This is so that I can write out a project then all milestones associated with it, then the next followed by its milestones, etc. This is for formatting the Excel sheet to look nice. So I go through the project table, select (from stored procedure) the milestones for that project and write out, then go to the next and do the same.

I believe the problem is that the milestones are piling up, from not being cleared from something. Any ideas? Your previous link was a really good guide!

Thanks,
James

 

[ca8msm]

I think the easiest method here will be to forget about the excel bit for now. Get it working on the screen exactly as you want it to look and then you can worry about the export later. This way should be much easier as you can at least see what is going on.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Aptitude]

Try sticking in Response.Clear(); before you start and Response.End(); after you finished. This should get rid of all the other HTML the page generates.