JavaCast float rounds generously

Question

I calculate sums and put these into an Excel sheet I generate using POI library in Coldfusion. As the Java library expects typed vars I always call setCellValue( JavaCast( "float", myVar ) ). I was made aware of a rounding error by .03. Way bigger than differences typically known after casting to float.

<cfset s = 601761.66>
<cfoutput>
#s#<br>
#JavaCast( "float", s )#<br>
#LSNumberFormat( JavaCast( "float", s ), ".0000000" )#<br><br>
</cfoutput>

The first line prints 601761.66
The second rounds to 601761.7
The third however prints: 601761,6875000 which is equal to 601761,69 and by .03 larger than the value I put in.

I know, LSNumberFormat returns a string. I called it just for comparison. POI seems to store the float value, Excel eventually displays the value as LSNumberFormat does.

How can I pass a value to setCellValue that is so close to my value that at least the second digit after the decmal is rounded correctly?

Why would you use `LSNumberFormat` instead of `NumberFormat`? — Dan Bracuk, Apr 10 '17 at 14:08
Probably because NumberFormat only supports U.S. formatting rules. — Leigh, Apr 10 '17 at 14:20
In this case I use LSNumberformat just to see more than two decimal digits. In real life applications i use this function to get the comma as decimal separator instead of the dot. — Bernhard Döbler, Apr 10 '17 at 19:31

score 4 · Accepted Answer · edited May 23 '17 at 12:17

Short answer:

Use type double instead of float, i.e. javacast("double", value)

Longer answer:

The Cell.setCellValue() method actually expects type Double (not Float). Double is also what CF uses for most numeric operations and functions . When you pass a Float into those methods, it is implicitly converted into a Double. That conversion is (indirectly) causing the unexpected result.

The reason is that both Float and Double are approximate types. However, Double has greater precision:

float: The float data type is a single-precision 32-bit IEEE 754 floating point. ...

double: The double data type is a double-precision 64-bit IEEE 754 floating point. ...

So as this thread points out (emphasis mine):

It's not that you're actually getting extra precision - it's that the float didn't accurately represent the number you were aiming for originally. The double is representing the original float accurately; toString is showing the "extra" data which was already present. ... [When converted to a double, it] will have exactly the same value, but when you convert it to a string it will "trust" that it's accurate to a higher precision, so won't round off as early, and you'll see the "extra digits" which were already there, but hidden from you

That is why both "601761.66" and "601761.6875" seem to be rounded to "601761.7" when cast as a float, but are displayed expected when cast as a double.

<cfscript>
    value1 = "601761.66";
    value2 = "601761.6875";

    WriteOutput("<br>[Float] "& value1 &" = "& javacast("float", value1));
    WriteOutput("<br>[Float] "& value2 &" = "& javacast("float", value2));
    WriteOutput("<br>[Float=>Double] "& value1 &" = "& javacast("double", javacast("float", value1)));
    WriteOutput("<br>[Double] "& value1 &" = "& javacast("double", value1));
    WriteOutput("<br>[Double] "& value2 &" = "& javacast("double", value2));
</cfscript>

Output:

[Float] 601761.66 = 601761.7
[Float] 601761.6875 = 601761.7
[Float=>Double] 601761.66 = 601761.6875
[Double] 601761.66 = 601761.66
[Double] 601761.6875 = 601761.6875

NB: CF uses Float.toString() and Double.ToString() to display the values via cfoutput/writeOutput/cfdump.

JavaCast float rounds generously

1 Answers1