[Fwd: Re: VLOOKUP, HLOOKUP is not evaluating]

[Adrian Butnaru]

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


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: user-unsubscribe@...
> For additional commands, e-mail: user-help@...
>
>
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...