The built-in spreadsheet manipulation functionality is one of the new features of CF9 I use with great regularity. So much simpler and more powerful than the various third-party tools one had previously to fall back on.

But there are a few shortcomings currently awaiting attention from Adobe, such as the problems working with data that may be interpreted as numeric.

Another concerns SpreadSheetAddRow(). Want to add a single row of data to a spreadsheet? This function is your friend... unless your data happens to contain any commas, in which case it is your enemy. Why? Because SpreadSheetAddRow() expects the columns in your row to be delimited by commas, not contain them.

<cfscript>
// Create a spreadsheet with a single column
mySheet = SpreadsheetNew( "Test" );
SpreadsheetAddColumn( mySheet,"MySingleColumn" );
// Add a row of data: a single value for the single column
mySingleColumnValue="As you can see, this sentence contains commas, which force the value to be split over 3 columns instead of one.";
SpreadsheetAddRow( mySheet,mySingleColumnValue );
</cfscript>
<!--- Download the spreadsheet--->
<cfheader name="Content-Disposition" value="attachment; filename=#Chr( 34 )#test.xls#Chr( 34 )#">
<cfcontent type="application/msexcel" variable="#SpreadSheetReadBinary( mySheet )#" reset="true">

A simple workaround is to proceed as if you were adding multiple rows and use SpreadsheetAddRows(). Let's just create a single cell array and pass that in.

<cfscript>
mySheet = SpreadsheetNew( "Test" );
SpreadsheetAddColumn( mySheet,"MySingleColumn" );
mySingleColumnValue=[ "As you can see, this sentence contains commas, but let's try adding it using an array." ];
SpreadsheetAddRows( mySheet,mySingleColumnValue );
</cfscript>
<cfheader name="Content-Disposition" value="attachment; filename=#Chr( 34 )#test.xls#Chr( 34 )#">
<cfcontent type="application/msexcel" variable="#SpreadSheetReadBinary( mySheet )#" reset="true">

Except that's no better than our previous attempt. I checked that the value was being passed in as an array but it is apparently being treated by CF exactly as if I'd passed in the string value in my first example. Strange.

Reliable friend

OK, so let's just stick to a good old-fashioned query object.

<cfscript>
mySheet = SpreadsheetNew( "Test" );
SpreadsheetAddColumn( mySheet,"MySingleColumn" );
mySingleColumnValue="As you can see, this sentence contains commas, added using a query.";
q = QueryNew( "MySingleColumn","VarChar" );
QueryAddRow( q );
QuerySetCell( q,"MySingleColumn",mySingleColumnValue );
SpreadsheetAddRows( mySheet,q );
</cfscript>
<cfheader name="Content-Disposition" value="attachment; filename=#Chr( 34 )#test.xls#Chr( 34 )#">
<cfcontent type="application/msexcel" variable="#SpreadSheetReadBinary( mySheet )#" reset="true">

Verbose it may be, but it works.

If this issue affects you, then please consider voting on the Adobe bug tracker for it to be fixed.

UPDATE 15 September 2011: Kunal from Adobe has pointed out a much more straightforward workaround, which is to wrap the variable in single quotes and then in double quotes, ie:

<cfscript>
mySheet = SpreadsheetNew( "Test" );
SpreadsheetAddColumn( mySheet,"MySingleColumn" );
mySingleColumnValue="As you can see, this sentence contains commas, which force the value to be split over 3 columns instead of one.";
SpreadsheetAddRow( mySheet,"'#mySingleColumnValue#'" );
</cfscript>
<cfheader name="Content-Disposition" value="attachment; filename=#Chr( 34 )#test.xls#Chr( 34 )#">
<cfcontent type="application/msexcel" variable="#SpreadSheetReadBinary( mySheet )#" reset="true">

This works even if the variable contains single quotes, but it's not terribly obvious as a solution, so I do hope we get an option to define our own column delimiter in the next version of CF.