View | Details | Raw Unified | Return to bug 50587
Collapse All | Expand All

(-)src/documentation/content/xdocs/spreadsheet/user-defined-functions.xml (+421 lines)
Line 0 Link Here
1
<?xml version="1.0" encoding="UTF-8"?>
2
<!--
3
   ====================================================================
4
   Licensed to the Apache Software Foundation (ASF) under one or more
5
   contributor license agreements.  See the NOTICE file distributed with
6
   this work for additional information regarding copyright ownership.
7
   The ASF licenses this file to You under the Apache License, Version 2.0
8
   (the "License"); you may not use this file except in compliance with
9
   the License.  You may obtain a copy of the License at
10
11
       http://www.apache.org/licenses/LICENSE-2.0
12
13
   Unless required by applicable law or agreed to in writing, software
14
   distributed under the License is distributed on an "AS IS" BASIS,
15
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16
   See the License for the specific language governing permissions and
17
   limitations under the License.
18
   ====================================================================
19
-->
20
<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V1.1//EN" "../dtd/document-v11.dtd">
21
22
<document>
23
    <header>
24
        <title>User Defined Functions</title>
25
        <authors>
26
            <person email="jon@loquatic.com" name="Jon Svede" id="JDS"/>
27
            <person email="brian.bush@nrel.gov" name="Brian Bush" id="BWB"/>
28
        </authors>
29
    </header>
30
  <body>
31
    <section><title>How to Create and Use User Defined Functions</title>
32
33
     <section><title>Description</title>
34
            <p>This document describes the User Defined Functions within POI.
35
            User defined functions allow you to take code that is written in VBA
36
            and re-write in Java and use within POI. Consider the following example.</p>
37
     </section>
38
     <section><title>An Example</title>
39
        <p>Suppose you are given a spreadsheet that can calculate the principal and interest
40
        payments for a mortgage.  The user enters the principal loan amount, the interest rate
41
        and the term of the loan.  The Excel spreadsheet does the rest.</p>
42
        <p>
43
            <img src="../resources/images/simple-xls-with-function.jpg" alt="mortgage calculation spreadsheet"/>
44
        </p>
45
        <p>When you actually look at the workbook you discover that rather than having
46
        the formula in a cell it has been written as VBA function.  You review the 
47
        function and determine that it could be written in Java:</p>
48
        <p>
49
            <img src="../resources/images/calculatePayment.jpg" alt="VBA code"/>
50
        </p>
51
        <p>If we write a small program to try to evaluate this cell, we'll fail.  Consider this source code:</p>
52
        <source><![CDATA[
53
import java.io.File ;
54
import java.io.FileInputStream ;
55
import java.io.FileNotFoundException ;
56
import java.io.IOException ;
57
58
import org.apache.poi.openxml4j.exceptions.InvalidFormatException ;
59
import org.apache.poi.ss.formula.functions.FreeRefFunction ;
60
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder ;
61
import org.apache.poi.ss.formula.udf.DefaultUDFFinder ;
62
import org.apache.poi.ss.formula.udf.UDFFinder ;
63
import org.apache.poi.ss.usermodel.Cell ;
64
import org.apache.poi.ss.usermodel.CellValue ;
65
import org.apache.poi.ss.usermodel.Row ;
66
import org.apache.poi.ss.usermodel.Sheet ;
67
import org.apache.poi.ss.usermodel.Workbook ;
68
import org.apache.poi.ss.usermodel.WorkbookFactory ;
69
import org.apache.poi.ss.util.CellReference ;
70
71
public class Evaluator {
72
73
    
74
    
75
    public static void main( String[] args ) {
76
        
77
        System.out.println( "fileName: " + args[0] ) ;
78
        System.out.println( "cell: " + args[1] ) ;
79
        
80
        File workbookFile = new File( args[0] ) ;
81
        
82
        try {
83
            FileInputStream fis = new FileInputStream(workbookFile);
84
            Workbook workbook = WorkbookFactory.create(fis);
85
            
86
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
87
            
88
            CellReference cr = new CellReference( args[1] ) ;
89
            String sheetName = cr.getSheetName() ;
90
            Sheet sheet = workbook.getSheet( sheetName ) ;
91
            int rowIdx = cr.getRow() ;
92
            int colIdx = cr.getCol() ;
93
            Row row = sheet.getRow( rowIdx ) ;
94
            Cell cell = row.getCell( colIdx ) ;
95
            
96
            CellValue value = evaluator.evaluate( cell ) ;
97
            
98
            System.out.println("returns value: " +  value ) ;
99
                        
100
        } catch( FileNotFoundException e ) {
101
            e.printStackTrace();
102
        } catch( InvalidFormatException e ) {
103
            e.printStackTrace();
104
        } catch( IOException e ) {
105
            e.printStackTrace();
106
        }
107
    }
108
}
109
        
110
]]></source>
111
        <p>If you run this code, you're likely to get the following error:</p>
