Hi Nick, the Cells have no Format. Take a look at the attached File, cell F10. Andreas tells me it was entered as 0.1066913 & that's how its displayed too, although it has no format.
Internally, in the XML, its saved as: <v>0.10669129999999999</v> which has an exact Java double representation. POI returns 0.10669129999999999. But Excel & LibreOffice both somehow manage to show 0.1066913 ! In columns P-Q I've tried various permutations. But Excel is not able to format that value in full length (see Cell T10). Take a look at Cell F12, 0.0866913. Internally its stored as: <v>8.6691299999999985E-2</v> This value is NOT representable as a Java double. POI returns 0.08669129999999999. Excel value (XML)..: 0.086691299999999985 (8.6691299999999985E-2) ---------------------------------------------------------------------- Java double (Init).: 0.08669129999999997 3fb63166aa59047c (raw) Java double (Next).: 0.08669129999999999 3fb63166aa59047d (raw) Java double (Next).: 0.0866913 3fb63166aa59047e (raw) Internally, Excel seems to be storing some values in an extended precision? Can you give us some insight? All the best, DaveLaw On 20/10/2019 18:21, Nick Burch wrote:
On Sun, 20 Oct 2019, Andreas Reichel wrote:Opening the file in an XML Text Editor, I get 0.10669129999999999. Opening the file in GNUMERIC, I get 0.10669129999999999. (Both the shown cell content as well as the editable text box). Opening the file in LibreOffice, I get 0.1066913. (Same file, I have tried 3 times. Both the shown cell content as well as the editable text box). Opening the file in MS Excel, I get 0.1066913. So, while it is still an Excel problem, I wonder how/why LibreOffice and Excel know/decide about interpreting the value as 0.1066913?!If you ask POI to format the value to a string based on the formatting rules applied to the cell, using DataFormatter or similar, do you get the value you expect?Nick --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
ifrsbox_DataCaptureExample.xlsx
Description: MS-Excel 2007 spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
