Lucee Spreadsheet Library: How to populate multiple sheets from separate queries

11 November 2015

Among the convenience methods the Lucee Spreadsheet library provides is workbookFromQuery(). You simply pass in a query and it will return a new spreadsheet object with the first sheet populated with your data.

I've been asked if it's possible to generate a workbook from multiple queries where each populates a separate sheet within the workbook.

There's currently no single method for that, but here's a simple example of how you could do it using existing methods.


/* Alter the component path to point to the spreadsheet library wherever it's located */
spreadsheet=New spreadsheet();
/* Our data: 2 separate queries */
queryForSheet1=QueryNew( "Column1","VarChar",[ [ "Sheet1 data" ] ] );
queryForSheet2=QueryNew( "Column1","VarChar",[ [ "Sheet2 data" ] ] );
/* Generate a blank workbook object */
workbook=spreadsheet.new();
/* By default there will be 1 sheet called "Sheet1". Let's rename it and fill it from the first query  */
spreadsheet.renameSheet( workbook,"Query1",1 );
spreadsheet.addRows( workbook,queryForSheet1 );
/* Now create a second sheet, make it active and fill it with the second query. Repeat as required */
spreadsheet.createSheet( workbook,"Query2" );
spreadsheet.setActiveSheet( workbook,"Query2" );
spreadsheet.addRows( workbook,queryForSheet2 );
/* Reset the active sheet so the workbook opens on the first sheet */
spreadsheet.setActiveSheetNumber( workbook,1 );
/* Download the spreadsheet object */
binary=spreadsheet.readBinary( workbook );
filename="multisheets.xls";
header name="Content-Disposition" value="attachment; filename=#Chr(34)##filename##Chr(34)#";
content type="application/msexcel" variable="#binary#" reset="true";

Posted by Julian on . Updated

Comments

  • 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