As simple as possible, as complex as necessary

The simplicity of chaining calls in Spreadsheet CFML

9 November 2021

Spreadsheet CFML 3.0 was released a few weeks ago but despite being a major version update, the only potentially breaking change is to the name (from Lucee Spreadsheet).

The new version has been heavily refactored to improve internal modularity, but there is no change to the minimum requirements and with a suite of approaching 500 unit tests you can upgrade with confidence that your existing code will continue to work.

But you may want to look at a new feature of the library which could improve the simplicity and expressiveness of that code.

Method chaining

"Method chaining" means invoking more than one method sequentially in a single call to an object. Each method call is appended to the previous to form a "chain".

So separate calls...


... can be replaced with a single chained call.


Any method that doesn't return a value (i.e. returns void) can be made chainable by instead returning a reference to the object itself, i.e. this

All of the methods in Spreadsheet CFML 3.x which previously returned void, now return the library object to allow chaining.

An example

Let's say you want to create a spreadsheet object, populate it with some data, bold the header, add some links and then have it download to the user's browser. In version 2.x you might do the following.

spreadsheet = New Spreadsheet();
workbook = spreadsheet.newXlsx();
spreadsheet.addRow( workbook, [ "Name", "Owner" ] );
spreadsheet.formatRow( workbook, { bold: true }, 1 );
spreadsheet.addRows( workbook, [ [ "Github", "Microsoft" ], [ "BitBucket", "Atlassian" ] ] );
spreadsheet.setCellHyperLink( workbook, "", 2, 1 );
spreadsheet.setCellHyperLink( workbook, "", 3, 1 ); workbook, "Git-Hosts" );

In version 3.x with method chaining you can simplify it somewhat to:

spreadsheet = New Spreadsheet();
workbook = spreadsheet.newXlsx();
  .addRow( workbook, [ "Name", "Owner" ] )
  .formatRow( workbook, { bold: true }, 1 )
  .addRows( workbook, [ [ "Github", "Microsoft" ], [ "BitBucket", "Atlassian" ] ] )
  .setCellHyperLink( workbook, "", 2, 1 )
  .setCellHyperLink( workbook, "", 3, 1 )
  .download( workbook, "Git-Hosts" );

This definitely reads better, but wouldn't it be nice not to have to pass in the workbook object to each of the chained calls?


Well by using the newChainable() function, we don't have to. This method creates a "wrapper" object for the library which only requires the workbook object to be specified once, either as a new spreadsheet to be created or an existing one passed in. Here's how we'd apply it to our example.

spreadsheet = New Spreadsheet();
spreadsheet.newChainable( "xlsx" )
  .addRow( [ "Name", "Owner" ] )
  .formatRow( { bold: true }, 1 )
  .addRows( [ [ "Github", "Microsoft" ], [ "BitBucket", "Atlassian" ] ] )
  .setCellHyperLink( "", 2, 1 )
  .setCellHyperLink( "", 3, 1 )
  .download( "Git-Hosts" );

Cleaner, simpler, more... "fluent".

More details on the new chaining feature can be found in the docs.


  • Formatting comments: See this list of formatting tags you can use in your comments.
  • Want to paste code? Enclose within <pre><code> tags for syntax higlighting and better formatting and if possible use script. If your code includes "self-closing" tags, such as <cfargument>, you must add an explicit closing tag, otherwise it is likely to be mangled by the Disqus parser.
Back to the top