Java POI : How to read Excel cell value and not the formula computing it?

Question

I am using Apache POI API to getting values from an Excel file. Everything is working great except with cells containing formulas. In fact, the cell.getStringCellValue() is returning the formula used in the cell and not the value of the cell.

I tried to use evaluateFormulaCell() method but it's not working because I am using GETPIVOTDATA Excel formula and this formula is not implemented in the API:

Exception in thread "main" org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Landscape!K11     at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321)     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)     at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320)     at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:213)     at fromExcelToJava.ExcelSheetReader.unAutreTest(ExcelSheetReader.java:193)     at fromExcelToJava.ExcelSheetReader.main(ExcelSheetReader.java:224) Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: GETPIVOTDATA     at org.apache.poi.hssf.record.formula.functions.NotImplementedFunction.evaluate(NotImplementedFunction.java:42) 

Answer 1

For formula cells, excel stores two things. One is the Formula itself, the other is the "cached" value (the last value that the forumla was evaluated as)

If you want to get the last cached value (which may no longer be correct, but as long as Excel saved the file and you haven't changed it it should be), you'll want something like:

 for(Cell cell : row) {      if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {         System.out.println("Formula is " + cell.getCellFormula());         switch(cell.getCachedFormulaResultType()) {             case Cell.CELL_TYPE_NUMERIC:                 System.out.println("Last evaluated as: " + cell.getNumericCellValue());                 break;             case Cell.CELL_TYPE_STRING:                 System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");                 break;         }      }  } 

Answer 2

Previously posted solutions did not work for me. cell.getRawValue() returned the same formula as stated in the cell. The following function worked for me:

public void readFormula() throws IOException {     FileInputStream fis = new FileInputStream("Path of your file");     Workbook wb = new XSSFWorkbook(fis);     Sheet sheet = wb.getSheetAt(0);     FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();      CellReference cellReference = new CellReference("C2"); // pass the cell which contains the formula     Row row = sheet.getRow(cellReference.getRow());     Cell cell = row.getCell(cellReference.getCol());      CellValue cellValue = evaluator.evaluate(cell);      switch (cellValue.getCellType()) {         case Cell.CELL_TYPE_BOOLEAN:             System.out.println(cellValue.getBooleanValue());             break;         case Cell.CELL_TYPE_NUMERIC:             System.out.println(cellValue.getNumberValue());             break;         case Cell.CELL_TYPE_STRING:             System.out.println(cellValue.getStringValue());             break;         case Cell.CELL_TYPE_BLANK:             break;         case Cell.CELL_TYPE_ERROR:             break;          // CELL_TYPE_FORMULA will never happen         case Cell.CELL_TYPE_FORMULA:             break;     }  }