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> 

Configuring ColdFusion: Version 9,0,1,274733
Publishing company
Windows 2003 operating system
OS Version 5.2

Excel version Office 2010 Version 14.0.6129.5000 (32 bit).

If you run this code on your system, will 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 ++; } 

Line 4F also displays only the number.

Answer 1

The problem here is that the POI interprets F and D as single / double precision suffixes that Java has. See Docs here .

I would say that this is a mistake with CF, since CFML does not have the concept of these suffixes (or, indeed, the concept of a single or double precision float), so he must make sure that such lines are treated as strings when transferred to the POI.

Answer 2

Using the original Dan code to check for problematic characters, I updated it to search for characters (for use by adding or adding to this 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 ++; } 

Invokes the addition or addition of non-printable characters chr (127) and chr (160) that support 4F or 4D representation.

Answer 3

If Miguel answers, I will mark him as correct. The purpose of this answer is to show the various things I tried and how they turned out.

 <cfoutput> <cfscript> Workbook = Spreadsheetnew("Workbook"); RowNumber = 1; for (i = 1; i <= 26; i++){ ThisUpperCaseLetter = chr(i + 64); ThisLowerCaseLetter = chr(i + 96); SpreadSheetSetCellValue(WorkBook, ThisUpperCaseLetter, RowNumber, 1); SpreadSheetSetCellValue(WorkBook, "4#ThisUpperCaseLetter#", RowNumber, 2); SpreadSheetSetCellValue(WorkBook, ThisLowerCaseLetter, RowNumber, 3); SpreadSheetSetCellValue(WorkBook, "4#ThisLowerCaseLetter#", RowNumber, 4); SpreadSheetSetCellValue(WorkBook, "'4#ThisLowerCaseLetter#'", RowNumber, 5); // SpreadSheetSetCellFormula(WorkBook, "'4#ThisLowerCaseLetter#'", RowNumber, 6); /* The line above threw this error org.apache.poi.ss.formula.FormulaParser$FormulaParseException: Parse error near char 0 ''' in specified formula ''4a''. Expected number, string, or defined name */ SpreadSheetSetCellValue(WorkBook, """4#ThisLowerCaseLetter#""", RowNumber, 6); SpreadSheetSetCellValue(WorkBook, "\'4#ThisLowerCaseLetter#\'", RowNumber, 7); // the next line is the only one that will achieve the desired result SpreadSheetSetCellFormula(WorkBook, """4#ThisLowerCaseLetter#""", RowNumber, 8); RowNumber ++; } MYfile = "d:\dw\dwtest\dan\abc.xls"; </cfscript> </cfoutput> <cfspreadsheet action="write" filename="#MYFile#" name="Workbook" sheet=1 overwrite=true> 

Anything that matters SpreadsheetCellValue will display quotes as well as the backslash that I used to try to avoid them. As stated above, triple-quoted SpreadsheetSetCellFormula is the only way that seems to give the expected results in 100% of cases.

Additional Information In my actual application, I use cfheader / cfcontent to suggest a file. If I select Open, with IE9, Excel displays a warning about dangerous content and offers me the "Enable Editing" button. In addition, any cells created using SpreadSheetSetCellFormula () display the number 0. Selecting the cell displays the actual value in the excel value field or anything else that is called. In addition, enabling editing changes the display to the expected values.

Answer 4

FWIW, CF11 + introduced a new datatype attribute for SpreadSheetSetCellValue . Using the type "string" saves the original value and gives the expected result, that is, "4D"

 SpreadSheetSetCellValue(WorkBook, "4D", 1, 1, "string");