CODEWITHSUNDEEP
excelcoldfusion
Cmaso
Cmaso

Reputation: 1113

Coldfusion SpreadsheetAddRow() - How to get around values with commas

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?

Upvotes: 2

Views: 1815

Answers (2)

Cmaso
Cmaso

Reputation: 1113

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

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

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.

Upvotes: 2

Related Questions