XSSFWorkbook Write to MemoryStream

Jul 23, 2015 at 1:33 PM
Edited Jul 23, 2015 at 1:34 PM
I have seen many posts about this issue but no working solutions. I have a web application which writes XLS files using HSSFWorkbook through MemoryStream just fine. I want to have my application export files as XLSX now. I changed all HSSF objects to XSSF figuring that would work. I got the following error:

"An exception of type 'System.ObjectDisposedException' occurred in mscorlib.dll but was not handled in user code. Additional information: Cannot access a closed Stream."

Apparently the XSSF Write method is designed to close the stream once it is written by default. To me this makes absolutely zero sense since if it closes immediately you can never do anything with the object you just attempted to write. Does anyone have an alternative way to write XLSX files to a MemoryStream for download from a web application or should I ditch NPOI for another library?

NOTE: I do not want FileStream examples. We do not want to write temporary files to disk in order to download.
context.Response.Clear();
context.Response.ContentType = Helper.ContentType(".xlsx");
context.Response.AddHeader("Content-Disposition", string.Format("inline;filename={0}.xlsx;", this.FileName));
using (MemoryStream MyMemory = new MemoryStream())
{
    MyWorkbook.Write(MyMemory);
    context.Response.AddHeader("Content-Length", MyMemory.Length.ToString());
    context.Response.BinaryWrite(MyMemory.GetBuffer());
}
context.Response.End();
Jul 27, 2015 at 11:41 AM
Try
BinaryWrite(MyMemory.ToArray())
Jul 27, 2015 at 12:26 PM
rei0429 wrote:
Try
BinaryWrite(MyMemory.ToArray())
The issue is not the BinaryWrite command. The issue is the default logic in the NPOI library to close the stream after completing the write command in the XSSFWorkbook. Based on other posts this is the chosen functionality for the NPOI library but it only applies to XSSF and not HSSF. The same issue does not apply to the EPPlus 4.0.4 library when it writes XLSX so I believe this is a coding mistake. The stream being written needs to stay open.
Coordinator
Aug 9, 2015 at 10:53 PM
It's not a code mistake. We know the issue very well. But it's not reasonable that a zip stream is kept open after all the content is included. The stream in .Net is not so convenient as Java does.

Anyway, we will reconsider to keep the stream open in next version although it's weird.