Bug 58883 - Numbers after the decimal point are being cut (rounded) in version 3.13, but not in version 3.10.1 (BWC break)
Summary: Numbers after the decimal point are being cut (rounded) in version 3.13, but ...
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.13-FINAL
Hardware: All All
: P1 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-01-18 08:43 UTC by shahar
Modified: 2016-07-24 19:11 UTC (History)
0 users



Attachments
Contains a number that is being read not correctly (9.52 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-01-18 08:43 UTC, shahar
Details

Note You need to log in before you can comment on or make changes to this bug.
Description shahar 2016-01-18 08:43:12 UTC
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
Comment 1 Nick Burch 2016-01-18 08:59:12 UTC
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
Comment 2 shahar 2016-01-18 09:30:35 UTC
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);
			}
Comment 3 shahar 2016-01-18 09:35:43 UTC
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
Comment 4 Nick Burch 2016-01-18 09:38:51 UTC
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 :)
Comment 5 shahar 2016-01-18 09:43:35 UTC
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
Comment 6 Andreas Beeker 2016-01-18 14:11:07 UTC
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
Comment 7 Dominik Stadler 2016-07-24 19:11:04 UTC
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.