112
        
113
       <source><![CDATA[
114
Exception in thread "main" org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Sheet1!B4
115
    at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321)
116
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
117
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)
118
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320)
119
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:182)
120
    at poi.tests.Evaluator.main(Evaluator.java:61)
121
Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: calculatePayment
122
    at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:59)
123
    at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
124
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:456)
125
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:279)
126
    ... 4 more
127
        
128
]]></source>
129
        
130
        <p>How would we make it so POI can use this sheet?</p>
131
     </section>
132
     
133
     <section><title>Defining Your Function</title>
134
        <p>To 'convert' this code to Java and make it available to POI you to implement
135
        a FreeRefFunction instance.  FreeRefFunction is an interface in the org.apache.poi.ss.formula.functions 
136
        package.  This interface defines one method, evaluate(ValueEval[] args, OperationEvaluationContext ec),
137
        which is how you will receive the argument values from POI.</p>
138
        <p>The evaluate() method as defined above is where you will convert the ValueEval instances to the 
139
        proper number types.  The following code snippet shows you how to get your values:</p>
140
 
141
      <source><![CDATA[
142
public class CalculateMortgage implements FreeRefFunction {
143
144
public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
145
    if (args.length != 3) {  
146
        return ErrorEval.VALUE_INVALID;
147
    }
148
149
    double principal, rate, years,  result;
150
    try {
151
        ValueEval v1 = OperandResolver.getSingleValue( args[0], 
152
                                                       ec.getRowIndex(), 
153
                                                       ec.getColumnIndex() ) ;
154
        ValueEval v2 = OperandResolver.getSingleValue( args[1], 
155
                                                       ec.getRowIndex(), 
156
                                                       ec.getColumnIndex() ) ;
157
        ValueEval v3 = OperandResolver.getSingleValue( args[2], 
158
                                                       ec.getRowIndex(), 
159
                                                       ec.getColumnIndex() ) ;
160
161
        principal  = OperandResolver.coerceValueToDouble( v1 ) ; 
162
        rate  = OperandResolver.coerceValueToDouble( v2 ) ;
163
        years = OperandResolver.coerceValueToDouble( v3 ) ;
164
     ]]></source>
165
     
166
     <p>The first thing we do is check the number of arguments being passed since there is no sense
167
     in attempting to go further if you are missing critical information.</p>
168
     <p>Next we declare our variables, in our case we need variables for:</p>
169
     <ul>
170
        <li>principal - the amount of the loan</li>
171
        <li>rate - the interest rate as a decimal</li>
172
        <li>years - the length of the loan in years</li>
173
        <li>result - the result of the calculation</li>
174
     </ul>
175
     <p>Next, we use the OperandResolver to convert the ValueEval instances to doubles, though not directly.  
176
     First we start by getting discreet values.  Using the OperandResolver.getSingleValue() method
177
     we retrieve each of the values passed in by the cell in the spreadsheet.  Next, we use the
178
     OperandResolver again to convert the ValueEval instances to doubles, in this case.  This
179
     class has other methods of coercion for gettings Strings, ints and booleans.  Now that we've 
180
     got our primitive values we can move on to calculating the value.</p>
181
     <p>As shown previously, we have the VBA source.  We need to add code to our class to calculate 
182
     	the payment.  To do this you could simply add it to the method we've already created but I've
183
     	chosen to add it as its own method.  Add the following method: </p>
184
     	<source><![CDATA[
185
public double calculateMortgagePayment( double p, double r, double y ) {
186
187
    double i = r / 12 ;
188
    double n = y * 12 ;
189
    
190
    // P * ((i * (1 + i) ^ n) / ((1 + i) ^ n - 1))
191
    double principalAndInterest = 
192
         p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1))  ;
193
    
194
    System.out.println( "i = " + i + "\tn = " + n ) ;
195
    
196
    return principalAndInterest ;
197
}	
198
     	]]></source>
