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

genericHCU
  • 4,394
  • 2
  • 22
  • 34
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • Are the "D" characters actually in the cells and some formatting is hiding them or are the letters completely missing? – Miguel-F Mar 21 '13 at 17:24
  • They are completely missing. – Dan Bracuk Mar 21 '13 at 17:28
  • The D characters are missing, I ran into this before as well and the only solution I came up with was manual editing after the spreadsheet was created. Which worked as it was a one time report. – Busches Mar 21 '13 at 17:28
  • My report will be on demand. I can append a period if the last characters are a D or F, but that's a bit of a hack. – Dan Bracuk Mar 21 '13 at 17:36
  • You are not the only one. Check this other post http://stackoverflow.com/questions/5346396/coldfusion-9-mysteriously-removes-characters-d-and-f-after-numbers-when-ex – Miguel-F Mar 21 '13 at 17:45
  • (Edit) Oops. I should have read the complete thread first. That is what you get for skimming ;) – Leigh Mar 21 '13 at 18:13

4 Answers4

3

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.

Adam Cameron
  • 29,677
  • 4
  • 37
  • 78
  • Lol, I said the exact same thing in earlier comments, but deleted it after realizing @Miguel-F's link already pointed that out. I thought for sure he would post it as an answer. Oh well ... too late :) You beat him to it! – Leigh Mar 23 '13 at 20:08
  • Dammit. I wish I'd seen the link: coulda saved me some R&D time. – Adam Cameron Mar 24 '13 at 02:09
1

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.

0

If Miguel answers, I'll mark it as correct. The purpose of this answer is to show various things that 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 with SpreadsheetCellValue would display the quotes, plus the backslash I used to attempt to escape them. As indicated above, SpreadsheetSetCellFormula with triple quotes is the only way that appears to give expected results 100% of the time.

More Info In my actual application, I use cfheader/cfcontent to offer the file. If I select Open, with IE9, Excel shows a dangerous content warning and offers me a button to Enable Editing. Also, any cells created with SpreadSheetSetCellFormula() display the number 0. Selecting the cell shows the actual value in the excel value box, or whatever that's called. Also, enabling editing changes the display to the expected values.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • Just mark this or the other answer as correct. Not really my answer anyway, I just found it. Glad it worked for you. – Miguel-F Mar 21 '13 at 18:07
  • Dan - Just curious, any change if you format the cell as text first? Probably not, I suspect CF converts `4d` to a number long before then. But worth a shot. – Leigh Mar 21 '13 at 18:09
  • Leigh - That may work as well. Don't know if you saw it but there was a reference in the post that I referenced to a similar solution. See the answer here http://stackoverflow.com/questions/3081202/how-to-format-spreadsheet-columns-using-coldfusion – Miguel-F Mar 21 '13 at 18:13
  • @Leigh, when I added SpreadsheetFormatColumn(Workbook,{ dataformat="text" }, 2); to the code above, the results did not change. – Dan Bracuk Mar 21 '13 at 18:26
  • @Miguel-F - Oh yeah, I remember that one now. Different versions have different quirks. So I was just curious if it made any difference in Dan's version. (I cannot test it right now) @ Dan - That is what I was afraid of - I was hoping 9.0.1 considered the target cell's format before converting the value, but obviously not. Thanks for the confirm. – Leigh Mar 21 '13 at 19:08
0

FWIW, CF11+ introduced a new datatype attribute for SpreadSheetSetCellValue. Using type "string" preserves the original value and produces the expected result, ie "4D"

SpreadSheetSetCellValue(WorkBook, "4D", 1, 1, "string");
Leigh
  • 28,765
  • 10
  • 55
  • 103