0

I just want to stream a xlsx file using NPOI SXSSF method. But when I download streamed excel file in opening pops up message:

Excel found unreadable content in 'MyExcelFile.xlsx'. Do you want to recover the contents of the workbook? If you trust the source of this workbook, click Yes.

My code:

Response.Headers.Add("Content-disposition", "attachment; filename=\"MyExce.xlsx\"");
Response.Headers.Add("Content-type", "octet-stream");


SXSSFWorkbook wb = new SXSSFWorkbook();
SXSSFSheet sheet = (SXSSFSheet)wb.CreateSheet("FirstSheet");

IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue("firstCell");

//Write file to output stream
wb.Write(Response.Body);
wb.Close();
Response.Body.Flush();

What am I doing wrong?

Ondra Starenko
  • 428
  • 1
  • 6
  • 12

1 Answers1

1

For wb.Write(Response.Body), it will dispose the Stream after wb.Write and Response.Body will be broken.

Try code below, to save the stream and write the saved stream to body. 

public void DownloadFile()
{
    MemoryStream ms = new MemoryStream();
    using (MemoryStream stream = new MemoryStream())
    {
        Response.Headers.Add("Content-disposition", "attachment; filename=\"MyExce.xlsx\"");
        Response.Headers.Add("Content-type", "octet-stream");

        SXSSFWorkbook wb = new SXSSFWorkbook();
        SXSSFSheet sheet = (SXSSFSheet)wb.CreateSheet("FirstSheet");

        IRow row = sheet.CreateRow(0);
        ICell cell = row.CreateCell(0);
        cell.SetCellValue("firstCell");

        //Write file to output stream
        wb.Write(stream);
        var byteArray = stream.ToArray();
        ms.Write(byteArray, 0, byteArray.Length);
        ms.Seek(0, SeekOrigin.Begin);
        ms.WriteTo(Response.Body);
    }
}
Edward
  • 28,296
  • 11
  • 76
  • 121
  • Thank you, this looks good but I want to stream excel file that has more than 500 000 rows and I have problems with ram memory.. that's why I don't want to hold the file in ram memory. Do you have any idea how to do that? – Ondra Starenko Dec 17 '18 at 07:42
  • 1
    @OndraStarenko You may consider consulting `NPOI` developer in the github by submitting issues. – Edward Dec 17 '18 at 08:11