199
     	<p>The biggest change necessary is related to the exponents; Java doesn't have a notation for this
200
     		so we had to add calls to Math.pow().  Now we need to add this call to our previous method:</p>
201
     	<source><![CDATA[
202
     	 result = calculateMortgagePayment( principal, rate, years ) ;	
203
     		]]></source>
204
     	<p>Having done that, the last things we need to do are to check to make sure we didn't get a bad result and,
205
     		if not, we need to return the value. Add the following code to the class:</p>
206
     	<source><![CDATA[
207
public double calculateMortgagePayment( double p, double r, double y ) {
208
    double i = r / 12 ;
209
    double n = y * 12 ;
210
    
211
    //M = P [ i(1 + i)n ] / [ (1 + i)n - 1] 
212
    // P * ((i * (1 + i) ^ n) / ((1 + i) ^ n - 1))
213
    double principalAndInterest = 
214
         p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1))  ;
215
    
216
    System.out.println( "i = " + i + "\tn = " + n ) ;
217
    
218
    return principalAndInterest ;
219
}
220
     		]]></source>
221
     <p>Then add a line of code to our evaluate method to call this new static method, complete our try/catch and return the value:</p>
222
     	<source><![CDATA[
223
        checkValue(result);
224
        
225
    } catch (EvaluationException e) {
226
        e.printStackTrace() ;
227
        return e.getErrorEval();
228
    }
229
230
    return new NumberEval( result ) ;
231
     		]]></source>
232
     		
233
     		<p>So the whole class would be as follows:</p>
234
     		
235
     	<source><![CDATA[
236
import org.apache.poi.ss.formula.OperationEvaluationContext ;
237
import org.apache.poi.ss.formula.eval.ErrorEval ;
238
import org.apache.poi.ss.formula.eval.EvaluationException ;
239
import org.apache.poi.ss.formula.eval.NumberEval ;
240
import org.apache.poi.ss.formula.eval.OperandResolver ;
241
import org.apache.poi.ss.formula.eval.ValueEval ;
242
import org.apache.poi.ss.formula.functions.FreeRefFunction ;
243
244
/**
245
 * A simple function to calculate principal and interest.
246
 * 
247
 */
248
public class CalculateMortgage implements FreeRefFunction {
249
250
    public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
251
        if (args.length != 3) {  
252
            return ErrorEval.VALUE_INVALID;
253
        }
254
255
        double principal, rate, years,  result;
256
        try {
257
            ValueEval v1 = OperandResolver.getSingleValue( args[0], 
258
                                                           ec.getRowIndex(), 
259
                                                           ec.getColumnIndex() ) ;
260
            ValueEval v2 = OperandResolver.getSingleValue( args[1], 
261
                                                           ec.getRowIndex(), 
262
                                                           ec.getColumnIndex() ) ;
263
            ValueEval v3 = OperandResolver.getSingleValue( args[2], 
264
                                                           ec.getRowIndex(), 
265
                                                           ec.getColumnIndex() ) ;
266
267
            principal  = OperandResolver.coerceValueToDouble( v1 ) ; 
268
            rate  = OperandResolver.coerceValueToDouble( v2 ) ;
269
            years = OperandResolver.coerceValueToDouble( v3 ) ;
270
            
271
            result = calculateMortgagePayment( principal, rate, years ) ;
272
            System.out.println( "Result = " + result ) ;
273
            
274
            checkValue(result);
275
            
276
        } catch (EvaluationException e) {
277
            e.printStackTrace() ;
278
            return e.getErrorEval();
279
        }
280
281
        return new NumberEval( result ) ;
282
    }
283
    
284
    public double calculateMortgagePayment( double p, double r, double y ) {
285
        double i = r / 12 ;
286
        double n = y * 12 ;
287
        
288
        //M = P [ i(1 + i)n ] / [ (1 + i)n - 1] 
289
        double principalAndInterest = 
290
             p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1))  ;
291
        
292
        System.out.println( "i = " + i + "\tn = " + n ) ;
293
        return principalAndInterest ;
294
    }
295
    
