Hello guys, I am trying to read a value computed using a formula in an excel
sheet.
The formula is a simple sum function as follows: J1 + SUM(B2:B3) + SUM(E2:F3)
Using Apache POI 3.14 libraries, I came up with the following snippet of code:
public Double getCellValue() throws IOException, InvalidFormatException{
Workbook workBook = WorkbookFactory.create(new FileInputStream(new
File("data.xlsx")));
Sheet sheet = workBook.getSheetAt(0);
// i only need one specific cell
// i made sure I am targeting the correct cell
Row row = sheet.getRow(1);
Cell cell = row.getCell(9);
// this is where I am stuck
FormulaEvaluator evaluator =
workBook.getCreationHelper().createFormulaEvaluator();
CellValue cellValue = evaluator.evaluate(cell);
return cellValue.getNumberValue();
}
The method keeps returning 0.0 instead of the correct computed value.
I initially tried a different approach using cell.getNumericCellValue() instead
of FormulaEvaluator, however that was giving me the same incorrect result, 0.0.
After going through the documentation online, I failed to find an explanation
to my problem, any insight on the matter would be greatly appreciated.
Update: I moved the cell to a different location on the grid in Excel, now the
method does return a value, just not the correct one (e.g. 515 instead of 800).
It was initially at the last row in my table, and whenever I would manipulate
my table (add a new row of data), I would shift the last row down and update
the formula accordingly. Now it's position is fixed at the second row. I'm not
entirely sure why this would make any difference at all, maybe something that
has to do with the sheet.shiftRows() method?
Cheers.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
