diff options
Diffstat (limited to 'src/java')
7 files changed, 277 insertions, 253 deletions
diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java b/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java index 00eb86e943..04aff3ba27 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java @@ -31,14 +31,14 @@ import org.apache.poi.hssf.record.formula.eval.StringEval; import org.apache.poi.hssf.record.formula.functions.CountUtils.I_MatchPredicate; /** - * Implementation for the function COUNTIF<p/> - * - * Syntax: COUNTIF ( range, criteria ) + * Implementation for the function COUNTIF + * <p> + * Syntax: COUNTIF ( range, criteria ) * <table border="0" cellpadding="1" cellspacing="0" summary="Parameter descriptions"> * <tr><th>range </th><td>is the range of cells to be counted based on the criteria</td></tr> * <tr><th>criteria</th><td>is used to determine which cells to count</td></tr> * </table> - * <p/> + * </p> * * @author Josh Micich */ diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Errortype.java b/src/java/org/apache/poi/hssf/record/formula/functions/Errortype.java index dd72eb5c79..abd32c94d0 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Errortype.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Errortype.java @@ -25,13 +25,13 @@ import org.apache.poi.hssf.record.formula.eval.OperandResolver; import org.apache.poi.hssf.usermodel.HSSFErrorConstants; /** - * Implementation for the ERROR.TYPE() Excel function.<p/> - * + * Implementation for the ERROR.TYPE() Excel function. + * <p> * <b>Syntax:</b><br/> - * <b>ERROR.TYPE</b>(<b>errorValue</b>)<p/> - * + * <b>ERROR.TYPE</b>(<b>errorValue</b>)</p> + * <p> * Returns a number corresponding to the error type of the supplied argument.<p/> - * + * <p> * <table border="1" cellpadding="1" cellspacing="1" summary="Return values for ERROR.TYPE()"> * <tr><td>errorValue</td><td>Return Value</td></tr> * <tr><td>#NULL!</td><td>1</td></tr> @@ -46,7 +46,8 @@ import org.apache.poi.hssf.usermodel.HSSFErrorConstants; * * Note - the results of ERROR.TYPE() are different to the constants defined in * <tt>HSSFErrorConstants</tt>. - * + * </p> + * * @author Josh Micich */ public final class Errortype implements Function { diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Index.java b/src/java/org/apache/poi/hssf/record/formula/functions/Index.java index 1c149dbdfc..1c34322256 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Index.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Index.java @@ -26,7 +26,8 @@ import org.apache.poi.hssf.record.formula.eval.RefEval; import org.apache.poi.hssf.record.formula.eval.ValueEval; /** - * Implementation for the Excel function INDEX<p/> + * Implementation for the Excel function INDEX + * <p> * * Syntax : <br/> * INDEX ( reference, row_num[, column_num [, area_num]])</br> @@ -38,7 +39,7 @@ import org.apache.poi.hssf.record.formula.eval.ValueEval; * <tr><th>column_num</th><td>selects column within the array or area reference. default is 1</td></tr> * <tr><th>area_num</th><td>used when reference is a union of areas</td></tr> * </table> - * <p/> + * </p> * * @author Josh Micich */ diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Sumproduct.java b/src/java/org/apache/poi/hssf/record/formula/functions/Sumproduct.java index 1ed56e4718..c437ba5894 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Sumproduct.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Sumproduct.java @@ -31,7 +31,7 @@ import org.apache.poi.hssf.record.formula.eval.ValueEval; /** - * Implementation for the Excel function SUMPRODUCT<p/> + * Implementation for the Excel function SUMPRODUCT<p> * * Syntax : <br/> * SUMPRODUCT ( array1[, array2[, array3[, ...]]]) @@ -49,7 +49,7 @@ import org.apache.poi.hssf.record.formula.eval.ValueEval; * A<b>n</b><sub>(<b>i</b>,<b>j</b>)</sub> * ) * ) - * + * </p> * @author Josh Micich */ public final class Sumproduct implements Function { diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java index fd51b43857..82277661ae 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java @@ -671,9 +671,7 @@ public class HSSFCell implements Cell { * get the value of the cell as a string - for numeric cells we throw an exception. * For blank cells we return an empty string. * For formulaCells that are not string Formulas, we return empty String - * @deprecated Use the HSSFRichTextString return */ - public String getStringCellValue() { HSSFRichTextString str = getRichStringCellValue(); diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCreationHelper.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCreationHelper.java index 6986ef94b5..0eabcfa706 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFCreationHelper.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCreationHelper.java @@ -44,4 +44,14 @@ public class HSSFCreationHelper implements CreationHelper { public HSSFHyperlink createHyperlink(int type) { return new HSSFHyperlink(type); } + + /** + * Creates a HSSFFormulaEvaluator, the object that evaluates formula cells. + * + * @return a HSSFFormulaEvaluator instance + */ + public HSSFFormulaEvaluator createFormulaEvaluator(){ + return new HSSFFormulaEvaluator(workbook); + } + } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java index 0ca8d5723d..845284c456 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java @@ -28,6 +28,7 @@ import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment; 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;
/**
* Evaluates formula cells.<p/>
@@ -39,255 +40,268 @@ import org.apache.poi.ss.usermodel.CellValue; * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
* @author Josh Micich
*/
-public class HSSFFormulaEvaluator /* almost implements FormulaEvaluator */ {
+public class HSSFFormulaEvaluator implements FormulaEvaluator {
- private WorkbookEvaluator _bookEvaluator;
+ private WorkbookEvaluator _bookEvaluator;
- /**
- * @deprecated (Sep 2008) HSSFSheet parameter is ignored
- */
- public HSSFFormulaEvaluator(HSSFSheet sheet, HSSFWorkbook workbook) {
- this(workbook);
- if (false) {
- sheet.toString(); // suppress unused parameter compiler warning
- }
- }
- public HSSFFormulaEvaluator(HSSFWorkbook workbook) {
- _bookEvaluator = new WorkbookEvaluator(HSSFEvaluationWorkbook.create(workbook));
- }
-
- /**
- * Coordinates several formula evaluators together so that formulas that involve external
- * references can be evaluated.
- * @param workbookNames the simple file names used to identify the workbooks in formulas
- * with external links (for example "MyData.xls" as used in a formula "[MyData.xls]Sheet1!A1")
- * @param evaluators all evaluators for the full set of workbooks required by the formulas.
- */
- public static void setupEnvironment(String[] workbookNames, HSSFFormulaEvaluator[] evaluators) {
- WorkbookEvaluator[] wbEvals = new WorkbookEvaluator[evaluators.length];
- for (int i = 0; i < wbEvals.length; i++) {
- wbEvals[i] = evaluators[i]._bookEvaluator;
- }
- CollaboratingWorkbooksEnvironment.setup(workbookNames, wbEvals);
- }
+ /**
+ * @deprecated (Sep 2008) HSSFSheet parameter is ignored
+ */
+ public HSSFFormulaEvaluator(HSSFSheet sheet, HSSFWorkbook workbook) {
+ this(workbook);
+ if (false) {
+ sheet.toString(); // suppress unused parameter compiler warning
+ }
+ }
+ public HSSFFormulaEvaluator(HSSFWorkbook workbook) {
+ _bookEvaluator = new WorkbookEvaluator(HSSFEvaluationWorkbook.create(workbook));
+ }
- /**
- * Does nothing
- * @deprecated (Aug 2008) - not needed, since the current row can be derived from the cell
- */
- public void setCurrentRow(HSSFRow row) {
- // do nothing
- if (false) {
- row.getClass(); // suppress unused parameter compiler warning
- }
- }
+ /**
+ * Coordinates several formula evaluators together so that formulas that involve external
+ * references can be evaluated.
+ * @param workbookNames the simple file names used to identify the workbooks in formulas
+ * with external links (for example "MyData.xls" as used in a formula "[MyData.xls]Sheet1!A1")
+ * @param evaluators all evaluators for the full set of workbooks required by the formulas.
+ */
+ public static void setupEnvironment(String[] workbookNames, HSSFFormulaEvaluator[] evaluators) {
+ WorkbookEvaluator[] wbEvals = new WorkbookEvaluator[evaluators.length];
+ for (int i = 0; i < wbEvals.length; i++) {
+ wbEvals[i] = evaluators[i]._bookEvaluator;
+ }
+ CollaboratingWorkbooksEnvironment.setup(workbookNames, wbEvals);
+ }
- /**
- * Should be called whenever there are major changes (e.g. moving sheets) to input cells
- * in the evaluated workbook. If performance is not critical, a single call to this method
- * may be used instead of many specific calls to the notify~ methods.
- *
- * Failure to call this method after changing cell values will cause incorrect behaviour
- * of the evaluate~ methods of this class
- */
- public void clearAllCachedResultValues() {
- _bookEvaluator.clearAllCachedResultValues();
- }
- /**
- * Should be called to tell the cell value cache that the specified (value or formula) cell
- * has changed.
- * Failure to call this method after changing cell values will cause incorrect behaviour
- * of the evaluate~ methods of this class
- */
- public void notifyUpdateCell(HSSFCell cell) {
- _bookEvaluator.notifyUpdateCell(new HSSFEvaluationCell(cell));
- }
- /**
- * Should be called to tell the cell value cache that the specified cell has just been
- * deleted.
- * Failure to call this method after changing cell values will cause incorrect behaviour
- * of the evaluate~ methods of this class
- */
- public void notifyDeleteCell(HSSFCell cell) {
- _bookEvaluator.notifyDeleteCell(new HSSFEvaluationCell(cell));
- }
+ /**
+ * Does nothing
+ * @deprecated (Aug 2008) - not needed, since the current row can be derived from the cell
+ */
+ public void setCurrentRow(HSSFRow row) {
+ // do nothing
+ if (false) {
+ row.getClass(); // suppress unused parameter compiler warning
+ }
+ }
- /**
- * 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
- * evaluateInCell() when the call should not modify the contents of the
- * original cell.
- *
- * @param cell may be <code>null</code> signifying that the cell is not present (or blank)
- * @return <code>null</code> if the supplied cell is <code>null</code> or blank
- */
- public CellValue evaluate(Cell cell) {
- if (cell == null) {
- return null;
- }
+ /**
+ * Should be called whenever there are major changes (e.g. moving sheets) to input cells
+ * in the evaluated workbook. If performance is not critical, a single call to this method
+ * may be used instead of many specific calls to the notify~ methods.
+ *
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void clearAllCachedResultValues() {
+ _bookEvaluator.clearAllCachedResultValues();
+ }
+ /**
+ * Should be called to tell the cell value cache that the specified (value or formula) cell
+ * has changed.
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void notifyUpdateCell(HSSFCell cell) {
+ _bookEvaluator.notifyUpdateCell(new HSSFEvaluationCell(cell));
+ }
+ /**
+ * Should be called to tell the cell value cache that the specified cell has just been
+ * deleted.
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void notifyDeleteCell(HSSFCell cell) {
+ _bookEvaluator.notifyDeleteCell(new HSSFEvaluationCell(cell));
+ }
+ public void notifyDeleteCell(Cell cell) {
+ _bookEvaluator.notifyDeleteCell(new HSSFEvaluationCell((HSSFCell)cell));
+ }
- switch (cell.getCellType()) {
- case HSSFCell.CELL_TYPE_BOOLEAN:
- return CellValue.valueOf(cell.getBooleanCellValue());
- case HSSFCell.CELL_TYPE_ERROR:
- return CellValue.getError(cell.getErrorCellValue());
- case HSSFCell.CELL_TYPE_FORMULA:
- return evaluateFormulaCellValue(cell);
- case HSSFCell.CELL_TYPE_NUMERIC:
- return new CellValue(cell.getNumericCellValue());
- case HSSFCell.CELL_TYPE_STRING:
- return new CellValue(cell.getRichStringCellValue().getString());
- case HSSFCell.CELL_TYPE_BLANK:
- return null;
- }
- throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")");
- }
+ /**
+ * Should be called to tell the cell value cache that the specified (value or formula) cell
+ * has changed.
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void notifySetFormula(Cell cell) {
+ _bookEvaluator.notifyUpdateCell(new HSSFEvaluationCell((HSSFCell)cell));
+ }
+ /**
+ * 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
+ * evaluateInCell() when the call should not modify the contents of the
+ * original cell.
+ *
+ * @param cell may be <code>null</code> signifying that the cell is not present (or blank)
+ * @return <code>null</code> if the supplied cell is <code>null</code> or blank
+ */
+ public CellValue evaluate(Cell cell) {
+ if (cell == null) {
+ return null;
+ }
- /**
- * If cell contains formula, it evaluates the formula, and saves the result of the formula. The
- * cell remains as a formula cell. If the cell does not contain formula, this method returns -1
- * and leaves the cell unchanged.
- *
- * Note that the type of the <em>formula result</em> is returned, so you know what kind of
- * cached formula result is also stored with the formula.
- * <pre>
- * int evaluatedCellType = evaluator.evaluateFormulaCell(cell);
- * </pre>
- * Be aware that your cell will hold both the formula, and the result. If you want the cell
- * replaced with the result of the formula, use {@link #evaluateInCell(org.apache.poi.ss.usermodel.Cell)}
- * @param cell The cell to evaluate
- * @return -1 for non-formula cells, or the type of the <em>formula result</em>
- */
- public int evaluateFormulaCell(Cell cell) {
- if (cell == null || cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
- return -1;
- }
- CellValue cv = evaluateFormulaCellValue(cell);
- // cell remains a formula cell, but the cached value is changed
- setCellValue(cell, cv);
- return cv.getCellType();
- }
+ switch (cell.getCellType()) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ return CellValue.valueOf(cell.getBooleanCellValue());
+ case HSSFCell.CELL_TYPE_ERROR:
+ return CellValue.getError(cell.getErrorCellValue());
+ case HSSFCell.CELL_TYPE_FORMULA:
+ return evaluateFormulaCellValue(cell);
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ return new CellValue(cell.getNumericCellValue());
+ case HSSFCell.CELL_TYPE_STRING:
+ return new CellValue(cell.getRichStringCellValue().getString());
+ case HSSFCell.CELL_TYPE_BLANK:
+ return null;
+ }
+ throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")");
+ }
- /**
- * If cell contains formula, it evaluates the formula, and
- * puts the formula result back into the cell, in place
- * of the old formula.
- * Else if cell does not contain formula, this method leaves
- * the cell unchanged.
- * Note that the same instance of HSSFCell is returned to
- * allow chained calls like:
- * <pre>
- * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
- * </pre>
- * Be aware that your cell value will be changed to hold the
- * result of the formula. If you simply want the formula
- * value computed for you, use {@link #evaluateFormulaCell(org.apache.poi.ss.usermodel.Cell)}}
- * @param cell
- */
- public HSSFCell evaluateInCell(Cell cell) {
- if (cell == null) {
- return null;
- }
- HSSFCell result = (HSSFCell) cell;
- if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
- CellValue cv = evaluateFormulaCellValue(cell);
- setCellType(cell, cv); // cell will no longer be a formula cell
- setCellValue(cell, cv);
- }
- return result;
- }
- private static void setCellType(Cell cell, CellValue cv) {
- int cellType = cv.getCellType();
- switch (cellType) {
- case HSSFCell.CELL_TYPE_BOOLEAN:
- case HSSFCell.CELL_TYPE_ERROR:
- case HSSFCell.CELL_TYPE_NUMERIC:
- case HSSFCell.CELL_TYPE_STRING:
- cell.setCellType(cellType);
- return;
- case HSSFCell.CELL_TYPE_BLANK:
- // never happens - blanks eventually get translated to zero
- case HSSFCell.CELL_TYPE_FORMULA:
- // this will never happen, we have already evaluated the formula
- }
- throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
- }
- private static void setCellValue(Cell cell, CellValue cv) {
- int cellType = cv.getCellType();
- switch (cellType) {
- case HSSFCell.CELL_TYPE_BOOLEAN:
- cell.setCellValue(cv.getBooleanValue());
- break;
- case HSSFCell.CELL_TYPE_ERROR:
- cell.setCellErrorValue(cv.getErrorValue());
- break;
- case HSSFCell.CELL_TYPE_NUMERIC:
- cell.setCellValue(cv.getNumberValue());
- break;
- case HSSFCell.CELL_TYPE_STRING:
- cell.setCellValue(new HSSFRichTextString(cv.getStringValue()));
- break;
- case HSSFCell.CELL_TYPE_BLANK:
- // never happens - blanks eventually get translated to zero
- case HSSFCell.CELL_TYPE_FORMULA:
- // this will never happen, we have already evaluated the formula
- default:
- throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
- }
- }
+ /**
+ * If cell contains formula, it evaluates the formula, and saves the result of the formula. The
+ * cell remains as a formula cell. If the cell does not contain formula, this method returns -1
+ * and leaves the cell unchanged.
+ *
+ * Note that the type of the <em>formula result</em> is returned, so you know what kind of
+ * cached formula result is also stored with the formula.
+ * <pre>
+ * int evaluatedCellType = evaluator.evaluateFormulaCell(cell);
+ * </pre>
+ * Be aware that your cell will hold both the formula, and the result. If you want the cell
+ * replaced with the result of the formula, use {@link #evaluateInCell(org.apache.poi.ss.usermodel.Cell)}
+ * @param cell The cell to evaluate
+ * @return -1 for non-formula cells, or the type of the <em>formula result</em>
+ */
+ public int evaluateFormulaCell(Cell cell) {
+ if (cell == null || cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
+ return -1;
+ }
+ CellValue cv = evaluateFormulaCellValue(cell);
+ // cell remains a formula cell, but the cached value is changed
+ setCellValue(cell, cv);
+ return cv.getCellType();
+ }
- /**
- * 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(HSSFWorkbook wb) {
- HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
- for(int i=0; i<wb.getNumberOfSheets(); i++) {
- HSSFSheet sheet = wb.getSheetAt(i);
+ /**
+ * If cell contains formula, it evaluates the formula, and
+ * puts the formula result back into the cell, in place
+ * of the old formula.
+ * Else if cell does not contain formula, this method leaves
+ * the cell unchanged.
+ * Note that the same instance of HSSFCell is returned to
+ * allow chained calls like:
+ * <pre>
+ * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
+ * </pre>
+ * Be aware that your cell value will be changed to hold the
+ * result of the formula. If you simply want the formula
+ * value computed for you, use {@link #evaluateFormulaCell(org.apache.poi.ss.usermodel.Cell)}}
+ * @param cell
+ */
+ public HSSFCell evaluateInCell(Cell cell) {
+ if (cell == null) {
+ return null;
+ }
+ HSSFCell result = (HSSFCell) cell;
+ if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
+ CellValue cv = evaluateFormulaCellValue(cell);
+ setCellType(cell, cv); // cell will no longer be a formula cell
+ setCellValue(cell, cv);
+ }
+ return result;
+ }
+ private static void setCellType(Cell cell, CellValue cv) {
+ int cellType = cv.getCellType();
+ switch (cellType) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ case HSSFCell.CELL_TYPE_ERROR:
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ case HSSFCell.CELL_TYPE_STRING:
+ cell.setCellType(cellType);
+ return;
+ case HSSFCell.CELL_TYPE_BLANK:
+ // never happens - blanks eventually get translated to zero
+ case HSSFCell.CELL_TYPE_FORMULA:
+ // this will never happen, we have already evaluated the formula
+ }
+ throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
+ }
- for (Iterator rit = sheet.rowIterator(); rit.hasNext();) {
- HSSFRow r = (HSSFRow)rit.next();
+ private static void setCellValue(Cell cell, CellValue cv) {
+ int cellType = cv.getCellType();
+ switch (cellType) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ cell.setCellValue(cv.getBooleanValue());
+ break;
+ case HSSFCell.CELL_TYPE_ERROR:
+ cell.setCellErrorValue(cv.getErrorValue());
+ break;
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ cell.setCellValue(cv.getNumberValue());
+ break;
+ case HSSFCell.CELL_TYPE_STRING:
+ cell.setCellValue(new HSSFRichTextString(cv.getStringValue()));
+ break;
+ case HSSFCell.CELL_TYPE_BLANK:
+ // never happens - blanks eventually get translated to zero
+ case HSSFCell.CELL_TYPE_FORMULA:
+ // this will never happen, we have already evaluated the formula
+ default:
+ throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
+ }
+ }
- for (Iterator cit = r.cellIterator(); cit.hasNext();) {
- HSSFCell c = (HSSFCell)cit.next();
- 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 static void evaluateAllFormulaCells(HSSFWorkbook wb) {
+ HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
+ for(int i=0; i<wb.getNumberOfSheets(); i++) {
+ HSSFSheet sheet = wb.getSheetAt(i);
- /**
- * Returns a CellValue wrapper around the supplied ValueEval instance.
- * @param eval
- */
- private CellValue evaluateFormulaCellValue(Cell cell) {
- ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell((HSSFCell)cell));
- if (eval instanceof NumberEval) {
- NumberEval ne = (NumberEval) eval;
- return new CellValue(ne.getNumberValue());
- }
- if (eval instanceof BoolEval) {
- BoolEval be = (BoolEval) eval;
- return CellValue.valueOf(be.getBooleanValue());
- }
- if (eval instanceof StringEval) {
- StringEval ne = (StringEval) eval;
- return new CellValue(ne.getStringValue());
- }
- if (eval instanceof ErrorEval) {
- return CellValue.getError(((ErrorEval)eval).getErrorCode());
- }
- throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
- }
+ for (Iterator rit = sheet.rowIterator(); rit.hasNext();) {
+ HSSFRow r = (HSSFRow)rit.next();
+
+ for (Iterator cit = r.cellIterator(); cit.hasNext();) {
+ HSSFCell c = (HSSFCell)cit.next();
+ if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
+ evaluator.evaluateFormulaCell(c);
+ }
+ }
+ }
+ }
+
+ /**
+ * Returns a CellValue wrapper around the supplied ValueEval instance.
+ * @param eval
+ */
+ private CellValue evaluateFormulaCellValue(Cell cell) {
+ ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell((HSSFCell)cell));
+ if (eval instanceof NumberEval) {
+ NumberEval ne = (NumberEval) eval;
+ return new CellValue(ne.getNumberValue());
+ }
+ if (eval instanceof BoolEval) {
+ BoolEval be = (BoolEval) eval;
+ return CellValue.valueOf(be.getBooleanValue());
+ }
+ if (eval instanceof StringEval) {
+ StringEval ne = (StringEval) eval;
+ return new CellValue(ne.getStringValue());
+ }
+ if (eval instanceof ErrorEval) {
+ return CellValue.getError(((ErrorEval)eval).getErrorCode());
+ }
+ throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
+ }
}
|