C# – MemoryStream seems be closed after NPOI workbook.write

asp.net-web-apicexcelnpoi

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?

Best Answer

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.

Related Topic