You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2015/07/14 17:09:22 UTC
svn commit: r1690970 - in /poi/site: publish/spreadsheet/eval.html
src/documentation/content/xdocs/spreadsheet/eval.xml
Author: nick
Date: Tue Jul 14 15:09:22 2015
New Revision: 1690970
URL: http://svn.apache.org/r1690970
Log:
External (cross-workbook) reference docs
Modified:
poi/site/publish/spreadsheet/eval.html
poi/site/src/documentation/content/xdocs/spreadsheet/eval.xml
Modified: poi/site/publish/spreadsheet/eval.html
URL: http://svn.apache.org/viewvc/poi/site/publish/spreadsheet/eval.html?rev=1690970&r1=1690969&r2=1690970&view=diff
==============================================================================
--- poi/site/publish/spreadsheet/eval.html (original)
+++ poi/site/publish/spreadsheet/eval.html Tue Jul 14 15:09:22 2015
@@ -510,6 +510,58 @@ for(int sheetNum = 0; sheetNum < wb.g
+<a name="external"></a>
+ <a name="External+%28Cross-Workbook%29+references"></a>
+<div class="h3">
+<h3>External (Cross-Workbook) references</h3>
+</div>
+
+<p>It is possible for a formula in an Excel spreadsheet to
+ refer to a Named Range or Cell in a different workbook.
+ These cross-workbook references are normally called <em>External
+ References</em>. These are formulas which look something like:</p>
+
+<pre class="code">
+ =SUM([Finances.xlsx]Numbers!D10:D25)
+ =SUM('C:\Data\[Finances.xlsx]Numbers'!D10:D25)
+ =SUM([Finances.xlsx]Range20)
+ </pre>
+
+<p>If you don't have access to these other workbooks, then you
+ should call
+ <a href="../apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setIgnoreMissingWorkbooks(boolean)">setIgnoreMissingWorkbooks(true)</a>
+ to tell the Formula Evaluator to skip evaluating any external
+ references it can't look up.</p>
+
+<p>In order for POI to be able to evaluate external references, it
+ needs access to the workbooks in question. As these don't necessarily
+ have the same names on your system as in the workbook, you need to
+ give POI a map of external references to open workbooks, through
+ the
+ <a href="../apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setupReferencedWorkbooks(java.util.Map)">setupReferencedWorkbooks(java.util.Map<java.lang.String,FormulaEvaluator> workbooks)</a>
+ method. You should normally do something like:</p>
+
+<pre class="code">
+// Create a FormulaEvaluator to use
+FormulaEvaluator mainWorkbookEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
+
+// Track the workbook references
+Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
+// Add this workbook
+workbooks.put("report.xlsx", mainWorkbookEvaluator);
+// Add two others
+workbooks.put("input.xls", WorkbookFactory.create("c:\temp\input22.xls").getCreationHelper().createFormulaEvaluator());
+workbooks.put("lookups.xlsx", WorkbookFactory.create("/home/poi/data/tmp-lookups.xlsx").getCreationHelper().createFormulaEvaluator());
+
+// Attach them
+mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
+
+// Evaluate
+mainWorkbookEvaluator.evaluateAll();
+ </pre>
+
+
+
<a name="Performance"></a>
<a name="Performance+Notes"></a>
<div class="h3">
Modified: poi/site/src/documentation/content/xdocs/spreadsheet/eval.xml
URL: http://svn.apache.org/viewvc/poi/site/src/documentation/content/xdocs/spreadsheet/eval.xml?rev=1690970&r1=1690969&r2=1690970&view=diff
==============================================================================
--- poi/site/src/documentation/content/xdocs/spreadsheet/eval.xml (original)
+++ poi/site/src/documentation/content/xdocs/spreadsheet/eval.xml Tue Jul 14 15:09:22 2015
@@ -286,6 +286,49 @@ for(int sheetNum = 0; sheetNum < wb.g
</source>
</section>
+ <anchor id="external"/>
+ <section><title>External (Cross-Workbook) references</title>
+ <p>It is possible for a formula in an Excel spreadsheet to
+ refer to a Named Range or Cell in a different workbook.
+ These cross-workbook references are normally called <em>External
+ References</em>. These are formulas which look something like:</p>
+ <source>
+ =SUM([Finances.xlsx]Numbers!D10:D25)
+ =SUM('C:\Data\[Finances.xlsx]Numbers'!D10:D25)
+ =SUM([Finances.xlsx]Range20)
+ </source>
+ <p>If you don't have access to these other workbooks, then you
+ should call
+ <link href="../apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setIgnoreMissingWorkbooks(boolean)">setIgnoreMissingWorkbooks(true)</link>
+ to tell the Formula Evaluator to skip evaluating any external
+ references it can't look up.</p>
+ <p>In order for POI to be able to evaluate external references, it
+ needs access to the workbooks in question. As these don't necessarily
+ have the same names on your system as in the workbook, you need to
+ give POI a map of external references to open workbooks, through
+ the
+ <link href="../apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setupReferencedWorkbooks(java.util.Map)">setupReferencedWorkbooks(java.util.Map<java.lang.String,FormulaEvaluator> workbooks)</link>
+ method. You should normally do something like:</p>
+ <source>
+// Create a FormulaEvaluator to use
+FormulaEvaluator mainWorkbookEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
+
+// Track the workbook references
+Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
+// Add this workbook
+workbooks.put("report.xlsx", mainWorkbookEvaluator);
+// Add two others
+workbooks.put("input.xls", WorkbookFactory.create("c:\temp\input22.xls").getCreationHelper().createFormulaEvaluator());
+workbooks.put("lookups.xlsx", WorkbookFactory.create("/home/poi/data/tmp-lookups.xlsx").getCreationHelper().createFormulaEvaluator());
+
+// Attach them
+mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
+
+// Evaluate
+mainWorkbookEvaluator.evaluateAll();
+ </source>
+ </section>
+
<anchor id="Performance"/>
<section><title>Performance Notes</title>
<ul>
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org