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 CFReport 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. And as I demoed in my usergroup presentations last week, this is indeed planned for ColdFusion "Centaur".

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. The 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:

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