User Defined Function Documentation/Example, see Bugzilla 50587

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1060724 13f79535-47bb-0310-9956-ffa450edef68

3 files changed, 183 insertions, 0 deletions

diff --git a/src/examples/src/org/apache/poi/ss/examples/formula/CalculateMortgage.java b/src/examples/src/org/apache/poi/ss/examples/formula/CalculateMortgage.java
new file mode 100644
index 0000000000..4b9a325cdf
--- /dev/null
+++ b/src/examples/src/org/apache/poi/ss/examples/formula/CalculateMortgage.java
@@ -0,0 +1,93 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.examples.formula;
+
+import org.apache.poi.ss.formula.OperationEvaluationContext ;
+import org.apache.poi.ss.formula.eval.ErrorEval ;
+import org.apache.poi.ss.formula.eval.EvaluationException ;
+import org.apache.poi.ss.formula.eval.NumberEval ;
+import org.apache.poi.ss.formula.eval.OperandResolver ;
+import org.apache.poi.ss.formula.eval.ValueEval ;
+import org.apache.poi.ss.formula.functions.FreeRefFunction ;
+
+/**
+ * A simple user-defined function to calculate principal and interest.
+ * 
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class CalculateMortgage implements FreeRefFunction {
+
+    public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
+        
+        // verify that we have enough data
+        if (args.length != 3) {  
+            return ErrorEval.VALUE_INVALID;
+        }
+
+        // declare doubles for values
+        double principal, rate, years,  result;
+        try {
+            // extract values as ValueEval
+            ValueEval v1 = OperandResolver.getSingleValue( args[0], 
+                                                           ec.getRowIndex(), 
+                                                           ec.getColumnIndex() ) ;
+            ValueEval v2 = OperandResolver.getSingleValue( args[1], 
+                                                           ec.getRowIndex(), 
+                                                           ec.getColumnIndex() ) ;
+            ValueEval v3 = OperandResolver.getSingleValue( args[2], 
+                                                           ec.getRowIndex(), 
+                                                           ec.getColumnIndex() ) ;
+
+            // get data as doubles
+            principal  = OperandResolver.coerceValueToDouble( v1 ) ; 
+            rate  = OperandResolver.coerceValueToDouble( v2 ) ;
+            years = OperandResolver.coerceValueToDouble( v3 ) ;
+            
+            result = calculateMortgagePayment( principal, rate, years ) ;
+            System.out.println( "Result = " + result ) ;
+
+            checkValue(result);
+            
+        } catch (EvaluationException e) {
+            return e.getErrorEval();
+        }
+
+        return new NumberEval( result ) ;
+    }
+    
+    public double calculateMortgagePayment( double p, double r, double y ) {
+        double i = r / 12 ;
+        double n = y * 12 ;
+        
+        double principalAndInterest = 
+             p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1))  ;
+        
+        return principalAndInterest ;
+    }
+    /**
+     * Excel does not support infinities and NaNs, rather, it gives a #NUM! error in these cases
+     *
+     * @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt>
+     */
+     private void checkValue(double result) throws EvaluationException {
+         if (Double.isNaN(result) || Double.isInfinite(result)) {
+             throw new EvaluationException(ErrorEval.NUM_ERROR);
+         }
+     }    
+}
diff --git a/src/examples/src/org/apache/poi/ss/examples/formula/UserDefinedFunctionExample.java b/src/examples/src/org/apache/poi/ss/examples/formula/UserDefinedFunctionExample.java
new file mode 100644
index 0000000000..cd02b6433a
--- /dev/null
+++ b/src/examples/src/org/apache/poi/ss/examples/formula/UserDefinedFunctionExample.java
@@ -0,0 +1,90 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.examples.formula;
+
+import java.io.File ;
+import java.io.FileInputStream ;
+import java.io.FileNotFoundException ;
+import java.io.IOException ;
+
+import org.apache.poi.openxml4j.exceptions.InvalidFormatException ;
+import org.apache.poi.ss.formula.functions.FreeRefFunction ;
+import org.apache.poi.ss.formula.udf.AggregatingUDFFinder ;
+import org.apache.poi.ss.formula.udf.DefaultUDFFinder ;
+import org.apache.poi.ss.formula.udf.UDFFinder ;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellReference ;
+
+
+/**
+ * An example class of how to invoke a User Defined Function for a given
+ * XLS instance using POI's UDFFinder implementation.
+ * 
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ * 
+ */
+public class UserDefinedFunctionExample {
+
+    public static void main( String[] args ) {
+        
+        if(  args.length != 2 ) {
+            System.out.println( "usage: UserDefinedFunctionExample fileName cellId" ) ;
+            return;
+        }
+        
+        System.out.println( "fileName: " + args[0] ) ;
+        System.out.println( "cell: " + args[1] ) ;
+        
+        File workbookFile = new File( args[0] ) ;
+        
+        try {
+            FileInputStream fis = new FileInputStream(workbookFile);
+            Workbook workbook = WorkbookFactory.create(fis);
+            fis.close();
+
+            String[] functionNames = { "calculatePayment" } ;
+            FreeRefFunction[] functionImpls = { new CalculateMortgage() } ;
+            
+            UDFFinder udfToolpack = new DefaultUDFFinder( functionNames, functionImpls ) ;
+
+            // register the user-defined function in the workbook
+            workbook.addToolPack(udfToolpack);
+
+            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
+
+            CellReference cr = new CellReference( args[1] ) ;
+            String sheetName = cr.getSheetName() ;
+            Sheet sheet = workbook.getSheet( sheetName ) ;
+            int rowIdx = cr.getRow() ;
+            int colIdx = cr.getCol() ;
+            Row row = sheet.getRow( rowIdx ) ;
+            Cell cell = row.getCell( colIdx ) ;
+            
+            CellValue value = evaluator.evaluate( cell ) ;
+            
+            System.out.println("returns value: " +  value ) ;
+
+        } catch( FileNotFoundException e ) {
+            e.printStackTrace();
+        } catch( InvalidFormatException e ) {
+            e.printStackTrace();
+        } catch( IOException e ) {
+            e.printStackTrace();
+        }
+    }
+}
diff --git a/src/examples/src/org/apache/poi/ss/examples/formula/mortgage-calculation.xls b/src/examples/src/org/apache/poi/ss/examples/formula/mortgage-calculation.xls
new file mode 100644
index 0000000000..4e71ba8e65
--- /dev/null
+++ b/src/examples/src/org/apache/poi/ss/examples/formula/mortgage-calculation.xls