Bug 57018 - Problem in POI for reading decimal value in Xlsx file, for ex value 34.56012 returns as 34.560100234
Summary: Problem in POI for reading decimal value in Xlsx file, for ex value 34.56012 ...
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: unspecified
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Keywords: APIBug
Depends on:
Reported: 2014-09-25 06:27 UTC by rajeshwaran.n
Modified: 2014-09-25 15:05 UTC (History)
0 users

MyXSSFSheetHandler (8.30 KB, text/plain)
2014-09-25 07:32 UTC, rajeshwaran.n
xlsx file used to read (9.35 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-09-25 09:47 UTC, rajeshwaran.n

Note You need to log in before you can comment on or make changes to this bug.
Description rajeshwaran.n 2014-09-25 06:27:54 UTC
I am using POI XSSF for reading the xlsx file, I have the problem in reading the decimal values. For ex, value in sheet 34.56012 but returns as 34.560100234 for some specific values. Any solution for the problem could be appreciated!!..
Comment 1 Nick Burch 2014-09-25 06:59:42 UTC
How are you fetching the cell values, and what formatting are you applying to them?
Comment 2 rajeshwaran.n 2014-09-25 07:32:35 UTC
Created attachment 32054 [details]
Comment 3 rajeshwaran.n 2014-09-25 07:33:19 UTC
public ArrayList<ArrayList<String>> getSheetData() throws IOException,
			OpenXML4JException, ParserConfigurationException, SAXException {
	OPCPackage xlsxPackage = OPCPackage.open(fileName, PackageAccess.READ);
	ArrayList<ArrayList<String>> sheetData = new ArrayList<ArrayList<String>>();
	ReadonlySharedStringsTable strings = new ReadonlySharedStringsTable(
	XSSFReader xssfReader = new XSSFReader(xlsxPackage);
	StylesTable styles = xssfReader.getStylesTable();
	XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
	int index = 0;
	if (iter.hasNext()) {
		InputStream stream = iter.next();
		String sheetName = iter.getSheetName();
		this.output.println(sheetName + " [index=" + index + "]:");
		sheetData = processSheet(styles, strings, stream);
	return sheetData;

public ArrayList<ArrayList<String>> processSheet(StylesTable styles,
		ReadonlySharedStringsTable strings, InputStream sheetInputStream)
		throws IOException, ParserConfigurationException, SAXException {

	InputSource sheetSource = new InputSource(sheetInputStream);
	SAXParserFactory saxFactory = SAXParserFactory.newInstance();
	SAXParser saxParser = saxFactory.newSAXParser();
	XMLReader sheetParser = saxParser.getXMLReader();		
	ContentHandler handler = new MyXSSFSheetHandler(styles, strings,
			this.minColumns, this.output);
	return ((MyXSSFSheetHandler) handler).getData();

Used this logic to read the data
Comment 4 Nick Burch 2014-09-25 07:53:09 UTC
What's the format string that Excel thinks is applied to the cell, and what is the one that POI sees? And what's the raw number stored by Excel in the file?
Comment 5 rajeshwaran.n 2014-09-25 09:47:27 UTC
Created attachment 32056 [details]
xlsx file used to read
Comment 6 rajeshwaran.n 2014-09-25 09:49:32 UTC
There is no format applied in cell of the sheet as well as POI while reading the data.
Comment 7 Nick Burch 2014-09-25 15:05:42 UTC
If you don't set any formatting, you'll just get the raw number as stored by Excel

If you want formatting rules to apply to a cell, you need to set a cell style that includes them!