NPOI
NPOI copied to clipboard
create a new xlsx and write to memory stream
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<IActionResult> 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;
}
}
@livioco did you manage to find a solution to this? I have stumbled across the same issue.
I think it due to the using keyword. MemoryStream is getting disposed before being written into the http response
@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"); }
return File(memoryStream.ToArry(), "application/vnd.ms-excel", "test.xlsx");
As I remember, this issue has been fixed in NPOI official release. Please use nuget.org/packages/NPOI/ instead.
xhelper [email protected] 于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 https://github.com/dotnetcore/NPOI/issues/171#issuecomment-665476801, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAF4TULDTA4OJTRRVIHSZWLR57DBPANCNFSM4LSSK4TA .