5

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: Excel 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.

BrunoMartinsPro
  • 1,646
  • 1
  • 24
  • 48
  • 2
    I'm currently trying your example but on looking up something i came accross [this](https://stackoverflow.com/questions/5738123/using-epplus-with-a-memorystream). According to that Thread Initialising `ExcelPackage(stream)` is known to be giving Errors and you should use `var stream = new MemoryStream(package.GetAsByteArray());` while initialising `var package = ExcelPackage())` – Severin Jaeschke Aug 02 '18 at 11:55
  • There are very important chunks of code missing from the example. – Lewis86 Aug 02 '18 at 11:56
  • 1
    Your code is working properly for me and I am able to open excel too in asp.net core 2.0. – Riddhi Aug 02 '18 at 12:20
  • 1
    I get the same error using `package.GetAsByteArray` in similar code. Can you rework to using a `Stream` via `package.Stream`? No problems with that one. – pfx Aug 02 '18 at 12:45
  • @SeverinJaeschke added the memory stream example, it still doesn't work.. – BrunoMartinsPro Aug 02 '18 at 13:29
  • @Riddhi which version of excel are you using? im using office 365 – BrunoMartinsPro Aug 02 '18 at 13:29
  • 1
    I'll post my take in an answer ... too long for a comment – pfx Aug 02 '18 at 13:29
  • 1
    I'm sorry I currently can't test this with asp.net-core. With .net and feeding the byte array into a memorystream as return value I'm not getting any Errors. Tested with Office 365 and 2007. Which Asp.NET-Core Version are you running? – Severin Jaeschke Aug 02 '18 at 13:34
  • @SeverinJaeschke Asp.Net Core 1.1.5 – BrunoMartinsPro Aug 02 '18 at 13:36
  • 1
    Copy Pasted 1st example: `No Issues` using `.Net-Core v2.1 (newest)` with `EPPlus.Core`. Output tested with Office 365 and 2007. I'll try testing it with v1.1.5 but I suspect you need to update your Project to a newer core version. – Severin Jaeschke Aug 02 '18 at 16:05
  • 2
    @SeverinJaeschke It seems a project related issue, even upgrading the project to v2.1 the error persists. However creating a new project with v2.1 the excel is correctly exported. Comparing the generated files with the same code the files don't match, it seems something is changing the content of requests.. Going to investigate further and post what caused this error and its resolution. Everyone thank you very much for your support. – BrunoMartinsPro Aug 02 '18 at 18:47
  • @BrunoMartinsPro did you ever figure out what was causing this error? Because I have the same error. – GreySage Jul 21 '23 at 15:49

1 Answers1

4

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"
        };            
}
pfx
  • 20,323
  • 43
  • 37
  • 57
  • GetAsByteArray() calls Save() internally, so there is no difference in calling Save() manually or not. – GreySage Jul 21 '23 at 15:49
  • @GreySage This is a post from 2018, things might have changed in the meantime. Anyway, thanks for this notice. – pfx Jul 21 '23 at 16:02