Issue while printing the decimal values when reading from excel using java

Question

In Excel I have these decimal values:

TS	BS
5.60	4.10
10.00	10.00
10.00	10.00
10.00	10.00

While parsing, it shows different values:

TS	BS
0.1375	0.85
0.0125	0.7125
0.0125	0.8125000000000001
0.0125	0.7875000000000001

I need to print the exact values.

Here is the code:

Iterator<Row> itr = sheet.iterator();
while (itr.hasNext())
{
    Row row = itr.next();
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext())
    {
      Cell cell = cellIterator.next();
      switch (cell.getCellType())
      {
        case STRING:
        System.out.print(cell.getStringCellValue() + "\t\t\t");
        break;
        case NUMERIC:    
        if (DateUtil.isCellDateFormatted(cell)) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
        System.out.print(dateFormat.format(cell.getDateCellValue()) + "\t\t\t");
        } else {
           System.out.print(cell.getNumericCellValue() + "\t\t\t\t");
          }
           break;
            default:
             }
           }
            System.out.println("");
       }

I'm using Apache POI-4.1.1.

Could please someone help me with this?

"I need to print the exact values" Those values are totally different anyway (e.g. 5.60 vs 0.1375). — Andy Turner, Aug 12 '21 at 10:18
So, it's not that you need the _exact_ values, it's that it's getting totally wrong values? Silly question, are you sure you're reading the right file/sheet? — Andy Turner, Aug 12 '21 at 10:19
I am reading the right file. There's other columns too which printing the correct values (e. the date and string columns) — XXXX, Aug 12 '21 at 10:21
Are you sure you're not looking at the results of formulas in the sheets? ;) — g00se, Aug 12 '21 at 11:20
What happens with `BigDecimal value = new BigDecimal(cell.getNumericCellValue());`? — g00se, Aug 12 '21 at 11:28
@g00seNo, it didn't work with BigDecimal. It's giving something like this 0.84999999999999997779553950749686919152736663818359375. But, I guess I understood where's the issue. There's no formula but it seems there's an address of a path from sharepoint. — XXXX, Aug 12 '21 at 11:32
You need to use the POI DataFormatter to get the formatted values. Excel stores numbers as doubles (and doubles are just approximate values). https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DataFormatter.html — PJ Fanning, Aug 12 '21 at 14:56

XXXX · Answer 1 · 2021-08-13T07:29:56.560

g00se is right. My issue is solved. I had to parse the values from formula.

Iterator<Row> itr = sheet.iterator();
while (itr.hasNext()) {
 Row row = itr.next();
 //iterating over each column
 Iterator<Cell> cellIterator = row.cellIterator();
 while (cellIterator.hasNext()) {
 Cell cell = cellIterator.next();
 CellType cellType = cell.getCellType();
 if (cell.getCellType() == CellType.FORMULA) {
 switch (cell.getCachedFormulaResultType()) {
 case NUMERIC:
 System.out.print(cell.getNumericCellValue() + "\t\t");
 break;
 case STRING:
 System.out.print(cell.getRichStringCellValue() + "\t\t");
 break;
    }
 }
 switch (cell.getCellType()) {
 case STRING:
//field that represents string cell type
System.out.print(cell.getStringCellValue() + "\t\t");
break;
case NUMERIC:
//field that represents number cell type
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");                                System.out.print(dateFormat.format(cell.getDateCellValue()) + "\t\t");
   } 
  else {
double roundOff = Math.round(cell.getNumericCellValue() * 100.0) / 100.0;
System.out.print(roundOff + "\t\t");
        }
         break;
         case BLANK:
         break;
         default:
       }
     }
  System.out.println("");
 }
}

Issue while printing the decimal values when reading from excel using java

1 Answers1