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 17:21:12 GMT
On 02/06/14 17:01, Nick Burch wrote:
>>>> 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.
>
> Hmm, interesting
>
>>> 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...
>
> FWIW, the relevent code in XSSFCell is simply:
>
>                       return Double.parseDouble(_cell.getV());
>
> So we're relying on the default Java behaviour

Indeed, which does not seem unreasonable as Java's behaviour is closely
related to IEEE 754, which is also what Excel claims to use.

I've posted here to see if someone from Microsoft can give me a
definitive answer:

http://openxmldeveloper.org/discussions/formats/f/14/p/7774/163115.aspx

Wading through the Office Open XML Specifications has failed to throw up
a definitive reference on number parsing.

-- 
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