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

(-)src/documentation/content/xdocs/spreadsheet/user-defined-functions.xml (+428 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.hssf.usermodel.HSSFFormulaEvaluator ;
59
import org.apache.poi.hssf.usermodel.HSSFWorkbook ;
60
import org.apache.poi.openxml4j.exceptions.InvalidFormatException ;
61
import org.apache.poi.ss.formula.functions.FreeRefFunction ;
62
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder ;
63
import org.apache.poi.ss.formula.udf.DefaultUDFFinder ;
64
import org.apache.poi.ss.formula.udf.UDFFinder ;
65
import org.apache.poi.ss.usermodel.Cell ;
66
import org.apache.poi.ss.usermodel.CellValue ;
67
import org.apache.poi.ss.usermodel.Row ;
68
import org.apache.poi.ss.usermodel.Sheet ;
69
import org.apache.poi.ss.usermodel.Workbook ;
70
import org.apache.poi.ss.usermodel.WorkbookFactory ;
71
import org.apache.poi.ss.util.CellReference ;
72
73
public class Evaluator {
74
75
    
76
    
77
    public static void main( String[] args ) {
78
        
79
        System.out.println( "fileName: " + args[0] ) ;
80
        System.out.println( "cell: " + args[1] ) ;
81
        
82
        File workbookFile = new File( args[0] ) ;
83
        
84
        try {
85
            FileInputStream fis = new FileInputStream(workbookFile);
86
            Workbook workbook = WorkbookFactory.create(fis);
87
            
88
            HSSFFormulaEvaluator evaluator = HSSFFormulaEvaluator.create(
89
                    (HSSFWorkbook) workbook, null, null) ;
90
91
            
92
            CellReference cr = new CellReference( args[1] ) ;
93
            String sheetName = cr.getSheetName() ;
94
            Sheet sheet = workbook.getSheet( sheetName ) ;
95
            int rowIdx = cr.getRow() ;
96
            int colIdx = cr.getCol() ;
97
            Row row = sheet.getRow( rowIdx ) ;
98
            Cell cell = row.getCell( colIdx ) ;
99
            
100
            CellValue value = evaluator.evaluate( cell ) ;
101
            
102
            System.out.println("returns value: " +  value ) ;
103
            
104
            
105
        } catch( FileNotFoundException e ) {
106
            e.printStackTrace();
107
        } catch( InvalidFormatException e ) {
108
            e.printStackTrace();
109
        } catch( IOException e ) {
110
            e.printStackTrace();
111
        }
112
    }
113
}
114
        
115
]]></source>
116
        <p>If you run this code, you're likely to get the following error:</p>
117
        
118
       <source><![CDATA[
119
Exception in thread "main" org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Sheet1!B4
120
    at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321)
121
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
122
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)
123
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320)
124
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:182)
125
    at poi.tests.Evaluator.main(Evaluator.java:61)
126
Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: calculatePayment
127
    at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:59)
128
    at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
129
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:456)
130
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:279)
131
    ... 4 more
132
        
133
]]></source>
134
        
135
        <p>How would we make it so POI can use this sheet?</p>
136
     </section>
137
     
138
     <section><title>Defining Your Function</title>
139
        <p>To 'convert' this code to Java and make it available to POI you need to implement
140
        a FreeRefFunction instance.  FreeRefFunction is an interface in the org.apache.poi.ss.formula.functions 
141
        package.  This interface defines one method, evaluate(ValueEval[] args, OperationEvaluationContext ec),
142
        which is how you will receive the argument values from POI.</p>
143
        <p>The evaluate() method as defined above is where you will convert the ValueEval instances to the 
144
        proper number types.  The following code snippet shows you how to get your values:</p>
145
 
