EPPlus Large Dataset Issue with Out of Memory Exception

Question

System Out of Memory Exception. I see the memory Stream is only flushed when saved. We have 1.5 - 2GB Datasets.

I am using EPPlus Version 3.1.3.0

We do the following in code.

We loop through

     --> Create a Package
        --> each table in the datareader
            -->   Add WorkSheet to the Package 
        --> Dispose Each table.
     --> Save the  Package.

Each Datatable is of a 300Mg Size up to 15 Tables out form the System.

This is causing a issue, I have logged this in detail @ https://epplus.codeplex.com/workitem/15085

I still want to be able to use EPPlus its very nice API. but is there a better way to free up a worksheet once we add it to the package.

Thank you for helping.

Madelaine

Answer 1

I had this problem, but I fixed it by switching the option of "Platform target", from x86 to x64 or "Any CPU". ( right click on the project, then select "Properties", then the tab "Build", then on "Platform target" select "x64" )

The problem is that for platform x86 you can use only about 1.8 GB of RAM. For platform x64, you do not have this limitation.

Chrestomathy

This worked for me as well. Had an Excel spreadsheet with 210,000 records and was getting out-of-memory exceptions. At first I thought it was because I was doing lots of stuff in parallel; recompiling in x64 mode resolved it. – Heptarchy

Answer 2

Unfortunately, this seems to be a major limitation of EPPlus - you can find others posting about it on their codeplex page. I ran into a similar issue when exporting large dataset - single tables with 115+ columns wide and 60K+ rows tall. Typically around 30 to 35k rows is when it ran out of memory. What is happening is every cell that is created is it own object which is fine for small dataset but in my case it would be 115x60K= ~7 million. Since each cell is an object with content (mostly strings) its memory footprint adds up quick.

At some point in the future my plan was to create the XML files manually using Linq2Xml. An xlsx is just a zip file renamed with XML files making up the content of the workbook and worksheets. So, you could create an empty xlsx using EPP, save it, open it as a zip, pull out sheet1.xml and add the data content via string manipulation. You would also have to work on the sharedstring.xml file which Excel uses to help keep the file size down. There are probably other xml files that will need updating as well with keys or name.

If you rename any xlxs to a .zip extension you can see this.

Example sheet1.xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <dimension ref="A1:C2"/>
    <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
            <selection activeCell="C5" sqref="C5"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
    <sheetData>
        <row r="1" spans="1:3" x14ac:dyDescent="0.25">
            <c r="A1" t="s">
                <v>0</v>
            </c><c r="B1" t="s">
                <v>1</v>
            </c><c r="C1" t="s">
                <v>0</v>
            </c>
        </row>
        <row r="2" spans="1:3" x14ac:dyDescent="0.25">
            <c r="A2" t="s">
                <v>1</v>
            </c><c r="B2" t="s">
                <v>0</v>
            </c><c r="C2" t="s">
                <v>1</v>
            </c>
        </row>
    </sheetData>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

Example sharedstrings.xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="6" uniqueCount="2">
    <si>
        <t>AA</t>
    </si>
    <si>
        <t>BB</t>
    </si>
</sst>

You can see how I did xml manipulation in my other post:

Create Pivot Table Filters With EPPLUS

Sorry I couldnt give you a better answer but hopefully that helps.

Nydianye

Answer 3

@Ernie is correct about some of the limitations of the current version of EPPlus. They've acknowledged that, and have been working on fixing it. This leaves you with one of two possible options for getting this to work:

1) Switch to the EPPlus 4.0 Beta, where they've fixed this issue, along with some other things as well (although you'll be using a beta version).

2) The ExcelPackage and ExcelWorksheet classes both implement IDisposable, so you might start getting better performance if you were to wrap your usage of them in a using() statement.

Kingpin

Any documention that it is fixed in beta 4? It made no difference for me - granted this was a few months ago. Would love to it fixed but it looked like it requires a major overhaul of the core engine. I always use IDisposable (via Using's) and it makes no difference for me. The problem is the package/workbooks will remain in memory until closed completely. I even tried incrementally exporting the data by a series of open-save-close-reopen-add and it made no difference. Its an all or nothing thing - the moment you open the file it ALL gets loaded. Again, would love to be proven wrong. – Nydianye

EPPlus 4.1.0 does not solve it. And displosing does not help if you have only 1 worksheet to write to. – Pianist

Answer 4

Pay attention if you are passing streams to the ExcelPackage. In my case I had a windows-service, loading a Packages using a memorystream. Now the service crashed after some times with an OutOfMemory exception.

Reason: The dispose of the ExcelPackage does not dispose the stream!

Solution:

using (MemoryStream ms = new MemoryStream(Convert.FromBase64String(excelSheetBase64)))
using (ExcelPackage excelPackage = new ExcelPackage(ms))
{
    // Your code
}

Multiped

Answer 5

The problem is sometimes present during debugging of large amounts of data.

If you try the application in the server in true IIS or in your PC in tru IIS if you have the Win PRO edition

the problem on OutOFMemoryException does not occur.

Paterson

Answer 6

In my case, I used EPPlus 7.0.1 on x86 platform, with 4 sheets of total ~100k rows. When calling sheet.Cells.AutoFitColumns() on the ExcelWorksheet, it consumed another 300MB of RAM and throw OutOfMemoryException.

If you have limited memory, consider not using that method.

Salba