cfspreadsheet escape comma in comma separated row insert

Question

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

Answer 1

Looks like the ability to specify a different delimiter is not supported yet. Since you are already looping, you may as well skip the array and use SpreadsheetSetCellValue instead. You should be able to eliminate the evaluate() as well.

<cfset cols = structKeyArray(yourStruct) >
<cfloop from="1" to="#arrayLen(cols)#" index="c">
   <cfset SpreadsheetSetCellValue(xlsObj, yourStruct[ cols[c] ], lastRow, c)>
</cfloop>
<cfset lastRow++>
...

Update: However, if the base object is a query, not a structure, then it is more efficient to use CfSimplicity's suggestion of SpreadSheetAddRows.

Answer 2

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