146
      <source><![CDATA[
147
public class CalculateMortgage implements FreeRefFunction {
148
149
@Override
150
public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
151
    if (args.length != 3) {  
152
        return ErrorEval.VALUE_INVALID;
153
    }
154
155
    double principal, rate, years,  result;
156
    try {
157
        ValueEval v1 = OperandResolver.getSingleValue( args[0], 
158
                                                       ec.getRowIndex(), 
159
                                                       ec.getColumnIndex() ) ;
160
        ValueEval v2 = OperandResolver.getSingleValue( args[1], 
161
                                                       ec.getRowIndex(), 
162
                                                       ec.getColumnIndex() ) ;
163
        ValueEval v3 = OperandResolver.getSingleValue( args[2], 
164
                                                       ec.getRowIndex(), 
165
                                                       ec.getColumnIndex() ) ;
166
167
        principal  = OperandResolver.coerceValueToDouble( v1 ) ; 
168
        rate  = OperandResolver.coerceValueToDouble( v2 ) ;
169
        years = OperandResolver.coerceValueToDouble( v3 ) ;
170
     ]]></source>
171
     
172
     <p>The first thing we do is check the number of arguments being passed since there is no sense
173
     in attempting to go further if you are missing critical information.</p>
174
     <p>Next we declare our variables, in our case we need variables for:</p>
175
     <ul>
176
        <li>principal - the amount of the loan</li>
177
        <li>rate - the interest rate as a decimal</li>
178
        <li>years - the length of the loan in years</li>
179
        <li>result - the result of the calculation</li>
180
     </ul>
181
     <p>Next, we use the OperandResolver to convert the ValueEval instances to doubles, though not directly.  
182
     First we start by getting discreet values.  Using the OperandResolver.getSingleValue() method
183
     we retrieve each of the values passed in by the cell in the spreadsheet.  Next, we use the
184
     OperandResolver again to convert the ValueEval instances to doubles, in this case.  This
185
     class has other methods of coercion for gettings Strings, ints and booleans.  Now that we've 
186
     got our primitive values we can move on to calculating the value.</p>
187
     <p>As shown previously, we have the VBA source.  We need to add code to our class to calculate 
188
     	the payment.  To do this you could simply add it to the method we've already created but I've
189
     	chosen to add it as its own method.  Add the following method: </p>
190
     	<source><![CDATA[
191
public double calculateMortgagePayment( double p, double r, double y ) {
192
193
    double i = r / 12 ;
194
    double n = y * 12 ;
195
    
196
    double principalAndInterest = 
197
         p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1))  ;
198
    
199
    return principalAndInterest ;
200
}	
201
     	]]></source>
202
     	<p>The biggest change necessary is related to the exponents; Java doesn't have a notation for this
203
     		so we had to add calls to Math.pow().  Now we need to add this call to our previous method:</p>
204
     	<source><![CDATA[
205
     	 result = calculateMortgagePayment( principal, rate, years ) ;	
206
     		]]></source>
207
     	<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,
208
     		if not, we need to return the value. Add the following code to the class:</p>
209
     	<source><![CDATA[
210
private void checkValue(double result) throws EvaluationException {
211
    if (Double.isNaN(result) || Double.isInfinite(result)) {
212
        throw new EvaluationException(ErrorEval.NUM_ERROR);
213
    }
214
} 
215
     		]]></source>
216
     <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>
217
     	<source><![CDATA[
218
        checkValue(result);
219
        
220
    } catch (EvaluationException e) {
221
        e.printStackTrace() ;
222
        return e.getErrorEval();
223
    }
224
225
    return new NumberEval( result ) ;
226
     		]]></source>
227
     		
228
     		<p>So the whole class would be as follows:</p>
229
     		
