You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by abcdefghij <un...@hotmail.com> on 2010/06/04 14:51:25 UTC
Apache POI excel function VLOOKUP
Hi,
I'm using Apache POI 3.6 for reading excel files.
Now i came to a situation where i need to evaluate a formula VLOOKUP.
When i do this with the following code:
if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
internBereikId = evaluator.evaluateFormulaCell(cell);
}
I get the runtime exception: Complex name formulas not supported yet
Is it not possible what i'm doing?
The function in excel looks like: =VLOOKUP(A2;BereikArray;3;FALSE)
where BereikArray is a validation list.
kind regards
--
View this message in context: http://old.nabble.com/Apache-POI-excel-function-VLOOKUP-tp28779554p28779554.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI excel function VLOOKUP
Posted by abcdefghij <un...@hotmail.com>.
I have made an excel file with 2 sheets,
In the first sheet i have put 1 cell with a value of 3 (no formula).
In the second sheet i also made 1 cell with a formula that reference the
cell lin sheet 1.
Now it always evaluate to 0 in code by using the HSSFFormulaEvaluator...
Is this normal?
kind regards
--
View this message in context: http://old.nabble.com/Apache-POI-excel-function-VLOOKUP-tp28779554p28814271.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI excel function VLOOKUP
Posted by abcdefghij <un...@hotmail.com>.
Josh Micich wrote:
>
> The RuntimeException ('Complex name formulas not supported yet') that
> you received indicates that POI is not able to evaluate 'BereikArray'.
> POI currently only handles evaluation of very simple internal defined
> names (i.e. cell references and area references). There is an open
> feature request to add support for evaluating external defined names:
> https://issues.apache.org/bugzilla/show_bug.cgi?id=48996
> This hasn't been applied yet because I was considering working out a
> more complete solution (evaluating names which have complex formulas)
> in order to make the code less of a patchwork of special cases.
>
>
> The second error you describe involves POI functionality that is
> supposed to work (VLOOKUP, OFFSET and COUNTA are all supported). If
> you can cut down your example to a simple case that shows POI
> producing the wrong result, it would be very helpful. Please open a
> new bugzilla entry once you have collected these details.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
What's a bugzilla entry?
What do you need? an excell sheet and code example?
kind regard
--
View this message in context: http://old.nabble.com/Apache-POI-excel-function-VLOOKUP-tp28779554p28789108.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI excel function VLOOKUP
Posted by Josh Micich <jo...@gmail.com>.
The RuntimeException ('Complex name formulas not supported yet') that
you received indicates that POI is not able to evaluate 'BereikArray'.
POI currently only handles evaluation of very simple internal defined
names (i.e. cell references and area references). There is an open
feature request to add support for evaluating external defined names:
https://issues.apache.org/bugzilla/show_bug.cgi?id=48996
This hasn't been applied yet because I was considering working out a
more complete solution (evaluating names which have complex formulas)
in order to make the code less of a patchwork of special cases.
The second error you describe involves POI functionality that is
supposed to work (VLOOKUP, OFFSET and COUNTA are all supported). If
you can cut down your example to a simple case that shows POI
producing the wrong result, it would be very helpful. Please open a
new bugzilla entry once you have collected these details.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Apache POI excel function VLOOKUP
Posted by abcdefghij <un...@hotmail.com>.
when i replace the validation list, the following formula needs to be
evaluated:
=VLOOKUP(A2;OFFSET('04-BEREIK'!$A$2;0;0;COUNTA('04-BEREIK'!$A:$A)-1;3);3;FALSE)
But the evaluation always result to 0...
Can anyone help with this?
kind regards
--
View this message in context: http://old.nabble.com/Apache-POI-excel-function-VLOOKUP-tp28779554p28779684.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org