javaexcelapache-poihssf

apache.poi HSSFCell getNumericCellValue incorrect read

I read numeric values from excel using apache.poi.

hssfCell.getNumericCellValue()

But here is example of excel file where the result is wrong.

Example of excel file

The correct result for cell N14 is 11,115 (or 11,12 with excel formatting). But hssfCell.getNumericCellValue() get result 11.114999999999998.

It's formula cell, so I can use dateFormatter, then result is 11,11.

HSSFWorkbook wb = new HSSFWorkbook(new ByteArrayInputStream(importFile));
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(wb);
DataFormatter dataFormatter = new DataFormatter();
formulaEvaluator.evaluate(hssfCell);
dataFormatter.formatCellValue(hssfCell, formulaEvaluator);

Excel shows me 11,12. How can I get this value?

Solution

  • You could use this:

    DecimalFormat decimalFormat = new DecimalFormat("#.##");
decimalFormat.setRoundingMode(RoundingMode.CEILING);      
decimalFormat.format(hssfCell.getNumericCellValue());

    The problem is that the precision is hard-code. If you will only use 2 decimal places, this solution will work fine.