Reading Formula fields from Excel in Java using Apache POI

Question

I am trying to read formula values from Excel using Apache POI. I am almost able to read all values perfectly. My code snippet:

case Cell.CELL_TYPE_FORMULA:
        switch(cell.getCachedFormulaResultType()) {
         case Cell.CELL_TYPE_NUMERIC:
           e=cell.getNumericCellValue();
          break;
          case Cell.CELL_TYPE_STRING
           e=cell.getRichStringCellValue();
              break;
           }

This is working fine in most of the cases, like for integers and float values, but when formula is working with % then it is giving a problem.

When formula=1234-1200 then it is reading value =34
but when formula=100%-40% then it is reading value =0.6 but not 60%.

How to overcome this?

Answer 1

Try something like this within your case Cell.CELL_TYPE_NUMERIC: statement

if (cell.getCellStyle().getDataFormatString().contains("%")) {
    // Detect Percent Values 
    Double value = cell.getNumericCellValue() * 100;
    System.out.println("Percent value found = " + value.toString() +"%");
} else {
    Double value = cell.getNumericCellValue();
    System.out.println("Non percent value found = " + value.toString());
}