You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Stefano Bianchi <st...@softeco.it> on 2011/11/09 15:29:17 UTC
POI-3.7 formula support (FREQUENCY)
Hi,
I am using POI-3.7-20101029.
I have a complex Excel xls file with nested formulas (inserted as matric formulas!) using the basic FREQUENCY function.
Example:
=SUM (
IF (
FREQUENCY (
IF (LEN(DECLARED_VIALS)>0,MATCH(DECLARED_VIALS,DECLARED_VIALS,0),""),IF(LEN(DECLARED_VIALS)>0,MATCH(DECLARED_VIALS,DECLARED_VIALS,0),""))>0,1))
I see (http://poi.apache.org/spreadsheet/formula.html) that there are:
References: single cell & area, 2D & 3D, relative & absolute
Literals: Number, text, boolean, error and array
Operators: arithmetic and logical, some region operators
>>> Built-in functions: over 350 recognised, 280 evaluatable <<<
Add-in functions: 3 from Analysis Toolpack
but could not find the list of built in functions.
As I get:
Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: FREQUENCY
at org.apache.poi.hssf.record.formula.functions.NotImplementedFunction.evaluate(NotImplementedFunction.java:42)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:456)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:279)
... 41 more
and I can not find any
org.apache.poi.hssf.record.formula.functions.Frequency
class in the src distribution, I suppose this is not supported yet.
Can anybody confirm?
Will it be added? If yes, any plan?
Is there anybody having an implementation to share?
Thanks in advance,
cheers
Stefano
Re: POI-3.7 formula support (FREQUENCY)
Posted by Stefano Bianchi <st...@softeco.it>.
Dear all,
I found information on the web
http://www.jarfinder.com/index.php/java/info/org.apache.poi.hssf.record.formula.functions.Frequency
that
org.apache.poi.hssf.record.formula.functions.Frequency
was actually included in previous releases, and in fact I could find the
related .class file in the 3.5 jar.
Any reason why it was not further included?
May I try to force using this class file in my code?
Any hint appreciated,
cheers
Stefano
----- Original Message -----
From: "Nick Burch" <ni...@alfresco.com>
To: "POI Users List" <us...@poi.apache.org>
Sent: Wednesday, November 09, 2011 9:02 PM
Subject: Re: POI-3.7 formula support (FREQUENCY)
> On Wed, 9 Nov 2011, Stefano Bianchi wrote:
>> I am using POI-3.7-20101029.
>
> You might want to try the most recent 3.8 beta, though I think it may not
> help for this case
>
>> but could not find the list of built in functions.
>
> You'll need to check the sourcecode for that. It'd probably be handy if we
> could have something that would export the list from code to the site, if
> anyone fancies working up a patch... :)
>
>> org.apache.poi.hssf.record.formula.functions.Frequency
>>
>> class in the src distribution, I suppose this is not supported yet.
>>
>> Can anybody confirm?
>> Will it be added? If yes, any plan?
>
> I'd suggest you make a start on adding it in. It's not actually all that
> hard to add new functions to POI. Yegor gave a talk last year that's a
> good introduction to this:
> http://people.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx
>
> If you get it working, please send in a patch!
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: POI-3.7 formula support (FREQUENCY)
Posted by Dave Fisher <da...@comcast.net>.
On Nov 9, 2011, at 12:02 PM, Nick Burch wrote:
> On Wed, 9 Nov 2011, Stefano Bianchi wrote:
>> I am using POI-3.7-20101029.
>
> You might want to try the most recent 3.8 beta, though I think it may not help for this case
>
>> but could not find the list of built in functions.
>
> You'll need to check the sourcecode for that. It'd probably be handy if we could have something that would export the list from code to the site, if anyone fancies working up a patch... :)
I am thinking about converting the site from Forrest to the Apache CMS - that would make it really easy to update such a page. But it is also quite a bit of work... ... ...
Regards,
Dave
>
>> org.apache.poi.hssf.record.formula.functions.Frequency
>>
>> class in the src distribution, I suppose this is not supported yet.
>>
>> Can anybody confirm?
>> Will it be added? If yes, any plan?
>
> I'd suggest you make a start on adding it in. It's not actually all that hard to add new functions to POI. Yegor gave a talk last year that's a good introduction to this:
> http://people.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx
>
> If you get it working, please send in a patch!
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: POI-3.7 formula support (FREQUENCY)
Posted by Nick Burch <ni...@alfresco.com>.
On Wed, 9 Nov 2011, Stefano Bianchi wrote:
> I am using POI-3.7-20101029.
You might want to try the most recent 3.8 beta, though I think it may not
help for this case
> but could not find the list of built in functions.
You'll need to check the sourcecode for that. It'd probably be handy if we
could have something that would export the list from code to the site, if
anyone fancies working up a patch... :)
> org.apache.poi.hssf.record.formula.functions.Frequency
>
> class in the src distribution, I suppose this is not supported yet.
>
> Can anybody confirm?
> Will it be added? If yes, any plan?
I'd suggest you make a start on adding it in. It's not actually all that
hard to add new functions to POI. Yegor gave a talk last year that's a
good introduction to this:
http://people.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx
If you get it working, please send in a patch!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org