References passed to the AreaReference must be contiguous

[Martin Studer <ma...@gmail.com>]

Hi,

I'm trying to create an AreaReference (org.apache.poi.ss.util.AreaReference)
using the following reference:
OFFSET($'SEA 05 Staff Data'.$A$2,,,COUNTIF($'SEA 05 Staff
Data'.$L$2:$L$100,">0"),14)

When doing so I receive the following exception:

Exception in thread "main" java.lang.IllegalArgumentException: References
passed to the AreaReference must be contiguous, use generateContiguous(ref)
if you have non-contiguous references

The result of the above formula is a contiguous AreaReference. Since an
AreaReference object does not reference a workbook, it has no means of
evaluating the above formula - which I guess is the ultimate reason for the
above exception.

How can I evaluate a formula into an AreaReference? FormulaEvaluators
(org.apache.poi.ss.usermodel.FormulaEvaluator) only seem to evaluate single
cells.

Thanks & Best regards,
Martin



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/References-passed-to-the-AreaReference-must-be-contiguous-tp5723848.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: References passed to the AreaReference must be contiguous

[Javen O'Neal <ja...@gmail.com>]

Try
https://poi.apache.org/apidocs/org/apache/poi/ss/formula/FormulaParser.html

That will give you a list of parse nodes (in RPN order, I believe. That
will need to be evaluated by some FormulaEvaluator to resolve the offset.

It's marked @Internal so it may be modified or removed without notice.

If you're up for a small challenge, you could try extending one of the
evaluators (BaseXSSFFormulaEvaluator?) to have a method that operates on a
formula, workbook, and the sheet index/name/object, row, and column where
the formula would reside.

If you come up with something useful to others, we gladly accept patches
with unit tests.
Hi,

I'm trying to create an AreaReference (org.apache.poi.ss.util.AreaReference)
using the following reference:
OFFSET($'SEA 05 Staff Data'.$A$2,,,COUNTIF($'SEA 05 Staff
Data'.$L$2:$L$100,">0"),14)

When doing so I receive the following exception:

Exception in thread "main" java.lang.IllegalArgumentException: References
passed to the AreaReference must be contiguous, use generateContiguous(ref)
if you have non-contiguous references

The result of the above formula is a contiguous AreaReference. Since an
AreaReference object does not reference a workbook, it has no means of
evaluating the above formula - which I guess is the ultimate reason for the
above exception.

How can I evaluate a formula into an AreaReference? FormulaEvaluators
(org.apache.poi.ss.usermodel.FormulaEvaluator) only seem to evaluate single
cells.

Thanks & Best regards,
Martin



--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/References-passed-to-the-AreaReference-must-be-contiguous-tp5723848.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