230
     	<source><![CDATA[
231
import org.apache.poi.ss.formula.OperationEvaluationContext ;
232
import org.apache.poi.ss.formula.eval.ErrorEval ;
233
import org.apache.poi.ss.formula.eval.EvaluationException ;
234
import org.apache.poi.ss.formula.eval.NumberEval ;
235
import org.apache.poi.ss.formula.eval.OperandResolver ;
236
import org.apache.poi.ss.formula.eval.ValueEval ;
237
import org.apache.poi.ss.formula.functions.FreeRefFunction ;
238
239
/**
240
 * A simple function to calculate principal and interest.
241
 * 
242
 * @author jsvede
243
 *
244
 */
245
public class CalculateMortgage implements FreeRefFunction {
246
247
    @Override
248
    public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
249
        if (args.length != 3) {  
250
            return ErrorEval.VALUE_INVALID;
251
        }
252
253
        double principal, rate, years,  result;
254
        try {
255
            ValueEval v1 = OperandResolver.getSingleValue( args[0], 
256
                                                           ec.getRowIndex(), 
257
                                                           ec.getColumnIndex() ) ;
258
            ValueEval v2 = OperandResolver.getSingleValue( args[1], 
259
                                                           ec.getRowIndex(), 
260
                                                           ec.getColumnIndex() ) ;
261
            ValueEval v3 = OperandResolver.getSingleValue( args[2], 
262
                                                           ec.getRowIndex(), 
263
                                                           ec.getColumnIndex() ) ;
264
265
            principal  = OperandResolver.coerceValueToDouble( v1 ) ; 
266
            rate  = OperandResolver.coerceValueToDouble( v2 ) ;
267
            years = OperandResolver.coerceValueToDouble( v3 ) ;
268
            
269
            result = calculateMortgagePayment( principal, rate, years ) ;
270
            
271
            checkValue(result);
272
            
273
        } catch (EvaluationException e) {
274
            e.printStackTrace() ;
275
            return e.getErrorEval();
276
        }
277
278
        return new NumberEval( result ) ;
279
    }
280
    
281
    public double calculateMortgagePayment( double p, double r, double y ) {
282
        double i = r / 12 ;
283
        double n = y * 12 ;
284
        
285
        //M = P [ i(1 + i)n ] / [ (1 + i)n - 1] 
286
        double principalAndInterest = 
287
             p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1))  ;
288
        
289
        return principalAndInterest ;
290
    }
291
    
292
    /**
293
     * @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt>
294
     */
295
     static final void checkValue(double result) throws EvaluationException {
296
         if (Double.isNaN(result) || Double.isInfinite(result)) {
297
             throw new EvaluationException(ErrorEval.NUM_ERROR);
298
         }
299
     }
300
301
}
302
	
303
     		]]></source>
304
     		
305
     		<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>
306
307
     </section>
308
     
309
     <section><title>Registering Your Function</title>
310
     <p>If you refer back to the original program you will notice the following line of code:</p>
311
     	<source><![CDATA[
312
HSSFFormulaEvaluator evaluator = HSSFFormulaEvaluator.create((HSSFWorkbook) workbook, null, null) ;
313
     		]]></source>
314
     		<p>The first argument is obviously the Workbook instance we want.  In this example we are using the HSSFWorkbook but this generally works the same 
315
     			for your XSSFWorkbook.  The next argument is for the IStabilityClassifier, which is out of scope in this discussion; the last argument is something
316
     			called a UDFFinder.   The UDFFinder manages FreeRefFunctions which are our analogy for the VBA code.  We need to create a UDFFinder. There are 
317
     			a few things we need to know in order to do this:</p>
318
     			<ul>
319
     				<li>The name of the function in the VBA code (in our case it is calculatePayment)</li>
320
     				<li>The Class name of our FreeRefFunction</li>
321
     			</ul>
322
     		<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
323
     		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 
324
     		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
325
     		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
326
     		arrays.  Add the following code:</p>
327
     	  <source><![CDATA[
328
String[] names = { "calculatePayment" } ;
329
FreeRefFunction[] functions = { new CalculateMortgage() } ; 
330
331
UDFFinder udff1 = new DefaultUDFFinder( names, functions ) ;
332
UDFFinder udff = new AggregatingUDFFinder( udff1 ) ;	
333
     	  	]]></source>
334
     	  <p>Now we have our UDFFinder instance and we've created the AggregatingUDFFinder instance.  The last step is to pass this to our FormulaEvaluator:</p>
335
     	  
336
     	  <source><![CDATA[
337
HSSFFormulaEvaluator evaluator = HSSFFormulaEvaluator.create(
338
(HSSFWorkbook) workbook, null, udff) ;	
339
     	  	]]></source>
340
     	  <p>So now the whole class will look like this:</p>
