You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by yhqian99 <yh...@163.com> on 2009/03/03 04:59:21 UTC
a float number set to a numeric cell is changed to a double
Hello all,
I am setting a float number like 34.44 to a numerical cell, but when I open
the .xls file by Microsoft Excel, it show as 34.4399986267089, it looks like
the HSSFCell's setCellValue(double value) automatically convert a float to a
double. But I don't want the extra digits, how can I make the .xls file
shows 34.44 instead of 34.4399986267089
Thanks,
Yhqian99
--
View this message in context: http://www.nabble.com/a-float-number-set-to-a-numeric-cell-is-changed-to-a-double-tp22302151p22302151.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: a float number set to a numeric cell is changed to a double
Posted by Josh Micich <jo...@gmail.com>.
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
Re: a float number set to a numeric cell is changed to a double
Posted by yhqian99 <yh...@163.com>.
Hello MSB,
You post is very helpful, and my problem is gone, thank you a million!
Yhqian99
MSB wrote:
>
> In Excel, there is a difference between what a cell contains and how that
> data is presented to the user - think of a date as a good example; a
> numeric value that can be formatted to appear in different ways.
>
> So, bearing that in mind, you need to create an apply a style to the cell
> (HSSFCellStyle). Most importantly, you need to associate a data format
> (HSSFDataFormat) with the cell style that will ensure the value is
> displayed exactly as you want.
>
> Take a look at this piece of code;
>
> http://poi.apache.org/spreadsheet/quick-guide.html#DataFormats
> http://poi.apache.org/spreadsheet/quick-guide.html#DataFormats
>
> Not all of it applies to your example, the bit that does is here;
>
> row = sheet.createRow(rowNum++);
> cell = row.createCell(colNum);
> cell.setCellValue(11111.25);
> style = wb.createCellStyle();
> style.setDataFormat(format.getFormat("#,##0.0000"));
> cell.setCellStyle(style);
>
> The line you want to look at most closely is this one;
>
> style.setDataFormat(format.getFormat("#,##0.0000"));
>
> as it contains the formatting string. If you change that String to
> "#,##0.00" then it will display just two digits after the decimal point.
>
>
>
> yhqian99 wrote:
>>
>> Hello all,
>>
>> I am setting a float number like 34.44 to a numerical cell, but when I
>> open the .xls file by Microsoft Excel, it show as 34.4399986267089, it
>> looks like the HSSFCell's setCellValue(double value) automatically
>> convert a float to a double. But I don't want the extra digits, how can
>> I make the .xls file shows 34.44 instead of 34.4399986267089
>>
>> Thanks,
>>
>> Yhqian99
>>
>>
>
>
--
View this message in context: http://www.nabble.com/a-float-number-set-to-a-numeric-cell-is-changed-to-a-double-tp22302151p22306896.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: a float number set to a numeric cell is changed to a double
Posted by MSB <ma...@tiscali.co.uk>.
In Excel, there is a difference between what a cell contains and how that
data is presented to the user - think of a date as a good example; a numeric
value that can be formatted to appear in different ways.
So, bearing that in mind, you need to create an apply a style to the cell
(HSSFCellStyle). Most importantly, you need to associate a data format
(HSSFDataFormat) with the cell style that will ensure the value is displayed
exactly as you want.
Take a look at this piece of code;
http://poi.apache.org/spreadsheet/quick-guide.html#DataFormats
http://poi.apache.org/spreadsheet/quick-guide.html#DataFormats
Not all of it applies to your example, the bit that does is here;
row = sheet.createRow(rowNum++);
cell = row.createCell(colNum);
cell.setCellValue(11111.25);
style = wb.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.0000"));
cell.setCellStyle(style);
The line you want to look at most closely is this one;
style.setDataFormat(format.getFormat("#,##0.0000"));
as it contains the formatting string. If you change that String to
"#,##0.00" then it will display just two digits after the decimal point.
yhqian99 wrote:
>
> Hello all,
>
> I am setting a float number like 34.44 to a numerical cell, but when I
> open the .xls file by Microsoft Excel, it show as 34.4399986267089, it
> looks like the HSSFCell's setCellValue(double value) automatically convert
> a float to a double. But I don't want the extra digits, how can I make
> the .xls file shows 34.44 instead of 34.4399986267089
>
> Thanks,
>
> Yhqian99
>
>
--
View this message in context: http://www.nabble.com/a-float-number-set-to-a-numeric-cell-is-changed-to-a-double-tp22302151p22304157.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org