You may recall that back in February I posted about some issues we were having with creating spreadsheets using CFSPREADSHEET and formatting not applying when dealing with multiple sheets. Apparently this is only an issue when building a multi-sheet workbook using ColdFusion tags! You see, in the same application that we discovered that problem one, our clients have been having increasing issues with performance issues in sheets with a large amount of data and random errors caused when the system suddenly "lost" the workbook and the update failed for whatever asinine reason.
We were already planning on revisiting all of that stuff for our next patch, however the tipping point came this week, when for one data set, the system began throwing a whole new error (at least for us): "java.lang.IllegalArgumentException: The maximum column width for an individual cell is 255 characters." Goggling this error turned out to be an exercise in extreme frustration as there were few search results found and even fewer that actually . The main issue appeared to be that that ColdFusion seems to auto trigger POI's autosizing of columns based on data (or maybe POI did it on it's own). The version of POI that is used with ColdFusion 9 has a bug that causes it to not account for Excel's limits on column width.
This bug in POI has long been fixed, as far as we could tell, but attempts to update POI failed completely. ColdFusion wouldn't even use the new JAR libraries. We also tried switching to OpenOffice but we had the same issue. There is no option of just telling the clients "no you can't have this data that has potential fiscal and/or administrative purposes." Nor was there really any other clean way we could think of to deal with it.
We were finally to the point of considering reinstalling the full POI and going back to using Ben Nadel's POI utility, which we at least knew would work though we'd lose other features from using ColdFusion's built in stuff. Then I finally stumbled one forum post from someone who was having the same problem, and the solution that worked for him. Short version is switch to using the CFScript version because this bug only happens when doing an update – like we were doing. It was never an issue on a write.
Now, we have never used any CFScript in our shop before, beyond bits that might be included in some third-party stuff we use. In my case, while I do see more and more developers seeming to prefer it, I never saw any point in it. The tag-based CFML worked fine for me and it could do all the same stuff in an easier to read format. I have also read plenty to indicate that, on the whole, CFScript stuff can't do everything that tags can. But apparently, at some point, Adobe did give some CFScript functions abilities that their tag equivalents do not have. In this case, the ability to build a proper spreadsheet object that has all of the individual sheets in it then just do a single write, versus as asinine write then update crap.
So my partner set out to work, changing the code over to using CFScript for the actual spreadsheet object creating and saving to file. When it was done, not only did it fix the error we had with the maximum column width, but to our delight, it ran significantly faster for this three sheet object. What was taking 5-10 minutes to run and we kept having to update the time out for now only took 1-2 minutes. Oh, and the issues we had seen with the dataFormats not applying on subsequent sheets? GONE! That's right, those bugs also only affect the tag-based spreadsheet creation!
Now I'm not saying go and switch immediately to CFScript for everything or even for all spreadsheets. But I would suggest that if you are dealing with multiple sheets, like we were, where data may be very dynamic, large, or need formatting all over the place, the CFScript version just may be your best bet.