341
     	  <source><![CDATA[ 
342
import java.io.File ;
343
import java.io.FileInputStream ;
344
import java.io.FileNotFoundException ;
345
import java.io.IOException ;
346
347
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator ;
348
import org.apache.poi.hssf.usermodel.HSSFWorkbook ;
349
import org.apache.poi.openxml4j.exceptions.InvalidFormatException ;
350
import org.apache.poi.ss.formula.functions.FreeRefFunction ;
351
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder ;
352
import org.apache.poi.ss.formula.udf.DefaultUDFFinder ;
353
import org.apache.poi.ss.formula.udf.UDFFinder ;
354
import org.apache.poi.ss.usermodel.Cell ;
355
import org.apache.poi.ss.usermodel.CellValue ;
356
import org.apache.poi.ss.usermodel.Row ;
357
import org.apache.poi.ss.usermodel.Sheet ;
358
import org.apache.poi.ss.usermodel.Workbook ;
359
import org.apache.poi.ss.usermodel.WorkbookFactory ;
360
import org.apache.poi.ss.util.CellReference ;
361
362
import functions.CalculateMortgage ;
363
364
public class Evaluator {
365
    
366
    public static void main( String[] args ) {
367
        
368
        System.out.println( "fileName: " + args[0] ) ;
369
        System.out.println( "cell: " + args[1] ) ;
370
        
371
        File workbookFile = new File( args[0] ) ;
372
        
373
        try {
374
            FileInputStream fis = new FileInputStream(workbookFile);
375
            Workbook workbook = WorkbookFactory.create(fis);
376
            
377
            String[] names = { "calculatePayment" } ;
378
            FreeRefFunction[] functions = { new CalculateMortgage() } ; 
379
            
380
            UDFFinder udff1 = new DefaultUDFFinder( names, functions ) ;
381
            UDFFinder udff = new AggregatingUDFFinder( udff1 ) ;
382
            
383
            HSSFFormulaEvaluator evaluator = HSSFFormulaEvaluator.create(
384
                    (HSSFWorkbook) workbook, null, udff) ;
385
386
//            HSSFFormulaEvaluator evaluator = HSSFFormulaEvaluator.create(
387
//                    (HSSFWorkbook) workbook, null, null) ;
388
389
            
390
            CellReference cr = new CellReference( args[1] ) ;
391
            String sheetName = cr.getSheetName() ;
392
            Sheet sheet = workbook.getSheet( sheetName ) ;
393
            int rowIdx = cr.getRow() ;
394
            int colIdx = cr.getCol() ;
395
            Row row = sheet.getRow( rowIdx ) ;
396
            Cell cell = row.getCell( colIdx ) ;
397
            
398
            CellValue value = evaluator.evaluate( cell ) ;
399
            
400
            System.out.println("returns value: " +  value ) ;
401
            
402
            
403
        } catch( FileNotFoundException e ) {
404
            // TODO Auto-generated catch block
405
            e.printStackTrace();
406
        } catch( InvalidFormatException e ) {
407
            // TODO Auto-generated catch block
408
            e.printStackTrace();
409
        } catch( IOException e ) {
410
            // TODO Auto-generated catch block
411
            e.printStackTrace();
412
        }
413
    }
414
}
415
     	  	
416
     	  ]]></source>
417
     	  <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>
418
     	  <source><![CDATA[
419
cell: 'sheet1'!$B$4
420
i = 0.004166666666666667	n = 180.0
421
Result = 790.7936267415464
422
returns value: org.apache.poi.ss.usermodel.CellValue [790.7936267415464]]]></source>
423
     		<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>
424
     		<p>This example can be found in the src/examples/org/apache/poi/ss/examples/formula folder in the source.</p>
425
    </section>
426
  </section>
