This project has moved. For the latest updates, please go here.

Out of Memory Exception

Oct 23, 2015 at 4:07 PM
Started using this library. Was very happy with it
Unfortunately didn't find it good with large data.

Out of Memory Exception when trying to export nearly 70000 rows with nearly 50 columns . Columns size may vary . Maximum data that a column may have is 4000 characters and there are many like that.

Works like a charm for a smaller dataset say 5000 rows.

Using EPPlus 4.0.4.0

Sample code:

Using excelPackage As New ExcelPackage
                    Dim worksheet = excelPackage.Workbook.Worksheets.Add("Exported Search List")
                    worksheet.Cells("A1").LoadFromCollection(objList)

                    Dim fInfo As New FileInfo(XlsPathAndFilename)
                    excelPackage.SaveAs(fInfo)
End Using

Any clue if i missed anything somewhere?
Nov 9, 2015 at 4:01 PM
I am also using this version, and have had the same issue. In a couple of other places I found reference to the 65k row limit and so changed my usage for just datasets of less that 64k rows.
That worked for that particular use, however I'm not getting (on a different data set of 9200 rows) the error 'Store must be open for this operation' which is apparently related to some IsolationStorage API issue...
I'm about to drop the use of EPPlus for anything over about 5000 rows... :( Can't have my users continuing to hit errors like this.
Anyone else have solutions to these issues?
Dec 7, 2015 at 7:41 AM
@brian1204 I have exact same problem when saving large files.
For now my temporary solution is to cut dataset into chunks 200000 rows and saving then into separate excel files.

You wrote that You are dropping use of EPPlus, are You using other library? Could You write it name? Ideally I would like to save 1 million records per file, but EPPlus can't handle that :(
Jul 21 at 12:23 AM
Edited Jul 21 at 12:31 AM
I'm using EPPlus with Powershell. So my code will reflect this...

I'm using "SqlCommand" module in Powershell (from SSMS 2016) to run SQL Server queries...

So. Here's the gist of what I'm doing in Powershell:
###################<start>##########
$res = Invoke-SqlCmd ... -OutputAs DataTables
#$res is a System.Data.DataTable object

#$ws is setup already to $Excel, $wb, etc. 

### Load worksheet in a column x row fashion, from a DataTable object ###

foreach ($c in $res.columns)
{
  $cn = $c.Ordinal  #this is the column number, 0-based... 
  $rn = 1 #row number... the DataRow object doesn't have a row number property... 
  
  #load the header row:
    $ws.Cells[$rn, $cn].Value = $c.ColumnName

  foreach($r in $res.Rows) 
  {
    $ws.Cells[(++$rn), ($cn+1)].Value = $r[$cn]
  }
}
$ws.Cells.AutoFitColumns()

##--> save the workbook... 

###################<end>##########
This is loading ~500K records in about a minute for me now. no out of memory errors, etc., whereas Cells[].LoadFromDataTable() was bogging down for me on a result set of only about 30K records.

It shouldn't be too hard to make it work for C#, VB.Net, etc.

sorry, the + should be
+ 
chars