An alternative way of working with spreadsheets in Lucee
I described recently how getting spreadsheet functionality to work in Railo was more difficult than expected, but possible by jumping through some hoops.
I didn't feel comfortable though with having to manually customise individual web contexts, resulting in a lot of duplicate code and requiring a Railo restart for each.
Library vs extension
Since most of my apps only use a small subset of the spreadsheet functionality - mainly exporting queries - I decided in the end to extract the parts I needed from the Railo extension code and re-build them as a standalone library component. This could then be integrated as part of my application code rather than requiring installation as a bolt-on to the engine.
This has worked well and cut down the number of contexts needing the extension.
Railo extension compatibility with Lucee?
On switching from Railo to Lucee, it seemed at first as if the existing manually installed Railo spreadsheet extension would continue to work where needed. However, reports started coming in on the Lucee discussion list of java.lang.NoClassDefFoundError errors when reading spreadsheet files.
It turns out the java files responsible for read operations within the extension had hard-coded references to "Railo" classes which had been renamed in Lucee.
The java source being unavailable and the Railo extension project apparently dormant, there seemed to be an urgent need for a way of allowing Lucee to read spreadsheets, so I set about adding this capability to my library.
Enhanced library
Taking inspiration from Ben Nadel's now somewhat long-in-the-tooth POI Utility I succeeded in implementing a read() method, but by then Andrew Kretzer had cleverly managed to hack the Railo extension's compiled classes into a version that would work with Lucee.
Still, having felt so much more at ease with the library approach, I decided to press on and try to reproduce as much of the extension's functionality in my library version as I could.
The result is a full-featured Spreadsheet Library for Lucee which implements just about all the functionality available in ColdFusion 9 along with a few extra capabilities.
Missing features
At the time of writing there are a couple of read() features still to implement:
Reading a spreadsheet directly to CSV or HTML.Reading specific row or column ranges from a spreadsheet.
8 June 2015: Specifying rows/columns and column names and reading to HTML/CSV are all now supported.
All other CF9/Railo extension features are supported. A full list of functions is available in the project wiki.
Additional benefits
As well as not requiring installation, the library offers a number of other benefits over the spreadsheet extension.
Singleton service approach
The cfc behind the Railo extension was designed to be instantiated on every call, for which it created and returned a spreadsheet workbook object whether or not one was needed.
The Lucee spreadsheet library component does not create an internal workbook. Instead you pass in the workbook object to be processed as the first argument - matching how the ColdFusion function syntax works.
spreadsheet = New spreadsheet();
data = QueryNew( "First,Last","VarChar,VarChar",[ [ "Susi","Sorglos" ],[ "Julian","Halliwell" ] ] );
workbook = spreadsheet.new();
spreadsheet.addRows( workbook,data );This gives you the option of creating a single instance of the library in application scope to service all your spreadsheet processing.
Enhanced read() method
The CFML SpreadsheetRead() script function only supports returning a spreadsheet workbook object from the file being read. The Lucee spreadsheet library's read() method gives you the additional option of returning the data as a query - only possible using the <cfspreadsheet> tag in ColdFusion.
myQuery = spreadsheet.read( src=mypath,format="query" );The library will read both binary "xls" and Excel 2007+ XML "xlsx" formats.
Convenience methods
As mentioned, much of my day-to-day spreadsheet handling involves exporting queries as spreadsheets that clients can download on demand, so I've added some specific functions to make that easy:
binaryFromQuery()downloadFileFromQuery()workbookFromQuery()writeFileFromQuery()
Full documentation in the wiki.
Latest version of POI
The Railo extension hasn't been updated in a while and uses version 3.7 of Apache POI (the underlying java spreadsheet handling library), dating back to October 2010.
For the Lucee spreadsheet library I've updated POI to 3.11 released in December 2014 3.12 released in May 2015 3.15 released in September 2016.
No Railo/Lucee-specific java dependencies
The read functionality uses a combination of POI and cfml script, so there are no specific compiled java dependencies to break should Lucee change.
Bug fixes
Refactoring the extension brought to light a handful of minor issues and omissions with the original code, in particular to do with cell data types. These have been fixed.
Update 16 March 2015: A bug in the modified extension when reading certain types of .xlsx files appears to be fixed in the Lucee spreadsheet library.
Written in Lucee script
The original extension was largely written using tags. Not only is the Lucee spreadsheet library written entirely in script, I've endeavoured also to use modern syntax - such as "member functions" and "elvis operators" - where appropriate.
Tested
At the outset I wasn't sure how much test coverage would be achievable with a spreadsheet library, but in fact I've been able to build a fairly extensive suite of over one hundred unit tests using TestBox and its wonderful "BDD style". A few of the more visually oriented functions escaped the harness, but most can be automatically exercised with every code change.
Extension or library?
Despite the hacks and manual hoop-jumping, the modified extension is unquestionably the path of least resistance for anyone migrating to Lucee with code requiring CFML spreadsheet tag or function support. You need to manually customise each context once, but your app code won't need changing.
Nonetheless the Lucee spreadsheet library is offered as an alternative approach. One which may prove somewhat less brittle as the engine evolves.
Comments