Inability to set Session values or Update page objects

Mar 24, 2011 at 8:59 PM

I discovered a behavior involving ClosedXML that I don't fully understand, but I would like to create a workaround (or solve the problem directly!). The behavior involves a postback during which a ClosedXML call is made to display a spreadsheet in the browser by using the "SaveAs" feature on a memory stream. If I attempt to set a textbox value on the web page during this postback, it fails every time (even though I see the step being processed in debug mode). The same is true for trying to set a Session value during the postback - fails every time. If I comment-out the call to ClosedXML, either method of setting a value DOES work every time! My code is successfully writing to a SQL database during this postback - just Page and Session objects seem to be affected.

I am using the default ("InProc") Session State method. How is ClosedXML interfering with Session and Page changes during postback? Is there a way to prevent this interference? Is there a workaround to avoid this behavior (e.g. change Session State method)?

Mar 24, 2011 at 9:12 PM

I honestly don't know what could be happening here. How can I reproduce this error?

Mar 24, 2011 at 10:17 PM

Assuming you are using "InProc" Session State, just create a sample web page with a single textbox and a button. When the button is clicked, get any dataset and display it using ClosedXML. During the same button click operation (either before or after the call to ClosedXML - both fail!), set the value of the textbox to anything you wish. After you view the spreadsheet and close it, see if the value displays in the textbox. In my case, it was blank if ClosedXML was called, but displayed properly if ClosedXML was not called.

Mar 24, 2011 at 10:32 PM

Can you post the piece of code where you deliver the excel file to the user? My guess is that you're saving to the response stream and thus overriding the change in the textbox.

Mar 24, 2011 at 10:46 PM

        Dim ms As New System.IO.MemoryStream


        Response.Buffer = True
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"
        Response.AddHeader("Content-Disposition", "attachment; filename=" & strFile & ".xlsx")

        Response.Charset = ""

Mar 24, 2011 at 10:53 PM

Yup. What gets back to the client is an Excel file, not a web page with the new information (the changes in the textbox). You'll get the same behavior if you use any other stream of data, not just one populated with ClosedXML.

Mar 24, 2011 at 11:02 PM

Thanks for the education! I'll have to separate the "save data" (save to db and set flags on page) and "view file" actions into 2 separate steps. Somewhat awkward for the user, but I do need a stream at the end of the process.