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. David -- David North, Technical Lead, CoreFiling Limited http://www.corefiling.com Phone: +44-1865-203192 --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org
