JavaCast floats in rounds generously
I am calculating the sums and putting them in an Excel sheet that I generate using the POI library in Coldfusion. Since the Java library expects typed vars, I always call setCellValue( JavaCast( "float", myVar ) )
. I was informed about a rounding error on .03
. The way more differences are usually known after casting in a swim.
<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
- Second round before
601761.7
- The third, however, prints:,
601761,6875000
which is equal to601761,69
and.03
greater than the value I was inserting.
I know LSNumberFormat
returns a string. I named it just for comparison. The POI seems to store the float value, Excel will end up displaying the value as LSNumberFormat.
How do I pass a value setCellValue
that is so close to my value that at least the second digit after rounding is rounded correctly?
source to share
The short answer is:
Use double instead of float, i.e. javacast("double", value)
Longer answer:
The Cell.setCellValue () method does indeed expect a Double (not Float ) type. Double is also what CF uses for most of the numeric operations and functions . When you pass Float to these methods, it is implicitly converted to Double. This conversion (indirectly) produces unexpected results.
The reason is that Float and Double are approximate types . However, Double has more precision:
float . The float data type is a single precision 32-bit IEEE 754 floating point 32-bit ...
double . The double data type is a double precision 64-bit IEEE 754 floating point ....
Since this thread points out (emphasis mine):
It's not that you actually get the extra precision - it's that the float doesn't exactly represent the number you were aiming for initially. The double accurately represents the original float; toString shows "extra" data that was already present .... [When converted to double, it] will have exactly the same value, but when you convert it to a string, it will "trust" to be accurate with more precision , so it will not be rounded off as early and you will see "extra digits" that were already there, but hidden from you
This is why both "601761.66" and "601761.6875" appear to be rounded to "601761.7" when exposed as a float, but are displayed pending on double conversion.
<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 values via cfoutput / writeOutput / cfdump.
source to share