Apache POI › POI - Dev

Formula Evaluation

Classic

List

Threaded

5 messages Options

Amol Deshmukh

Formula Evaluation

I wanted to know if any work has been done for providing formula evaluation
functionality in POI. (I realise that POI is basically a file-format
translator, but such a utility could be useful when reading from files.)

If there has been no work done so far wrt formula evaluation and it is
deemed a useful feature, I would like to contribute my solution for this. I
have a partially implemented solution (based on FormulaParser and *Ptg
classes) that works for most common formulas with limited function support
(although thats only because I havent had the time to implement all std
excel functions, not a limitation of the approach per se). What is the best
way to submit this code for evaluation (no pun intended :)?

Regards,
~ amol

Notes:
Brief description of approach:
1. added function: "abstract Ptg evaluate(Ptg[] operands)" to OperationPtg
and implemented it in every concrete operation class.
2. Used FormulaParser to get RPN tokens from FormulaString and using simple
stack operations called evaluate on an operation token.
3. Functions are implemented as individual classes each having a "Ptg
evaluate(Ptg[] operands)" method.
4. All above operations are driven by FormulaEvaluator which also performs
evaluation of AreaPtg and ReferencePtg before invoking operations or
functions.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/

Avik Sengupta

Re: Formula Evaluation

tho I'm personally happy to see POI just as a file format reader... well, as
you'd have noticed, its often requested. So yeah, I'd be interested in seeing
this.

My concerns would be first that this have adequate documentation and
unit tests.
I think that is paramount in functionality of this nature (and shouldnt be
difficult to test, either)

Also, I wonder what kind of API would be appropriate for this functionality.
One, since it is impossible to implement ALL excel functions immediately, the
code should degrade gracefully, while at the same time provide enuf
feedback to
the called. Also, I would therefore like that adding new implementations of
functions should be easy.

I would also prefer if the math implementation could be pluggable.. if someone
were to want to integrate with pre-built math libraries in the future.

But in any case, lets see what you have. I am happy to have this added so long
as there are sufficient tests and docs.

Quoting Amol Deshmukh <[hidden email]>:

> I wanted to know if any work has been done for providing formula evaluation
> functionality in POI. (I realise that POI is basically a file-format
> translator, but such a utility could be useful when reading from files.)
>
> If there has been no work done so far wrt formula evaluation and it is
> deemed a useful feature, I would like to contribute my solution for this. I
> have a partially implemented solution (based on FormulaParser and *Ptg
> classes) that works for most common formulas with limited function support
> (although thats only because I havent had the time to implement all std
> excel functions, not a limitation of the approach per se). What is the best
> way to submit this code for evaluation (no pun intended :)?
>
>
> Regards,
> ~ amol
>
> Notes:
> Brief description of approach:
> 1. added function: "abstract Ptg evaluate(Ptg[] operands)" to OperationPtg
> and implemented it in every concrete operation class.
> 2. Used FormulaParser to get RPN tokens from FormulaString and using simple
> stack operations called evaluate on an operation token.
> 3. Functions are implemented as individual classes each having a "Ptg
> evaluate(Ptg[] operands)" method.
> 4. All above operations are driven by FormulaEvaluator which also performs
> evaluation of AreaPtg and ReferencePtg before invoking operations or
> functions.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/

andy-2

Re: Formula Evaluation

First of all, Welcome Amol! We look forward to your contributions. I
don't want to seem that we aren't or seem unfriendly -- but:

So I'm -1 to putting it in the main branch of the
src/org/apache/poi/hssf package unless its a full implementation but
I've no particular objection to putting it in the scratchpad. I want to
start seeing that as a bit more of a default case for new features
that aren't 100%.

I'm +1 to adding plugs and hooks to hook in evaluation engines.

The HWPF experience has lead me to want to raise the bar for scratchpad
contributions as well. Contrib/scratchpad should be "non-core" and
"partially-implemented" stuff respectively. However, implemented
features must have Javadoc, testcases and reasonable documentation. It
has become clear to me that stuff will never stabilize and leave the
scratchpad unless these standards are present and enforced.

so if we can integrate this code under those conditions, I think it will
make a fine addition.

-Andy

[hidden email] wrote:

> tho I'm personally happy to see POI just as a file format reader...
> well, as
> you'd have noticed, its often requested. So yeah, I'd be interested in
> seeing
> this.
>
> My concerns would be first that this have adequate documentation and
> unit tests.
> I think that is paramount in functionality of this nature (and shouldnt be
> difficult to test, either)
>
> Also, I wonder what kind of API would be appropriate for this
> functionality.
> One, since it is impossible to implement ALL excel functions
> immediately, the
> code should degrade gracefully, while at the same time provide enuf
> feedback to
> the called. Also, I would therefore like that adding new implementations of
> functions should be easy.
>
> I would also prefer if the math implementation could be pluggable.. if
> someone
> were to want to integrate with pre-built math libraries in the future.
>
> But in any case, lets see what you have. I am happy to have this added
> so long
> as there are sufficient tests and docs.
>
>
> Quoting Amol Deshmukh <[hidden email]>:
>
>> I wanted to know if any work has been done for providing formula
>> evaluation
>> functionality in POI. (I realise that POI is basically a file-format
>> translator, but such a utility could be useful when reading from files.)
>>
>> If there has been no work done so far wrt formula evaluation and it is
>> deemed a useful feature, I would like to contribute my solution for
>> this. I
>> have a partially implemented solution (based on FormulaParser and *Ptg
>> classes) that works for most common formulas with limited function
>> support
>> (although thats only because I havent had the time to implement all std
>> excel functions, not a limitation of the approach per se). What is the
>> best
>> way to submit this code for evaluation (no pun intended :)?
>>
>>
>> Regards,
>> ~ amol
>>
>> Notes:
>> Brief description of approach:
>> 1. added function: "abstract Ptg evaluate(Ptg[] operands)" to
>> OperationPtg
>> and implemented it in every concrete operation class.
>> 2. Used FormulaParser to get RPN tokens from FormulaString and using
>> simple
>> stack operations called evaluate on an operation token.
>> 3. Functions are implemented as individual classes each having a "Ptg
>> evaluate(Ptg[] operands)" method.
>> 4. All above operations are driven by FormulaEvaluator which also
>> performs
>> evaluation of AreaPtg and ReferencePtg before invoking operations or
>> functions.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>> The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
>>
>>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
> .
>

Amol Deshmukh

RE: Formula Evaluation

In reply to this post by Amol Deshmukh

Andy,

Thanks for your response.
I understand your concern about putting an incomplete
impl in the main branch, and the code I have written
can very well go in the scratchpad till it is mostly
finished.

I have now refactored the design, so that there is
absolutely no dependence of main-branch classes on
the FormulaEvaluator - which meant that I had to
almost redo the Ptg classes (which are no longer
called *Ptg ofcourse :) But now I can do without
any additional hooks into the existing classes
- I think.

(Currently I have impls of ~30functions, but there
are ~400 functions in all, so that will be a while
I guess :)

Also, every function implementation is a separate
class by itself, so it should be easy for anyone to
contribute function implementations later on.

Once I'm done with a significant number of functions,
should I just post the code as a Patch?

Again, thanks for your input and I hope to have
a useful contribution for HSSF.