296
    /**
297
     * Excel does not support infinities and NaNs, rather, it gives a #NUM! error in these cases
298
     * 
299
     * @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt>
300
     */
301
     static final void checkValue(double result) throws EvaluationException {
302
         if (Double.isNaN(result) || Double.isInfinite(result)) {
303
             throw new EvaluationException(ErrorEval.NUM_ERROR);
304
         }
305
     }
306
307
}
308
	
309
     		]]></source>
310
     		
311
     		<p>Great!  Now we need to go back to our original program that failed to evaluate our cell and add code that will allow it run our new Java code.</p>
312
313
     </section>
314
     
315
     <section><title>Registering Your Function</title>
316
     		<p>Now we need to register our function in the Workbook, so that the Formula Evaluator can resolve the name "calculatePayment" 
317
and map it to the actual implementation (CalculateMortgage). This is done using the UDFFinder object. 
318
The UDFFinder manages FreeRefFunctions which are our analogy for the VBA code.  We need to create a UDFFinder. There are
319
     			a few things we need to know in order to do this:</p>
320
     			<ul>
321
     				<li>The name of the function in the VBA code (in our case it is calculatePayment)</li>
322
     				<li>The Class name of our FreeRefFunction</li>
323
     			</ul>
324
     		<p>UDFFinder is actually an interface, so we need to use an actual implementation of this interface.  Therefore we use the org.apache.poi.ss.formula.udf.DefaultUDFFinder class.  If you refer to the Javadocs you'll see that this class expects to get two arrays, one
325
     		containing the alias and the other containing an instance of the class that will represent that alias.  In our case our alias will be calculatePayment 
326
     		and our class instance will be of the  CalculateMortgage type.  This class needs to be available at compile and runtime.  Be sure to keep these arrays
327
     		well organized because you'll run into problems if these arrays are of different sizes or the alias aren't in the same relative position in their respective
328
     		arrays.  Add the following code:</p>
329
     	  <source><![CDATA[
330
String[] functionNames = { "calculatePayment" } ;
331
FreeRefFunction[] functionImpls = { new CalculateMortgage() } ;
332
333
UDFFinder udfs = new DefaultUDFFinder( functionNames, functionImpls ) ;
334
UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ;	
335
     	  	]]></source>
336
     	  <p>Now we have our UDFFinder instance and we've created the AggregatingUDFFinder instance.  The last step is to register the UDF toolpack in the Workbook:</p>
337
     	  
338
     	  <source><![CDATA[
339
workbook.addToolPack(udfToolpack);
340
     	  	]]></source>
341
     	  <p>So now the whole class will look like this:</p>
342
     	  <source><![CDATA[ 
343
import java.io.File ;
344
import java.io.FileInputStream ;
345
import java.io.FileNotFoundException ;
346
import java.io.IOException ;
347
348
import org.apache.poi.openxml4j.exceptions.InvalidFormatException ;
349
import org.apache.poi.ss.formula.functions.FreeRefFunction ;
350
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder ;
351
import org.apache.poi.ss.formula.udf.DefaultUDFFinder ;
352
import org.apache.poi.ss.formula.udf.UDFFinder ;
353
import org.apache.poi.ss.usermodel.Cell ;
354
import org.apache.poi.ss.usermodel.CellValue ;
355
import org.apache.poi.ss.usermodel.Row ;
356
import org.apache.poi.ss.usermodel.Sheet ;
357
import org.apache.poi.ss.usermodel.Workbook ;
358
import org.apache.poi.ss.usermodel.WorkbookFactory ;
359
import org.apache.poi.ss.util.CellReference ;
360
361
public class Evaluator {
362
    
363
    public static void main( String[] args ) {
364
        
365
        System.out.println( "fileName: " + args[0] ) ;
366
        System.out.println( "cell: " + args[1] ) ;
367
        
368
        File workbookFile = new File( args[0] ) ;
369
        
370
        try {
371
            FileInputStream fis = new FileInputStream(workbookFile);
372
            Workbook workbook = WorkbookFactory.create(fis);
373
            
374
            String[] functionNames = { "calculatePayment" } ;
375
            FreeRefFunction[] functionImpls = { new CalculateMortgage() } ;
376
377
            UDFFinder udfs = new DefaultUDFFinder( functionNames, functionImpls ) ;
378
            UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ;	
379
                      
380
            workbook.addToolPack(udfToolpack);
381
382
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
383
            
384
            CellReference cr = new CellReference( args[1] ) ;
385
            String sheetName = cr.getSheetName() ;
386
            Sheet sheet = workbook.getSheet( sheetName ) ;
387
            int rowIdx = cr.getRow() ;
388
            int colIdx = cr.getCol() ;
389
            Row row = sheet.getRow( rowIdx ) ;
390
            Cell cell = row.getCell( colIdx ) ;
391
            
392
            CellValue value = evaluator.evaluate( cell ) ;
393
            
394
            System.out.println("returns value: " +  value ) ;
395
            
396
            
397
        } catch( FileNotFoundException e ) {
398
            e.printStackTrace();
399
        } catch( InvalidFormatException e ) {
400
            e.printStackTrace();
401
        } catch( IOException e ) {
402
            e.printStackTrace();
403
        }
404
    }
405
}
406
     	  	
407
     	  ]]></source>
