create a new xlsx and write to memory stream

[livioco]

I have to create a new xlsx from server side (without save it on server side), but I have to download the file on client side.

I have this error " Cannot access a closed Stream"

This is the code. Any suggestions?

[HttpGet]
public async Task DownloadXLS()
{
try
{

            IWorkbook workbook = new XSSFWorkbook();

            ISheet sheet1 = workbook.CreateSheet("Sheet1");

            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
            var rowIndex = 0;
            IRow row = sheet1.CreateRow(rowIndex);
            row.Height = 30 * 80;
            row.CreateCell(0).SetCellValue("this is content");
            sheet1.AutoSizeColumn(0);
            rowIndex++;

            var sheet2 = workbook.CreateSheet("Sheet2");
            var style1 = workbook.CreateCellStyle();
            style1.FillForegroundColor = HSSFColor.Blue.Index2;
            style1.FillPattern = FillPattern.SolidForeground;

            var style2 = workbook.CreateCellStyle();
            style2.FillForegroundColor = HSSFColor.Yellow.Index2;
            style2.FillPattern = FillPattern.SolidForeground;

            var cell2 = sheet2.CreateRow(0).CreateCell(0);
            cell2.CellStyle = style1;
            cell2.SetCellValue(0);

            cell2 = sheet2.CreateRow(1).CreateCell(0);
            cell2.CellStyle = style2;
            cell2.SetCellValue(1);

            using (var memoryStream = new MemoryStream()) //creating memoryStream
            {
                workbook.Write(memoryStream);

                return File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "test.xlsx");
            }

        }
        catch(Exception ex)
        {
            return null;

        }


    }

[matfricker]

@livioco did you manage to find a solution to this? I have stumbled across the same issue.

[Yousefjb]

I think it due to the using keyword.
MemoryStream is getting disposed before being written into the http response

[nmase88]

@matfricker @Yousefjb if you do the below instead you won't get this error, it will return a FileContentResult instead of FileStreamResult however. If you need a FileStreamResult you would have to create a new Memorystream and pass it the byte array.
using (var memoryStream = new MemoryStream()) //creating memoryStream { workbook.Write(memoryStream); var byteArray = memoryStream.ToArray(); return File(byteArray , "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "test.xlsx"); }

[ternnice]

return File(memoryStream.ToArry(), "application/vnd.ms-excel", "test.xlsx");

[tonyqus]

As I remember, this issue has been fixed in NPOI official release. Please use nuget.org/packages/NPOI/ instead. xhelper <notifications@github.com> 于2020年7月29日周三 下午3:07写道：

…

return File(memoryStream.GetBuffer(), "application/vnd.ms-excel", "test.xlsx"); 流关闭了再读取一次数据，使用另外一个重载方法返回； — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#171 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAF4TULDTA4OJTRRVIHSZWLR57DBPANCNFSM4LSSK4TA> .