You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Tom Chiverton <to...@gmail.com> on 2015/11/24 12:43:19 UTC
Advice on tracking down an error thrown by evaluateAllFormulaCells()
I am trying to evaluate all the formula's in an Excel file. There are about
a dozen sheets, with several tens of formula on each, all driven by a few
input fields on the first sheet.
This all works fine in Excel 365 itself.
However, when I try and run it via the latest POI,
evaluateAllFormulaCells() is throwing "Unexpected ptg class
(org.apache.poi.ss.formula.ptg.ArrayPtg)".
What's the best way to track this down ?
Is there a way to have POI log what it's doing, maybe find the specific
cell that is causing issues ? I tried setDebugEvaluationOutputForNextEval()
but this didn't seem to output anything ?
--
Tom
Re: Advice on tracking down an error thrown by evaluateAllFormulaCells()
Posted by Yegor Kozlov <ye...@dinom.ru>.
The weird syntax like {1,0} indicate it is an array formula . Unfortunately
POI does not support it and I don't know an easy workaround. The formula
API is pluggable, but the formula parser API isn't, this is where the PTG
exception is coming from: PTG stands for parsed token and POI cannot parse
the funny syntax in curly braces and translate it into evaluation tree.
Teaching the formula parser to respect curly braces is not a big deal. A
much harder task is to support evaluation of array formulas, i.e. to
correctly interpert them.
Have a look at Bugzilla 46989: support array formulas. The patch provides
initial support for array formulas but there is a long way to apply it in
trunk. I did worked on it but gave up because of lack of time.
Yegor
24 Ноя 2015 г. 18:51 пользователь "Tom Chiverton" <to...@gmail.com>
написал:
> OK, so I made a dummy implementation of TREND, I think, and cells that use
> either the 2 or 3 argument version now evaluate to 0 rather than throwing
> the unsupported exception.
>
> But cells that use the weird (to my eyes) IF({1,0}, construct still throw
> "Unexpected ptg class".
> So I am wondering if there is something up elsewhere in POI that is failing
> to parse the expression.
>
> Here is my dummy TREND() and the test spread sheet:
> https://www.dropbox.com/sh/jb84qvpl4cknod5/AAA9uZs6NnFpbAu3KTck6xOAa?dl=0
>
> Could someone who knows the guts of the formula evaluator take a look ?
>
> Full stack from error
>
> Unexpected ptg class (org.apache.poi.ss.formula.ptg.ArrayPtg) at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:663):663
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:505):505
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:263):263
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:205):205
> at
>
> org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:374):374
> at
>
> org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:202):202
>
>
> On 24 November 2015 at 14:24, Tom Chiverton <to...@gmail.com>
> wrote:
>
> >
> > On 24 November 2015 at 14:09, Nick Burch <ap...@gagravarr.org> wrote:
> >
> >>
> >>
> http://people.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx
> >> for information on how evaluation works, functions, ptgs etc, if it's
> new
> >> to you
> >
> >
> > It is new, but my Java skills are hopefully OK :-)
> >
> > Is there a full working example of loading a new function at runtime, as
> > https://poi.apache.org/spreadsheet/eval-devguide.html
> > indicates I should be able to do ?
> > The syntax there is a little different from the one on page 18 of the
> > power point ?
> >
> >
> > --
> > Tom
> >
>
>
>
> --
> Tom
>
Re: Advice on tracking down an error thrown by evaluateAllFormulaCells()
Posted by Tom Chiverton <to...@gmail.com>.
OK, so I made a dummy implementation of TREND, I think, and cells that use
either the 2 or 3 argument version now evaluate to 0 rather than throwing
the unsupported exception.
But cells that use the weird (to my eyes) IF({1,0}, construct still throw
"Unexpected ptg class".
So I am wondering if there is something up elsewhere in POI that is failing
to parse the expression.
Here is my dummy TREND() and the test spread sheet:
https://www.dropbox.com/sh/jb84qvpl4cknod5/AAA9uZs6NnFpbAu3KTck6xOAa?dl=0
Could someone who knows the guts of the formula evaluator take a look ?
Full stack from error
Unexpected ptg class (org.apache.poi.ss.formula.ptg.ArrayPtg) at
org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:663):663
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:505):505
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:263):263
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:205):205
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:374):374
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:202):202
On 24 November 2015 at 14:24, Tom Chiverton <to...@gmail.com> wrote:
>
> On 24 November 2015 at 14:09, Nick Burch <ap...@gagravarr.org> wrote:
>
>>
>> http://people.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx
>> for information on how evaluation works, functions, ptgs etc, if it's new
>> to you
>
>
> It is new, but my Java skills are hopefully OK :-)
>
> Is there a full working example of loading a new function at runtime, as
> https://poi.apache.org/spreadsheet/eval-devguide.html
> indicates I should be able to do ?
> The syntax there is a little different from the one on page 18 of the
> power point ?
>
>
> --
> Tom
>
--
Tom
Re: Advice on tracking down an error thrown by evaluateAllFormulaCells()
Posted by Tom Chiverton <to...@gmail.com>.
On 24 November 2015 at 14:09, Nick Burch <ap...@gagravarr.org> wrote:
>
> http://people.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx
> for information on how evaluation works, functions, ptgs etc, if it's new
> to you
It is new, but my Java skills are hopefully OK :-)
Is there a full working example of loading a new function at runtime, as
https://poi.apache.org/spreadsheet/eval-devguide.html
indicates I should be able to do ?
The syntax there is a little different from the one on page 18 of the power
point ?
--
Tom
Re: Advice on tracking down an error thrown by
evaluateAllFormulaCells()
Posted by Nick Burch <ap...@gagravarr.org>.
On Tue, 24 Nov 2015, Tom Chiverton wrote:
> So, TREND() isn't implemented. Why don't I get a NotImplementedException
> then ?
>
> I'll see if I can knock up a quick implementation to contribute.
POI only has some support for array functions, so I wonder if it's
tripping up on that first?
A trend function would be wonderful if you could do one though! See
http://people.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx
for information on how evaluation works, functions, ptgs etc, if it's new
to you
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Advice on tracking down an error thrown by evaluateAllFormulaCells()
Posted by Javen O'Neal <ja...@gmail.com>.
On Nov 24, 2015 5:57 AM, "Tom Chiverton" <to...@gmail.com> wrote:
> So, TREND() isn't implemented. Why don't I get a NotImplementedException
> then ?
If it's silently failing rather than raising an exception
(UnsupportedOperationException("Not Implemented")), then this is something
else that should be fixed, regardless of whether TREND gets implemented.
Re: Advice on tracking down an error thrown by evaluateAllFormulaCells()
Posted by Tom Chiverton <to...@gmail.com>.
So, TREND() isn't implemented. Why don't I get a NotImplementedException
then ?
I'll see if I can knock up a quick implementation to contribute.
On 24 November 2015 at 13:43, Tom Chiverton <to...@gmail.com> wrote:
> It looks like TREND() is causing the error.
>
> The two lookups inside it run find by themselves through POI, so the
> SMALL/LARGE and COUNTIF must all be working too.
>
> I admit to not having seen this syntax of IF({1,0},... before. Is TREND
> not implemented, or could I try a different syntax for the look up ?
>
> Tom
>
> On 24 November 2015 at 13:02, Tom Chiverton <to...@gmail.com>
> wrote:
>
>> Great plan, and I can dump out the values as I go too.
>>
>> I have narrowed (at least one of the errors) down to formula's of the
>> pattern below. I think it returns an exact match from the VLOOKUP if one
>> exists, otherwise it takes the immediate before and after rows and looks it
>> up via TREND.
>>
>> Is some of this maybe not implemented in POI yet ?
>>
>> IF( ISNUMBER(MATCH(isk,$A$4:$A$105,0)),
>> VLOOKUP(isk,$A$4:$B$105,B2,0),
>> TREND(
>> IF({1,0},
>>
>> VLOOKUP(SMALL($A$4:$A$105,COUNTIF($A$4:$A$105,"<"&isk)),$A$4:$B$105,B2,0),
>>
>> VLOOKUP(LARGE($A$4:$A$105,COUNTIF($A$4:$A$105,">"&isk)),$A$4:$B$105,B2,0)),
>> IF({1,0},
>> SMALL($A$4:$A$105,COUNTIF($A$4:$A$105,"<"&isk)),
>> LARGE($A$4:$A$105,COUNTIF($A$4:$A$105,">"&isk))
>> ),
>> isk
>> )
>> )
>>
>> On 24 November 2015 at 12:01, Nick Burch <ap...@gagravarr.org> wrote:
>>
>>> On Tue, 24 Nov 2015, Tom Chiverton wrote:
>>>
>>>> I am trying to evaluate all the formula's in an Excel file. There are
>>>> about
>>>> a dozen sheets, with several tens of formula on each, all driven by a
>>>> few
>>>> input fields on the first sheet.
>>>> This all works fine in Excel 365 itself.
>>>>
>>>> However, when I try and run it via the latest POI,
>>>> evaluateAllFormulaCells() is throwing "Unexpected ptg class
>>>> (org.apache.poi.ss.formula.ptg.ArrayPtg)".
>>>>
>>>> What's the best way to track this down ?
>>>>
>>>
>>> Do the same logic as that method does - loop over the sheets, then the
>>> rows, then the cells, and evaluate each cell one at a time. Use that to
>>> identify which cell, and hence which formula is the problem
>>>
>>> Nick
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>
>>
>> --
>> Tom
>>
>
>
>
> --
> Tom
>
--
Tom
Re: Advice on tracking down an error thrown by evaluateAllFormulaCells()
Posted by Tom Chiverton <to...@gmail.com>.
It looks like TREND() is causing the error.
The two lookups inside it run find by themselves through POI, so the
SMALL/LARGE and COUNTIF must all be working too.
I admit to not having seen this syntax of IF({1,0},... before. Is TREND not
implemented, or could I try a different syntax for the look up ?
Tom
On 24 November 2015 at 13:02, Tom Chiverton <to...@gmail.com> wrote:
> Great plan, and I can dump out the values as I go too.
>
> I have narrowed (at least one of the errors) down to formula's of the
> pattern below. I think it returns an exact match from the VLOOKUP if one
> exists, otherwise it takes the immediate before and after rows and looks it
> up via TREND.
>
> Is some of this maybe not implemented in POI yet ?
>
> IF( ISNUMBER(MATCH(isk,$A$4:$A$105,0)),
> VLOOKUP(isk,$A$4:$B$105,B2,0),
> TREND(
> IF({1,0},
>
> VLOOKUP(SMALL($A$4:$A$105,COUNTIF($A$4:$A$105,"<"&isk)),$A$4:$B$105,B2,0),
>
> VLOOKUP(LARGE($A$4:$A$105,COUNTIF($A$4:$A$105,">"&isk)),$A$4:$B$105,B2,0)),
> IF({1,0},
> SMALL($A$4:$A$105,COUNTIF($A$4:$A$105,"<"&isk)),
> LARGE($A$4:$A$105,COUNTIF($A$4:$A$105,">"&isk))
> ),
> isk
> )
> )
>
> On 24 November 2015 at 12:01, Nick Burch <ap...@gagravarr.org> wrote:
>
>> On Tue, 24 Nov 2015, Tom Chiverton wrote:
>>
>>> I am trying to evaluate all the formula's in an Excel file. There are
>>> about
>>> a dozen sheets, with several tens of formula on each, all driven by a few
>>> input fields on the first sheet.
>>> This all works fine in Excel 365 itself.
>>>
>>> However, when I try and run it via the latest POI,
>>> evaluateAllFormulaCells() is throwing "Unexpected ptg class
>>> (org.apache.poi.ss.formula.ptg.ArrayPtg)".
>>>
>>> What's the best way to track this down ?
>>>
>>
>> Do the same logic as that method does - loop over the sheets, then the
>> rows, then the cells, and evaluate each cell one at a time. Use that to
>> identify which cell, and hence which formula is the problem
>>
>> Nick
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
> --
> Tom
>
--
Tom
Re: Advice on tracking down an error thrown by evaluateAllFormulaCells()
Posted by Tom Chiverton <to...@gmail.com>.
Great plan, and I can dump out the values as I go too.
I have narrowed (at least one of the errors) down to formula's of the
pattern below. I think it returns an exact match from the VLOOKUP if one
exists, otherwise it takes the immediate before and after rows and looks it
up via TREND.
Is some of this maybe not implemented in POI yet ?
IF( ISNUMBER(MATCH(isk,$A$4:$A$105,0)),
VLOOKUP(isk,$A$4:$B$105,B2,0),
TREND(
IF({1,0},
VLOOKUP(SMALL($A$4:$A$105,COUNTIF($A$4:$A$105,"<"&isk)),$A$4:$B$105,B2,0),
VLOOKUP(LARGE($A$4:$A$105,COUNTIF($A$4:$A$105,">"&isk)),$A$4:$B$105,B2,0)),
IF({1,0},
SMALL($A$4:$A$105,COUNTIF($A$4:$A$105,"<"&isk)),
LARGE($A$4:$A$105,COUNTIF($A$4:$A$105,">"&isk))
),
isk
)
)
On 24 November 2015 at 12:01, Nick Burch <ap...@gagravarr.org> wrote:
> On Tue, 24 Nov 2015, Tom Chiverton wrote:
>
>> I am trying to evaluate all the formula's in an Excel file. There are
>> about
>> a dozen sheets, with several tens of formula on each, all driven by a few
>> input fields on the first sheet.
>> This all works fine in Excel 365 itself.
>>
>> However, when I try and run it via the latest POI,
>> evaluateAllFormulaCells() is throwing "Unexpected ptg class
>> (org.apache.poi.ss.formula.ptg.ArrayPtg)".
>>
>> What's the best way to track this down ?
>>
>
> Do the same logic as that method does - loop over the sheets, then the
> rows, then the cells, and evaluate each cell one at a time. Use that to
> identify which cell, and hence which formula is the problem
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Tom
Re: Advice on tracking down an error thrown by
evaluateAllFormulaCells()
Posted by Nick Burch <ap...@gagravarr.org>.
On Tue, 24 Nov 2015, Tom Chiverton wrote:
> I am trying to evaluate all the formula's in an Excel file. There are about
> a dozen sheets, with several tens of formula on each, all driven by a few
> input fields on the first sheet.
> This all works fine in Excel 365 itself.
>
> However, when I try and run it via the latest POI,
> evaluateAllFormulaCells() is throwing "Unexpected ptg class
> (org.apache.poi.ss.formula.ptg.ArrayPtg)".
>
> What's the best way to track this down ?
Do the same logic as that method does - loop over the sheets, then the
rows, then the cells, and evaluate each cell one at a time. Use that to
identify which cell, and hence which formula is the problem
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org