0

I want to generate excel report using EPPlus library. Here is my code for loading file in initialization:

ExcelPackage pkg = new ExcelPackage(new FileInfo(filePath));

I face the OutOfMemoryException because of large data in my report. An codeproject article describes how to create large excel report. The author approach is to save report for portion of data into Excel file, close the package and then reopen the excel file and add another portion of data to it and so on... . I applied this to my project:

//Going to save log package.
pkg.Save();
//Package saved and Disposed.
var fileInfo = pkg.File;

//Again loading file into package...
pkg.Load(new FileStream(fileInfo.FullName, FileMode.Open));

The point here is that when I Save package, it will be disposed automatically. After disposing I suppose that memory will be released, but when I see the task manager it does not happens. After that I again load excel file as discribed in another post in stackoverflow.

Community
  • 1
  • 1
Seyed Morteza Mousavi
  • 6,855
  • 8
  • 43
  • 69

1 Answers1

0

You can't control garbage collection by simply releasing objects. Even calling GC.Collect() is no guarantee that memory will be released.

I use EPPlus to regularly generate spreadsheets in excess of 30,000 rows, with 20 or more columns, some containing significant text strings and complex formulas. I used to have some memory issues when EPPlus used the MS zip packager, but the later versions have been no problem.

So do release early, but don't expect the task manager to react quickly.

Another strategy is to not use a memory stream for the output. Write to disk instead. If you're generating the spreadsheets on IIS, you can either redirect the browser to the temporary file, or open and stream the file to the Response after the workbook has been generated and you've released the EPPlus objects.

richardtallent
  • 34,724
  • 14
  • 83
  • 123