5

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.

  • You should use mysql and read this en.wikipedia.org/wiki/Database_normalization about normalizing your data. You can export data to cvs to import to excel. Yes, restructuring your data in a way that make sense is hard work, but its worth it. Besides bad design 500+ columns should not happen. – cybernard Apr 18 '16 at 21:34
  • @cybernard The data is normalized. The report has 500 columns, the tables do not. A 'datatable' is the C# object we store the data in when we pull it from a stored procedure. – Lawtonfogle Apr 19 '16 at 12:45
1

Essential XlsIO is an option to consider. The library has been optimized to load Excel files with minimal memory footprint.

The entire product is available for free with no limitations through the community license if you qualify.

Note: I work for Syncfusion

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.