FlexCel XLSX Row Limit Issue – Handling Over 1 Million Records

Dear TMS Support Team,

We are using a reporting service built on the .NET Framework, which utilizes FlexCel.dll version 6.26.0.0 to generate Excel .xlsx files.

Our report process uses an .xlsx template where data is first loaded into a data sheet and then referenced across multiple sheets in the workbook.

However, as our data volume has grown, we now need to process more than 1 million records. We understand that Excel has a maximum row limit of 1,048,576 rows per sheet.

When attempting to load more than 1 million records, we encounter the "System.OutOfMemoryException"

Additionally, we have recently purchased and upgraded to FlexCel.dll version 7.2.0.0.

Questions:

  1. Is there any recommended approach in FlexCel for handling data volumes that exceed the Excel row limit?
  2. Does the newer version (7.2.0.0) provide any tools or techniques to manage this scenario (e.g., splitting data across sheets, or exporting to multiple files)?
  3. Is there a best practice for referencing such large datasets from other sheets using FlexCel templates?

Please let us know if there’s a way to structure or split the output efficiently within FlexCel’s capabilities.

Looking forward to your guidance.

Hi,

  1. Sadly as you mention, this is an Excel limit. We just can't write a file where a sheet has more than 1 million rows, or Excel would just fail to open it. In fact, FlexCel internally doesn't have that limit, but we check it because if we didn't the file would be invalid.
    The only options I can think are:
    • Split the data in different sheets
    • Split the data in different files

Of course, this depends on the data you have: Sometimes it might make more sense to split into different files (maybe one file per day if this has dates?) and sometimes more into sheets.
If possible, I would recommend splitting into files, because the files will be smaller, and easier to work with in Excel.

Excel can become slow too with those huge files, and the users using the report will surely appreciate not waiting minutes for Excel to load the file.

Remember that both FlexCel and Excel keep the full file in memory (because we need that to calculate the formulas). But while Excel is written in C, FlexCel is in C#, where we have much less control over the memory allocations. FlexCel goes through a lot of trouble to try to minimize the memory usage, but in GC languages, this is always going to be higher.

  1. We do have a "Split" command (FlexCel Reports Designer Guide | FlexCel Studio for the .NET Framework documentation ), but honestly, if you are having out-of-memory issues, the simpler you make the export the better. It is just faster and uses less memory to split the table directly when you write the SQL.
  2. What I normally recommend if you need to export more than 1 million rows is to:
    • Avoid all formatting, formulas, and "beautification" of the sheet. A sheet with more than 1 million rows is not likely going to be read by a human, but by other machine. It would take a long time for a human to read 1 million rows. And if the file is going to be processed by another app, colors, etc don't count.
    • If possible, use the API instead of report for those huge files. As said in my previous point, the report when so huge should be simple in formatting and other stuff. So normally, you can loop over your data and just call xls.SetCellValue(row, col, cellvalue). Some code like:
      Dumping a Dataset into Excel | FlexCel Studio for the .NET Framework documentation Note that this tip was written using a dataset, but you will get even better results if you use some data access tech that fetches the records as you go using them. A DataSet loads all records in memory, which is again more memory used.

Finally, a comment on here:

When attempting to load more than 1 million records, we encounter the "System.OutOfMemoryException"

Is your app 64 bit? A 64-bit app should not really go into out of memory exceptions. You should get some FlexCelException telling you that you have too many rows, but it should not complain about memory. Here in our test suite we have one test that create a 3 million row spreadsheet (1 million x sheet), and we don't see memory issues.

Also, if you are in an older .NET version, it might make sense to try with a newer one: there have been many enhancements in the garbage collector from the .NET guys in latest releases.