4

I'm using NPOI to deal with Excel files. Here's how I'm reading files:

using (FileStream stream = File.OpenRead(excelFilePath))
{
    IWorkbook wb = WorkbookFactory.Create(stream);
    ...
}

However, for any XLSX file larger than a few megabytes, it causes memory usage to shot up to about 1GB and eventually throw an OOM exception.

Doing some research, I've found out that, strangely, loading a workbook from a File rather than a Stream results in less memory consumption by POI. The closest C# equivalent to the provided Java examples I've come up with to use Files is the following:

OPCPackage pkg = OPCPackage.Open(new FileInfo(excelFilePath));
XSSFWorkbook wb = new XSSFWorkbook(pkg);

But it seems to use the same underlying implementation since the memory usage is still the same and causes OutOfMemory exceptions.

Does NPOI have anything built-in for handling large XLSX files?

Suggestions on alternative libraries that can handle both XLS and XLSX files are also welcome.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Saeb Amini
  • 23,054
  • 9
  • 78
  • 76
  • It's more work, but not toooo much more, to read it with SAX using the various helpers. Did you try [that approach, as documented in the POI website](http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api)? – Gagravarr Nov 12 '15 at 12:27

2 Answers2

3

It seems XLSX support is rather new in NPOI and it simply can't handle large files yet.

After trying a few libraries, EPPlus was able to process the large XLSX file without a hitch, so I finally settled on having two libraries for reading in Excel files, NPOI for XLS and EPPlus for XLSX.

Saeb Amini
  • 23,054
  • 9
  • 78
  • 76
0

As a suggestion for alternative libraries, a good one is Apache POI. I've used it extensively for both XLSX and XLS files and it does the job well. Here's a gist to do a quick test on your file.

The only format Apache POI doesn't cover is the old format XML files for which Xelem can be used instead.

driu
  • 108
  • 5