poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From n...@apache.org
Subject svn commit: r1690970 - in /poi/site: publish/spreadsheet/eval.html src/documentation/content/xdocs/spreadsheet/eval.xml
Date Tue, 14 Jul 2015 15:09:22 GMT
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&lt;java.lang.String,FormulaEvaluator&gt;
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&lt;String,FormulaEvaluator&gt; workbooks = new HashMap&lt;String, FormulaEvaluator&gt;();
+// 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 &lt; 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&lt;java.lang.String,FormulaEvaluator&gt;
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&lt;String,FormulaEvaluator> workbooks = new HashMap&lt;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


Mime
View raw message