Use NPIO To Make Excel Then Send Back With FileStreamResult?

Question

I am trying to make an excel file and then send it back via the FileStreamResult. I am using asp.net core

I am starting to use NIPO example

var newFile = @"newbook.core.xlsx";

            var fs = new MemoryStream();


                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);

                workbook.Write(fs);

But it seems to save it to my project solution.

I am also seeing this error

   <div class="titleerror">ObjectDisposedException: Cannot access a closed Stream.</div>

score 0 · Answer 1 · answered Dec 10 '19 at 03:19

According to your code , you can return File with correct content-type :

var newFile = @"newbook.core.xlsx";

var fs = new MemoryStream();


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);

workbook.Write(fs);
MemoryStream memoryStream = new MemoryStream(fs.ToArray());
memoryStream.Position = 0;


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

@chobo2 The NOPI write function seems will dispose the steam , you can also try inherits MemoryStream, and overrides the Close method as shown [here](https://stackoverflow.com/a/37398007/5751404) . But use 2 stream looks simpler . — Nan Yu, Dec 11 '19 at 01:00

Use NPIO To Make Excel Then Send Back With FileStreamResult?

1 Answers1