VLOOKUP, HLOOKUP is not evaluating

[Adrian Butnaru]

Hi,
I am having problem evaluating cells containing VLOOKUP and HLOOKUP
functions referencing cells from other worksheets.
Is this possible? Can somebody guide me how could I achieve this?

Regards,
Adrian

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

[Josh Micich]

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@...

[Adrian Butnaru]

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@...

[Adrian Butnaru]

I am investigating my sheet and it looks that the first term of the VLOOKUP
VLOOKUP($P$37;Exemptions!$E$10:$I$31;2)

is

which is a formula like that:
!Sheet0.B26

I am evaluating this formula and it gives me

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)


even if Sheet0 is part of this workbook.

I have the impression that the evaluator is not evaluating from a
different worksheet. !Sheet0.B26 is a HLOOKUP in the same sheet.



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@...

[Josh Micich]

From your second posting:
> The printed value is always the same even if in Excel
> file if I do the recalculation I receive different values.

This sounds like a potential bug. Can you reduce your java code and
XLS file(s) to a simple example that still demonstrates the problem?

BTW
> evaluator.clearAllCachedResultValues();
This is a performance hit (but should not cause a bug).  You should
only need to call this when making major changes to the spreadsheet -
like shifting rows or adding/deleting sheets.  Typically you're
setting single input cells before evaluation and methods like
notifyUpdateCell(), notifySetFormula() are all you need to call.



Your most recent problem:
> java.lang.RuntimeException: Specified sheet from a different book

This means that you have passed a cell from one workbook to an
evaluator of a different workbook.  It has nothing to do with the
actual formula in any cell.  Here is some sample code which shows this
problem:

HSSFWorkbook wbA = ... ;
HSSFWorkbook wbB = ... ;
HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
HSSFFormulaEvaluator evaluatorB = new HSSFFormulaEvaluator(wbB);

// Hook up the workbook evaluators to enable evaluation of formulas across books
String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls", };
HSSFFormulaEvaluator[] evaluators = { evaluatorA, evaluatorB, };
HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);

HSSFCell cellA = wbA..getSheetAt(0).getRow(0).getCell(0);
evaluatorA.evaluate(cellA); // OK
evaluatorB.evaluate(cellA); // error: "Specified sheet from a different book"

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

[Adrian Butnaru]

I will try to reduce the java code and excel file todemonstrate the problem.
It is not very clear now for me what to use: evaluateFormulaCell or
notifyUpdateCell?

For example if CellA receives new input and CellB  = Cell A+CellC  ,
CellC = CellA + CellD and Cell E = CellA + CellB + CellC.
Which functions should I use to obtain the correct value of CellE?

About the error message I just can reconfirm that both evaluated cells
are in the same workbook and it should not give this error:

java.lang.RuntimeException: Specified sheet from a different book



Josh Micich schreef:

> >From your second posting:
>  
>> The printed value is always the same even if in Excel
>> file if I do the recalculation I receive different values.
>>    
>
> This sounds like a potential bug. Can you reduce your java code and
> XLS file(s) to a simple example that still demonstrates the problem?
>
> BTW
>  
>> evaluator.clearAllCachedResultValues();
>>    
> This is a performance hit (but should not cause a bug).  You should
> only need to call this when making major changes to the spreadsheet -
> like shifting rows or adding/deleting sheets.  Typically you're
> setting single input cells before evaluation and methods like
> notifyUpdateCell(), notifySetFormula() are all you need to call.
>
>
>
> Your most recent problem:
>  
>> java.lang.RuntimeException: Specified sheet from a different book
>>    
>
> This means that you have passed a cell from one workbook to an
> evaluator of a different workbook.  It has nothing to do with the
> actual formula in any cell.  Here is some sample code which shows this
> problem:
>
> HSSFWorkbook wbA = ... ;
> HSSFWorkbook wbB = ... ;
> HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
> HSSFFormulaEvaluator evaluatorB = new HSSFFormulaEvaluator(wbB);
>
> // Hook up the workbook evaluators to enable evaluation of formulas across books
> String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls", };
> HSSFFormulaEvaluator[] evaluators = { evaluatorA, evaluatorB, };
> HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);
>
> HSSFCell cellA = wbA..getSheetAt(0).getRow(0).getCell(0);
> evaluatorA.evaluate(cellA); // OK
> evaluatorB.evaluate(cellA); // error: "Specified sheet from a different book"
>
> ---------------------------------------------------------------------
> 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@...

