Working with spreadsheets in ColdFusion

Spreadsheets are key to just about all businesses and organizations, and ColdFusion developers have long sought a way to access and manipulate spreadsheet data programmatically. The truth is, ColdFusion has supported spreadsheet access for a while in a variety of ways, it's been possible to access Excel files from an ODBC driver, and it's been possible to generate these files using the CFReport tag as well as by generating HTML or CSV content and then setting the appropriate MIME type to force the browser to display the data using Excel.

But ColdFusion developers have been asking for more, greater and more control, and the ability to read and write specific parts of spreadsheet files.

Just like we did with the ColdFusion image manipulation functionality, spreadsheets in ColdFusion are manipulated using a tag or functions, or some combination thereof. The CFSPREADSHEET tag is used to read a sheet from a spreadsheet file (as a spreadsheet object, a query, a CSV string, or HTML), write sheets to XLS files, and add sheets to XLS files. This tag has over 30 supporting functions like SpreadsheetSetCellValue() , SpreadsheetAddRow() , and SpreadsheetSetCellFormula() allow for more granular spreadsheet manipulation, and can be used in conjunction with supporting functions like SpreadsheetNew() to create a new spreadsheet object, and SpreadsheetInfo() which returns title, subject, sheet names, last saved date and time, and more.

Here are some of the examples I used at my presentations. This first example reads an entire spreadsheet as a query and dumps the contents:

This next example reads a specific cell on a specific sheet and returns it in a variable:

CFSPREADSHEET is also used to write (or overwrite) a spreadsheet, as seen here:

To update a specific cell, you need to read, update, and save, like this:

This example uses form fields to specify the row, column, and value.

There's a lot more to it, but you get the idea. CFSPREADSHEET supports Excel and Open Office spreadsheet files.

Oh, and this one came up several times, so worth noting. Just like any other file access (for example, using CFFILE or CFIMAGE ), you'd not want concurrent access, and so you'd want to use CFLOCK calls when accessing and manipulating spreadsheet files, read locks when reading, exclusive locks when updating, using named locks (perhaps with the spreadsheet file name as the name).

We'll start with a simple Application.cfc to define the data source needed for this app:

Now to create a spreadsheet using a database query, use the following code:

That's all it takes. The cfspreadsheet tag 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 showing you how to use the tag to control formulas and format in your spreadsheet:

Once again, we start with a database query. Then, you use the SpreadsheetNew() function to create a new spreadsheet object.

The code then creates the header row. Use the SpreadsheetAddRow() function 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). Use the SpreadsheetFormatObject() function 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 that will total all orders. So as to not hard code the row, use a few <cfset> statements to calculate the first and last data row and the row for the total (second row after the end of the data, so leaving one empty row in between data and total). The formula can't be hard coded either, so instead of SUM(D2:D24) , the row variables are used to build the formula string dynamically. Use the SpreadsheetSetCellValue()function to add a title, and the SpreadsheetSetCellFormula() inserts the formula.

Next, the entire fourth column, containing the order amounts and the calculated total, is formatted to display as currency using the SpreadsheetFormatColumn() function.

All that is left to do then is to save the file using the cfspreadsheet tag.

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

Next, check out my Adobe TV video (9:27 minute). Ben shows how ColdFusion 9 adds powerful and sophisticated spreadsheet support. Follow along as Ben Forta explains how to work with new and existing spreadsheets.