Skip to main content
marks84376706
marks84376706
New Participant
February 27, 2025
Question

spreadsheetAddRows treating strings like numbers and ending up with -1 as a value

  • February 27, 2025
  • 2 replies
  • 286 views

Hi,

We have certain values  (e.g., '1002-1', '1002-2',  ...   '1002-12') that end up as -1 when added to a spreadsheet via spreadsheetAddRows.  Weirdly, '1002-13' is fine.  Maybe spreadsheetAddRows is making bad guesses about whether a value is text vs a number?   spreadsheetSetCellValue doesn't seem to have the problem.  I reproduced the issue in cffiddle, although it looks like CF 2025 is going to fix the problem.   

This cffiddle link shows the issue:  https://cffiddle.org/app/file?filepath=b1d4c4bd-0989-4481-bcd3-46200ec39e7f/f4b909fd-a448-49ff-87da-91f8eb9f4d16/f29fc59a-821f-4481-9ad3-8f32869c6dd4.cfm

Has anyone run into this before, and do you happen to have a workaround?  
thanks,  Mark


<cfset testArray = [[ 'Bar, Foo', '1002-1', 'G009']]>

<cfset theSheetObj = SpreadsheetNew("tempSheet",true)>

<cfset spreadsheetAddRows(theSheetObj, testArray)>
<cfoutput>#SpreadsheetGetColumnCount(theSheetObj,1)#<br></cfoutput>

<cfoutput>#spreadsheetGetCellValue(theSheetObj, 1,1)#<br></cfoutput>
<cfoutput>#spreadsheetGetCellValue(theSheetObj, 1,2)#<br></cfoutput>
<cfoutput>#spreadsheetGetCellValue(theSheetObj, 1,3)#</cfoutput><br>

<cfset spreadsheetSetCellValue(theSheetObj, '1002-1', 1, 2)>
<cfoutput>#spreadsheetGetCellValue(theSheetObj, 1,2)#<br></cfoutput>

    2 replies

    D
    Dave Watts
    Community Expert
    February 28, 2025

    The CFFiddle script shows the same problem for CF 2021 & 2023, but the problem has been fixed in CF 2025. So you should probably open a support ticket with Adobe about it here (I think):

     

    https://tracker.adobe.com/

     

    Beyond this, you might be able to use JavaCast to force these to be seen as strings instead of dates. Spreadsheets tend to think of lots of things as dates that aren't.

     

    https://www.reddit.com/r/ExplainTheJoke/comments/1estm8c/i_dont_get_it/

     

    Dave Watts, Eidolon LLC
      BKBK
      BKBK
      Community Expert
      February 28, 2025

      It indeed looks like you've discovered a bug in the spreadsheet functions of ColdFusion 2023. You should report it. 

      If you do, please include the URL of this discussion in your report.

       

      I have been able to reproduce the issue. The following CF2023 test-code dumps a query and writes the same data to an XLSX file.

      <cfset mydata = queryNew("mycolumn1,mycolumn2,mycolumn3,mycolumn4", "varchar,varchar,varchar,varchar",
	[{"mycolumn1"='FooBar1',"mycolumn2"='Bar1, Foo1',"mycolumn3"='1002-1',"mycolumn4"='G007'},
	{"mycolumn1"='FooBar2',"mycolumn2"='Bar2, Foo2',"mycolumn3"='2003-2',"mycolumn4"='G008'},
	{"mycolumn1"='FooBar3',"mycolumn2"='Bar3, Foo3',"mycolumn3"='3004-3',"mycolumn4"='G009'}])
>

<cfset writeDump(var=mydata, label="MyData")>

<cfscript>
      
       // Create a spreadsheet object with sheetname MyDataSheet
       spreadsheetObj=spreadsheetNew("MyDataSheet",true);
       
       // Add rows with data from query result. The data start from row 1, col 1. 
       // The spreadsheet will have column names mycolumn1, mycolumn2, ....
       spreadsheetAddrows(spreadsheetObj,mydata,1,1,true,[""],true);
       
       dirName=GetDirectoryFromPath(GetCurrentTemplatePath());
       // Write spreadsheet to a file myDataSpreadsheet.xlsx in the current directory
       SpreadsheetWrite(spreadsheetObj,"#dirName#myDataSpreadsheet.xlsx",true);
</cfscript>

      The query contains the expected data. However, the spreadsheet functions seem to have converted the strings "1002-1", "2003-2" and "3004-3" to dates, even though mycolumn3's datatype is varchar. 

      BKBK
      BKBK
      Community Expert
      March 1, 2025

      Here is a strategy: search the list of ColdFusion's spreadsheet functions for an appropriate one that explicitly sets the datatype. One such function is SpreadsheetSetCellValue: SpreadsheetSetCellValue(spreadsheetObj, value, row, column, datatype)

       

      Using this strategy I found the following workaround:

       

      <cfset mydata = queryNew("mycolumn1,mycolumn2,mycolumn3,mycolumn4", "varchar,varchar,varchar,varchar",
	[{"mycolumn1"='FooBar1',"mycolumn2"='Bar1, Foo1',"mycolumn3"=javacast('string','1002-1'),"mycolumn4"='G007'},
	{"mycolumn1"='FooBar2',"mycolumn2"='Bar2, Foo2',"mycolumn3"=javacast('string','2003-2'),"mycolumn4"='G008'},
	{"mycolumn1"='FooBar3',"mycolumn2"='Bar3, Foo3',"mycolumn3"=javacast('string','3004-3'),"mycolumn4"='G009'}])
>

<cfset writeDump(var=mydata, label="MyData")>

<!--- Create a spreadsheet object with sheetname MyDataSheet --->
<cfset spreadsheetObj=spreadsheetNew("MyDataSheet",true)>

<!--- 
Add rows with data from query result. 
The first row consists of the columns.
The spreadsheet will have column names mycolumn1, mycolumn2, ...
The data start from row 2, col 1. 
--->
<cfset SpreadsheetSetCellValue(spreadsheetObj, "mycolumn1", 1, 1, "string")>
<cfset SpreadsheetSetCellValue(spreadsheetObj, "mycolumn2", 1, 2, "string")>
<cfset SpreadsheetSetCellValue(spreadsheetObj, "mycolumn3", 1, 3, "string")>
<cfset SpreadsheetSetCellValue(spreadsheetObj, "mycolumn4", 1, 4, "string")>

<cfloop query="mydata">
	<cfset SpreadsheetSetCellValue(spreadsheetObj, mycolumn1[currentRow], currentRow+1, 1, "string")>
	<cfset SpreadsheetSetCellValue(spreadsheetObj, mycolumn2[currentRow], currentRow+1, 2, "string")>
	<cfset SpreadsheetSetCellValue(spreadsheetObj, mycolumn3[currentRow], currentRow+1, 3, "string")>
	<cfset SpreadsheetSetCellValue(spreadsheetObj, mycolumn4[currentRow], currentRow+1, 4, "string")>
</cfloop>

<cfset dirName=GetDirectoryFromPath(GetCurrentTemplatePath())>
<!---Write spreadsheet to a file myDataSpreadsheet.xlsx in the current directory--->
  <cfset SpreadsheetWrite(spreadsheetObj,"#dirName#myDataSpreadsheet.xlsx",true)>

       

       

       

       

       

      BKBK
      BKBK
      Community Expert
      March 4, 2025

      Bug report: https://tracker.adobe.com/#/view/CF-4225712 

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices