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

[marks84376706]

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

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>

[BKBK]

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]

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]

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

[Dave Watts]

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/