Skip to main content
S
skilzkid
Inspiring
November 6, 2015
Answered

CF11 - cfspreadsheet errors with more than 4000 cells

  • November 6, 2015
  • 3 replies
  • 1030 views

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

    This topic has been closed for replies.
    Correct answer skilzkid

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

    3 replies

    S
    skilzkidAuthorCorrect answer
    Inspiring
    November 18, 2015

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

    WolfShade
    WolfShade
    Legend
    November 18, 2015

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

    V/r,

    ^_^

    S
    skilzkidAuthor
    Inspiring
    November 23, 2015

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

    WolfShade
    WolfShade
    Legend
    November 13, 2015

    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,

    ^_^

    S
    skilzkidAuthor
    Inspiring
    November 13, 2015

    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
    WolfShade
    Legend
    November 13, 2015

    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.

    ^_^

    S
    skilzkidAuthor
    Inspiring
    November 13, 2015

    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.

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices