Author: nick
Date: Thu Jan 24 06:13:05 2008
New Revision: 614878
URL: http://svn.apache.org/viewvc?rev=614878&view=rev
Log:
Add another formula evaluation method, evaluateFormulaCell(cell), which will
re-calculate the value for a formula, without affecting the formula itself. Add
tests too, and update the documentation
Added:
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java
(with props)
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/hssf/eval.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=614878&r1=614877&r2=614878&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Thu Jan 24 06:13:05
2008
@@ -36,6 +36,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.0.2-FINAL" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">Add another formula
evaluation method, evaluateFormulaCell(cell), which will re-calculate the value
for a formula, without affecting the formula itself.</action>
<action dev="POI-DEVELOPERS" type="fix">41726 - Fix how we handle
signed cell offsets in relative areas and references</action>
<action dev="POI-DEVELOPERS" type="add">44233 - Support for
getting and setting a flag on the sheet, which tells excel to re-calculate all
formulas on it at next reload</action>
<action dev="POI-DEVELOPERS" type="fix">44201 - Enable cloning of
sheets with data validation rules</action>
Modified: poi/trunk/src/documentation/content/xdocs/hssf/eval.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/hssf/eval.xml?rev=614878&r1=614877&r2=614878&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/hssf/eval.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/hssf/eval.xml Thu Jan 24 06:13:05
2008
@@ -55,10 +55,12 @@
<p>The following code demonstrates how to use the
HSSFFormulaEvaluator
in the context of other POI excel reading code.
</p>
- <p>There are two ways in which you can use the
HSSFFormulaEvalutator API.</p>
+ <p>There are several ways in which you can use the
HSSFFormulaEvalutator API.</p>
<anchor id="Evaluate"/>
<section><title>Using
HSSFFormulaEvaluator.<strong>evaluate</strong>(HSSFCell cell)</title>
+ <p>This evaluates a given cell, and returns the
new value,
+ without affecting the cell</p>
<source>
FileInputStream fis = new FileInputStream("c:/temp/test.xls");
HSSFWorkbook wb = new HSSFWorkbook(fis);
@@ -102,12 +104,60 @@
</p>
</section>
+ <anchor id="EvaluateFormulaCell"/>
+ <section><title>Using
HSSFFormulaEvaluator.<strong>evaluateFormulaCell</strong>(HSSFCell cell)</title>
+
<p><strong>evaluateFormulaCell</strong>(HSSFCell cell)
+ will check to see if the supplied cell is a
formula cell.
+ If it isn't, then no changes will be made to
it. If it is,
+ then the formula is evaluated. The value for
the formula
+ is saved alongside it, to be displayed in
excel. The
+ formula remains in the cell, just with a new
value</p>
+ <p>The return of the function is the type of the
+ formula result, such as
HSSFCell.CELL_TYPE_BOOLEAN</p>
+ <source>
+FileInputStream fis = new FileInputStream("/somepath/test.xls");
+HSSFWorkbook wb = new HSSFWorkbook(fis);
+HSSFSheet sheet = wb.getSheetAt(0);
+HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
+
+// suppose your formula is in B3
+CellReference cellReference = new CellReference("B3");
+HSSFRow row = sheet.getRow(cellReference.getRow());
+HSSFCell cell = row.getCell(cellReference.getCol());
+evaluator.setCurrentRow(row);
+
+if (cell!=null) {
+ switch (<strong>evaluator.evaluateFormulaCell</strong>(cell)) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ System.out.println(cell.getBooleanCellValue());
+ break;
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ System.out.println(cell.getNumberCellValue());
+ break;
+ case HSSFCell.CELL_TYPE_STRING:
+ System.out.println(cell.getStringCellValue());
+ break;
+ case HSSFCell.CELL_TYPE_BLANK:
+ break;
+ case HSSFCell.CELL_TYPE_ERROR:
+ System.out.println(cell.getErrorCellValue());
+ break;
+
+ // CELL_TYPE_FORMULA will never occur
+ case HSSFCell.CELL_TYPE_FORMULA:
+ break;
+ }
+}
+ </source>
+ </section>
+
<anchor id="EvaluateInCell"/>
<section><title>Using
HSSFFormulaEvaluator.<strong>evaluateInCell</strong>(HSSFCell cell)</title>
<p><strong>evaluateInCell</strong>(HSSFCell
cell) will check to
see if the supplied cell is a formula cell. If
it isn't,
then no changes will be made to it. If it is,
then the
- formula is evaluated, and the new value saved
into the cell.</p>
+ formula is evaluated, and the new value saved
into the cell,
+ in place of the old formula.</p>
<source>
FileInputStream fis = new FileInputStream("/somepath/test.xls");
HSSFWorkbook wb = new HSSFWorkbook(fis);
@@ -154,14 +204,14 @@
HSSFSheet sheet = wb.getSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
- for(Iterator rit = s.rowIterator(); rit.hasNext();) {
+ for(Iterator rit = sheet.rowIterator(); rit.hasNext();) {
HSSFRow r = (HSSFRow)rit.next();
evaluator.setCurrentRow(r);
for(Iterator cit = r.cellIterator(); cit.hasNext();) {
HSSFCell c = (HSSFCell)cit.next();
if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
- evaluator.evaluateInCell(c);
+ evaluator.evaluateFormulaCell(c);
}
}
}
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=614878&r1=614877&r2=614878&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Thu Jan 24 06:13:05
2008
@@ -33,6 +33,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.0.2-FINAL" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">Add another formula
evaluation method, evaluateFormulaCell(cell), which will re-calculate the value
for a formula, without affecting the formula itself.</action>
<action dev="POI-DEVELOPERS" type="fix">41726 - Fix how we handle
signed cell offsets in relative areas and references</action>
<action dev="POI-DEVELOPERS" type="add">44233 - Support for
getting and setting a flag on the sheet, which tells excel to re-calculate all
formulas on it at next reload</action>
<action dev="POI-DEVELOPERS" type="fix">44201 - Enable cloning of
sheets with data validation rules</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java?rev=614878&r1=614877&r2=614878&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java Thu Jan 24
06:13:05 2008
@@ -534,7 +534,13 @@
{
setCellType(CELL_TYPE_NUMERIC, false, row, col, styleIndex);
}
- (( NumberRecord ) record).setValue(value);
+
+ // Save into the apropriate record
+ if(record instanceof FormulaRecordAggregate) {
+ (( FormulaRecordAggregate )
record).getFormulaRecord().setValue(value);
+ } else {
+ (( NumberRecord ) record).setValue(value);
+ }
}
/**
Modified:
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java?rev=614878&r1=614877&r2=614878&view=diff
==============================================================================
---
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
(original)
+++
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
Thu Jan 24 06:13:05 2008
@@ -217,14 +217,66 @@
/**
- * If cell contains formula, it evaluates the formula, and puts the
- * formula result back into the cell.
- * Else if cell does not contain formula, this method leaves the cell
- * unchanged. Note that the same instance of HSSFCell is returned to
+ * If cell contains formula, it evaluates the formula,
+ * and saves the result of the formula. The cell
+ * remains as a formula cell.
+ * Else if cell does not contain formula, this method leaves
+ * the cell unchanged.
+ * Note that the type of the formula result is returned,
+ * so you know what kind of value 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 [EMAIL PROTECTED]
#evaluateInCell(HSSFCell)}
+ * @param cell The cell to evaluate
+ * @return The type of the formula result (the cell's type remains as
HSSFCell.CELL_TYPE_FORMULA however)
+ */
+ public int evaluateFormulaCell(HSSFCell cell) {
+ if (cell != null) {
+ switch (cell.getCellType()) {
+ case HSSFCell.CELL_TYPE_FORMULA:
+ CellValue cv = getCellValueForEval(internalEvaluate(cell, row,
sheet, workbook));
+ switch (cv.getCellType()) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ cell.setCellValue(cv.getBooleanValue());
+ break;
+ case HSSFCell.CELL_TYPE_ERROR:
+ cell.setCellValue(cv.getErrorValue());
+ break;
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ cell.setCellValue(cv.getNumberValue());
+ break;
+ case HSSFCell.CELL_TYPE_STRING:
+ cell.setCellValue(cv.getRichTextStringValue());
+ break;
+ case HSSFCell.CELL_TYPE_BLANK:
+ break;
+ case HSSFCell.CELL_TYPE_FORMULA: // this will never happen, we
have already evaluated the formula
+ break;
+ }
+ return cv.getCellType();
+ }
+ }
+ return -1;
+ }
+
+ /**
+ * 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 [EMAIL PROTECTED]
#evaluateFormulaCell(HSSFCell)}
* @param cell
*/
public HSSFCell evaluateInCell(HSSFCell cell) {
Added:
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java?rev=614878&view=auto
==============================================================================
---
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java
(added)
+++
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java
Thu Jan 24 06:13:05 2008
@@ -0,0 +1,117 @@
+package org.apache.poi.hssf.usermodel;
+
+import java.util.Iterator;
+
+import junit.framework.TestCase;
+
+/**
+ * Tests to show that our documentation at
+ * http://poi.apache.org/hssf/eval.html
+ * all actually works as we'd expect them to
+ */
+public class TestFormulaEvaluatorDocs extends TestCase {
+ protected void setUp() throws Exception {
+ super.setUp();
+ }
+
+ /**
+ * http://poi.apache.org/hssf/eval.html#EvaluateAll
+ */
+ public void testEvaluateAll() throws Exception {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet s1 = wb.createSheet();
+ HSSFSheet s2 = wb.createSheet();
+ wb.setSheetName(0, "S1");
+ wb.setSheetName(1, "S2");
+
+ HSSFRow s1r1 = s1.createRow(0);
+ HSSFRow s1r2 = s1.createRow(1);
+ HSSFRow s2r1 = s2.createRow(0);
+
+ HSSFCell s1r1c1 = s1r1.createCell((short)0);
+ HSSFCell s1r1c2 = s1r1.createCell((short)1);
+ HSSFCell s1r1c3 = s1r1.createCell((short)2);
+ s1r1c1.setCellValue(22.3);
+ s1r1c2.setCellValue(33.4);
+ s1r1c3.setCellFormula("SUM(A1:B1)");
+
+ HSSFCell s1r2c1 = s1r2.createCell((short)0);
+ HSSFCell s1r2c2 = s1r2.createCell((short)1);
+ HSSFCell s1r2c3 = s1r2.createCell((short)2);
+ s1r2c1.setCellValue(-1.2);
+ s1r2c2.setCellValue(-3.4);
+ s1r2c3.setCellFormula("SUM(A2:B2)");
+
+ HSSFCell s2r1c1 = s2r1.createCell((short)0);
+ s2r1c1.setCellFormula("S1!A1");
+
+ // Not evaluated yet
+ assertEquals(0.0, s1r1c3.getNumericCellValue(), 0);
+ assertEquals(0.0, s1r2c3.getNumericCellValue(), 0);
+ assertEquals(0.0, s2r1c1.getNumericCellValue(), 0);
+
+ // Do a full evaluate, as per our docs
+ // uses evaluateFormulaCell()
+ for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets();
sheetNum++) {
+ HSSFSheet sheet = wb.getSheetAt(sheetNum);
+ HSSFFormulaEvaluator evaluator = new
HSSFFormulaEvaluator(sheet, wb);
+
+ for(Iterator rit = sheet.rowIterator(); rit.hasNext();)
{
+ HSSFRow r = (HSSFRow)rit.next();
+ evaluator.setCurrentRow(r);
+
+ for(Iterator cit = r.cellIterator();
cit.hasNext();) {
+ HSSFCell c = (HSSFCell)cit.next();
+ if(c.getCellType() ==
HSSFCell.CELL_TYPE_FORMULA) {
+
evaluator.evaluateFormulaCell(c);
+
+ // For testing - all should be
numeric
+
assertEquals(HSSFCell.CELL_TYPE_NUMERIC, evaluator.evaluateFormulaCell(c));
+ }
+ }
+ }
+ }
+
+ // Check now as expected
+ assertEquals(55.7,
wb.getSheetAt(0).getRow(0).getCell((short)2).getNumericCellValue(), 0);
+ assertEquals("SUM(A1:B1)",
wb.getSheetAt(0).getRow(0).getCell((short)2).getCellFormula());
+ assertEquals(HSSFCell.CELL_TYPE_FORMULA,
wb.getSheetAt(0).getRow(0).getCell((short)2).getCellType());
+
+ assertEquals(-4.6,
wb.getSheetAt(0).getRow(1).getCell((short)2).getNumericCellValue(), 0);
+ assertEquals("SUM(A2:B2)",
wb.getSheetAt(0).getRow(1).getCell((short)2).getCellFormula());
+ assertEquals(HSSFCell.CELL_TYPE_FORMULA,
wb.getSheetAt(0).getRow(1).getCell((short)2).getCellType());
+
+ assertEquals(22.3,
wb.getSheetAt(1).getRow(0).getCell((short)0).getNumericCellValue(), 0);
+ assertEquals("S1!A1",
wb.getSheetAt(1).getRow(0).getCell((short)0).getCellFormula());
+ assertEquals(HSSFCell.CELL_TYPE_FORMULA,
wb.getSheetAt(1).getRow(0).getCell((short)0).getCellType());
+
+
+ // Now do the alternate call, which zaps the formulas
+ // uses evaluateInCell()
+ for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets();
sheetNum++) {
+ HSSFSheet sheet = wb.getSheetAt(sheetNum);
+ HSSFFormulaEvaluator evaluator = new
HSSFFormulaEvaluator(sheet, wb);
+
+ for(Iterator rit = sheet.rowIterator(); rit.hasNext();)
{
+ HSSFRow r = (HSSFRow)rit.next();
+ evaluator.setCurrentRow(r);
+
+ for(Iterator cit = r.cellIterator();
cit.hasNext();) {
+ HSSFCell c = (HSSFCell)cit.next();
+ if(c.getCellType() ==
HSSFCell.CELL_TYPE_FORMULA) {
+ evaluator.evaluateInCell(c);
+ }
+ }
+ }
+ }
+
+ assertEquals(55.7,
wb.getSheetAt(0).getRow(0).getCell((short)2).getNumericCellValue(), 0);
+ assertEquals(HSSFCell.CELL_TYPE_NUMERIC,
wb.getSheetAt(0).getRow(0).getCell((short)2).getCellType());
+
+ assertEquals(-4.6,
wb.getSheetAt(0).getRow(1).getCell((short)2).getNumericCellValue(), 0);
+ assertEquals(HSSFCell.CELL_TYPE_NUMERIC,
wb.getSheetAt(0).getRow(1).getCell((short)2).getCellType());
+
+ assertEquals(22.3,
wb.getSheetAt(1).getRow(0).getCell((short)0).getNumericCellValue(), 0);
+ assertEquals(HSSFCell.CELL_TYPE_NUMERIC,
wb.getSheetAt(1).getRow(0).getCell((short)0).getCellType());
+ }
+}
Propchange:
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java
------------------------------------------------------------------------------
svn:eol-style = native
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
