MemoryStream seems be closed after NPOI workbook.w

Question

I am using NPOI to convert DataTable to Excel in a ASP.NET Web API project.

But the I got nothing from the response. Here's my code:

public HttpResponseMessage GetExcelFromDataTable(DataTable dt)
{
    IWorkbook workbook = new XSSFWorkbook(); // create *.xlsx file, use HSSFWorkbook() for creating *.xls file.
    ISheet sheet1 = workbook.CreateSheet();
    IRow row1 = sheet1.CreateRow(0);
    for (int i = 0; dt.Columns.Count > i; i++)
    {
        row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
    }

    for (int i = 0; dt.Rows.Count > i; i++)
    {
        IRow row = sheet1.CreateRow(i + 1);
        for (int j = 0; dt.Columns.Count > j; j++)
        {
            row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
        }
    }

    MemoryStream ms = new MemoryStream();
    workbook.Write(ms);
    HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
    result.Content = new StreamContent(ms);
    result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
    result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
    result.Content.Headers.ContentDisposition.FileName = string.Format("{0}.xlsx", dt.TableName);
    return result;
}

I set a break point to inspect the ms.Length after workbook.Write(ms), but it return a exception : System.ObjectDisposedException.

Where did I go wrong?

Answer 1

Update 1/3/2020: As Florian Dendorfer pointed out, there is an override added in October 2018 to prevent the stream from closing. Please try the overload first before using this workaround (and upvote Florian's answer!)

Leaving original answer for historical purposes.

---

Another workaround to this issue...which doesn't use multiple MemoryStream objects.

Create a NpoiMemoryStream class that inherits MemoryStream, and overrides the Close method:

public class NpoiMemoryStream : MemoryStream
{
    public NpoiMemoryStream()
    {
        // We always want to close streams by default to
        // force the developer to make the conscious decision
        // to disable it.  Then, they're more apt to remember
        // to re-enable it.  The last thing you want is to
        // enable memory leaks by default.  ;-)
        AllowClose = true;
    }

    public bool AllowClose { get; set; }

    public override void Close()
    {
        if (AllowClose)
            base.Close();
    }
}

Then, use that stream like this:

var ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;

At some point between the flush and seek, NPOI will attempt to close the stream, but since we overrode Close() and the AllowClose flag is false, we can keep the stream open. Then, set AllowClose back to true so normal disposal mechanisms can close it.

Don't get me wrong...this is still a hack that shouldn't need to be implemented...but it's a bit cleaner from a memory usage standpoint.

Answer 2

I don't know if this is still needed, but there's an overload

Write(Stream stream, bool leaveOpen)

where, if you set leaveOpen = true, leaves your MemoryStream open

Answer 3

As alun stated above, and also in this question you can feed the stream into another MemoryStream:

...
MemoryStream ms = new MemoryStream();
using(MemoryStream tempStream = new MemoryStream)
{
    workbook.Write(tempStream);
    var byteArray = tempStream.ToArray();
    ms.Write(byteArray, 0, byteArray.Length);
    HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
    result.Content = new StreamContent(ms);
    result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
    result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
    result.Content.Headers.ContentDisposition.FileName = string.Format("{0}.xlsx", dt.TableName);
    return result;
}

There is a little code smell from having to do this. However, this is only necessary when outputting .xlsx files due to the way the 3rd party libraries involved handle the stream.

Answer 4

I've encountered similar issues with APIs that close/dispose streams that they don't own. I'm not familiar with NPOI, but I assume that the Write method is accepting Stream, not MemoryStream. If that is the case, you can create a wrapper Stream class that forwards all calls (read/write/seek, etc) to the inner stream (your MemoryStream in this case), but doesn't forward calls to close/dispose. Pass the wrapper to the Write method, when it returns your MemoryStream should contain all the content and still be "open".

Additionally, you'll probably need to ms.Seek(0, SeekOrigin.Begin). After the call to Write your memory stream will be positioned at the end of the stream, so if you try to read from that position it will appear emtpy.