tracker issue : CF-4198591

select a category, or use search below
(searches all categories and all time range)
Title:

<CFSPREADSHEET> generates a file with data corrupted. SPREADSHEETNEW() does not.

| View in Tracker

Status/Resolution/Reason: Closed/Won't Fix/AsDesigned

Reporter/Name(from Bugbase): Stephen Johnson / Stephen Johnson ()

Created: 05/04/2017

Components: Language, Tags

Versions: 11.0

Failure Type: Data Corruption

Found In Build/Fixed In Build: 11,0,11,301867 /

Priority/Frequency: Normal / Few users will encounter

Locale/System: ALL / Win 2012 Server x64

Vote Count: 0

Problem Description: I validated that this bug goes back to CF 9. Assume that an XLSX is being generated on the basis on a query where one of those query values happens to be an integer followed by an 'f' or a 'd', for example '234123f'. From a CF perspective these values should be interpreted as strings (VARCHAR). Opening up the XLSX in Excel or inspecting the XML in the XLSX archive post generation shows a value of '234123' not '234123f'. From what I can tell CF is not translating '234123f' to a Java String class, but to a Java float, 234123f, or Java double 234123d. Interestingly, SPREADSHEETNEW() and SPREADHSHEETADDROWS()  works fine, even when you don't assign a datatype. 

Steps to Reproduce:
<cfscript>
	qTemp = queryNew("ID,NAME,MYVALUEFLOAT,MYVALUEDOUBLE","INTEGER,VARCHAR,VARCHAR,VARCHAR");
	queryAddRow(qTemp);
	querySetCell(qTemp,"ID",1);
	querySetCell(qTemp,"NAME","WTF");
	querySetCell(qTemp,"MYVALUEFLOAT","11f");
	querySetCell(qTemp,"MYVALUEDOUBLE","11d");

	cfspreadsheet(action="write", filename="c:\download.xlsx", query="qTemp", overwrite=false);
</cfscript>

versas

<cfscript>
	qTemp = queryNew("ID,NAME,MYVALUEFLOAT,MYVALUEDOUBLE","INTEGER,VARCHAR,VARCHAR,VARCHAR");
	queryAddRow(qTemp);
	querySetCell(qTemp,"ID",1);
	querySetCell(qTemp,"NAME","WTF");
	querySetCell(qTemp,"MYVALUEFLOAT","11f");
	querySetCell(qTemp,"MYVALUEDOUBLE","11d");
	xSpreadSheet = SpreadsheetNew("mySpreadSheet", true);

	datatype = [""];

	spreadsheetAddRows( xSpreadSheet, qTemp,1,1,"true",datatype);
	spreadsheetWrite(xSpreadSheet, "c:\download.xlsx","true");
</cfscript>


Actual Result:  See above

Expected Result: See above

Any Workarounds: Don't use CFSPREADSHEET or move onto Lucee.

Attachments:

Comments:

Stephen, The behavior is intentional. While processing query data, if the double values are detected the cell type is set to numeric. While the point you are making is also reasonable, but the case can be argued both ways. If you need the data to be interpreted as string, you can make use of relevant spreadsheet methods. The spreadsheet functions are data-aware CF11 onwards, enabling the user to control how the functions interpret the datatype of the passed data. Even if you don't pass the datatype parameter the spreadsheetAddRow(s) method will default to string. If you prefer using the cfspreadsheet tag, you can pass a spreadsheet object to the tag instead of passing the query. Let me know if this works for you. Changing the behavior for the cfspreadsheet tag with respect to the issue you've reported, may upset the behavior of a lot of existing application that adhere to the existing behavior.
Comment by Piyush K.
808 | May 17, 2017 01:19:38 PM GMT
You know, I've been writing enterprise apps in CF since the mid to late nineties. One of the reasons I stick with the language is that the data types are simple, convenient, and I can write stuff FAST. I'm dealing with numbers and strings. If I want something more, I write Java. The fact that you are interpreting these values as Java floats and doubles, not only totally breaks the spirit of the language, but you have 2 completely different behaviours across language syntax. Really? I'm also troubled because there seems to be a theme here. See my ticket CF-3993581. CF is supposed to abstract from Java.
Comment by Stephen J.
809 | May 23, 2017 04:51:53 PM GMT