Sorry I made a mistake
I make the recalculation with:
evalA.evaluateFormulaCell(risksheet.getRow(35).getCell(18));
System.err.println("VLOOKUP
"+risksheet.getRow(35).getCell(18).getNumericCellValue());
and I receive
java.lang.RuntimeException: Specified sheet from a different book
org.apache.poi.ss.formula.WorkbookEvaluator.getSheetIndex(WorkbookEvaluator.java:168)
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:177)
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:297)
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:190)
Regards,
Adrian
--- Begin Message ---
I have to calculate in a cell this formula
=VLOOKUP($P$37;Exemptions!$E$10:$I$31;2)
First I am recalculating all the cells in Exemptions worksheet with the
following code:
public void recalculate(HSSFFormulaEvaluator evaluator, int sheetNum)
throws IOException
{
// Recalculate all cells
evaluator = setupEnvironment();
HSSFSheet sh = wbA.getSheetAt(sheetNum);
for(Row r : sh) {
for(Cell c : r) {
if (c != null && c.getCellType() ==
HSSFCell.CELL_TYPE_FORMULA) {
String formula = c.getCellFormula();
if (formula != null) {
evaluator.evaluateFormulaCell (c);
evaluator.clearAllCachedResultValues();
}
}
}
}
}
Then I am reevaluating the cell with VLOOKUP like that:
recalculate(evalA, 2);
evalA.evaluateFormulaCell(wbA.getSheetAt(2).getRow(35).getCell(18));
System.err.println("VLOOKUP
"+risksheet.getRow(35).getCell(18).getNumericCellValue());
where evalA is the HSSFFormulaEvaluator for my main workbook (A because
I have 4 files linked).
The printed value is always the same even if in Excel file if I do the
recalculation I receive different values.
Maybe you could give me a hint.
Thanks,
Adrian
Josh Micich schreef:
Evaluation of VLOOKUP and HLOOKUP are both supported, as is evaluation
across multiple workbooks, so you should expect any basic use case to
work. It's possible that you're doing something slightly beyond what
POI supports so far. Please provide more details to help diagnose
your problem.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
--- End Message ---
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