Regards,
~ amol

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> Sent: Saturday, May 07, 2005 2:13 PM
> To: [hidden email]
> Subject: Re: Formula Evaluation
>
>
> First of all, Welcome Amol! We look forward to your
> contributions. I
> don't want to seem that we aren't or seem unfriendly -- but:
>
> So I'm -1 to putting it in the main branch of the
> src/org/apache/poi/hssf package unless its a full implementation but
> I've no particular objection to putting it in the scratchpad.
> I want to
> start seeing that as a bit more of a default case for new features
> that aren't 100%.
>
> I'm +1 to adding plugs and hooks to hook in evaluation engines.
>
> The HWPF experience has lead me to want to raise the bar for
> scratchpad
> contributions as well. Contrib/scratchpad should be "non-core" and
> "partially-implemented" stuff respectively. However, implemented
> features must have Javadoc, testcases and reasonable
> documentation. It
> has become clear to me that stuff will never stabilize and leave the
> scratchpad unless these standards are present and enforced.
>
> so if we can integrate this code under those conditions, I
> think it will
> make a fine addition.
>
> -Andy
>
> [hidden email] wrote:
> > tho I'm personally happy to see POI just as a file format reader...
> > well, as
> > you'd have noticed, its often requested. So yeah, I'd be
> interested in
> > seeing
> > this.
> >
> > My concerns would be first that this have adequate
> documentation and
> > unit tests.
> > I think that is paramount in functionality of this nature
> (and shouldnt be
> > difficult to test, either)
> >
> > Also, I wonder what kind of API would be appropriate for this
> > functionality.
> > One, since it is impossible to implement ALL excel functions
> > immediately, the
> > code should degrade gracefully, while at the same time provide enuf
> > feedback to
> > the called. Also, I would therefore like that adding new
> implementations of
> > functions should be easy.
> >
> > I would also prefer if the math implementation could be
> pluggable.. if
> > someone
> > were to want to integrate with pre-built math libraries in
> the future.
> >
> > But in any case, lets see what you have. I am happy to have
> this added
> > so long
> > as there are sufficient tests and docs.
> >
> >
> > Quoting Amol Deshmukh <[hidden email]>:
> >
> >> I wanted to know if any work has been done for providing formula
> >> evaluation
> >> functionality in POI. (I realise that POI is basically a
> file-format
> >> translator, but such a utility could be useful when
> reading from files.)
> >>
> >> If there has been no work done so far wrt formula
> evaluation and it is
> >> deemed a useful feature, I would like to contribute my
> solution for
> >> this. I
> >> have a partially implemented solution (based on
> FormulaParser and *Ptg
> >> classes) that works for most common formulas with limited function
> >> support
> >> (although thats only because I havent had the time to
> implement all std
> >> excel functions, not a limitation of the approach per se).
> What is the
> >> best
> >> way to submit this code for evaluation (no pun intended :)?
> >>
> >>
> >> Regards,
> >> ~ amol
> >>
> >> Notes:
> >> Brief description of approach:
> >> 1. added function: "abstract Ptg evaluate(Ptg[] operands)" to
> >> OperationPtg
> >> and implemented it in every concrete operation class.
> >> 2. Used FormulaParser to get RPN tokens from FormulaString
> and using
> >> simple
> >> stack operations called evaluate on an operation token.
> >> 3. Functions are implemented as individual classes each
> having a "Ptg
> >> evaluate(Ptg[] operands)" method.
> >> 4. All above operations are driven by FormulaEvaluator which also
> >> performs
> >> evaluation of AreaPtg and ReferencePtg before invoking
> operations or
> >> functions.
> >>
> >>
> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: [hidden email]
> >> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> >> The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
> >>
> >>
> >
> >
> >
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [hidden email]
> > Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> > The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
> > .
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
>

andy-2

Re: Formula Evaluation

Amol Deshmukh wrote:
> Andy,
>
> Thanks for your response.
> I understand your concern about putting an incomplete
> impl in the main branch, and the code I have written
> can very well go in the scratchpad till it is mostly
> finished.
>

Excellent.

> I have now refactored the design, so that there is
> absolutely no dependence of main-branch classes on
> the FormulaEvaluator - which meant that I had to
> almost redo the Ptg classes (which are no longer
> called *Ptg ofcourse :) But now I can do without
> any additional hooks into the existing classes
> - I think.

Whoa, there was no need to do that.. Stuff in contrib/scratchpad can
depend on stuff in the core just not the other directions.

>
> (Currently I have impls of ~30functions, but there
> are ~400 functions in all, so that will be a while
> I guess :)
>
> Also, every function implementation is a separate
> class by itself, so it should be easy for anyone to
> contribute function implementations later on.
>
> Once I'm done with a significant number of functions,
> should I just post the code as a Patch?
>

Naw just send the patch now. Attach in bugzilla with [PATCH] prepended.

cd jakarta-poi
cvs diff -u > foo.patch
tar -czf formuloticpatch.tar.gz foo.patch src/scatchpad/x/y/z
src/scratchpad/x/y/zz

(where each src/scratchpad is a "NEW" file rather than an existing file)

> Again, thanks for your input and I hope to have
> a useful contribution for HSSF.

Thanks! Welcome amol.