427
</body>
428
</document>    
(-)src/examples/src/org/apache/poi/ss/examples/formula/CalculateMortgage.java (+93 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 ( jon [at] loquatic [dot] com )
31
 * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
32
 *
33
 */
34
public class CalculateMortgage implements FreeRefFunction {
35
36
    public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
37
        
38
        // verify that we have enough data
39
        if (args.length != 3) {  
40
            return ErrorEval.VALUE_INVALID;
41
        }
42
43
        // declare doubles for values
44
        double principal, rate, years,  result;
45
        try {
46
            // extract values as ValueEval
47
            ValueEval v1 = OperandResolver.getSingleValue( args[0], 
48
                                                           ec.getRowIndex(), 
49
                                                           ec.getColumnIndex() ) ;
50
            ValueEval v2 = OperandResolver.getSingleValue( args[1], 
51
                                                           ec.getRowIndex(), 
52
                                                           ec.getColumnIndex() ) ;
53
            ValueEval v3 = OperandResolver.getSingleValue( args[2], 
54
                                                           ec.getRowIndex(), 
55
                                                           ec.getColumnIndex() ) ;
56
57
            // get data as doubles
58
            principal  = OperandResolver.coerceValueToDouble( v1 ) ; 
59
            rate  = OperandResolver.coerceValueToDouble( v2 ) ;
60
            years = OperandResolver.coerceValueToDouble( v3 ) ;
61
            
62
            result = calculateMortgagePayment( principal, rate, years ) ;
63
            System.out.println( "Result = " + result ) ;
64
65
            checkValue(result);
66
            
67
        } catch (EvaluationException e) {
68
            return e.getErrorEval();
69
        }
70
71
        return new NumberEval( result ) ;
72
    }
73
    
74
    public double calculateMortgagePayment( double p, double r, double y ) {
75
        double i = r / 12 ;
76
        double n = y * 12 ;
77
        
78
        double principalAndInterest = 
79
             p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1))  ;
80
        
81
        return principalAndInterest ;
82
    }
83
    /**
84
     * Excel does not support infinities and NaNs, rather, it gives a #NUM! error in these cases
85
     *
86
     * @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt>
87
     */
88
     private void checkValue(double result) throws EvaluationException {
89
         if (Double.isNaN(result) || Double.isInfinite(result)) {
90
             throw new EvaluationException(ErrorEval.NUM_ERROR);
91
         }
92
     }    
93
}
(-)src/examples/src/org/apache/poi/ss/examples/formula/UserDefinedFunctionExample.java (+90 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
 * An example class of how to invoke a User Defined Function for a given
35
 * XLS instance using POI's UDFFinder implementation.
36
 * 
37
 * @author Jon Svede ( jon [at] loquatic [dot] com )
38
 * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
39
 * 
40
 */
41
public class UserDefinedFunctionExample {
42
43
    public static void main( String[] args ) {
44
        
45
        if(  args.length != 2 ) {
46
            System.out.println( "usage: UserDefinedFunctionExample fileName cellId" ) ;
47
            return;
48
        }
49
        
50
        System.out.println( "fileName: " + args[0] ) ;
51
        System.out.println( "cell: " + args[1] ) ;
52
        
53
        File workbookFile = new File( args[0] ) ;
54
        
55
        try {
56
            FileInputStream fis = new FileInputStream(workbookFile);
57
            Workbook workbook = WorkbookFactory.create(fis);
58
            fis.close();
59
60
            String[] functionNames = { "calculatePayment" } ;
61
            FreeRefFunction[] functionImpls = { new CalculateMortgage() } ;
62
            
63
            UDFFinder udfToolpack = new DefaultUDFFinder( functionNames, functionImpls ) ;
64
65
            // register the user-defined function in the workbook
66
            workbook.addToolPack(udfToolpack);
67
68
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
69
70
            CellReference cr = new CellReference( args[1] ) ;
71
            String sheetName = cr.getSheetName() ;
72
            Sheet sheet = workbook.getSheet( sheetName ) ;
73
            int rowIdx = cr.getRow() ;
74
            int colIdx = cr.getCol() ;
75
            Row row = sheet.getRow( rowIdx ) ;
76
            Cell cell = row.getCell( colIdx ) ;
77
            
78
            CellValue value = evaluator.evaluate( cell ) ;
79
            
80
            System.out.println("returns value: " +  value ) ;
81
82
        } catch( FileNotFoundException e ) {
83
            e.printStackTrace();
84
        } catch( InvalidFormatException e ) {
85
            e.printStackTrace();
86
        } catch( IOException e ) {
87
            e.printStackTrace();
88
        }
89
    }
90
}

Return to bug 50587