1

I have 16 digit number that needs to show all the digits. If I just key it into Excel I can put a ' in front of the number and it keeps the number from going into scientific notation. When I do the same in <cfspreadsheet> I get the number with a ' in front of it. I have also tried to use cell formatting:

<cfscript>
variables.hugenumber = {

color="dark blue;", 
dataformat = "0"
};
</cfscript>

Which doesn't help either

James A Mohler
  • 11,060
  • 15
  • 46
  • 72

1 Answers1

2

It turns out that a format like this is needed:

<cfscript>
variables.hugenumber = {

color="dark blue;", 
dataformat = "################"
};
</cfscript>
James A Mohler
  • 11,060
  • 15
  • 46
  • 72
  • 1
    Just curious, what is your version? `dataformat="0"` produced a numeric cell for me w/9.0.1.27xxxx – Leigh Jul 23 '13 at 19:27
  • 9,0,1,274733 Enterprise – James A Mohler Jul 23 '13 at 19:45
  • Also, it was a 16 digit number. Maybe excel switches at a certain point – James A Mohler Jul 23 '13 at 19:47
  • Yeah, I tested with a 17 digit number (same CF version). It displayed as a plain number, not scientific notation. Granted Excel will round the numbers after a certain point. `spreadsheetFormatCell(sheet, {dataFormat="0"}, 3, 1); spreadsheetSetCellValue(sheet, "98765432101234567", 3, 1);` – Leigh Jul 23 '13 at 19:51
  • I cannot test it now, but that could explain it. I remember there are still a few formatting bugs in `SpreadsheetAddRows`. – Leigh Jul 24 '13 at 16:25
  • 1
    `SpreadsheetAddRows` will auto-format data in sometimes undesirable ways. I came up with a workaround for CF9.0.1: http://cfsimplicity.com/16/forcing-values-to-be-inserted-into-spreadsheets-as-text – CfSimplicity Jul 25 '13 at 08:33
  • Just got a chance to test it. Confirmed it works if you apply the format *after* adding the cell values. Though it does round numbers that exceed Excel's capacity (double). To preserve the exact number, you would probably need to use "text" format. – Leigh Jul 25 '13 at 15:30