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

apache-poiexceljavanotimplementedexception

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)

Best Answer

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

Related Solutions

Java – What are the differences between a HashMap and a Hashtable in Java

There are several differences between HashMap and Hashtable in Java:

Hashtable is synchronized, whereas HashMap is not. This makes HashMap better for non-threaded applications, as unsynchronized Objects typically perform better than synchronized ones.
Hashtable does not allow null keys or values. HashMap allows one null key and any number of null values.
One of HashMap's subclasses is LinkedHashMap, so in the event that you'd want predictable iteration order (which is insertion order by default), you could easily swap out the HashMap for a LinkedHashMap. This wouldn't be as easy if you were using Hashtable.

Since synchronization is not an issue for you, I'd recommend HashMap. If synchronization becomes an issue, you may also look at ConcurrentHashMap.

Java – How does the Java ‘for each’ loop work

for (Iterator<String> i = someIterable.iterator(); i.hasNext();) {
    String item = i.next();
    System.out.println(item);
}

Note that if you need to use i.remove(); in your loop, or access the actual iterator in some way, you cannot use the for ( : ) idiom, since the actual iterator is merely inferred.

As was noted by Denis Bueno, this code works for any object that implements the Iterable interface.

Also, if the right-hand side of the for (:) idiom is an array rather than an Iterable object, the internal code uses an int index counter and checks against array.length instead. See the Java Language Specification.

Best Answer

Related Solutions

Java – What are the differences between a HashMap and a Hashtable in Java

Java – How does the Java ‘for each’ loop work

Related Topic