Blog posts made on 22-Jun-09
More On ColdFusion And Spreadsheets

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:

view plain print about
2    <cfset this.datasource="cfartgallery">

Now to create a spreadsheet using a database query:

view plain print about
1<!--- Get data --->
2<cfquery name="ArtOrders">
3SELECT orderid, customerfirstname, customerlastname, total
4FROM orders
5ORDER BY orderid
8<!--- Save it --->
9<cfspreadsheet action="write"
10    query="ArtOrders"
11    filename="Orders.xls"
12    overwrite="true">

That's all it takes. <cfspreadsheet> creates a spreadsheet populated with the retrieved data, using the query column names as column headers in the first spreadsheet row, and the data starting in the second row. Nice, huh?

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:

view plain print about
1<!--- Get data --->
2<cfquery name="ArtOrders">
3SELECT orderid, customerfirstname, customerlastname, total
4FROM orders
5ORDER BY orderid
8<!--- Create new spreadsheet --->
9<cfset sObj=SpreadsheetNew()>
11<!--- Create header row --->
12<cfset SpreadsheetAddRow(sObj, "Order,First Name,Last Name,Amount")>
13<cfset SpreadsheetFormatRow(sObj, {bold=TRUE, alignment="center"}, 1)>
15<!--- Add orders from query --->
16<cfset SpreadsheetAddRows(sObj, ArtOrders)>
18<!--- Figure out row for formula, 2 after data --->
19<cfset rowDataStart=2>
20<cfset rowDataEnd=ArtOrders.recordCount+1>
21<cfset rowTotal=rowDataEnd+2>
22<cfset totalFormula="SUM(D#rowDataStart#:D#rowDataEnd#)">
24<!--- Add total formula --->
25<cfset SpreadsheetSetCellValue(sObj, "TOTAL:", rowTotal, 3)>
26<cfset spreadsheetSetCellFormula(sObj, totalFormula, rowTotal, 4)>
28<!--- Format amount column as currency --->    
29<cfset SpreadsheetFormatColumn(sObj, {dataformat="$00000.00"}, 4)>
31<!--- Save it --->
32<cfspreadsheet action="write"
33    name="sObj"
34    filename="Orders.xls"
35    overwrite="true">

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 <cfset> statements are used to calculate the first and last data row, and the row for the total (2nd row after the end of the data, so leaving one empty row in between data and total). The formula too can't be hard coded, so instead of SUM(D2:D24), the row variables are used to build the formula string dynamically. SpreadsheetSetCellValue() is used to add a title, and SpreadsheetSetCellFormula() inserts the formula.

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 <cfspreadsheet>.

<cfspreadsheet> and its 30+ supporting functions can do lots more, but this should give you a taste of just what's possible using this innovative new feature.

Read More ›

For Now, Avoid Kindle Version of My RegEx Book

My Sams Teach Yourself Regular Expressions in 10 Minutes has proven to be a very popular book, and tens of thousands of developers have used it to get started with Regular Expressions. The book teaches RegEx incrementally, and for each example a block of text is used, and matching text is shaded to clearly demonstrate what the Regular Expression is doing.

Sams Teach Yourself Regular Expressions in 10 Minutes has apparently now been released for the Amazon Kindle. I've not seen the Kindle version yet (as I don't have a Kindle yet), but readers are reporting to me that the Kindle version is missing the shading, making all of the examples rather useless!

I've contacted the publisher about this problem, and hope they'll correct it quickly. But for now, I suggest you avoid the Kindle version of the book, and stick with the print version.

Read More ›

Executing Code On Server Startup

ColdFusion can execute code when an application starts and ends, when a session starts and ends, when a request starts and ends, and more. But what if you need to execute code when a server starts up? Granted, this is not a common use case, but when needed, we've had to use onApplicationStart for this, probably doing something like:

view plain print about
1<cfif not IsDefined("SERVER.myVar")>

ColdFusion Centaur adds the ability to define code to be executed onServerStart.

By default this is a method named onServerStart in server.cfc. But, actually, it can be in any ColdFusion Component, including an Application.cfc. In ColdFusion Administrator you can point to the CFC and method to be invoked, and ColdFusion will execute the code on server start-up before processing any requests.

Read More ›