Comma is invalid when using dataformat

Question

Here is my code to output a query to a spreadsheet.

<cfscript> 
  //Use an absolute path for the files. ---> 
  theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
  theFile=theDir & "getTestInv.xls"; 
  //Create an empty ColdFusion spreadsheet object. ---> 
  theSheet = SpreadsheetNew("invoicesData"); 
  //Populate the object with a query. ---> 
  SpreadsheetAddRows(theSheet,getTestInv);
</cfscript>

<cfset format = StructNew()>
<cfset format.dataformat = "#,###0.00">
<cfset SpreadsheetFormatColumn(theSheet,format,10)

<cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheetname="getTestInv" overwrite=true>

The error I am getting is:

Invalid CFML construct found on line 125 at column 32.
ColdFusion was looking at the following text:

,

The CFML compiler was processing:

An expression beginning with /", on line 125, column 30.This message is usually caused by a problem in the expressions structure.
A cfset tag beginning on line 125, column 4.
A cfset tag beginning on line 125, column 4.

125: <cfset format.dataformat = "#,###0.00">

For some reason, it doesn't like the comma, even though it is valid according to the documentation. If I take the comma out, it works, but I need it for the thousands grouping.

Anyone encountered this?

BKK · Accepted Answer · 2013-01-18T10:34:36.263

In ColdFusion, the # is a reserved character. To escape it, you'll have to double them up to escape them:

<cfset format.dataformat = "##,######0.00">

Silly that they didn't account for this either in the documentation or followed ColdFusion's formatting rules using 9s instead of #s.

Here is my full working standalone test code:

<cfset myQuery = QueryNew('number')>
<cfset newRow = QueryAddRow(MyQuery, 2)> 
<cfset temp = QuerySetCell(myQuery, "number", "349348394", 1)> 
<cfset temp = QuerySetCell(myQuery, "number", "10000000", 2)> 

<cfscript> 
  //Use an absolute path for the files. ---> 
  theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
  theFile=theDir & "getTestInv.xls"; 
  //Create an empty ColdFusion spreadsheet object. ---> 
  theSheet = SpreadsheetNew("invoicesData"); 
  //Populate the object with a query. ---> 
  SpreadsheetAddRows(theSheet,myQuery,1,1);
</cfscript>


<cfset format = StructNew()>
<cfset format.dataformat = "##,######0.00">
<cfset SpreadsheetFormatColumn(theSheet,format,1)>

<cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheetname="theSheet" overwrite=true>

Getting a similar error now, only now saying: `ColdFusion was looking at the following text: /"` There is no forward slash anywhere near it. — Alias, Jan 18 '13 at 10:30
Thanks. Do you know how to have the Excel file opened on click? At the moment it just saves it in the current directory without opening and I have to navigate to the directory to open it, which obviously a user cannot do. — Alias, Jan 18 '13 at 10:45
You want to generate the file and serve it to the browser in one click? After writing the file, you can serve it up with CFCONTENT tag. Read the docs on it. — BKK, Jan 18 '13 at 10:57

score 0 · Answer 2 · answered Jan 18 '13 at 10:26

0

it should be like that

<cfset format = StructNew()>
<cfset format.dataformat = "##,####0.00">

answered Jan 18 '13 at 10:26

PPShein

13,309
42
142
227

Comma is invalid when using dataformat

2 Answers2