Coldfusion SpreadsheetAddRow() - How to get around

Question

I've read some of the other solutions to this problem on the web, but none of the help in my particular situation. I'm looping over a query and using spreadsheetAddRow() to add rows to my Excel spreadsheet with each iteration. The problem is that any value with a comma in it causes CF to throw a "String index out of range: -1" error. This happens even though I'm wrapping these values in single quotes. So something like:

<cfset spreadsheetAddRow(s, "'foobar','foo,bar'")>

The first value is fine but the second value trows an error. This happens even if I use variables instead of string literals:

<cfset val1 = "foobar">
<cfset val2 = "foo,bar">
<cfset spreadsheetAddRow(s, "'#val1#','#val2#'")>

Or if I try the method suggested here at the very bottom of the page.

I would use spreadsheetAddRows() instead to grab all rows of the query at once, but the problem is with the way I want to display the info the spread sheet. For each row in my query, I am making 3 rows on the Excel sheet -- one for some values in the query row, one below it for some other values in the query row, and then a blank row.

I'm going to try using SpreadsheetSetCellValue(), where for each cell value I look for a comma, temporarily substitute it with a special character, and then after the spreadsheetAddRow() go back and replace all instances of that special character in the row with a comma. But that's inefficient and hacky. Is there any way to "escape" commas, so that CF recognizes the comma is a part of the value?

Answer 1

Also, if it's not obvious, I couldn't loop over qReconciled.columnlist because some of the columns needed to be included on row 1 of each iteration, and some needed to be on row 2 of each iteration. General idea was doing a join on two tables, and displaying such that that certain columns in both tables that are supposed to have identical values per record can be compared.

Final code looks like:

<cfset currentSpreadSheetRow = 1> <!---start at 1, to ignore spreadsheet header row--->

<cfset s = spreadsheetNew("true")><!---true makes it support xlsx format --->

<!--- Add header row --->
<cfset spreadsheetAddRow(s, "Assessment Source,Client Last,Client First,SSN,Gender,Client Case Number,...")>


<!---For each row in the query, create 3 rows in the spreadsheet. One for STARS, one for SACAP, and a blank row--->
<cfloop query="qReconciled">

    <!---Need to do this, else spreadsheet won't recognize them as strings--->
    <cfset tCheckin = " #timeformat(dcheckin,'short')#" />
    <cfset tAssessmentStart = " #timeformat(dAssessmentStart,'short')#" />
    <cfset tCheckouttime = " #timeformat(dCheckouttime,'short')#" />
    <cfset tTimeInBin = " #timeformat(timeInBin,'short')#" />
    <cfset tApptBeginTime = " #timeformat(dApptBeginTime,'short')#" />
    <cfset tApptEndTime = " #timeformat(dApptEndTime,'short')#" />
    <cfset tAppointmentTime = " #timeformat(dAppointmentdate,'short')#" />
    <cfset tScheduledTime = " #timeformat(scheduledTime, 'short')#" />

    <cfset arrThisRowSTARSValues = ['STARS','#vClientlname#','#vClientfname#','#vSSN#','#vGender#',#listFirst(vClientcasenumber, '-')#,...] />

    <cfset arrThisRowSACAPValues = ['SACAP','#sacap_clientLName#','#sacap_clientFName#','#sacap_ssn#','#sacap_gender#','#vcientId#',...]/>

    <!---This method of populating the spreadsheet is necessary; with spreadsheetAddRow(), commas in values will be interpreted as a new column, even if the value is wrapped in single-quotes, and this screws everything up--->
    <cfset currentSpreadSheetRow = currentSpreadSheetRow + 1 />

    <!---STARS Row--->
    <cfloop from="1" to="39" index="x">
        <cfset spreadsheetSetCellValue(s,arrThisRowSTARSValues[x],currentSpreadSheetRow,x) />
    </cfloop>

    <cfset currentSpreadSheetRow = currentSpreadSheetRow + 1 />

    <!---SACAP Row--->
    <cfloop from="1" to="39" index="x">
        <cfset spreadsheetSetCellValue(s,arrThisRowSACAPValues[x],currentSpreadSheetRow,x) />
    </cfloop>

    <!---Blank Row--->
    <cfset spreadsheetAddRow(s, "") />

    <cfset currentSpreadSheetRow = currentSpreadSheetRow + 1 />
</cfloop>

<cfheader name="content-disposition" value="attachment; filename=#vImportName#_Reconciled.xlsx">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

Answer 2

You said this:

For each row in my query, I am making 3 rows on the Excel sheet -- one for some values in the query row, one below it for some other values in the query row, and then a blank row.

I suggest that you simply do what you say you want to do. Something like this:

<cfset currentSpreadSheetRow = 0>
<cfloop query = "yourQuery">
  <cfset columnNumber = 1>
  <cfloop list = "#yourQuery.columnlist#" index = "field">
    <cfset SpreadsheetSetCellValue(yourSheet,
    yourQuery[field][currentRow]
    , currentSpreadSheetRow + 1
    , columnNumber) >
    <cfset columnNumber +=1>
  </cfloop>
  <!--- code for second row --->
  <cfset currentSpreadSheetRow += 3>
</cfloop>

This is the general idea. You can change the details to suit your specific requirements.