CODEWITHSUNDEEP
javaexcelapache-poi
NNJ
NNJ

Reputation: 3

Problem in reading Numeric value form Excel file using Apache POI

I have one Column in numeric and the value is 0.142 but convert to 0.14200000000000002

This is my code , I use cell.getNumericCellValue() 

case Cell.CELL_TYPE_NUMERIC : 
vaTmpVal = String.valueOf(voCel.getNumericCellValue());

Upvotes: 0

Views: 3462

Answers (2)

Axel Richter
Axel Richter

Reputation: 61852

The Cell.getNumericCellValue() gets numeric cell values exact as they are stored. But Excel always uses only 15 significant digits for numeric values. So the exact value 0.14200000000000002 (having 17 significant digits) will be 0.142 for Excel.

If the need is to get all cell values as string, then you should not get the cell values by CellType. Instead you should using apache poi's DataFormatter. The DataFormatter.formatCellValue method gets all cell values as strings by formatting them the same kind as Excel will do. So if Excel shows 0.142 as the cell value then also DataFormatter will get 0.142 even if the exact numeric cell value is 0.14200000000000002.

Example:

import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.util.Locale;

class GetDataFromExcelUsingDataFormatter {

 public static void main(String[] args) throws Exception {

  Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));

  DataFormatter dataFormatter = new DataFormatter();
  FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) {
   for (Cell cell : row) {
    String cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);
    System.out.println(cellValue);
   }
  }

  workbook.close();
 }
}

Upvotes: 1

adchilds
adchilds

Reputation: 963

Floating point numbers, as you understand them in a base-10 numbering system (i.e. 0 - 9), cannot be accurately represented in binary (i.e. 0 - 1). Thus, most of the time what you'll see (from a programming language construct) is an approximation of the value.

In your case, if you don't care about the precision of your values past the thousandths position, you can use DecimalFormat:

import java.text.DecimalFormat;

public class Scratch {

    public static void main(String[] args) {
        System.out.println(new DecimalFormat("0.000").format(0.142));
    }
}

Just for your reference as well, this exact question has been asked on poi's bug tracker and has been closed as "RESOLVED INVALID".

Additionally, as a fun little experiment to prove out the above, execute the following in a jshell session:

% jshell
|  Welcome to JShell -- Version 11
|  For an introduction type: /help intro

jshell> 0.1*0.1
$1 ==> 0.010000000000000002

You'd expect the above answer to be only 0.01; however, that's not the case.

Upvotes: 1

Related Questions