Merged revisions 614878-614909 via svnmerge from

https://svn.apache.org/repos/asf/poi/trunk

........
  r614878 | nick | 2008-01-24 15:13:05 +0100 (Thu, 24 Jan 2008) | 1 line
  
  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
........
  r614909 | nick | 2008-01-24 17:05:27 +0100 (Thu, 24 Jan 2008) | 1 line
  
  From bug #44254 - avoid some unread bytes warnings, and process the contents of DVALRecord
........


git-svn-id: https://svn.apache.org/repos/asf/poi/branches/ooxml@615185 13f79535-47bb-0310-9956-ffa450edef68

2 files changed, 173 insertions, 4 deletions

diff --git a/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java b/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
index 2a9fc6c64b..f60a6adaac 100644
--- a/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
+++ b/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
@@ -217,14 +217,66 @@ public class HSSFFormulaEvaluator {
     
     
     /**
-     * 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 {@link #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 {@link #evaluateFormulaCell(HSSFCell)}
      * @param cell
      */
     public HSSFCell evaluateInCell(HSSFCell cell) {
diff --git a/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java b/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java
new file mode 100644
index 0000000000..cd2acc7ea9
--- /dev/null
+++ b/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java
@@ -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());
+	}
+}