Migrating from ColdFusion to Railo Part 2: Spreadsheets
← Migrating from ColdFusion 9 to Railo 4.2 (Part 1)
Of all the features introduced in ColdFusion 9, the built-in spreadsheet functionality is probably my favourite. Why do I like it so much? Because it makes it simple to satisfy a common client requirement: data exports in a familiar format.
I find it disappointing then that Railo doesn't support the spreadsheet tag and functions out of the box.
I completely understand and endorse the decision to exclude some of the UI/client-side aspects of CF from Railo (<cfpod>
etc). They never seemed an appropriate focus for a server side language and I've not used them in 17 years of developing with the product. The less bloat the better.
But the ability to get the contents of a spreadsheet into the database and back again seems to me completely consonant with what I've always considered as CFML's main strength: the simplicity with which it allows data to be accessed and processed via the web.
Spreadsheet Extension
Fortunately, Railo's ethos of openness does provide a mechanism for gaps in functionality to be filled, and a group of kind-spirited developers has produced a spreadsheet extension for Railo.
Unfortunately, it doesn't seem to have been updated for newer versions of Railo and I was unable to get it to work with 4.2.1, the current stable release, using the documented installation process. Others seem to have hit the same roadblock, among them Jamie Jackson who nevertheless found that it could be done manually by extracting the package components into the expected locations.
For some reason Jamie's instructions didn't quite work for me—some of the jar files are in a sub-folder of the "poiLib.zip" which isn't accounted for. The version of org.cfpoi.spreadsheet.Spreadsheet.cfc I downloaded expects all the .jar files to be at the same level in the /lib folder, whereas the zip has three of them in their own ooxml-lib directory.
Manual installation
Here's what did work:
- Download the library files zip by going to this page on Github and clicking "view the full file".
- Extract the cfspreadsheetInstaller.zip to a folder cfspreadsheetInstaller.
- Within cfspreadsheetInstaller, extract cfpoi.zip to a folder cfpoi.
- Extract the contents of the cfpoi folder into your {railo-context} as follows (where {railo-context} refers to the folder normally called WEB-INF/railo below your app's web root):
- Unzip the contents of (the second) cfpoi.zip into {railo-context}/components/org.
- Unzip the contents of functions.zip into {railo-context}/library/function.
- Unzip contents of tag.zip into {railo-context}/library/tag.
- Unzip contents of poiLib.zip into {railo-context}/lib.
- Now move the contents of {railo-context}/lib/ooxml-lib - should be 3 jar files beginning "dom", "geronimo" and xmlbeans" - up one level to {railo-context}/lib/.
- Delete the now empty ooxml-lib folder.
Rather long-winded, and of course needs repeating for each application requiring spreadsheet support (nearly all of mine). Happily, automating the process with a copying script isn't difficult.
Spreadsheets in the core?
Although grateful for the unpaid work people have done to make spreadsheet handling possible in Railo, it would be good if it were available and kept updated within the core Railo engine—as it is in ColdFusion—so that this additional per-app configuration wasn't necessary.
I understand there are counter arguments, but if you agree then a vote on this apparently abandoned ticket would be appreciated.
Formatting works properly
It's not all bad news with spreadsheets in Railo, though. The formatting issues ColdFusion 9 has with certain types of data do not seem to be present. Bravo Team CF Advance.