The function for adding a row to a coldfusion spreadsheet is SpreadsheetAddrow which accepts data as "A comma delimited list of cell entries, one per column."
Some of my data has commas within it. How do I escape the commas within my data without escaping the commas in the list?
I am currently creating an array with the contents of the row, then converting it to a list to add to the spreadsheet:
<cfset row = ArrayNew(1)>
<cfloop list="#structKeyList(setRecord.columns)#" index="key">
<cfset ArrayAppend(row, "#Evaluate(key)#")>
</cfloop>
<cfset spreadsheetAddRow(xlsObj, "#ArrayToList(row)#")>
If the data you want to add to the sheet is in a query object (recordset) then the simplest solution is to use SpreadSheetAddRows(), (as opposed to SpreadSheetAddRow - singular).
<cfset SpreadSheetAddRows( xlsObj,query )>
The sheet columns are mapped from the query columns, so commas in the data won't matter.
Even if the data is in another format or you are only adding a single row, converting it to a query object is an effective way of getting round the issue, see http://cfsimplicity.com/30/workaround-for-spreadsheetaddrow-limitation-when-column-values-contain-commas