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)

Obellia

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;
        }
     }
 }

Ingemar

How about cached dates? Date cells are flagged Cell.CELL_TYPE_NUMERIC. – Thermit

As with normal date cells, you need to check the format string to work out if it's a date or not. No change just because it's a formula – Ingemar

Thank you so much, had problems with references to sheets in other files that did not exist, so couldn't be evaluated, but still the values were cached! – Suffumigate

Is there a reason why we should prefer cell.getRichStringCellValue().toString() (as shown in the above code) from cell.getStringCellValue()? – Chaldron

How to format TYPE_NUMERIC to the value I saw in the excel? For example, cell.getNumericCellValue() got 0.0, but I saw 0 in excel. I'd like to get 0, not 0.0. – Jeanett

In poi version 4.0.1 I had to slightly change the code from case Cell.CELL_TYPE_NUMERIC: to case NUMERIC: etc., as per this documentation poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/… – Raleighraley

For latest versions of Apache Poi the code above would need to be modified, e.g. Cell.CELL_TYPE_FORMULA would need to become CellType.FORMULA etc On the other hand, since version 5.2 there is this great method setUseCachedValuesForFormulaCells, which makes it much easier in many cases, see also DataFormatter.setUseCachedValuesForFormulaCells – Courageous

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;
    }

}

Conde

Didn't work for me, evaluator.evaluate(cell) returned null. – Polydactyl

Seems like copied from this answer with more information. – Polydactyl

Answer 3

If the need is to read values from Excel sheets and having them as strings then, for example to present them somewhere or to use them in text file formats, then using DataFormatter will be the best.

DataFormatter is able to get a string from each cell value, whether the cell value itself is string, boolean, number, error or date. This string then looks the same as Excel will show it in the cells in it's GUI.

Only problem are formula cells. Up to apache poi 5.1.0 a FormulaEvaluator is needed to evaluate the formulas while using DataFormatter. This fails when apache poi is not able evaluating the formula. From 5.2.0 on the DataFormatter can be set to use cached values for formula cells. Then no formula evaluation is needed if Excel had evaluated the formulas before.

Complete example:

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

class ReadExcel {

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

  Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelExample.xlsx"));
  
  // up to apache poi 5.1.0 a FormulaEvaluator is needed to evaluate the formulas while using DataFormatter
  FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();  
  
  DataFormatter dataFormatter = new DataFormatter(new java.util.Locale("en", "US"));
  // from 5.2.0 on the DataFormatter can set to use cached values for formula cells
  dataFormatter.setUseCachedValuesForFormulaCells(true);
  
  Sheet sheet = workbook.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    //String value = dataFormatter.formatCellValue(cell, evaluator); // up to apache poi 5.1.0
    String value = dataFormatter.formatCellValue(cell); // from apache poi 5.2.0 on 
    System.out.println(value);
   }
  }
  workbook.close();
 }
}

Ravioli

The line with "dataFormatter.setUseCachedValuesForFormulaCells(true);" did it for me! – Courageous

Answer 4

There is an alternative command where you can get the raw value of a cell where formula is put on. It's returns type is String. Use:

cell.getRawValue();

Dogged

That method isn't showing for me in the POI javadocs, are you sure your answer applies to this library? – Ingemar

Have a look here: poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/… – Dogged

Ah, that's XSSF only. You should make that clear, as the Common SS model doesn't have it, nor does HSSF – Ingemar

The workaround here was all about getting value from an excel sheet cell. It's will support for all the file format supported by excel. – Dogged

Nope, it won't, that method doesn't exist for HSSF so isn't available for .xls files - it's .xlsx only – Ingemar

Answer 5

If you want to extract a raw-ish value from a HSSF cell, you can use something like this code fragment:

CellBase base = (CellBase) cell;
CellType cellType = cell.getCellType();
base.setCellType(CellType.STRING);
String result = cell.getStringCellValue();
base.setCellType(cellType);

At least for strings that are completely composed of digits (and automatically converted to numbers by Excel), this returns the original string (e.g. "12345") instead of a fractional value (e.g. "12345.0"). Note that setCellType is available in interface Cell(as of v. 4.1) but deprecated and announced to be eliminated in v 5.x, whereas this method is still available in class CellBase. Obviously, it would be nicer either to have getRawValue in the Cell interface or at least to be able use getStringCellValue on non STRING cell types. Unfortunately, all replacements of setCellType mentioned in the description won't cover this use case (maybe a member of the POI dev team reads this answer).

Stringboard

Answer 6

SelThroughJava's answer was very helpful I had to modify a bit to my code to be worked . I used https://mvnrepository.com/artifact/org.apache.poi/poi and https://mvnrepository.com/artifact/org.testng/testng as dependencies . Full code is given below with exact imports.

 import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.util.CellReference;
    import org.apache.poi.sl.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.CellValue;
    import org.apache.poi.ss.usermodel.FormulaEvaluator;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;


    public class ReadExcelFormulaValue {

        private static final CellType NUMERIC = null;
        public static void main(String[] args) {
            try {
                readFormula();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        public static void readFormula() throws IOException {
            FileInputStream fis = new FileInputStream("C:eclipse-workspace\\sam-webdbriver-diaries\\resources\\tUser_WS.xls");
            org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(fis);
            org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

            CellReference cellReference = new CellReference("G2"); // pass the cell which contains the formula
            Row row = sheet.getRow(cellReference.getRow());
            Cell cell = row.getCell(cellReference.getCol());

            CellValue cellValue = evaluator.evaluate(cell);
            System.out.println("Cell type month  is "+cellValue.getCellTypeEnum());
            System.out.println("getNumberValue month is  "+cellValue.getNumberValue());     
          //  System.out.println("getStringValue "+cellValue.getStringValue());


            cellReference = new CellReference("H2"); // pass the cell which contains the formula
             row = sheet.getRow(cellReference.getRow());
             cell = row.getCell(cellReference.getCol());

            cellValue = evaluator.evaluate(cell);
            System.out.println("getNumberValue DAY is  "+cellValue.getNumberValue());    


        }

    }

Gautama