NPOI export excel directly to frontend without saving it on server

Question

Is it possible to write an excel file (with NPOI) directly to browser without saving it first on the server.

I tried following in my controller without success:

public async Task<IActionResult> ExportExcel(){

    var fs = new MemoryStream();

    IWorkbook workbook;
    workbook = new XSSFWorkbook();
    ISheet excelSheet = workbook.CreateSheet("Test);

    IRow row = excelSheet.CreateRow(0);
    row.CreateCell(0).SetCellValue("ID");
    row.CreateCell(1).SetCellValue("Name");

    row = excelSheet.CreateRow(1);
    row.CreateCell(0).SetCellValue(1);
    row.CreateCell(1).SetCellValue("User 1");

    workbook.Write(fs);

    byte[] bytes = new byte[fs.Length];
    fs.Read(bytes, 0, (int)fs.Length);


    return File(bytes, "application/vnd.ms-excel", sampleType.Abbreviation+".xlsx"); 
}

When executing above method I always get following error:

ObjectDisposedException: Cannot access a closed Stream.

...
System.IO.MemoryStream.get_Length()
byte[] bytes = new byte[fs.Length];
...

Or is their another great nuget package to handle (read and write) excel files without storing files on server?

PS: I am using dotnet core 2.1 en nuget package: https://www.nuget.org/packages/NPOI/

Answer 1

1. Write directly to the Response.Body.
2. Because Excel is treated as an attachment, we also need to set the Response.Headers

To make life easy, we can create an extension method firstly:

public static class IWorkBookExtensions {

    public static void WriteExcelToResponse(this IWorkbook book, HttpContext httpContext, string templateName)
    {
        var response = httpContext.Response;
        response.ContentType = "application/vnd.ms-excel";
        if (!string.IsNullOrEmpty(templateName))
        {
            var contentDisposition = new Microsoft.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
            contentDisposition.SetHttpFileName(templateName);
            response.Headers[HeaderNames.ContentDisposition] = contentDisposition.ToString();
        }
        book.Write(response.Body);
    }
}

and now we can export the excel file directly :

public async Task ExportExcel(){

    IWorkbook workbook;
    workbook = new XSSFWorkbook();
    ISheet excelSheet = workbook.CreateSheet("Test");

    IRow row = excelSheet.CreateRow(0);
    row.CreateCell(0).SetCellValue("ID");
    row.CreateCell(1).SetCellValue("Name");

    row = excelSheet.CreateRow(1);
    row.CreateCell(0).SetCellValue(1);
    row.CreateCell(1).SetCellValue("User 1");

    workbook.WriteExcelToResponse(HttpContext,"test.xlsx");
}