>
> Regards,
> ~ amol
>
>
>
>>-----Original Message-----
>>From: [hidden email] [mailto:[hidden email]]
>>Sent: Saturday, May 07, 2005 2:13 PM
>>To: [hidden email]
>>Subject: Re: Formula Evaluation
>>
>>
>>First of all, Welcome Amol! We look forward to your
>>contributions. I
>>don't want to seem that we aren't or seem unfriendly -- but:
>>
>>So I'm -1 to putting it in the main branch of the
>>src/org/apache/poi/hssf package unless its a full implementation but
>>I've no particular objection to putting it in the scratchpad.
>> I want to
>> start seeing that as a bit more of a default case for new features
>>that aren't 100%.
>>
>>I'm +1 to adding plugs and hooks to hook in evaluation engines.
>>
>>The HWPF experience has lead me to want to raise the bar for
>>scratchpad
>>contributions as well. Contrib/scratchpad should be "non-core" and
>>"partially-implemented" stuff respectively. However, implemented
>>features must have Javadoc, testcases and reasonable
>>documentation. It
>>has become clear to me that stuff will never stabilize and leave the
>>scratchpad unless these standards are present and enforced.
>>
>>so if we can integrate this code under those conditions, I
>>think it will
>>make a fine addition.
>>
>>-Andy
>>
>>[hidden email] wrote:
>>
>>>tho I'm personally happy to see POI just as a file format reader...
>>>well, as
>>>you'd have noticed, its often requested. So yeah, I'd be
>>
>>interested in
>>
>>>seeing
>>>this.
>>>
>>>My concerns would be first that this have adequate
>>
>>documentation and
>>
>>>unit tests.
>>>I think that is paramount in functionality of this nature
>>
>>(and shouldnt be
>>
>>>difficult to test, either)
>>>
>>>Also, I wonder what kind of API would be appropriate for this
>>>functionality.
>>>One, since it is impossible to implement ALL excel functions
>>>immediately, the
>>>code should degrade gracefully, while at the same time provide enuf
>>>feedback to
>>>the called. Also, I would therefore like that adding new
>>
>>implementations of
>>
>>>functions should be easy.
>>>
>>>I would also prefer if the math implementation could be
>>
>>pluggable.. if
>>
>>>someone
>>>were to want to integrate with pre-built math libraries in
>>
>>the future.
>>
>>>But in any case, lets see what you have. I am happy to have
>>
>>this added
>>
>>>so long
>>>as there are sufficient tests and docs.
>>>
>>>
>>>Quoting Amol Deshmukh <[hidden email]>:
>>>
>>>
>>>>I wanted to know if any work has been done for providing formula
>>>>evaluation
>>>>functionality in POI. (I realise that POI is basically a
>>
>>file-format
>>
>>>>translator, but such a utility could be useful when
>>
>>reading from files.)
>>
>>>>If there has been no work done so far wrt formula
>>
>>evaluation and it is
>>
>>>>deemed a useful feature, I would like to contribute my
>>
>>solution for
>>
>>>>this. I
>>>>have a partially implemented solution (based on
>>
>>FormulaParser and *Ptg
>>
>>>>classes) that works for most common formulas with limited function
>>>>support
>>>>(although thats only because I havent had the time to
>>
>>implement all std
>>
>>>>excel functions, not a limitation of the approach per se).
>>
>>What is the
>>
>>>>best
>>>>way to submit this code for evaluation (no pun intended :)?
>>>>
>>>>
>>>>Regards,
>>>>~ amol
>>>>
>>>>Notes:
>>>>Brief description of approach:
>>>>1. added function: "abstract Ptg evaluate(Ptg[] operands)" to
>>>>OperationPtg
>>>>and implemented it in every concrete operation class.
>>>>2. Used FormulaParser to get RPN tokens from FormulaString
>>
>>and using
>>
>>>>simple
>>>>stack operations called evaluate on an operation token.
>>>>3. Functions are implemented as individual classes each
>>
>>having a "Ptg
>>
>>>>evaluate(Ptg[] operands)" method.
>>>>4. All above operations are driven by FormulaEvaluator which also
>>>>performs
>>>>evaluation of AreaPtg and ReferencePtg before invoking
>>
>>operations or
>>
>>>>functions.
>>>>
>>>>
>>
>>---------------------------------------------------------------------
>>
>>>>To unsubscribe, e-mail: [hidden email]
>>>>Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>>>>The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
>>>>
>>>>
>>>
>>>
>>>
>>>
>>---------------------------------------------------------------------
>>
>>>To unsubscribe, e-mail: [hidden email]
>>>Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>>>The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
>>>.
>>>
>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: [hidden email]
>>Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>>The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
> .
>