cfspreadsheet escape comma in comma separated row insert

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 creati开发者_Python百科ng 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)#")>

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.

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

You can replace the commas with the character "#130;" (see the source: https://www.petefreitag.com/cheatsheets/ascii-codes/), which looks the same as comma but doesn't create any problems.