Last week I posted an entry on the spreadsheet support planned for ColdFusion Centaur. That post (unsurprisingly) generated lots of questions and comments, and so I thought it worthwhile to post a bigger and more sophisticated example, this time building a new spreadsheet from scratch.
We’ll start with a simple Application.cfc to define the datasource needed for this app:
Now to create a spreadsheet using a database query:
SELECT orderid, customerfirstname, customerlastname, total
FROM orders
ORDER BY orderid
That’s all it takes.
But what if you want more control? What if you want to explicitly control data placement? What if you needed to provide row or column or cell level formatting? And what if you needed a total cell containing a formula to SUM the total column?
Here’s a complete example:
SELECT orderid, customerfirstname, customerlastname, total
FROM orders
ORDER BY orderid
Once again, we start with a database query. Then, SpreadsheetNew() is used to create a new spreadsheet object.
The code then creates the header row. SpreadsheetAddRow() is used to add a specific row, the column headers (as no row number was specified, SpreadsheetAddRow() adds the row to the next free row, the first). SpreadsheetFormatObject() is then used to format row 1, this function accepts a structure (which here is implicitly created inline).
Next comes the data. SpreadsheetAddRows() adds the entire query to the spreadsheet (again, as no row was specified the data gets added to the next free row).
Now for the formula which will total all orders. So as to not hard code the row, a few
Next, the entire fourth column, containing the order amounts and the calculated total, is formatted to display as currency using SpreadsheetFormatColumn().
All that is left to do then is to save the file using
Leave a Reply