408
     	  <p>Now that our evaluator is aware of the UDFFinder which in turn is aware of our FreeRefFunction, we're ready to re-run our example:</p>
409
        <source>Evaluator mortgage-calculation.xls Sheet1!B4</source>
410
        <p>which prints the following output in the console:</p>
411
     	  <source><![CDATA[
412
cell: 'Sheet1'!$B$4
413
i = 0.004166666666666667	n = 180.0
414
Result = 790.7936267415464
415
returns value: org.apache.poi.ss.usermodel.CellValue [790.7936267415464]]]></source>
416
     		<p>That is it!  Now you can create Java code and register it, allowing your POI based appliction to run spreadsheets that previously were inaccessible.</p>
417
     		<p>This example can be found in the <link href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/org/apache/poi/ss/examples/formula">src/examples/org/apache/poi/ss/examples/formula</link> folder in the source.</p>
418
    </section>
419
  </section>
420
</body>
421
</document>    
(-)src/documentation/content/xdocs/spreadsheet/book.xml (+1 lines)
Lines 39-44 Link Here
39
        <menu-item label="Use Case" href="use-case.html"/>
39
        <menu-item label="Use Case" href="use-case.html"/>
40
        <menu-item label="Pictorial Docs" href="diagrams.html"/>
40
        <menu-item label="Pictorial Docs" href="diagrams.html"/>
41
        <menu-item label="Limitations" href="limitations.html"/>
41
        <menu-item label="Limitations" href="limitations.html"/>
42
        <menu-item label="User Defined Functions" href="user-defined-functions.html"/>
42
    </menu>
43
    </menu>
43
44
44
    <menu label="Contributer's Guide">
45
    <menu label="Contributer's Guide">
