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 2011/05/09 16:04:21 UTC
svn commit: r1101033 - in /poi/trunk/src:
documentation/content/xdocs/spreadsheet/
java/org/apache/poi/hssf/usermodel/ java/org/apache/poi/ss/formula/
java/org/apache/poi/ss/usermodel/ java/org/apache/poi/ss/util/
ooxml/java/org/apache/poi/xssf/usermodel/
Author: nick
Date: Mon May 9 14:04:21 2011
New Revision: 1101033
URL: http://svn.apache.org/viewvc?rev=1101033&view=rev
Log:
As Java doesn't allow static methods on interfaces, allow HSSFFormulaEvaluator or XSSFFormulaEvaluator to generically recalculate all formulas in a workbook
Modified:
poi/trunk/src/documentation/content/xdocs/spreadsheet/eval.xml
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java
poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java
Modified: poi/trunk/src/documentation/content/xdocs/spreadsheet/eval.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/spreadsheet/eval.xml?rev=1101033&r1=1101032&r2=1101033&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/spreadsheet/eval.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/spreadsheet/eval.xml Mon May 9 14:04:21 2011
@@ -197,7 +197,6 @@ if (cell!=null) {
<anchor id="EvaluateAll"/>
<section><title>Re-calculating all formulas in a Workbook</title>
<source>
-
FileInputStream fis = new FileInputStream("/somepath/test.xls");
Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
@@ -212,6 +211,11 @@ for(int sheetNum = 0; sheetNum < wb.g
}
}
</source>
+
+ <p>Alternately, if you know which of HSSF or XSSF you're working
+ with, then you can call the static
+ <strong>evaluateAllFormulaCells</strong> method on the appropriate
+ HSSFFormulaEvaluator or XSSFFormulaEvaluator class.</p>
</section>
</section>
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java?rev=1101033&r1=1101032&r2=1101033&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java Mon May 9 14:04:21 2011
@@ -17,21 +17,21 @@
package org.apache.poi.hssf.usermodel;
-import java.util.Iterator;
-
+import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment;
+import org.apache.poi.ss.formula.IStabilityClassifier;
+import org.apache.poi.ss.formula.WorkbookEvaluator;
import org.apache.poi.ss.formula.eval.BoolEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.udf.UDFFinder;
-import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment;
-import org.apache.poi.ss.formula.IStabilityClassifier;
-import org.apache.poi.ss.formula.WorkbookEvaluator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
/**
* Evaluates formula cells.<p/>
@@ -46,6 +46,7 @@ import org.apache.poi.ss.usermodel.Row;
public class HSSFFormulaEvaluator implements FormulaEvaluator {
private WorkbookEvaluator _bookEvaluator;
+ private HSSFWorkbook _book;
/**
* @deprecated (Sep 2008) HSSFSheet parameter is ignored
@@ -55,9 +56,11 @@ public class HSSFFormulaEvaluator implem
if (false) {
sheet.toString(); // suppress unused parameter compiler warning
}
+ this._book = workbook;
}
public HSSFFormulaEvaluator(HSSFWorkbook workbook) {
this(workbook, null);
+ this._book = workbook;
}
/**
* @param stabilityClassifier used to optimise caching performance. Pass <code>null</code>
@@ -296,21 +299,52 @@ public class HSSFFormulaEvaluator implem
* cells, and calling evaluateFormulaCell on each one.
*/
public static void evaluateAllFormulaCells(HSSFWorkbook wb) {
- HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
- for(int i=0; i<wb.getNumberOfSheets(); i++) {
- HSSFSheet sheet = wb.getSheetAt(i);
-
- for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
- Row r = rit.next();
-
- for (Iterator<Cell> cit = r.cellIterator(); cit.hasNext();) {
- Cell c = cit.next();
- if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
- evaluator.evaluateFormulaCell(c);
- }
- }
- }
+ evaluateAllFormulaCells(wb, new HSSFFormulaEvaluator(wb));
}
+
+ /**
+ * Loops over all cells in all sheets of the supplied
+ * workbook.
+ * For cells that contain formulas, their formulas are
+ * evaluated, and the results are saved. These cells
+ * remain as formula cells.
+ * For cells that do not contain formulas, no changes
+ * are made.
+ * This is a helpful wrapper around looping over all
+ * cells, and calling evaluateFormulaCell on each one.
+ */
+ public static void evaluateAllFormulaCells(Workbook wb) {
+ FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+ evaluateAllFormulaCells(wb, evaluator);
+ }
+ private static void evaluateAllFormulaCells(Workbook wb, FormulaEvaluator evaluator) {
+ for(int i=0; i<wb.getNumberOfSheets(); i++) {
+ Sheet sheet = wb.getSheetAt(i);
+
+ for(Row r : sheet) {
+ for (Cell c : r) {
+ if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
+ evaluator.evaluateFormulaCell(c);
+ }
+ }
+ }
+ }
+ }
+
+ /**
+ * Loops over all cells in all sheets of the supplied
+ * workbook.
+ * For cells that contain formulas, their formulas are
+ * evaluated, and the results are saved. These cells
+ * remain as formula cells.
+ * For cells that do not contain formulas, no changes
+ * are made.
+ * This is a helpful wrapper around looping over all
+ * cells, and calling evaluateFormulaCell on each one.
+ */
+ public void evaluateAll() {
+ evaluateAllFormulaCells(_book, this);
+ }
/**
* Returns a CellValue wrapper around the supplied ValueEval instance.
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java?rev=1101033&r1=1101032&r2=1101033&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java Mon May 9 14:04:21 2011
@@ -202,7 +202,7 @@ public final class WorkbookEvaluator {
int sheetIndex = getSheetIndex(cell.getSheet());
_cache.notifyDeleteCell(_workbookIx, sheetIndex, cell);
}
-
+
private int getSheetIndex(EvaluationSheet sheet) {
Integer result = _sheetIndexesBySheet.get(sheet);
if (result == null) {
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java?rev=1101033&r1=1101032&r2=1101033&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java Mon May 9 14:04:21 2011
@@ -57,6 +57,16 @@ public interface FormulaEvaluator {
void notifyUpdateCell(Cell cell);
/**
+ * Loops over all cells in all sheets of the associated workbook.
+ * For cells that contain formulas, their formulas are evaluated,
+ * and the results are saved. These cells remain as formula cells.
+ * For cells that do not contain formulas, no changes are made.
+ * This is a helpful wrapper around looping over all cells, and
+ * calling evaluateFormulaCell on each one.
+ */
+ void evaluateAll();
+
+ /**
* If cell contains a formula, the formula is evaluated and returned,
* else the CellValue simply copies the appropriate cell value from
* the cell and also its cell type. This method should be preferred over
Modified: poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java?rev=1101033&r1=1101032&r2=1101033&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java Mon May 9 14:04:21 2011
@@ -61,6 +61,7 @@ public class SheetUtil {
public void notifyUpdateCell(Cell cell) {}
public CellValue evaluate(Cell cell) {return null; }
public Cell evaluateInCell(Cell cell) { return null; }
+ public void evaluateAll() {}
public int evaluateFormulaCell(Cell cell) {
return cell.getCachedFormulaResultType();
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java?rev=1101033&r1=1101032&r2=1101033&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java Mon May 9 14:04:21 2011
@@ -17,21 +17,19 @@
package org.apache.poi.xssf.usermodel;
-import java.util.Iterator;
-
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.ss.formula.IStabilityClassifier;
+import org.apache.poi.ss.formula.WorkbookEvaluator;
import org.apache.poi.ss.formula.eval.BoolEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.udf.UDFFinder;
-import org.apache.poi.ss.formula.IStabilityClassifier;
-import org.apache.poi.ss.formula.WorkbookEvaluator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
/**
* Evaluates formula cells.<p/>
@@ -46,6 +44,7 @@ import org.apache.poi.ss.usermodel.Sheet
public class XSSFFormulaEvaluator implements FormulaEvaluator {
private WorkbookEvaluator _bookEvaluator;
+ private XSSFWorkbook _book;
public XSSFFormulaEvaluator(XSSFWorkbook workbook) {
this(workbook, null, null);
@@ -59,9 +58,11 @@ public class XSSFFormulaEvaluator implem
@Deprecated
public XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier) {
_bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(workbook), stabilityClassifier, null);
+ _book = workbook;
}
private XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) {
_bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(workbook), stabilityClassifier, udfFinder);
+ _book = workbook;
}
/**
@@ -234,21 +235,22 @@ public class XSSFFormulaEvaluator implem
* cells, and calling evaluateFormulaCell on each one.
*/
public static void evaluateAllFormulaCells(XSSFWorkbook wb) {
- XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(wb);
- for(int i=0; i<wb.getNumberOfSheets(); i++) {
- Sheet sheet = wb.getSheetAt(i);
-
- for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
- Row r = rit.next();
-
- for (Iterator cit = r.cellIterator(); cit.hasNext();) {
- XSSFCell c = (XSSFCell) cit.next();
- if (c.getCellType() == XSSFCell.CELL_TYPE_FORMULA)
- evaluator.evaluateFormulaCell(c);
- }
- }
- }
+ HSSFFormulaEvaluator.evaluateAllFormulaCells((Workbook)wb);
}
+ /**
+ * Loops over all cells in all sheets of the supplied
+ * workbook.
+ * For cells that contain formulas, their formulas are
+ * evaluated, and the results are saved. These cells
+ * remain as formula cells.
+ * For cells that do not contain formulas, no changes
+ * are made.
+ * This is a helpful wrapper around looping over all
+ * cells, and calling evaluateFormulaCell on each one.
+ */
+ public void evaluateAll() {
+ HSSFFormulaEvaluator.evaluateAllFormulaCells(_book);
+ }
/**
* Returns a CellValue wrapper around the supplied ValueEval instance.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org