Hello Yhqian99,
For clarity, here is the code which seems to reproduce your problem:
HSSFWorkbook wb = new HSSFWorkbook();
wb.createSheet("Sheet1").createRow(0).createCell(0).setCellValue(34.44F); //
F for float literal
OutputStream os = new FileOutputStream("Test34.44.xls");
wb.write(os);
os.close();
In Excel(2007) the cell value displays as 34.4399986267089.
POI uses double (forcing a conversion from float) because that's what Excel
needs.
The root of the problem is that many seemingly simple decimal values cannot
be representable exactly with an IEEE floating point (for example 0.01).
In this example, the float->double conversion seems to be the main source of
the precision loss, as seen with the following code:
System.out.println((double)34.44F); // outputs "34.439998626708984"
It's not just in type conversion that these floating point rounding
limitations can be seen. The following code uses only doubles:
System.out.println(0.05+0.01); // outputs "0.060000000000000005"
Since, POI/Excel uses doubles, doubles have higher precision than floats and
conversions are especially prone to rounding errors, your best bet is to use
doubles as much as possible. This won't eliminate all rounding errors, but
should improve things a lot.
As MSB wrote, you can also use cell style data formats to hide these
rounding errors (but this has no effect on how the values are used in
calculations).
Hope this helps,
Josh
