poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David North <dtn-...@corefiling.co.uk>
Subject Re: Floating-point behaviour - difference between POI and Excel?
Date Mon, 02 Jun 2014 13:42:57 GMT
On 02/06/14 13:09, Nick Burch wrote:
> On Mon, 2 Jun 2014, David North wrote:
>> The value in sheet 1, cell A1, looks like this in the XML:
>>
>> <v>62408.000000000007</v>
>
> Isn't floating point fun?
>
That's one word for it!
>> Now, when opening the attached sheet in Excel 2010 or 2013, the UI shows
>> the value in cell A1 as:
>>
>> 62408
>>
>> No matter how I format it, the UI refuses to acknowledge or display the
>> .000000000007 in the underlying file.
>
> What about if you set a format string of something like
> 0.000000000000000 ? (I suspect the 62408 displayed is due to a format
> string of 0 or General)

Setting that format string results in 62408.000000000000000 - adding
more zeroes to the format string just adds more zeroes to the displayed
number, with no sign of any non-zero digits after the decimal point.

>
>> However, when reading this cell using POI, getNumericCellValue returns
>>
>> 62408.00000000001
>>
>> Is this correct?
>
> Quite possibly. What does DataFormatter give you if you ask for the
> formatted String representation of the cell?

new DataFormatter().formatCellValue(theCell) gives the string 62408
>
> Also, if you save the file as a .xls, does that change how both POI
> and Excel see it? (.xlsx and .xls use different ways of serialising
> numeric values to disk)

No, Excel 2010/2013 and POI both behave the same when the example is
converted to .xls - including DataFormatter giving "62408".

I think I'll have to resort to reading the office formats spec to try
and find the official word on how applications are supposed to deal with
reading values outside the range of double-precision floating point...

David

-- 
David North, Technical Lead, CoreFiling Limited
http://www.corefiling.com
Phone: +44-1865-203192


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Mime
View raw message