You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2021/07/28 08:39:12 UTC
svn commit: r1891845 -
/poi/site/publish/components/spreadsheet/user-defined-functions.html
Author: fanningpj
Date: Wed Jul 28 08:39:12 2021
New Revision: 1891845
URL: http://svn.apache.org/viewvc?rev=1891845&view=rev
Log:
change svn url
Modified:
poi/site/publish/components/spreadsheet/user-defined-functions.html
Modified: poi/site/publish/components/spreadsheet/user-defined-functions.html
URL: http://svn.apache.org/viewvc/poi/site/publish/components/spreadsheet/user-defined-functions.html?rev=1891845&r1=1891844&r2=1891845&view=diff
==============================================================================
--- poi/site/publish/components/spreadsheet/user-defined-functions.html (original)
+++ poi/site/publish/components/spreadsheet/user-defined-functions.html Wed Jul 28 08:39:12 2021
@@ -646,8 +646,8 @@ document.write("Last Published: " + docu
class has other methods of coercion for getting Strings, ints and booleans. Now that we've
got our primitive values we can move on to calculating the value.</p>
<p>As shown previously, we have the VBA source. We need to add code to our class to calculate
- the payment. To do this you could simply add it to the method we've already created but I've
- chosen to add it as its own method. Add the following method: </p>
+ the payment. To do this you could simply add it to the method we've already created but I've
+ chosen to add it as its own method. Add the following method: </p>
<div class="code">
<div class="codeline">
<span class="lineno"></span><span class="codebody"></span>
@@ -683,24 +683,24 @@ document.write("Last Published: " + docu
<span class="lineno"></span><span class="codebody">} </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
</div>
<p>The biggest change necessary is related to the exponents; Java doesn't have a notation for this
- so we had to add calls to Math.pow(). Now we need to add this call to our previous method:</p>
+ so we had to add calls to Math.pow(). Now we need to add this call to our previous method:</p>
<div class="code">
<div class="codeline">
<span class="lineno"></span><span class="codebody"></span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">result = calculateMortgagePayment( principal, rate, years ) ; </span>
+<span class="lineno"></span><span class="codebody">result = calculateMortgagePayment( principal, rate, years ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"></span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
</div>
<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,
- if not, we need to return the value. Add the following code to the class:</p>
+ if not, we need to return the value. Add the following code to the class:</p>
<div class="code">
<div class="codeline">
<span class="lineno"></span><span class="codebody"></span>
@@ -721,7 +721,7 @@ document.write("Last Published: " + docu
<span class="lineno"></span><span class="codebody">} </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
</div>
<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>
@@ -754,7 +754,7 @@ document.write("Last Published: " + docu
<span class="lineno"></span><span class="codebody">rn new NumberEval( result ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"></span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
</div>
<p>So the whole class would be as follows:</p>
@@ -982,10 +982,10 @@ document.write("Last Published: " + docu
<span class="lineno"></span><span class="codebody">}</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"></span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
</div>
<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>
@@ -994,7 +994,7 @@ document.write("Last Published: " + docu
<p>Now we need to register our function in the Workbook, so that the Formula Evaluator can resolve the name "calculatePayment"
and map it to the actual implementation (CalculateMortgage). This is done using the UDFFinder object.
The UDFFinder manages FreeRefFunctions which are our analogy for the VBA code. We need to create a UDFFinder. There are
- a few things we need to know in order to do this:</p>
+ a few things we need to know in order to do this:</p>
<ul>
<li>The name of the function in the VBA code (in our case it is calculatePayment)</li>
@@ -1003,10 +1003,10 @@ The UDFFinder manages FreeRefFunctions w
</ul>
<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
- 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
- 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
- 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
- arrays. Add the following code:</p>
+ 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
+ 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
+ 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
+ arrays. Add the following code:</p>
<div class="code">
<div class="codeline">
<span class="lineno"></span><span class="codebody"></span>
@@ -1027,7 +1027,7 @@ The UDFFinder manages FreeRefFunctions w
<span class="lineno"></span><span class="codebody">UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ; </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
</div>
<p>Now we have our UDFFinder instance and we've created the AggregatingUDFFinder instance. The last step is to pass this to our Workbook:</p>
@@ -1039,7 +1039,7 @@ The UDFFinder manages FreeRefFunctions w
<span class="lineno"></span><span class="codebody">workbook.addToolPack(udfToolpack);</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
</div>
<p>So now the whole class will look like this:</p>
@@ -1048,199 +1048,199 @@ The UDFFinder manages FreeRefFunctions w
<span class="lineno"></span><span class="codebody"></span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport java.io.File ;</span>
+<span class="lineno"></span><span class="codebody">import java.io.File ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport java.io.FileInputStream ;</span>
+<span class="lineno"></span><span class="codebody">import java.io.FileInputStream ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport java.io.FileNotFoundException ;</span>
+<span class="lineno"></span><span class="codebody">import java.io.FileNotFoundException ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport java.io.IOException ;</span>
+<span class="lineno"></span><span class="codebody">import java.io.IOException ;</span>
</div>
<div class="codeline">
<span class="lineno"></span><span class="codebody"></span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.openxml4j.exceptions.InvalidFormatException ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.openxml4j.exceptions.InvalidFormatException ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.ss.formula.functions.FreeRefFunction ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.formula.functions.FreeRefFunction ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.ss.formula.udf.AggregatingUDFFinder ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.formula.udf.AggregatingUDFFinder ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.ss.formula.udf.DefaultUDFFinder ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.formula.udf.DefaultUDFFinder ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.ss.formula.udf.UDFFinder ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.formula.udf.UDFFinder ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.ss.usermodel.Cell ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.usermodel.Cell ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.ss.usermodel.CellValue ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.usermodel.CellValue ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.ss.usermodel.Row ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.usermodel.Row ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.ss.usermodel.Sheet ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.usermodel.Sheet ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.ss.usermodel.Workbook ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.usermodel.Workbook ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.ss.usermodel.WorkbookFactory ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.usermodel.WorkbookFactory ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">mport org.apache.poi.ss.util.CellReference ;</span>
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.util.CellReference ;</span>
</div>
<div class="codeline">
<span class="lineno"></span><span class="codebody"></span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody">ublic class Evaluator {</span>
+<span class="lineno"></span><span class="codebody">public class Evaluator {</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> public static void main( String[] args ) {</span>
+<span class="lineno"></span><span class="codebody"> public static void main( String[] args ) {</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> System.out.println( "fileName: " + args[0] ) ;</span>
+<span class="lineno"></span><span class="codebody"> System.out.println( "fileName: " + args[0] ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> System.out.println( "cell: " + args[1] ) ;</span>
+<span class="lineno"></span><span class="codebody"> System.out.println( "cell: " + args[1] ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> File workbookFile = new File( args[0] ) ;</span>
+<span class="lineno"></span><span class="codebody"> File workbookFile = new File( args[0] ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> try {</span>
+<span class="lineno"></span><span class="codebody"> try {</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> FileInputStream fis = new FileInputStream(workbookFile);</span>
+<span class="lineno"></span><span class="codebody"> FileInputStream fis = new FileInputStream(workbookFile);</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> Workbook workbook = WorkbookFactory.create(fis);</span>
+<span class="lineno"></span><span class="codebody"> Workbook workbook = WorkbookFactory.create(fis);</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> String[] functionNames = { "calculatePayment" } ;</span>
+<span class="lineno"></span><span class="codebody"> String[] functionNames = { "calculatePayment" } ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> FreeRefFunction[] functionImpls = { new CalculateMortgage() } ;</span>
+<span class="lineno"></span><span class="codebody"> FreeRefFunction[] functionImpls = { new CalculateMortgage() } ;</span>
</div>
<div class="codeline">
<span class="lineno"></span><span class="codebody"></span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> UDFFinder udfs = new DefaultUDFFinder( functionNames, functionImpls ) ;</span>
+<span class="lineno"></span><span class="codebody"> UDFFinder udfs = new DefaultUDFFinder( functionNames, functionImpls ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ; </span>
+<span class="lineno"></span><span class="codebody"> UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ; </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> workbook.addToolPack(udfToolpack);</span>
+<span class="lineno"></span><span class="codebody"> workbook.addToolPack(udfToolpack);</span>
</div>
<div class="codeline">
<span class="lineno"></span><span class="codebody"></span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();</span>
+<span class="lineno"></span><span class="codebody"> FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> CellReference cr = new CellReference( args[1] ) ;</span>
+<span class="lineno"></span><span class="codebody"> CellReference cr = new CellReference( args[1] ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> String sheetName = cr.getSheetName() ;</span>
+<span class="lineno"></span><span class="codebody"> String sheetName = cr.getSheetName() ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> Sheet sheet = workbook.getSheet( sheetName ) ;</span>
+<span class="lineno"></span><span class="codebody"> Sheet sheet = workbook.getSheet( sheetName ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> int rowIdx = cr.getRow() ;</span>
+<span class="lineno"></span><span class="codebody"> int rowIdx = cr.getRow() ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> int colIdx = cr.getCol() ;</span>
+<span class="lineno"></span><span class="codebody"> int colIdx = cr.getCol() ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> Row row = sheet.getRow( rowIdx ) ;</span>
+<span class="lineno"></span><span class="codebody"> Row row = sheet.getRow( rowIdx ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> Cell cell = row.getCell( colIdx ) ;</span>
+<span class="lineno"></span><span class="codebody"> Cell cell = row.getCell( colIdx ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> CellValue value = evaluator.evaluate( cell ) ;</span>
+<span class="lineno"></span><span class="codebody"> CellValue value = evaluator.evaluate( cell ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> System.out.println("returns value: " + value ) ;</span>
+<span class="lineno"></span><span class="codebody"> System.out.println("returns value: " + value ) ;</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> } catch( FileNotFoundException e ) {</span>
+<span class="lineno"></span><span class="codebody"> } catch( FileNotFoundException e ) {</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> e.printStackTrace();</span>
+<span class="lineno"></span><span class="codebody"> e.printStackTrace();</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> } catch( InvalidFormatException e ) {</span>
+<span class="lineno"></span><span class="codebody"> } catch( InvalidFormatException e ) {</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> e.printStackTrace();</span>
+<span class="lineno"></span><span class="codebody"> e.printStackTrace();</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> } catch( IOException e ) {</span>
+<span class="lineno"></span><span class="codebody"> } catch( IOException e ) {</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> e.printStackTrace();</span>
+<span class="lineno"></span><span class="codebody"> e.printStackTrace();</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> }</span>
+<span class="lineno"></span><span class="codebody"> }</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> }</span>
+<span class="lineno"></span><span class="codebody"> }</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"></span>
+<span class="lineno"></span><span class="codebody">}</span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"></span>
</div>
<div class="codeline">
-<span class="lineno"></span><span class="codebody"> </span>
+<span class="lineno"></span><span class="codebody"> </span>
</div>
</div>
<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>
@@ -1268,7 +1268,7 @@ The UDFFinder manages FreeRefFunctions w
</div>
</div>
<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>
-<p>This example can be found in the <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/examples/ss/formula">src/examples/src/org/apache/poi/examples/ss/formula</a> folder in the source.</p>
+<p>This example can be found in the <a href="https://svn.apache.org/repos/asf/poi/trunk/poi-examples/src/main/java/org/apache/poi/examples/ss/formula">poi-examples/src/main/java/org/apache/poi/examples/ss/formula</a> folder in the source.</p>
</div>
<p align="right">
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org