cfspreadsheet alphanumeric values ending in d

Question

<cfscript>
Workbook = Spreadsheetnew("Workbook");
SpreadSheetSetCellValue(WorkBook, "4D", 1, 1);
// displayed 4
SpreadSheetSetCellValue(WorkBook, "4C", 1, 2);
// displayed 4C
SpreadSheetSetCellValue(WorkBook, "4E", 1, 3);
// displayed 4E
SpreadSheetSetCellValue(WorkBook, "5C", 1, 4);
// displayed 5C
SpreadSheetSetCellValue(WorkBook, "5D", 1, 5);
// displayed 5
SpreadSheetSetCellValue(WorkBook, "4d", 1, 6);
// displayed 4
MYfile = "d:\dw\dwtest\dan\abc.xls";

</cfscript>
<cfspreadsheet action="write" filename="#MYFile#" name="Workbook"  
    sheet=1 overwrite=true>

ColdFusion Setttings: Version 9,0,1,274733
Edition Enterprise
Operating System Windows 2003
OS Version 5.2

Excel Version Office 2010 Version 14.0.6129.5000 (32 bit).

If you run this code on your system, do you get the same results?

More importantly, if you get the same results, do you know what to do about it?

Edit

Checking for other problematic letters:

RowNumber = 1;
for (i = 65; i <= 90; i++){
SpreadSheetSetCellValue(WorkBook, chr(i), RowNumber, 1);
SpreadSheetSetCellValue(WorkBook, "4#chr(i)#", RowNumber, 2);
RowNumber ++;
}

The string 4F also displayed the number only.

Answer 1

The issue here is that POI is interpreting the F and D as the single/double precision suffixes that Java has. See docs here.

I would say this is a bug with CF, as CFML does not have the concept of these suffixes (or indeed the notion of single or double precision floats), so it should make sure such strings get treated as strings when being passed to POI.

Answer 2

Using Dan's original code to check for troublesome characters I updated it to search for characters (to use by prepending or appending to the given text) to hide this ColdFusion feature:

WorkBook = spreadsheetNew('Test', true);
RowNumber = 1;  
for (i = 1; i <= 255; i++){
    SpreadSheetSetCellValue(WorkBook, i, RowNumber, 1);

    // what character are we displaying
    SpreadSheetSetCellValue(WorkBook, chr(i), RowNumber, 2);

    // see if appending chr(i) allows 4F to display
    SpreadSheetSetCellValue(WorkBook, "4F#chr(i)#", RowNumber, 3);

    // see if appending chr(i) allows 4F to display
    SpreadSheetSetCellValue(WorkBook, "#chr(i)#4F", RowNumber, 4);
    RowNumber ++;
}

Turns out prepending or appending nonprintable characters chr(127) and chr(160) maintain the presentation of 4F or 4D.