Chaining

From version 3 you can chain method calls to make your code simpler and more expressive. There are two ways of doing this.

1. Directly chaining methods which don't return a result

Take the following multi-step procedure which creates, populates, formats and finally writes a new spreadsheet file.

spreadsheet = New spreadsheet.Spreadsheet();
data = [ [ "Firstname", "Lastname" ], [ "Susi","Sorglos" ], [ "Frumpo","McNugget" ] ];
filepath = "c:\files\test.xlsx";

workbook = spreadsheet.newXlsx();
spreadsheet.addRows( workbook, data );
spreadsheet.formatRow( workbook, { bold: true }, 1 );
spreadsheet.write( workbook, filepath, true );

You can now simplify this by chaining the methods that operate on the workbook as follows:

workbook = spreadsheet.newXlsx();
spreadsheet
  .addRows( workbook, data )
  .formatRow( workbook, { bold: true }, 1 )
  .write( workbook, filepath, true );

2. Create a chainable object

While directly chaining methods simplifies things a bit by not having to reference the library for each call, it still requires you to pass in the workbook object you want to operate on to each function.

To avoid having to do that, you can use the newChainable() function to create a chainable "wrapper" object for the library which only requires the workbook to be specified once. Subsequent chained calls don't need to reference the workbook, which will be stored in the object.

spreadsheet.newChainable( "xlsx" )
  .addRows( data )
  .formatRow( { bold: true }, 1 )
  .write( filepath, true );

Specifying the workbook

There are several ways of specifying the workbook you want the chainable object to operate on.

If you are creating a new workbook from scratch, just specify the type as a string when initializing the chainable object.

//create a binary spreadsheet
spreadsheet.newChainable( "xls" )
  // more operations

//create an xml spreadsheet
spreadsheet.newChainable( "xlsx" )
  // more operations

//create a streaming xml spreadsheet
spreadsheet.newChainable( "streamingXlsx" )
  // more operations

If you have already created a workbook object, you can just pass that into the chainable object.

spreadsheet.newChainable( existingWorkbook )
  // more operations

If you want to operate on an existing spreadsheet file on disk, you can read the file into the chainable object.

spreadsheet.newChainable().read( filepath )
  // more operations

You can also create the workbook directly from csv or query data.

spreadsheet.newChainable().fromCsv( csv=csv, firstRowIsHeader=true )
  // more operations

spreadsheet.newChainable().fromQuery( query )
  // more operations

Note: the .read(), .fromCsv()and .fromQuery()"member" methods allow all of the options available to the standard read(), workbookFromCsv() and workbookFromQuery() library methods.

Ending the chain

Note that if you add a library method to your chain that returns a result, such as a string or another type of object, then the chain will end. For example:

cellFormat = spreadsheet.newChainable( wb )
  .formatCell( { bold: true }, 1, 1 )
  .getCellFormat( 1, 1 );// End of the chain: no more methods can be added. The result is placed in the "cellFormat" variable.