Story
We have successfully been using Aspose cells for years, but we are looking ahead and noticing that as our reports grow in size, we are hitting limits as to what Aspose can handle. I'm looking for an alternate library for creating the excel files on a server.
Scenario
Take a large datatable (100k+ rows, 500+ columns) and write it to an excel file, formatting certain columns using excel's formatting options (e.g. "@" for text, "yyyy" for the 4 digit year). This will then be saved to a file on the server, where a user will later be able to access the file when desired.
Main Requirements
The ability to work with really large large data tables (200k+).
Ability to work on a server (so libraries dependent upon interop.excel are out).
Supports excel cell formats being programmatically applied.
Desired but not Required
Open source.
Gratis. While I'm not against paying for reporting software, it can take a lot of work to convince the procurement people to pay for the needed licenses, so the ability to skip that hassle is desired.
Possible Options
(I've updated with results after some testing.)
Aspose - This has been our go to solution since the project began and it works great for our small and medium data sets. But we are hitting memory limits where adding more memory to the machines do not appear to be helping when we process our larger data sets.
Microsoft.Office.Interop.Excel - There are some major concerns with using this on a server. Microsoft's comments concerning this.
EPPlus - Appears to work for our larger data sets. Runs about twice as slow as Aspose.
NPOI - We haven't tested it yet and probably will not be testing it in the near future.
Open XML SDK - Throws out of memory exceptions on data sets small enough that Aspose is still able to process them. Also is the most complex solution among those tested.
