This project has moved and is read-only. For the latest updates, please go here.

How do I send data to excel and then refresh aspx page?

Dec 26, 2013 at 8:32 PM
I have a gridview that the user can select rows from to export that data to excel. After they select the rows they want to export they press a button. After I export the data to the spread sheet I want my page to refresh. I rebind my gridview to nothing to clear it out, but my page is not getting refreshed.

Any ideas?

Here is my code to put the datatable that I extracted from the gridview to excel ...
 Dim wb As New ClosedXML.Excel.XLWorkbook()
 wb.Worksheets.Add(dt, dt.TableName.ToString)
 Response.Clear()
 Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  Response.AddHeader("content-disposition", "attachment;filename=MasterCredReport.xlsx")
  Using memoryStream As New MemoryStream()
          wb.SaveAs(memoryStream)
          memoryStream.WriteTo(Response.OutputStream)
          memoryStream.Close()
  End Using
Dec 27, 2013 at 3:03 PM
I don't believe that it is possible to return a file to the browser and simultaneously redirect or refresh the web page. Note that the browser is occupied interacting with the user to download the file, so it is not listening for other instructions.

However, if anyone out there knows how to do something like this, I would also appreciate hearing how it can be done.
Dec 27, 2013 at 3:53 PM
What your saying is that when I do the memoryStream write, the browser is doing the downloading and then prompting the user to either open or save the file and does not come back to finish interacting with my page? So is there a way to write the memorysteam to another browser window or tab?
Dec 30, 2013 at 4:36 PM
Russ, there may be some javascript functions that could be set up to intercept the file download response and do both the download and refresh or redirect the screen after the download completes. But I have to say that in all my time using the web, I don't recall any time where a file was downloaded and then I got redirected to another page.

However, you might be able to do what you ask by saving the file locally (either synchronously or asynchronously), returning a refresh or redirect command and then doing the download from the redirected screen. Though this would leave the file on the server.
Dec 30, 2013 at 5:58 PM
I just resolved it. Not sure if this is the best way, but it works.

First ... I created a new page that will save the data to the excel in the page load.

Second ... I save my data to a session variable ... Session("dataTableToExcel") = datatable

Then I call a subroutine that will open a new page in a new window ... OpenNewWindow("\DisplayExcel.aspx")
 Protected Sub OpenNewWindow(ByVal url As String)
        Dim sw As StringWriter = New StringWriter
        sw.WriteLine("<script language=""javascript"">")
        sw.WriteLine("var newWindow = window.open(""{0}"",""SendToExcel""", url)
        sw.Write(",""height=700,width=900,resizable=yes,scrollbars=yes,toolbar=no,menubar=no"");")
        sw.WriteLine("newWindow.focus();")
        sw.WriteLine("</script>")
        Page.ClientScript.RegisterStartupScript(Me.GetType(), "Open New Window", sw.ToString())
    End Sub
The new page looks like this ....

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            Dim dt As DataTable = Session("dataTableToExcel")

            Dim wb As New ClosedXML.Excel.XLWorkbook()
            wb.Worksheets.Add(dt, dt.TableName.ToString)
            Response.Clear()
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            Response.AddHeader("content-disposition", "attachment;filename=DelegateRecordsNotImported.xlsx")

            Using memoryStream As New MemoryStream()
                wb.SaveAs(memoryStream)
                Response.AddHeader("Content-Length", memoryStream.Length.ToString())

                memoryStream.WriteTo(Response.OutputStream)
                memoryStream.Close()

            End Using
        Catch ex As Exception
            Throw
        End Try
    End Sub
Marked as answer by russnesbitt on 12/30/2013 at 9:58 AM
Dec 30, 2013 at 7:45 PM
Russ, glad you found a way to do it. I have a few comments on using session variables for this type of thing.
  1. Generally it is advised to not use session variables for very large amounts of data, but if you know that your data table is relatively small, it's ok to do it temporarily.
  2. Be sure you remove the session variable after you add it to the Excel file by doing Session.Remove("dataTableToExcel") or Session.RemoveAll().
  3. I have run into major problems using session variables for page related data, because if you user opens a new tab and goes back to the original page a new dataTableToExcel variable will be placed in the session variable and overwrite the original data. It is possible that the action in one browser tab would corrupt the data stored by the other browser tab just when it is being read into the Excel file. You would end up with rare cases of data corruption that disappear when they rerun it, and it is very hard to debug things like that.
Dec 30, 2013 at 11:38 PM
Robert,
Thanks for your input. Shortly after I posted this I did just what you said.
 Dim dt As DataTable = Session("dataTableToExcel")
 Session.Remove("dataTableToExcel")
This does two things ...
 1. It removes the data right away.  I will not be needing it after I download it to excel.
 2. Since I remove it right after I created it, the user never has a chance to create a new tab between the time I create it, use it, and then remove it.    
My data is not very large, and should never really be. This is an in-house (intranet) application so we do have somewhat more control over how our users use this page.


Thanks again for your feed back.