Epplus Large Dataset Issue with Out of Memory Exception

EPPlus Excel download from MVC causes an out of memory exception

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.

EPPlus 'System.OutOfMemoryException'

You are probably hitting the ram limit as that is a big file. If you have the option to compile to 64 bit you might be able to solve the problem:

https://stackoverflow.com/a/29912563/1324284

But if you can only compile to x86 there is not a whole lot you can do with epplus. You will have to either use a different library or build the XML files for excel yourself:

https://stackoverflow.com/a/26802061/1324284

System.OutOfMemoryException' occurred in mscorlib.dll when exporting huge Excel file

public void DataToExcel(DataTable dataToConvert, HttpResponse response)
{
    FileInfo myFile = new FileInfo("~/MyProject/Inventory.xlsx");
    using (ExcelPackage package = new ExcelPackage(myFile))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

        worksheet.Cells["A1"].LoadFromDataTable(dataToConvert, true, OfficeOpenXml.Table.TableStyles.Light1);

        package.Save();

        response.Clear();
        response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
        response.AddHeader("Content-Disposition", string.Format("attachment;filename=\"{0}\"", "Inventory.xlsx"));
        response.WriteFile("~/MyProject/Inventory.xlsx");
        response.Flush();
        response.End();

    }

}

Load large amount of excel data with EPPlus

Those sounds like pretty big datasets so you might want to read this:

EPPlus Large Dataset Issue with Out of Memory Exception

Basically, you can run out of RAM with "larger" dataset. But its not just the number of rows that adds to the size but also the columns AND the content of each cell. Strings will generally take up a lot more room than numbers so it is sometime difficult to predict when EPP will start to have memory issues. Rumor has it the newest version of EPP is better but I havent tested it myself.

Seems like you got it working based on your comments and thats good but keep the memory limitations in mind. I aggree with you - doing it in Open XML would not be a simple exercise.

---

Related Topics