Re: Formula Evaluation using POI

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

Hi Alan,

I've never used this particular class, but I Googled it and found the 
following page:
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.html

That page lists an "evaluate" method, but it doesn't have the same 
signature as the one you have.  It uses a superclass as the parameter, 
so that it can be called with an XSSF or HSSF cell as a parameter.

To do the same thing in RPG, you'd need to do this:

d HSSFFormulaEvaluator_Evaluate...
d                 pr              O   class(*JAVA
d                                     :'org.apache.poi.ss.-
d                                     usermodel.CellValue')
d                                     ExtProc(*JAVA                  :
d                                     'org.apache.poi.hssf.usermodel.-
d                                     HSSFFormulaEvaluator'           :
d                                     'evaluate'                      )
d   PR_HSSFCell                   O   CLASS(*JAVA
d                                     :'org.apache.poi.ss.-
d                                     usermodel.Cell')
d                                     Const

Since HSSFCell is a subclass of Cell, you can still pass HSSFCell as a 
parameter, as you were before.  Likewise, HSSFCellValue is a subclass of 
CellValue, so you can still assign the return value to one.

Hope that helps...

Alan Campin wrote:
> I am attempting to extend Scott's HSSF service program to include
> support for formula evaluation.
>
> I have defined an object call HSSFFormulaEvaluator
>
> d HSSFFormulaEvaluator...
> d                 s               o   Class(*JAVA:
> d                                      'org.apache.poi.hssf.usermodel.-
> d                                      HSSFFormulaEvaluator')
>
> I have defined a new for creating a formula evaluator.
>
>  *-----------------------------------------------------------------
>  *  new_HSSFFormulaEvaluator
>  *      Create a new HSSF (Excel) FormulaEvaluator
>  *
>  *   Returns the new HSSFWorkbook object
>  *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> d new_HSSFFormulaEvaluator...
> d                 PR                  Like(HSSFFormulaEvaluator)
> d                                       ExtProc(*JAVA                   :
> d                                       'org.apache.poi.hssf.usermodel.-
> d                                       HSSFFormulaEvaluator'           :
> d                                      *CONSTRUCTOR)
> d   PR_HSSFWorkbook...
> d                                      Like(HSSFWorkbook)
> d                                      Const
>
> When I run this piece of code I get back a valid object.
>
> // Create a formula evaluator to evaulate a cell.
> FormulaEvaluator = new_HSSFFormulaEvaluator(Book);
>
> Now I need to evaluate the cell. Here is my definition for the evaluate.
>
>  *-----------------------------------------------------------------
>  *  HSSFFormulaEvalulator_Evalulate
>  *    Function evalulates a cell. If cell contains a formula, formula
>  *      is evaluated and value record.
>  *    peCell = Take a Cell object as input.
>  *
>  *  returns CellValue object.
>  *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> d HSSFFormulaEvaluator_Evaluate...
> d                 pr                  Like(HSSFCellValue)
> d                                      ExtProc(*JAVA                   :
> d                                      'org.apache.poi.hssf.usermodel.-
> d                                      HSSFFormulaEvaluator'           :
> d                                      'evaluate'                      )
> d   PR_HSSFCell...
> d                                     Like(HSSFCell)
> d                                     Const
>
> But when I run the evalulate I get a Java exception saying there is no
> such method.
>
>     CellValue = HSSFFormulaEvaluator_Evaluate(InFormulaEvaluator:
>
> Cell              );
>
> Cause . . . . . :   RPG procedure GETCELL in program ALANC/T2HSSF received
>    Java exception "java.lang.NoSuchMethodError:
>    org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.evaluate(Lorg/apache"
>    when calling method "evaluate" with signature
>    "(Lorg.apache.poi.hssf.usermodel.HSSFCell;)Lorg.apache.poi.ss.usermodel.Cell
>    Value;" in class "org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator".
>
> What am I doing wrong? I double checked the documenation.
>
> Thanks for any help.