On Mon, Apr 13, 2009 at 4:10 PM, Filip Defoort <[email protected]>
wrote:
>So... could I create a add-in function that is uses SUMIFS internally or
>would that just cause the same problem (not sure if the user defined
>functions require to be parsed) ?
This might work. The main problem POI has with parsing non-'built-in'
functions is creation of the DEFINEDNAME record. These are required for
'add-in' functions and local VB functions. As far as I understand, POI
*can* parse formulas which have calls to non-'built-in' functions, when the
DEFINEDNAME record already exists. If you save a copy of a spreadsheet with
an existing call to your wrapper 'add-in' func or or local VB func, POI
should be able to parse new formulas which use the same functions.
Note - this hack won't work when directly parsing SUMIFS, for 3 reasons:
1 - the required defined name name is different ("_xlfn.sumifs")
2 - the formula parser currently expects names to start with a letter
character
3 - POI gets some option flag bits wrong and also gets the operand token
classes wrong for some of the parameters
I'm not sure which part of reason (3) is important - I found those
differences using BiffViewer when Excel fixed a test file I generated from
POI.
>I was tempted to take a stab at implementing SUMIFS, but it seems to be
>quite a bit more complex than just copying Sumif.java and adjusting it...
Unfortunately, writing an implementation for Sumifs.java would be the
easiest of all the changes, but you probably don't even need if you are not
using POI to evaluate SUMIFS. If you *do* need POI evaluation, the biggest
effort is going to be in writing the general code to invoke this category of
function.
