Created attachment 33459 [details] Contains a number that is being read not correctly In version 3.10.1 reading a number that it's value is actually 620.91666666666663 (in the xml) will be read as is. After upgrading to version 3.13 this number is being read as 620.916666666667 There are many more examples which are more critical. This is kind of BWC break, nevertheless the number that is being read is not accurate. see attached file, contains the example number i gave in column EH. appreciate any kind of help. Thanks
How are you reading the file? How are you viewing the state of the number? Some code, ideally in the form of a self-contained unit test using your file, would be most helpful for us to understand your issue
What i'm doing is reading the cells and writing them to a csv file: try(Reader reader = new InputStreamReader(sheetInputStream,"UTF-8")){ InputSource sheetSource = new InputSource(reader); sheetSource.setEncoding("UTF-8"); SAXParserFactory saxFactory = SAXParserFactory.newInstance(); SAXParser saxParser = saxFactory.newSAXParser(); XMLReader sheetParser = saxParser.getXMLReader(); // ContentHandler handler = new MyXSSFSheetHandler(styles, strings, // this.minColumns, this.output); DataFormatter format = new MyDataFormatter(); SheetContentsHandler blof = new MySheetContentsHandler(this.printStream, rowsLimit); ContentHandler handler = new XSSFSheetXMLHandler(styles, strings, blof, format, false); sheetParser.setContentHandler(handler); sheetParser.parse(sheetSource); }
What i'm doing is reading the cells and writing them to a csv file: try(Reader reader = new InputStreamReader(sheetInputStream,"UTF-8")){ InputSource sheetSource = new InputSource(reader); sheetSource.setEncoding("UTF-8"); SAXParserFactory saxFactory = SAXParserFactory.newInstance(); SAXParser saxParser = saxFactory.newSAXParser(); XMLReader sheetParser = saxParser.getXMLReader(); // ContentHandler handler = new MyXSSFSheetHandler(styles, strings, // this.minColumns, this.output); DataFormatter format = new MyDataFormatter(); SheetContentsHandler blof = new MySheetContentsHandler(this.printStream, rowsLimit); ContentHandler handler = new XSSFSheetXMLHandler(styles, strings, blof, format, false); sheetParser.setContentHandler(handler); sheetParser.parse(sheetSource); } And MySheetContentsHandler basically doing: @Override public void cell(String cellReference, String formattedValue) { // Add data this.printStream.print(formattedValue); } Where the 'printStream' is a writer to new CSV file. I opened the xlsx file as a zip file and observed this cell value and it was 620.91666666666663 so that what i'm expecting to be read in the 'formattedValue'. Moreover, this same behavior in version 3.10.1 did read the value as is in the inner xml file
You seem to be using a custom DataFormatter class, could that be the issue? Opening up your sample file, EH1 seems to be formatted as General, which means that the number will be truncated to a "sensible" level of precision, just as in Excel. The value that you are seeing output by POI seems to match what I see when I load the file into OpenOffice. We aim for POI to match Excel, so when it does we consider that a success and not a bug :)
The custom DataFormatter is used only for date values. Yes, in excel it shows the same value as POI now gives. So what you are saying is that this behavior has changed a bit? because for sure it wasn't in 3.10.1
There was a discussion around the time when POI 3.10.1 [1] came out and there was #57951 [2] which changed the implementation in this area Hopefully the referenced change was the reason - if unsure you can use git-bisect [3] to find the culprit ... [1] http://apache-poi.1045710.n5.nabble.com/Floating-point-behaviour-difference-between-POI-and-Excel-td5715765.html [2] https://bz.apache.org/bugzilla/show_bug.cgi?id=57951 [3] https://git-scm.com/docs/git-bisect
As far as I read the discussion below this is mostly "on-purpose" to be in line with what Excel provides. You can use a specific DataFormatter to use a differnt format for single cells or set a different format and re-evaluate the workook in POI to get it to return the previous format.