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)
This question is related to
java
excel
apache-poi
notimplementedexception
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();
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());
}
}
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;
}
}
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;
}
}
}
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).
Source: Stackoverflow.com