Blog

Blog posts made on 18-Jun-09
18Jun
2009
Rethinking MAX BOFs

I just posted some thoughts on MAX BOFs over on the Adobe MAX blog.

Read More ›

18Jun
2009
LiveCycle Data Services3 Now On Labs

LiveCycle Data Services 3 is now in beta and available for download on Labs. Product Manager Anil Channappa has posted details on what's new in LCDS 3.

Read More ›

18Jun
2009
MAX 2009 Press Release Posted

The MAX team has published their first press release announcing Adobe MAX 2009. Just be warned, the cool Flash video at the bottom of the page is loud!

Read More ›

18Jun
2009
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:

view plain print about
1<!--- Read spreadsheet --->
2<cfspreadsheet action="read"
3                src="Sales.xls"
4                query="myQuery">

5<cfdump var="#myQuery#">

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

view plain print about
1<!--- Read a spreadsheet cell --->
2<cfspreadsheet action="read"
3                src="Sales.xls"
4                name="myVar"
5                sheet="1"
6                rows="C"
7                columns="3">

8<cfdump var="#myVar#">

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

view plain print about
1<!--- Write spreadsheet --->
2<cfspreadsheet action="write"
3    overwrite="true"
4    filename="Sales.xls"
5    name="sObj" />

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

view plain print about
1<!--- Read spreadsheet --->
2<cfspreadsheet action="read"
3        src="Sales.xls"
4        name="sObj" />

5<!--- Set cell value --->
6<cfset spreadsheetSetCellValue(sObj, FORM.sales, FORM.row, FORM.col)>
7<!--- Write spreadsheet --->
8<cfspreadsheet action="write" overwrite="true"
9        filename="Sales.xls"
10        name="sObj" />

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

Read More ›