If like me you make frequent use of ColdFusion's spreadsheet functions to provide clients with selective data dumps in a format they're familiar with, you'll want to pay attention to Summer Wilson's warning about the poor performance of SpreadsheetAddRows().

By instead writing the spreadsheet to file using <cfspreadsheet> and including the queried data in the query attribute, she was apparently able to reduce processing times of 5 minutes+ down to under a minute. And her recordsets of a couple of thousand rows are not what I would call unusually large, although there was some additional cell formatting going on.

A quick test on my local machine of a simple 3500 row, 2 varchar(255) column query added to a spreadsheet object using SpreadsheetAddRows(), with no formatting, took about 9 seconds (measured and repeated using getTickCount()).

The same task using <cfspreadsheet> took a mere 500 milliseconds. And that included reading the file back into a variable ready for any formatting or other operations to be applied.

UDF

To get the benefit of this remarkable speed improvement in script I've encapsulated it into the following addition to my general utility library.

<cffunction name="spreadsheetNewFromQuery" output="false">
	<cfargument name="data" type="query" required="true">
	<cfargument name="sheetName" type="string" default="Sheet1">
	<cfargument name="removeHeaderRow" type="boolean" default="false">
	<cfset var tempPath = GetTempDirectory() & CreateUuid() & ".xls">
	<cfspreadsheet action="write" filename="#tempPath#" query="data" sheetname="#sheetName#" overwrite="true">
	<cfscript>
		var spreadsheetObject = SpreadsheetRead( tempPath );
		FileDelete( tempPath );
		if( removeHeaderRow ){
			SpreadsheetShiftRows( spreadsheetObject,2,data.recordcount+1,-1 );
		}
		return spreadsheetObject;
	</cfscript>
</cffunction>

One difference to be aware of is that, unlike with SpreadsheetAddrows(), the query column names will be included in the first row (in upper case). Sometimes you don't want any column headers, so the function has an optional removeHeaderRow argument.

Also note that although the populated spreadsheet has to be written to disk, the file is only temporary and is deleted once it's been read back into a spreadsheet object to be returned. (Incidentally I considered using the Virtual File System for the temporary file storage, but CF9 threw an exception.)

Having returned the basic loaded spreadsheet in a fraction of the previous time, you are still free to apply any formatting/post-processing, such as ensuring phone numbers etc are rendered as text.