Bug 30565 - getNumericCellValue is returning more digits from a formula cell than expected
Summary: getNumericCellValue is returning more digits from a formula cell than expected
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.5-FINAL
Hardware: All All
: P3 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2004-08-10 16:27 UTC by Peter Hamann
Modified: 2005-11-30 10:20 UTC (History)
0 users



Attachments
xls file being tested (13.50 KB, application/octet-stream)
2005-11-08 19:14 UTC, Gopal
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Peter Hamann 2004-08-10 16:27:16 UTC
When getting data from a cell containing af formula, using getNumericCellValue, 
I get a lot of digits that I dont find in the sheet if opened in excel.

E.g. when opened in excel a cell might show the value 0,3, but when getting the 
same cell-value from getNumericCellValue () I will get 0,300000001. 

Why is that ?

/ Peter
Comment 1 Gopal 2005-11-08 15:13:48 UTC
related bug 30248: HSSFCell.getNumericCellValue() cannot handle large/small ... 

Not just from formula, but from any numeric cell, the numer of digits returned 
for even simple numeric values are "more" precise (or wrong). A method to get 
the value of the cell as a text string would definitely be helpful, so that 
user may use the value to do whatever required. Currently if the cell has 
something like 1092544061.35, the getNumericCellValue returns 
1092544061.3499994 !

I am going to add an attachment
Comment 2 Gopal 2005-11-08 19:14:19 UTC
Created attachment 16912 [details]
xls file being tested
Comment 3 Gopal 2005-11-08 19:16:45 UTC
(In reply to comment #2)
> Created an attachment (id=16912) [edit]
> xls file being tested

Here is the Java code for the reader, which prints different values than those 
present on the excel.

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigDecimal;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;

public class ReadFirstNumericCell {

	// will just read one cell and tell the value
	public static void main(String[] args) {
		test();
	}

	private static void test() { 
		String TEST_FILE_NAME = "c:\\test.xls";
		try {
			// get work book
			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(
					TEST_FILE_NAME));
			HSSFCell a1 = printNumericCellValueAt(wb, 0, 0);
			HSSFCell a2 = printNumericCellValueAt(wb, 1, 0);
			HSSFCell a3 = printNumericCellValueAt(wb, 2, 0);
			HSSFCell a4 = printNumericCellValueAt(wb, 3, 0);
			HSSFCell a5 = printNumericCellValueAt(wb, 4, 0);	
		
			
			printNumericCellValueAt(wb, 0, 2); 
			printNumericCellValueAt(wb, 1, 2);
			printNumericCellValueAt(wb, 2, 2);
			printNumericCellValueAt(wb, 3, 2);
			printNumericCellValueAt(wb, 4, 2);	

			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}


	private static HSSFCell printNumericCellValueAt(HSSFWorkbook wb, int 
idxRow, int idxCol) {
		HSSFCell cell = wb.getSheet("Sheet1").getRow(idxRow).getCell
((short) idxCol);
		String ref = (new CellReference(idxRow, idxCol)).toString();
		if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
			BigDecimal val = new BigDecimal(Double.toString
(cell.getNumericCellValue()));
			System.out.println(ref + " value : " + val);
		}
		return cell; 
	}
}

Comment 4 Andy Oliver 2005-11-08 20:57:58 UTC
they aren't wrong you just are using a display format that gives more percision.
 Use NumberFormat to munge it to the expected number of decimals..
Comment 5 Gopal 2005-11-30 15:01:06 UTC
I think i didnt make it very clear. 

In the excel file attached before (at c:\test.xls), i made the format of all 
the cell to 10 places of decimal. Now, all the values are "displayed" to 10 
places after decimal in the excel file, as suggested : 1820311666.4400000000 
and 0.10000000000 

However, now when i run the attached code, the values returned are: 
A1 value : 1820311666.440002
A2 value : 1820311666.44
A3 value : 1820311666.440002
A4 value : 1820311666.44
A5 value : 0.1
C1 value : 1820311666.44
C2 value : 1820311666.44
C3 value : 1820311666.44
C4 value : 1820311666.44
C5 value : 0.1

The issue is with the cells being displayed as 1820311666.4400000000 in the 
excel sheet, but being retrieved as 1820311666.440002! 

Is this a bug in HSSF POI? Why does the value returned from POI not show as 
1820311666.4400000000 ?

Thanks, 
Gopal
Comment 6 Avik Sengupta 2005-11-30 15:06:58 UTC
POI does not return 1820311666.4400000000 because that's not what excel stores. 
Read up on the precision issues with binary->decimal floating point 
conversions.  
 
This is how excel stores it, this is how we will return it. Excel formats it  
before display, you can do the same.   
  
Gopal, please dont add people as CC's to bugs without their consent. All bug  
activity goes to the POI dev lists. The poi user lists are also the best place  
to ask questions and seek clarifications.   
Comment 7 Andy Oliver 2005-11-30 19:20:37 UTC
Read this: http://en.wikipedia.org/wiki/Floating_point

These are floating point numbers.  Its not POIs fault that floating point
numbers are not precise.  You can make them LOOK like what you want with
numberformat, but  floating point numbers are the lies your computer tells you
to make you feel good about the precision of its estimations.  Excel is lying to
you.  It has no idea whether 1820311666.4400000000 is 1820311666.4400000002 or
1820311666.4400000003 or what.