Saving Excel file of 50k rows using "ClosedXML" NuGet taking a very long time

Question

I need to save data into an Excel file(.xlsx). I decided to use "ClosedXML" NuGet (v0.95.3) (https://www.nuget.org/packages/ClosedXML/) for implementing it, after getting several recommendations on this NuGet from different developers.

Recently I encounter a problem when exporting 50k rows:

• The saving process alone (after all the data was already added to the file) taking a very long time around ~10 seconds
• This is without mention that the styling of these rows: font, borders, adjust columns width etc. is taking around ~12 seconds.
• This without mentioning the fact which its taking me an around ~20 to retrieve and write the data into the file, so all the process in such case taking me ~45 seconds Total (too way much time!!).

I'm saving the data into a given stream using the "SaveAs()" method, and I already used the "XLEventTracking.Disabled" optimization.

Probably, I'm not the first one which dealing with Excel files, so:

1. Is anyone of you is familiar with "ClosedXML" NuGet and encounter such problem in the past?
2. Are you using a different NuGet for Excel files? (even if it cost money).

Thanks in advance!

Answer 1

I have used ClosedXML for a few years, and I have to admit that the library - as of version 0.95.1 - is pretty much unusable for larger Excel reports. The memory usage/footprint is a disaster. A really large report can easily allocate several gigabytes of RAM.

The performance problems you have seen are linked to GC (garbage collection). Looking at the code, you quickly realize that fixing this will require several iterations of improvements.

I would recommend looking at other libraries. Personally, I prefer to use the native libxlsxwriter library. You can easily integrate with it, using DllImport interop. For large reports, consider the Constant Memory mode. With constant memory, it outperforms ClosedXML in terms of both CPU and RAM.