(-)src/examples/src/org/apache/poi/ss/examples/formula/UserDefinedFunctionExample.java (+87 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
package org.apache.poi.ss.examples.formula;
18
19
import java.io.File ;
20
import java.io.FileInputStream ;
21
import java.io.FileNotFoundException ;
22
import java.io.IOException ;
23
24
import org.apache.poi.openxml4j.exceptions.InvalidFormatException ;
25
import org.apache.poi.ss.formula.functions.FreeRefFunction ;
26
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder ;
27
import org.apache.poi.ss.formula.udf.DefaultUDFFinder ;
28
import org.apache.poi.ss.formula.udf.UDFFinder ;
29
import org.apache.poi.ss.usermodel.*;
30
import org.apache.poi.ss.util.CellReference ;
31
32
33
/**
34
 *
35
 *
36
 * @author Jon Svede
37
 */
38
public class UserDefinedFunctionExample {
39
40
    public static void main( String[] args ) {
41
        
42
        if(  args.length != 2 ) {
43
            System.out.println( "usage: UserDefinedFunctionExample fileName cellId" ) ;
44
            return;
45
        }
46
        
47
        System.out.println( "fileName: " + args[0] ) ;
48
        System.out.println( "cell: " + args[1] ) ;
49
        
50
        File workbookFile = new File( args[0] ) ;
51
        
52
        try {
53
            FileInputStream fis = new FileInputStream(workbookFile);
54
            Workbook workbook = WorkbookFactory.create(fis);
55
            fis.close();
56
57
            String[] functionNames = { "calculatePayment" } ;
58
            FreeRefFunction[] functionImpls = { new CalculateMortgage() } ;
59
            
60
            UDFFinder udfToolpack = new DefaultUDFFinder( functionNames, functionImpls ) ;
61
62
            // register the user-defined function in the workbook
63
            workbook.addToolPack(udfToolpack);
64
65
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
66
67
            CellReference cr = new CellReference( args[1] ) ;
68
            String sheetName = cr.getSheetName() ;
69
            Sheet sheet = workbook.getSheet( sheetName ) ;
70
            int rowIdx = cr.getRow() ;
71
            int colIdx = cr.getCol() ;
72
            Row row = sheet.getRow( rowIdx ) ;
73
            Cell cell = row.getCell( colIdx ) ;
74
            
75
            CellValue value = evaluator.evaluate( cell ) ;
76
            
77
            System.out.println("returns value: " +  value ) ;
78
79
        } catch( FileNotFoundException e ) {
80
            e.printStackTrace();
81
        } catch( InvalidFormatException e ) {
82
            e.printStackTrace();
83
        } catch( IOException e ) {
84
            e.printStackTrace();
85
        }
86
    }
87
}
(-)src/examples/src/org/apache/poi/ss/examples/formula/CalculateMortgage.java (+94 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
package org.apache.poi.ss.examples.formula;
18
19
import org.apache.poi.ss.formula.OperationEvaluationContext ;
20
import org.apache.poi.ss.formula.eval.ErrorEval ;
21
import org.apache.poi.ss.formula.eval.EvaluationException ;
22
import org.apache.poi.ss.formula.eval.NumberEval ;
23
import org.apache.poi.ss.formula.eval.OperandResolver ;
24
import org.apache.poi.ss.formula.eval.ValueEval ;
25
import org.apache.poi.ss.formula.functions.FreeRefFunction ;
26
27
/**
28
 * A simple user-defined function to calculate principal and interest.
29
 * 
30
 * @author Jon Svede
31
 *
32
 */
33
public class CalculateMortgage implements FreeRefFunction {
34
35
    public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
36
        
37
        // verify that we have enough data
38
        if (args.length != 3) {  
39
            return ErrorEval.VALUE_INVALID;
40
        }
41
42
        // declare doubles for values
43
        double principal, rate, years,  result;
44
        try {
45
            // extract values as ValueEval
46
            ValueEval v1 = OperandResolver.getSingleValue( args[0], 
47
                                                           ec.getRowIndex(), 
48
                                                           ec.getColumnIndex() ) ;
49
            ValueEval v2 = OperandResolver.getSingleValue( args[1], 
50
                                                           ec.getRowIndex(), 
51
                                                           ec.getColumnIndex() ) ;
52
            ValueEval v3 = OperandResolver.getSingleValue( args[2], 
53
                                                           ec.getRowIndex(), 
54
                                                           ec.getColumnIndex() ) ;
55
56
            // get data as doubles
57
            principal  = OperandResolver.coerceValueToDouble( v1 ) ; 
58
            rate  = OperandResolver.coerceValueToDouble( v2 ) ;
59
            years = OperandResolver.coerceValueToDouble( v3 ) ;
60
            
61
            result = calculateMortgagePayment( principal, rate, years ) ;
62
            System.out.println( "Result = " + result ) ;
63
64
            checkValue(result);
65
            
66
        } catch (EvaluationException e) {
67
            return e.getErrorEval();
68
        }
69
70
        return new NumberEval( result ) ;
71
    }
72
    
73
    public double calculateMortgagePayment( double p, double r, double y ) {
74
        double i = r / 12 ;
75
        double n = y * 12 ;
76
        
77
        // P * ((i * (1 + i) ^ n) / ((1 + i) ^ n - 1))
78
        double principalAndInterest = 
79
             p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1))  ;
80
        
81
        System.out.println( "i = " + i + "\tn = " + n ) ;
82
        return principalAndInterest ;
83
    }
84
    /**
85
     * Excel does not support infinities and NaNs, rather, it gives a #NUM! error in these cases
86
     *
87
     * @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt>
88
     */
89
     static void checkValue(double result) throws EvaluationException {
90
         if (Double.isNaN(result) || Double.isInfinite(result)) {
91
             throw new EvaluationException(ErrorEval.NUM_ERROR);
92
         }
93
     }    
94
}

Return to bug 50587