5

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Lance
  • 3,193
  • 2
  • 32
  • 49
  • Thanks for reporting this. I've raised a ticket and will look at it as soon as I can: https://github.com/cfsimplicity/lucee-spreadsheet/issues/84 – CfSimplicity Jul 13 '16 at 11:42

2 Answers2

5

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.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Finbarr O'B
  • 1,435
  • 1
  • 15
  • 18
  • 1
    I'm the maintainer. Thanks for pointing this out. That portion of the code was inherited from a previous project, but I'll raise a ticket and see if it can be improved as you suggest. Thanks again. – CfSimplicity Jul 13 '16 at 11:35
  • Thank you both I will switch to formatCellRange and look for the update from cfSimplicity. – Lance Jul 13 '16 at 17:40
  • 1
    This was all fixed in the library by @CfSimplicity. AWESOME tool – Lance Jul 22 '16 at 20:27
1

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.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
TBrenner
  • 1,151
  • 2
  • 9
  • 8