On 04/06/14 13:26, David North wrote: > On 02/06/14 18:21, David North wrote: >> 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. >> > http://www.ecma-international.org/publications/files/ECMA-ST/Office%20Open%20XML%201st%20edition%20Part%204%20(PDF).zip > > Section 3.17.5.2 on page 2531: > > "[The value space of numbers in SpreadsheetML] is patterned after the > IEEE double-precision 64-bit floating-point type" > > So it looks like POI is right and Excel is wrong. LibreOffice is > consistent with Excel, so I'll try and find out the justification for that.
Further digging reveals this article on the Microsoft site: http://office.microsoft.com/en-us/excel-help/change-formula-recalculation-iteration-or-precision-HA102749052.aspx?CTT=1#_Toc305944079 "Precision in Excel means that any number exceeding 15 digits is stored and shown with only 15 digits of precision. Those digits can be in any combination before or after the decimal point. Any digits to the right of the 15^th digit will be zeros." So it looks like I could make my extracted numbers match what the user sees in Excel by dropping significant digits beyond the 15th. If we go down that route I'll consider submitting a patch, or at least some JavaDoc pointing out the trap. David -- David North, Technical Lead, CoreFiling Limited http://www.corefiling.com Phone: +44-1865-203192
