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:
我正在使用luce -spreadsheet，这是一个很棒的工具。我正在制作一个非常大的电子表格(在工作簿中有多个表格，每个表格有数千行)。一切正常。我用数据填充了这些表，并在客户机想要的组之间创建了空白行。我现在要回去尝试应用一些格式，我遇到了错误:
The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook
超过了单元格样式的最大数量。您可以在.xls工作簿中定义多达4000种样式。
The problem is that I have only tried to apply one style:
问题是我只尝试应用一种风格:
spreadsheet.formatColumns(workbook=workbook, format={dataformat="$##,####0.00"},range='5-20');
电子表格。formatColumns(工作簿=工作簿格式= { dataformat = " $ # #,# # # # 0.00”},范围= 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.
我还需要应用其他一些样式。我猜想，由于有超过500行，并且这个样式被应用到16列中，所以这个样式实际上是格式化每个单元格，而不是格式化列。
Is there a different way that I could be or should be applying this style?
我是否可以或者应该用另一种方式来运用这种风格?
2 个解决方案
#1
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.
查看lucee-spreadsheet源代码，formatColumns为它所格式化的每个单元格创建新的单元格样式。不幸的是，这是次优的，而且Apache POI对电子表格中的样式的限制正受到Excel 97 (HSSF)电子表格格式的影响。更好的方法是使用formatCellRange，它可以重用单元格样式，直到维护人员实现更好的格式列函数以利用样式重用。
#2
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.
一件可能有帮助的事情是将输出格式从.xls切换到.xlsx。使用.xls格式，我只在电子表格的前156行看到格式。这是.xls文件格式的一个限制。我使用了spreadsheet.newXlsx()而不是spreadsheet.new()，并且能够生成一个xlsx文件，并且在我处理的750行中格式显示正确。
#1
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.
查看lucee-spreadsheet源代码，formatColumns为它所格式化的每个单元格创建新的单元格样式。不幸的是，这是次优的，而且Apache POI对电子表格中的样式的限制正受到Excel 97 (HSSF)电子表格格式的影响。更好的方法是使用formatCellRange，它可以重用单元格样式，直到维护人员实现更好的格式列函数以利用样式重用。
#2
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.
一件可能有帮助的事情是将输出格式从.xls切换到.xlsx。使用.xls格式，我只在电子表格的前156行看到格式。这是.xls文件格式的一个限制。我使用了spreadsheet.newXlsx()而不是spreadsheet.new()，并且能够生成一个xlsx文件，并且在我处理的750行中格式显示正确。