Error formatting a lucee-spreadsheet: The maximum number of cell styles was exceeded

Question

I am using lucee-spreadsheet, which is a great tool. I am making a very large spreadsheet (multiple sheets in a workbook with thousands of row per sheet). Everything is working as expected. I have populated the sheets with data, and created blank rows in between groups that the client wants. I am now going back and trying to apply some formatting and I am running into the error:

The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook

The problem is that I have only tried to apply one style:

spreadsheet.formatColumns(workbook=workbook, format={dataformat="$##,####0.00"},range='5-20');

There are a number of other styles I need to apply. I am guessing that since there are over 500 rows and this style is being applied to 16 columns that the style is actually formatting each cell and not the column.

Is there a different way that I could be or should be applying this style?

Answer 1

Looking at the lucee-spreadsheet source code, formatColumns is creating a new cell style for every cell it formats. Unfortunately this is suboptimal, and the limits in Apache POI on styles in a spreadsheet are being hit for the Excel 97 (HSSF) spreadsheet format. It would be better to use formatCellRange, which reuses cell styles, until such time as the maintainers implement a better formatColumns function to take advantage of style re-use.

Answer 2

One thing that may help is switching the output format from .xls to .xlsx. Using the .xls format, I was only seeing formatting on the first 156 rows of my spreadsheet. This was a limitation of the .xls file format. I used spreadsheet.newXlsx() instead of spreadsheet.new(), and was able to generate an xlsx file, and the formatting appeared correctly for the 750 rows I was working with.