Bug 30565

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

Created attachment 16912 [details]
xls file being tested

(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; 
	}
}

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..

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

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.   

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.