[Josh Micich]

> It is not very clear now for me what to use: evaluateFormulaCell or
> notifyUpdateCell?
>
> For example if CellA receives new input and CellB  = Cell A+CellC  , CellC =
> CellA + CellD and Cell E = CellA + CellB + CellC.
> Which functions should I use to obtain the correct value of CellE?
The HSSFFormulaEvaluator.notify~() methods are only needed when
modifying spreadsheets *during* evaluation.  They are not needed if
all input values remain unchanged after calling any evaluate~()
method.

// one off evaluation:
cellA.setCellValue(5.0);  // CellA receives new input
CellValue result = evaluatorE.evaluate(cellE);

// for repeated re-calculations:
cellA.setCellValue(5.0);  // changing input value
evaluatorA.notifyUpdateCell(cellA);   // clear all cached result
values that depend on this
result = evaluatorE.evaluate(cellE);


> About the error message I just can reconfirm that both evaluated cells are
> in the same workbook and it should not give this error:
>
> java.lang.RuntimeException: Specified sheet from a different book
If you are sure of this (i.e. that the evaluated cell is in the same
workbook as that of the evaluator), then you probably have found a
bug. Please upload a simple example based on the following (fill in
the '...' bits:

HSSFWorkbook wbA = ...
int sheetIndex = ...
int rowIndex = ...
int columnIndex = ...

HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
// set up rest of multiple workbook environment
...

// evaluate cell
HSSFCell cellA =
wbA..getSheetAt(sheetIndex).getRow(rowIndex).getCell(columnIndex);
evaluatorA.evaluate(cellA); // unexpected crash "Specified sheet from
a different book"

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

[Adrian Butnaru]

Thanks for the explanation.
I will try to reproduce the problem and send a simple example.

Adrian

Josh Micich schreef:

>> It is not very clear now for me what to use: evaluateFormulaCell or
>> notifyUpdateCell?
>>
>> For example if CellA receives new input and CellB  = Cell A+CellC  , CellC =
>> CellA + CellD and Cell E = CellA + CellB + CellC.
>> Which functions should I use to obtain the correct value of CellE?
>>    
> The HSSFFormulaEvaluator.notify~() methods are only needed when
> modifying spreadsheets *during* evaluation.  They are not needed if
> all input values remain unchanged after calling any evaluate~()
> method.
>
> // one off evaluation:
> cellA.setCellValue(5.0);  // CellA receives new input
> CellValue result = evaluatorE.evaluate(cellE);
>
> // for repeated re-calculations:
> cellA.setCellValue(5.0);  // changing input value
> evaluatorA.notifyUpdateCell(cellA);   // clear all cached result
> values that depend on this
> result = evaluatorE.evaluate(cellE);
>
>
>  
>> About the error message I just can reconfirm that both evaluated cells are
>> in the same workbook and it should not give this error:
>>
>> java.lang.RuntimeException: Specified sheet from a different book
>>    
> If you are sure of this (i.e. that the evaluated cell is in the same
> workbook as that of the evaluator), then you probably have found a
> bug. Please upload a simple example based on the following (fill in
> the '...' bits:
>
> HSSFWorkbook wbA = ...
> int sheetIndex = ...
> int rowIndex = ...
> int columnIndex = ...
>
> HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
> // set up rest of multiple workbook environment
> ...
>
> // evaluate cell
> HSSFCell cellA =
> wbA..getSheetAt(sheetIndex).getRow(rowIndex).getCell(columnIndex);
> evaluatorA.evaluate(cellA); // unexpected crash "Specified sheet from
> a different book"
>
> ---------------------------------------------------------------------
> 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@...