You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ki...@apache.org on 2019/09/24 23:26:00 UTC
svn commit: r1867502 [14/20] - in /poi/site/publish: ./ components/
components/hmef/ components/hpbf/ components/hpsf/ components/poifs/
components/slideshow/ components/spreadsheet/ devel/ devel/plan/
devel/references/ skin/ skin/images/
Modified: poi/site/publish/components/spreadsheet/record-generator.html
URL: http://svn.apache.org/viewvc/poi/site/publish/components/spreadsheet/record-generator.html?rev=1867502&r1=1867501&r2=1867502&view=diff
==============================================================================
--- poi/site/publish/components/spreadsheet/record-generator.html (original)
+++ poi/site/publish/components/spreadsheet/record-generator.html Tue Sep 24 23:26:00 2019
@@ -325,26 +325,68 @@ document.write("Last Published: " + docu
<p>
The records themselves have the following general layout:
</p>
-<pre class="code">
-<record id="0x1032" name="Frame" package="org.apache.poi.hssf.record"
- excel-record-id="FRAME">
- <description>The frame record indicates whether there is a border
- around the displayed text of a chart.</description>
- <author>Glen Stampoultzis (glens at apache.org)</author>
- <fields>
- <field type="int" size="2" name="border type">
- <const name="regular" value="0" description="regular rectangle or no border"/>
- <const name="shadow" value="1" description="rectangle with shadow"/>
- </field>
- <field type="int" size="2" name="options">
- <bit number="0" name="auto size"
- description="excel calculates the size automatically if true"/>
- <bit number="1" name="auto position"
- description="excel calculates the position automatically"/>
- </field>
- </fields>
-</record>
- </pre>
+<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">record id="0x1032" name="Frame" package="org.apache.poi.hssf.record"</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> excel-record-id="FRAME"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> <description>The frame record indicates whether there is a border</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> around the displayed text of a chart.</description></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> <author>Glen Stampoultzis (glens at apache.org)</author></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> <fields></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> <field type="int" size="2" name="border type"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> <const name="regular" value="0" description="regular rectangle or no border"/></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> <const name="shadow" value="1" description="rectangle with shadow"/></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </field></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> <field type="int" size="2" name="options"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> <bit number="0" name="auto size"</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> description="excel calculates the size automatically if true"/></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> <bit number="1" name="auto position"</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> description="excel calculates the position automatically"/></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </field></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </fields></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">/record></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>
The following table details the allowable types and sizes for
the fields.
@@ -459,10 +501,20 @@ document.write("Last Published: " + docu
the field encodes and decodes. To code a custom type you
declare your field like this:
</p>
-<pre class="code">
- <field type="custom:org.apache.poi.hssf.record.LinkedDataFormulaField"
- size="var" name="formula of link" description="formula"/>
- </pre>
+<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">field type="custom:org.apache.poi.hssf.record.LinkedDataFormulaField"</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> size="var" name="formula of link" description="formula"/></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>
Where the class name specified after <span class="codefrag">custom:</span> is a
class implementing the interface <span class="codefrag">CustomField</span>.
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=1867502&r1=1867501&r2=1867502&view=diff
==============================================================================
--- poi/site/publish/components/spreadsheet/user-defined-functions.html (original)
+++ poi/site/publish/components/spreadsheet/user-defined-functions.html Tue Sep 24 23:26:00 2019
@@ -309,83 +309,233 @@ document.write("Last Published: " + docu
<img alt="VBA code" src="images/calculatePayment.jpg">
</p>
<p>If we write a small program to try to evaluate this cell, we'll fail. Consider this source code:</p>
-<pre class="code">
-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.Cell ;
-import org.apache.poi.ss.usermodel.CellValue ;
-import org.apache.poi.ss.usermodel.Row ;
-import org.apache.poi.ss.usermodel.Sheet ;
-import org.apache.poi.ss.usermodel.Workbook ;
-import org.apache.poi.ss.usermodel.WorkbookFactory ;
-import org.apache.poi.ss.util.CellReference ;
-
-public class Evaluator {
-
-
-
- public static void main( String[] args ) {
-
- 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);
-
- 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();
- }
- }
-}
-
-</pre>
+<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">import java.io.File ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">import java.io.FileInputStream ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">import java.io.FileNotFoundException ;</span>
+</div>
+<div class="codeline">
+<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">import org.apache.poi.openxml4j.exceptions.InvalidFormatException ;</span>
+</div>
+<div class="codeline">
+<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">import org.apache.poi.ss.formula.udf.AggregatingUDFFinder ;</span>
+</div>
+<div class="codeline">
+<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">import org.apache.poi.ss.formula.udf.UDFFinder ;</span>
+</div>
+<div class="codeline">
+<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">import org.apache.poi.ss.usermodel.CellValue ;</span>
+</div>
+<div class="codeline">
+<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">import org.apache.poi.ss.usermodel.Sheet ;</span>
+</div>
+<div class="codeline">
+<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">import org.apache.poi.ss.usermodel.WorkbookFactory ;</span>
+</div>
+<div class="codeline">
+<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">public class Evaluator {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>If you run this code, you're likely to get the following error:</p>
-<pre class="code">
-Exception in thread "main" org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Sheet1!B4
- at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321)
- at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
- at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)
- at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320)
- at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:182)
- at poi.tests.Evaluator.main(Evaluator.java:61)
-Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: calculatePayment
- at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:59)
- at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
- at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:456)
- at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:279)
- ... 4 more
-
-</pre>
+<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">Exception in thread "main" org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Sheet1!B4</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:182)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> at poi.tests.Evaluator.main(Evaluator.java:61)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: calculatePayment</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:59)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:456)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:279)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ... 4 more</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>How would we make it so POI can use this sheet?</p>
<a name="Defining+Your+Function"></a>
<h3 class="boxed">Defining Your Function</h3>
@@ -395,31 +545,83 @@ Caused by: org.apache.poi.ss.formula.eva
which is how you will receive the argument values from POI.</p>
<p>The evaluate() method as defined above is where you will convert the ValueEval instances to the
proper number types. The following code snippet shows you how to get your values:</p>
-<pre class="code">
-public class CalculateMortgage implements FreeRefFunction {
-
-@Override
-public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
- if (args.length != 3) {
- return ErrorEval.VALUE_INVALID;
- }
-
- double principal, rate, years, result;
- try {
- 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() ) ;
-
- principal = OperandResolver.coerceValueToDouble( v1 ) ;
- rate = OperandResolver.coerceValueToDouble( v2 ) ;
- years = OperandResolver.coerceValueToDouble( v3 ) ;
- </pre>
+<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">public class CalculateMortgage implements FreeRefFunction {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">@Override</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> if (args.length != 3) { </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> return ErrorEval.VALUE_INVALID;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> double principal, rate, years, result;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> try {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ValueEval v1 = OperandResolver.getSingleValue( args[0], </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getRowIndex(), </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getColumnIndex() ) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ValueEval v2 = OperandResolver.getSingleValue( args[1], </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getRowIndex(), </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getColumnIndex() ) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ValueEval v3 = OperandResolver.getSingleValue( args[2], </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getRowIndex(), </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getColumnIndex() ) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> principal = OperandResolver.coerceValueToDouble( v1 ) ; </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> rate = OperandResolver.coerceValueToDouble( v2 ) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> years = OperandResolver.coerceValueToDouble( v3 ) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>The first thing we do is check the number of arguments being passed since there is no sense
in attempting to go further if you are missing critical information.</p>
<p>Next we declare our variables, in our case we need variables for:</p>
@@ -443,120 +645,346 @@ public ValueEval evaluate( ValueEval[] a
<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>
-<pre class="code">
-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 ;
-}
- </pre>
+<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">public double calculateMortgagePayment( double p, double r, double y ) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> double i = r / 12 ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> double n = y * 12 ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> double principalAndInterest = </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1)) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> return principalAndInterest ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">} </span>
+</div>
+<div class="codeline">
+<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>
-<pre class="code">
- result = calculateMortgagePayment( principal, rate, years ) ;
- </pre>
+<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>
+</div>
+<div class="codeline">
+<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>
-<pre class="code">
-private void checkValue(double result) throws EvaluationException {
- if (Double.isNaN(result) || Double.isInfinite(result)) {
- throw new EvaluationException(ErrorEval.NUM_ERROR);
- }
-}
- </pre>
+<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">private void checkValue(double result) throws EvaluationException {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> if (Double.isNaN(result) || Double.isInfinite(result)) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> throw new EvaluationException(ErrorEval.NUM_ERROR);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">} </span>
+</div>
+<div class="codeline">
+<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>
-<pre class="code">
- checkValue(result);
-
- } catch (EvaluationException e) {
- e.printStackTrace() ;
- return e.getErrorEval();
- }
-
- return new NumberEval( result ) ;
- </pre>
+<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">checkValue(result);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">tch (EvaluationException e) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">e.printStackTrace() ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">return e.getErrorEval();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">rn new NumberEval( result ) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+</div>
<p>So the whole class would be as follows:</p>
-<pre class="code">
-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 function to calculate principal and interest.
- *
- * @author Jon Svede
- *
- */
-public class CalculateMortgage implements FreeRefFunction {
-
- @Override
- public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
- if (args.length != 3) {
- return ErrorEval.VALUE_INVALID;
- }
-
- double principal, rate, years, result;
- try {
- 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() ) ;
-
- principal = OperandResolver.coerceValueToDouble( v1 ) ;
- rate = OperandResolver.coerceValueToDouble( v2 ) ;
- years = OperandResolver.coerceValueToDouble( v3 ) ;
-
- result = calculateMortgagePayment( principal, rate, years ) ;
-
- checkValue(result);
-
- } catch (EvaluationException e) {
- e.printStackTrace() ;
- return e.getErrorEval();
- }
-
- return new NumberEval( result ) ;
- }
-
- public double calculateMortgagePayment( double p, double r, double y ) {
- double i = r / 12 ;
- double n = y * 12 ;
-
- //M = P [ i(1 + i)n ] / [ (1 + i)n - 1]
- 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>
- */
- static final void checkValue(double result) throws EvaluationException {
- if (Double.isNaN(result) || Double.isInfinite(result)) {
- throw new EvaluationException(ErrorEval.NUM_ERROR);
- }
- }
-
-}
-
- </pre>
+<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">import org.apache.poi.ss.formula.OperationEvaluationContext ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.formula.eval.ErrorEval ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.formula.eval.EvaluationException ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.formula.eval.NumberEval ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.formula.eval.OperandResolver ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">import org.apache.poi.ss.formula.eval.ValueEval ;</span>
+</div>
+<div class="codeline">
+<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"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">/**</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * A simple function to calculate principal and interest.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * @author Jon Svede</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> *</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> */</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">public class CalculateMortgage implements FreeRefFunction {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> @Override</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> if (args.length != 3) { </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> return ErrorEval.VALUE_INVALID;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> double principal, rate, years, result;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> try {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ValueEval v1 = OperandResolver.getSingleValue( args[0], </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getRowIndex(), </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getColumnIndex() ) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ValueEval v2 = OperandResolver.getSingleValue( args[1], </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getRowIndex(), </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getColumnIndex() ) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ValueEval v3 = OperandResolver.getSingleValue( args[2], </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getRowIndex(), </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ec.getColumnIndex() ) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> principal = OperandResolver.coerceValueToDouble( v1 ) ; </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> rate = OperandResolver.coerceValueToDouble( v2 ) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> years = OperandResolver.coerceValueToDouble( v3 ) ;</span>
+</div>
+<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>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> checkValue(result);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> } catch (EvaluationException e) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> e.printStackTrace() ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> return e.getErrorEval();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> return new NumberEval( result ) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> public double calculateMortgagePayment( double p, double r, double y ) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> double i = r / 12 ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> double n = y * 12 ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> //M = P [ i(1 + i)n ] / [ (1 + i)n - 1] </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> double principalAndInterest = </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1)) ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> return principalAndInterest ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> /**</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * Excel does not support infinities and NaNs, rather, it gives a #NUM! error in these cases</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> */</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> static final void checkValue(double result) throws EvaluationException {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> if (Double.isNaN(result) || Double.isInfinite(result)) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> throw new EvaluationException(ErrorEval.NUM_ERROR);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<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>
<a name="Registering+Your+Function"></a>
<h3 class="boxed">Registering Your Function</h3>
@@ -576,92 +1004,266 @@ The UDFFinder manages FreeRefFunctions w
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>
-<pre class="code">
-String[] functionNames = { "calculatePayment" } ;
-FreeRefFunction[] functionImpls = { new CalculateMortgage() } ;
-
-UDFFinder udfs = new DefaultUDFFinder( functionNames, functionImpls ) ;
-UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ;
- </pre>
+<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">String[] functionNames = { "calculatePayment" } ;</span>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</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>
-<pre class="code">
-workbook.addToolPack(udfToolpack);
- </pre>
+<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">workbook.addToolPack(udfToolpack);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>So now the whole class will look like this:</p>
-<pre class="code">
-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.Cell ;
-import org.apache.poi.ss.usermodel.CellValue ;
-import org.apache.poi.ss.usermodel.Row ;
-import org.apache.poi.ss.usermodel.Sheet ;
-import org.apache.poi.ss.usermodel.Workbook ;
-import org.apache.poi.ss.usermodel.WorkbookFactory ;
-import org.apache.poi.ss.util.CellReference ;
-
-public class Evaluator {
-
- public static void main( String[] args ) {
-
- 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);
-
- String[] functionNames = { "calculatePayment" } ;
- FreeRefFunction[] functionImpls = { new CalculateMortgage() } ;
-
- UDFFinder udfs = new DefaultUDFFinder( functionNames, functionImpls ) ;
- UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ;
-
- 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();
- }
- }
-}
-
- </pre>
+<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">mport java.io.File ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mport java.io.FileInputStream ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mport java.io.FileNotFoundException ;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mport 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>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mport 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>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mport 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>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mport 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>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mport 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>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mport 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>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mport 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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<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>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<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>
-<pre class="code">Evaluator mortgage-calculation.xls Sheet1!B4</pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Evaluator mortgage-calculation.xls Sheet1!B4</span>
+</div>
+</div>
<p>which prints the following output in the console:</p>
-<pre class="code">
-fileName: mortgage-calculation.xls
-cell: Sheet1!B4
-returns value: org.apache.poi.ss.usermodel.CellValue [790.7936267415464]
- </pre>
+<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">fileName: mortgage-calculation.xls</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell: Sheet1!B4</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">returns value: org.apache.poi.ss.usermodel.CellValue [790.7936267415464]</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</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/ss/examples/formula">src/examples/src/org/apache/poi/ss/examples/formula</a> folder in the source.</p>
</div>
Modified: poi/site/publish/devel/guidelines.html
URL: http://svn.apache.org/viewvc/poi/site/publish/devel/guidelines.html?rev=1867502&r1=1867501&r2=1867502&view=diff
==============================================================================
--- poi/site/publish/devel/guidelines.html (original)
+++ poi/site/publish/devel/guidelines.html Tue Sep 24 23:26:00 2019
@@ -351,9 +351,17 @@ document.write("Last Published: " + docu
<a name="Approach+1+-+use+Ant"></a>
<h3 class="boxed">Approach 1 - use Ant</h3>
<p>Use Ant to generate a patch file to POI: </p>
-<pre class="code">
- ant -f patch.xml
- </pre>
+<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">ant -f patch.xml</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+</div>
<p>
This will create a file named <span class="codefrag">patch.tar.gz</span> that will contain a unified diff of files that have been modified
and also include files that have been added. Review the file for completeness and correctness. This approach
@@ -364,9 +372,17 @@ document.write("Last Published: " + docu
To apply a previously generated <span class="codefrag">patch.tar.gz</span> file to a clean subversion checkout, use the following command.
It will unpack the tarball and add new files to the subversion working copy.
</p>
-<pre class="code">
- ant -f patch.xml apply
- </pre>
+<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">ant -f patch.xml apply</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+</div>
<a name="Approach+2+-+the+manual+way"></a>
<h3 class="boxed">Approach 2 - the manual way</h3>
<p>
@@ -376,31 +392,83 @@ document.write("Last Published: " + docu
</p>
<p>If you use a unix shell, you may find the following following
sequence of commands useful for building the files to attach.</p>
-<pre class="code">
- # run this in the root of the checkout, i.e. the directory holding
- # build.xml and poi.pom
-
- # build the directory to hold new files
- mkdir /tmp/poi-patch/
- mkdir /tmp/poi-patch/new-files/
-
- # get changes to existing files
- svn diff > /tmp/poi-patch/diff.txt
-
- # capture any new files, as svn diff won't include them
- # preserve the path
- svn status | grep "^\?" | awk '{printf "cp --parents %s /tmp/poi-patch/new-files/\n", $2 }' | sh -s
-
- # tar up the new files
- cd /tmp/poi-patch/new-files/
- tar jcvf ../new-files.tar.bz2
- cd ..
-
- # upload these to bugzilla
- echo "please upload to bugzilla:"
- echo " /tmp/poi-patch/diff.txt"
- echo " /tmp/poi-patch/new-files.tar.bz2"
- </pre>
+<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"># run this in the root of the checkout, i.e. the directory holding</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"># build.xml and poi.pom</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"># build the directory to hold new files</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mkdir /tmp/poi-patch/</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mkdir /tmp/poi-patch/new-files/</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"># get changes to existing files</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">svn diff > /tmp/poi-patch/diff.txt</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"># capture any new files, as svn diff won't include them</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"># preserve the path</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">svn status | grep "^\?" | awk '{printf "cp --parents %s /tmp/poi-patch/new-files/\n", $2 }' | sh -s</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"># tar up the new files</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cd /tmp/poi-patch/new-files/</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">tar jcvf ../new-files.tar.bz2</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cd ..</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"># upload these to bugzilla</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">echo "please upload to bugzilla:"</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">echo " /tmp/poi-patch/diff.txt"</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">echo " /tmp/poi-patch/new-files.tar.bz2"</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="Approach+3+-+the+git+way"></a>
<h3 class="boxed">Approach 3 - the git way</h3>
<p>
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org