Need push in the right direction to implement non-implemented formula

[Martin Peters <ma...@peters.tf>]

Hello all,

I've been thinking about implementing some formulas that I might need 
for a project, first of all "INTERCEPT", the problem is I don't really 
know where to start:

This page links to "Developing Formula Evaluation" and the javadocs.
http://poi.apache.org/spreadsheet/eval.html

Javadocs link is 404:
http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/package-summary.html

This developing-formula-page seems, for all I can deduce, to talk about 
some old implementation of POI.
http://poi.apache.org/spreadsheet/eval-devguide.html

I can't find any skeleton-class for the intercept formula like the page 
says should exist:
"The first thing to realise is that classes already exist, even for 
functions that are not yet implemented."

Also, the Sqrt example doesn't in any way correspond to the actual 
implementation in the latest svn-version of poi.

Any pointers would be appreciated

Cincerely,
Martin Peters

Re: Need push in the right direction to implement non-implemented formula

[Yegor Kozlov <ye...@dinom.ru>]

It looks like the Formula dev guide is not synced with current code in
svn. Thanks for pointing it out.

The purpose of the SQRT example is to demonstrate all steps to
implement a function. The real implementation of SQRT  is different,
it it scattered across multiple classes and harder to understand for a
beginner. The code on the web side puts all pieces together in a
readable and understandable form.

Steps to implement INTERCEPT:

SUMX2MY2

http://office.microsoft.com/en-us/excel-help/sumx2my2-function-HP010062468.aspx?CTT=5&origin=HA010277524

1. INTERCEPT takes exactly two arguments. So the way to go is to
create a sub-class of Fixed2ArgFunction. POI functions live in the
org.apache.poi.ss.formula.functions package.
2. Register INTERCEPT  in FunctionEval. All built-in Excel functions
are referenced by index and for INTERCEPT  the index is 311 (see
functionmetadata.txt). So the code to register INTERCEPT   is as
follows (line 202 and below in FunctionEval):

		retval[311] = new Intercept();

After this step POI  should recognize INTERCEPT  and you can set a
breakpoint and debug your code.

3. All the heavy-lifting happens in the evaluate function:

public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval arg0, ValueEval arg1){
   // your implementation goes here
}

where srcRowIndex and srcColumnIndex are coordinates of the  the cell
containing the formula under evaluation. arg0 and arg1 are x-values
and y-values.

POI already implements two functions that have semantic similar to
INTERCEPT: SUMX2MY2 and SUMX2PY2. They also take two arguments and
have the same validation rules:

- The arguments should be either numbers or names, arrays, or
references that contain numbers.
- If an array or reference argument contains text, logical values, or
empty cells, those values are ignored; however, cells with the value
zero are included.
- If array_x and array_y have a different number of values, SUMX2MY2
returns the #N/A error value.

See
http://office.microsoft.com/en-us/excel-help/sumx2my2-function-HP010062468.aspx?CTT=5&origin=HA010277524
http://office.microsoft.com/en-us/excel-help/intercept-function-HP010062512.aspx?CTT=5&origin=HA010277524

See how SUMX2MY2  and SUMX2PY2 are implemented and how they evaluate
input arguments.



Yegor

On Tue, Feb 7, 2012 at 9:26 PM, Martin Peters <ma...@peters.tf> wrote:
> Hello all,
>
> I've been thinking about implementing some formulas that I might need for a
> project, first of all "INTERCEPT", the problem is I don't really know where
> to start:
>
> This page links to "Developing Formula Evaluation" and the javadocs.
> http://poi.apache.org/spreadsheet/eval.html
>
> Javadocs link is 404:
> http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/package-summary.html
>
> This developing-formula-page seems, for all I can deduce, to talk about some
> old implementation of POI.
> http://poi.apache.org/spreadsheet/eval-devguide.html
>
> I can't find any skeleton-class for the intercept formula like the page says
> should exist:
> "The first thing to realise is that classes already exist, even for
> functions that are not yet implemented."
>
> Also, the Sqrt example doesn't in any way correspond to the actual
> implementation in the latest svn-version of poi.
>
> Any pointers would be appreciated
>
> Cincerely,
> Martin Peters

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org