Workaround for SpreadSheetAddRow() limitation when column values contain commas

20 June 2011

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">

screenshot of spreadsheet showing row value split over 3 columns instead of just 1

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">

screenshot of spreadsheet showing row value split over 3 columns instead of just 1

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">

screenshot of spreadsheet showing row value correctly added as a single column cell

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.

Posted by Julian on . Updated

Comments

  • Formatting comments: See this list of formatting tags you can use in your comments.
  • Want to paste code? Enclose within <pre><code> tags for syntax higlighting and better formatting and if possible use script. If your code includes "self-closing" tags, such as <cfargument>, you must add an explicit closing tag, otherwise it is likely to be mangled by the Disqus parser.
Back to the top