EPPlus Large Dataset Issue with Out of Memory Exception

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.

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.