epplus保存为流_EPPlus-保存在内存中创建的Excel并在excel上打开会出现错误

The objective is to create an excel file in memory and download it in the browser by using an API built in ASP.Net Core, however saving the excel as a stream and converting it to a byte array and then opening it on excel (Office 365 version 1803) gives the error:

Code Byte Array:

public IActionResult Export()

{

byte[] bytes;

MemoryStream stream = new MemoryStream();

using (ExcelPackage package = new ExcelPackage(stream))

{

ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Employee");

//First add the headers

worksheet.Cells[1, 1].Value = "ID";

worksheet.Cells[1, 2].Value = "Name";

worksheet.Cells[1, 3].Value = "Gender";

worksheet.Cells[1, 4].Value = "Salary (in $)";

//Add values

worksheet.Cells["A2"].Value = 1000;

worksheet.Cells["B2"].Value = "Jon";

worksheet.Cells["C2"].Value = "M";

worksheet.Cells["D2"].Value = 5000;

worksheet.Cells["A3"].Value = 1001;

worksheet.Cells["B3"].Value = "Graham";

worksheet.Cells["C3"].Value = "M";

worksheet.Cells["D3"].Value = 10000;

worksheet.Cells["A4"].Value = 1002;

worksheet.Cells["B4"].Value = "Jenny";

worksheet.Cells["C4"].Value = "F";

worksheet.Cells["D4"].Value = 5000;

bytes = package.GetAsByteArray();

}

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

}

UPDATE: Added Memory stream example

Code Memory Stream:

public IActionResult Export()

{

using (ExcelPackage package = new ExcelPackage())

{

ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Employee");

//First add the headers

worksheet.Cells[1, 1].Value = "ID";

worksheet.Cells[1, 2].Value = "Name";

worksheet.Cells[1, 3].Value = "Gender";

worksheet.Cells[1, 4].Value = "Salary (in $)";

//Add values

worksheet.Cells["A2"].Value = 1000;

worksheet.Cells["B2"].Value = "Jon";

worksheet.Cells["C2"].Value = "M";

worksheet.Cells["D2"].Value = 5000;

worksheet.Cells["A3"].Value = 1001;

worksheet.Cells["B3"].Value = "Graham";

worksheet.Cells["C3"].Value = "M";

worksheet.Cells["D3"].Value = 10000;

worksheet.Cells["A4"].Value = 1002;

worksheet.Cells["B4"].Value = "Jenny";

worksheet.Cells["C4"].Value = "F";

worksheet.Cells["D4"].Value = 5000;

var stream = new MemoryStream(package.GetAsByteArray());

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

}

}

UPDATE 2: Created a new project with .net core 2.1 and the code works. It seems to be a project related error, somehow the content of the requests are being manipulated.. Going to investigate what is causing the issue and also post it's resolution.

解决方案

I implemented a similar scenario using package.Stream instead of package.GetAsByteArray.

(This last one also fails for me.)

Notice the package.Save which I miss in your code.

(GetAsByteArray also fails, even with Package.Save.)

public IActionResult Export()

{

MemoryStream content = new MemoryStream(); // Gets disposed by FileStreamResult.

using (ExcelPackage package = new ExcelPackage(content))

{

// Code to create the content goes here.

package.Save();

}

content.Position = 0;

return new FileStreamResult(content, "application/octet-stream") {

FileDownloadName = "test.xlsx"

};

}