CF11 - cfspreadsheet errors with more than 4000 cells

[skilzkid]

I'm trying to generate a 3 tab Excel spreadsheet using cfspreadsheet.  My code worked in CF10, but errors in CF11 (fully patched as of 11/1/2015).  The error is:

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

First thing I tried was to comment out all SpreadsheetFormatSomething functions, but that failed.  I do see that another user encountered the error (https://bugbase.adobe.com/index.cfm?event=bug&id=4022999) but I have not found any solution. It looks like when Adobe "improved" the spreadsheet functions for CF11, they probably added/cloned a POI style with every new cell.  Does anyone have a solution/wokaround for this problem?  Will Adobe fix/patch this anytime soon?

Thanks in advance for the help,

Steve

[skilzkid]

If there isn't a solution, has anyone else at least encountered this?  I'll likely just try to call the underlying POI libraries directly, but am hoping not to have to go through that.

[WolfShade]

I can no longer access slexy from work, but I did manage to put an example of my code practice for spreadsheets there.  Hope it helps.

^_^

[skilzkid]

Thanks for the response.  You might be on to something with SpreadsheetAddRow being the problem as it is likely cloning cells and somehow creating new cell styles during cloning.  I'll try sticking with SpreadsheetSetCellValue before trying to go straight into the Java libs.

[WolfShade]

Personally, I avoid using CFSPREADSHEET and do everything within CFSCRIPT tags.  But, then, my demands have all been for streaming the Excel sheet directly to the browser or an email, as opposed to saving it to the server HD.

I also avoid using SpreadsheetAddRow(), and exclusively use SpreadsheetSetCellValue() (and SpreadsheetSetCellFormula(), if needed).

This has a disadvantage in that both rows and columns of data need a loop in order to be written (so it's more coding); but it gives much more granular control over what you want to do.

I'll look around to see if I have any sample code that I can provide, to give you an idea.  It's tedious, but (IMHO) well worth the effort.

HTH,

^_^

[skilzkid · Accepted solution]

It looks like CF11 Hotfix 7 (released 11/16/2015) resolved this issue.  Installed and no longer getting error.

[WolfShade]

Just curious.  Are you going to go with that as the resolution, or stick with a more granular SpreadsheetSetCellValue()?

V/r,

^_^

[skilzkid]

I think that since everyone wants to write less code, I'll go with "apply a hotfix and move on"