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 <apa...@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-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org
>
>
--
Tom
