EPPlus Excel download from MVC causes an out of memory exception

Question

I'm using the following code to send an EPPlus package to the client as an Excel file in asp.net MVC. However, I'm having an issue with large files throwing OutOfMemoryException exceptions.

public class ExcelPackageResult : ActionResult
{
    public ExcelPackage ExcelPackage { get; set; }
    private string _fileName;

    public ExcelPackageResult(ExcelPackage excelPackage, string fileName)
    {
        ExcelPackage = excelPackage;
        _fileName = fileName;
    }

    public override void ExecuteResult(ControllerContext context)
    {
        context.HttpContext.Response.ClearContent();
        ExcelPackage.SaveAs(context.HttpContext.Response.OutputStream);
        context.HttpContext.Response.AddHeader("content-disposition", string.Format(@"attachment;filename=""{0}.xlsx""", _fileName));
        context.HttpContext.Response.ContentType = "application/excel";
        context.HttpContext.Response.Flush();

        context.HttpContext.Response.End();

        ExcelPackage.Dispose();
    }
}

The issue is at the line: ExcelPackage.SaveAs(context.HttpContext.Response.OutputStream);

I've also tried: context.HttpContext.Response.BinaryWrite(ExcelPackage.GetAsByteArray());

Is there a way to stream the response directly from the ExcelPackage instead of allocating a block of memory for it before sending?

Answer 1

Thats probably going to be too much data for EPPlus unfortunately. See this for some background:

EPPlus, handling big ExcelWorksheet

One thing that has worked for some people is compiling to 64 bit if that is an option:

EPPlus Large Dataset Issue with Out of Memory Exception

Otherwise, you will have to do something like create the xml files yourself or some sort of VBA integration. Neither are pretty.