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
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:
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.
Oh, and this one came up several times, so worth noting. Just like any other file access (for example, using