Bug 61148: Fix calculating formula value, unfortunately we currently need to set the value twice

Also rework related tests to run more of the tests for HSSF, XSSF and SXSSF

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

3 files changed, 307 insertions, 71 deletions

diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java
index 229bf427c7..737d6619e5 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java
@@ -35,7 +35,6 @@ import org.apache.poi.ss.usermodel.BaseTestFormulaEvaluator;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellType;
 import org.apache.poi.ss.usermodel.CellValue;
-import org.apache.poi.ss.usermodel.FormulaError;
 import org.junit.Test;
 
 public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator {
@@ -61,38 +60,6 @@ public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator {
 	}
 
 	/**
-	 * Test for bug due to attempt to convert a cached formula error result to a boolean
-	 */
-    @Test
-	@Override
-	public void testUpdateCachedFormulaResultFromErrorToNumber_bug46479() throws IOException {
-
-		HSSFWorkbook wb = new HSSFWorkbook();
-		HSSFSheet sheet = wb.createSheet("Sheet1");
-		HSSFRow row = sheet.createRow(0);
-		HSSFCell cellA1 = row.createCell(0);
-		HSSFCell cellB1 = row.createCell(1);
-		cellB1.setCellFormula("A1+1");
-		HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
-
-		cellA1.setCellErrorValue(FormulaError.NAME.getCode());
-		fe.evaluateFormulaCell(cellB1);
-
-		cellA1.setCellValue(2.5);
-		fe.notifyUpdateCell(cellA1);
-		try {
-			fe.evaluateInCell(cellB1);
-		} catch (IllegalStateException e) {
-			if (e.getMessage().equals("Cannot get a numeric value from a error formula cell")) {
-				fail("Identified bug 46479a");
-			}
-		}
-		assertEquals(3.5, cellB1.getNumericCellValue(), 0.0);
-		
-		wb.close();
-	}
-
-	/**
 	 * When evaluating defined names, POI has to decide whether it is capable.  Currently
 	 * (May2009) POI only supports simple cell and area refs.<br>
 	 * The sample spreadsheet (bugzilla attachment 23508) had a name flagged as 'complex'
@@ -159,7 +126,6 @@ public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator {
 	 */
 	@Test
 	public void testShortCircuitIfEvaluation() throws IOException {
-
 		// Set up a simple IF() formula that has measurable evaluation cost for its operands.
 		HSSFWorkbook wb = new HSSFWorkbook();
 		HSSFSheet sheet = wb.createSheet("Sheet1");
@@ -183,10 +149,10 @@ public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator {
 		}
 		assertEquals(3, evalCount);
 		assertEquals(2.0, ((NumberEval)ve).getNumberValue(), 0D);
-		
+
 		wb.close();
 	}
-	
+
 	/**
 	 * Ensures that we can handle NameXPtgs in the formulas
 	 *  we parse.
@@ -265,7 +231,9 @@ public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator {
           cell = wb1.getSheetAt(0).getRow(1).createCell(42);
           cell.setCellFormula("[alt.xls]Sheet0!$A$1");
           fail("New workbook not linked, shouldn't be able to add");
-      } catch(Exception e) {}
+      } catch(Exception e) {
+      	// expected here
+	  }
       
       // Link our new workbook
       HSSFWorkbook wb3 = new HSSFWorkbook();
@@ -280,7 +248,9 @@ public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator {
       try {
           eval.evaluate(cell);
           fail("No cached value and no link to workbook, shouldn't evaluate");
-      } catch(Exception e) {}
+      } catch(Exception e) {
+		  // expected here
+	  }
       
       // Add a link, check it does
       HSSFFormulaEvaluator.setupEnvironment(
@@ -329,5 +299,4 @@ public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator {
     public void testSharedFormulas() throws IOException {
         baseTestSharedFormulas("shared_formulas.xls");
     }
-
 }
diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java
index b18661afa0..9f07cfb1c0 100644
--- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java
+++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java
@@ -733,6 +733,8 @@ public abstract class BaseTestCell {
         style = cell.getCellStyle();
         assertFalse(style2.getLocked());
         assertTrue(style2.getHidden());
+        assertTrue(style.getLocked());
+        assertFalse(style.getHidden());
 
         style2.setLocked(true);
         style2.setHidden(false);
@@ -887,6 +889,7 @@ public abstract class BaseTestCell {
      * Setting a cell value of a null RichTextString should set
      *  the cell to Blank, test case for 58558
      */
+    @SuppressWarnings("ConstantConditions")
     @Test
     public void testSetCellValueNullRichTextString() throws IOException {
         Workbook wb = _testDataProvider.createWorkbook();
@@ -1018,8 +1021,10 @@ public abstract class BaseTestCell {
             cell.setCellFormula("A2");
             cell.setCellErrorValue(FormulaError.NAME.getCode());
 
-            assertEquals(CellType.FORMULA, cell.getCellType());
-            assertEquals(CellType.ERROR, cell.getCachedFormulaResultType());
+            assertEquals("Should still be a formula even after we set an error value",
+                    CellType.FORMULA, cell.getCellType());
+            assertEquals("Should still be a formula even after we set an error value",
+                    CellType.ERROR, cell.getCachedFormulaResultType());
             assertEquals("A2", cell.getCellFormula());
             try {
                 cell.getNumericCellValue();
diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java
index d928f58446..9600b10d27 100644
--- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java
+++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java
@@ -280,30 +280,29 @@ public abstract class BaseTestFormulaEvaluator {
      */
     @Test
     public void testUpdateCachedFormulaResultFromErrorToNumber_bug46479() throws IOException {
-
-        Workbook wb = _testDataProvider.createWorkbook();
-        Sheet sheet = wb.createSheet("Sheet1");
-        Row row = sheet.createRow(0);
-        Cell cellA1 = row.createCell(0);
-        Cell cellB1 = row.createCell(1);
-        cellB1.setCellFormula("A1+1");
-        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
-
-        cellA1.setCellErrorValue(FormulaError.NAME.getCode());
-        fe.evaluateFormulaCell(cellB1);
-
-        cellA1.setCellValue(2.5);
-        fe.notifyUpdateCell(cellA1);
-        try {
-            fe.evaluateInCell(cellB1);
-        } catch (IllegalStateException e) {
-            if (e.getMessage().equals("Cannot get a numeric value from a error formula cell")) {
-                fail("Identified bug 46479a");
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet = wb.createSheet("Sheet1");
+            Row row = sheet.createRow(0);
+            Cell cellA1 = row.createCell(0);
+            Cell cellB1 = row.createCell(1);
+            cellB1.setCellFormula("A1+1");
+            FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+
+            cellA1.setCellErrorValue(FormulaError.NAME.getCode());
+            assertEquals(CellType.ERROR, fe.evaluateFormulaCell(cellB1));
+            assertEquals(CellType.FORMULA, cellB1.getCellType());
+
+            cellA1.setCellValue(2.5);
+            fe.notifyUpdateCell(cellA1);
+            try {
+                fe.evaluateInCell(cellB1);
+            } catch (IllegalStateException e) {
+                if (e.getMessage().equalsIgnoreCase("Cannot get a numeric value from a error formula cell")) {
+                    fail("Identified bug 46479a");
+                }
             }
+            assertEquals(3.5, cellB1.getNumericCellValue(), 0.0);
         }
-        assertEquals(3.5, cellB1.getNumericCellValue(), 0.0);
-        
-        wb.close();
     }
 
     @Test
@@ -330,12 +329,275 @@ public abstract class BaseTestFormulaEvaluator {
     
     @Test
     public void evaluateInCellReturnsSameCell() throws IOException {
-        Workbook wb = _testDataProvider.createWorkbook();
-        wb.createSheet().createRow(0).createCell(0);
-        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
-        Cell cell = wb.getSheetAt(0).getRow(0).getCell(0);
-        Cell same = evaluator.evaluateInCell(cell);
-        assertSame(cell, same);
-        wb.close();
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            wb.createSheet().createRow(0).createCell(0);
+            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+            Cell cell = wb.getSheetAt(0).getRow(0).getCell(0);
+            Cell same = evaluator.evaluateInCell(cell);
+            assertSame(cell, same);
+        }
+    }
+
+    @Test
+    public void testBug61148() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            final Cell cell = wb.createSheet().createRow(0).createCell(0);
+            cell.setCellFormula("1+2");
+
+            assertEquals(0, (int)cell.getNumericCellValue());
+            assertEquals("1+2", cell.toString());
+
+            FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
+
+            eval.evaluateInCell(cell);
+
+            assertEquals("3.0", cell.toString());
+        }
+    }
+
+    @Test
+    public void testMultisheetFormulaEval() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet1 = wb.createSheet("Sheet1");
+            Sheet sheet2 = wb.createSheet("Sheet2");
+            Sheet sheet3 = wb.createSheet("Sheet3");
+
+            // sheet1 A1
+            Cell cell = sheet1.createRow(0).createCell(0);
+            cell.setCellType(CellType.NUMERIC);
+            cell.setCellValue(1.0);
+
+            // sheet2 A1
+            cell = sheet2.createRow(0).createCell(0);
+            cell.setCellType(CellType.NUMERIC);
+            cell.setCellValue(1.0);
+
+            // sheet2 B1
+            cell = sheet2.getRow(0).createCell(1);
+            cell.setCellType(CellType.NUMERIC);
+            cell.setCellValue(1.0);
+
+            // sheet3 A1
+            cell = sheet3.createRow(0).createCell(0);
+            cell.setCellType(CellType.NUMERIC);
+            cell.setCellValue(1.0);
+
+            // sheet1 A2 formulae
+            cell = sheet1.createRow(1).createCell(0);
+            cell.setCellType(CellType.FORMULA);
+            cell.setCellFormula("SUM(Sheet1:Sheet3!A1)");
+
+            // sheet1 A3 formulae
+            cell = sheet1.createRow(2).createCell(0);
+            cell.setCellType(CellType.FORMULA);
+            cell.setCellFormula("SUM(Sheet1:Sheet3!A1:B1)");
+
+            wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
+
+            cell = sheet1.getRow(1).getCell(0);
+            assertEquals(3.0, cell.getNumericCellValue(), 0);
+
+            cell = sheet1.getRow(2).getCell(0);
+            assertEquals(4.0, cell.getNumericCellValue(), 0);
+        }
+    }
+
+    @Test
+    public void testBug55843() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet = wb.createSheet("test");
+            Row row = sheet.createRow(0);
+            Row row2 = sheet.createRow(1);
+            Cell cellA2 = row2.createCell(0, CellType.FORMULA);
+            Cell cellB1 = row.createCell(1, CellType.NUMERIC);
+            cellB1.setCellValue(10);
+            FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
+            cellA2.setCellFormula("IF(B1=0,\"\",((ROW()-ROW(A$1))*12))");
+            CellValue evaluate = formulaEvaluator.evaluate(cellA2);
+            assertEquals("12.0", evaluate.formatAsString());
+
+            cellA2.setCellFormula("IF(NOT(B1=0),((ROW()-ROW(A$1))*12),\"\")");
+            CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
+
+            assertEquals(evaluate.toString(), evaluateN.toString());
+            assertEquals("12.0", evaluateN.formatAsString());
+        }
+    }
+
+    @Test
+    public void testBug55843a() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet = wb.createSheet("test");
+            Row row = sheet.createRow(0);
+            Row row2 = sheet.createRow(1);
+            Cell cellA2 = row2.createCell(0, CellType.FORMULA);
+            Cell cellB1 = row.createCell(1, CellType.NUMERIC);
+            cellB1.setCellValue(10);
+            FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
+            cellA2.setCellFormula("IF(B1=0,\"\",((ROW(A$1))))");
+            CellValue evaluate = formulaEvaluator.evaluate(cellA2);
+            assertEquals("1.0", evaluate.formatAsString());
+
+            cellA2.setCellFormula("IF(NOT(B1=0),((ROW(A$1))),\"\")");
+            CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
+
+            assertEquals(evaluate.toString(), evaluateN.toString());
+            assertEquals("1.0", evaluateN.formatAsString());
+        }
+    }
+
+    @Test
+    public void testBug55843b() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet = wb.createSheet("test");
+            Row row = sheet.createRow(0);
+            Row row2 = sheet.createRow(1);
+            Cell cellA2 = row2.createCell(0, CellType.FORMULA);
+            Cell cellB1 = row.createCell(1, CellType.NUMERIC);
+            cellB1.setCellValue(10);
+            FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
+
+            cellA2.setCellFormula("IF(B1=0,\"\",((ROW())))");
+            CellValue evaluate = formulaEvaluator.evaluate(cellA2);
+            assertEquals("2.0", evaluate.formatAsString());
+
+            cellA2.setCellFormula("IF(NOT(B1=0),((ROW())),\"\")");
+            CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
+
+            assertEquals(evaluate.toString(), evaluateN.toString());
+            assertEquals("2.0", evaluateN.formatAsString());
+        }
+    }
+
+    @Test
+    public void testBug55843c() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet = wb.createSheet("test");
+            Row row = sheet.createRow(0);
+            Row row2 = sheet.createRow(1);
+            Cell cellA2 = row2.createCell(0, CellType.FORMULA);
+            Cell cellB1 = row.createCell(1, CellType.NUMERIC);
+            cellB1.setCellValue(10);
+            FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
+
+            cellA2.setCellFormula("IF(NOT(B1=0),((ROW())))");
+            CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
+            assertEquals("2.0", evaluateN.formatAsString());
+        }
+    }
+
+    @Test
+    public void testBug55843d() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet = wb.createSheet("test");
+            Row row = sheet.createRow(0);
+            Row row2 = sheet.createRow(1);
+            Cell cellA2 = row2.createCell(0, CellType.FORMULA);
+            Cell cellB1 = row.createCell(1, CellType.NUMERIC);
+            cellB1.setCellValue(10);
+            FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
+
+            cellA2.setCellFormula("IF(NOT(B1=0),((ROW())),\"\")");
+            CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
+            assertEquals("2.0", evaluateN.formatAsString());
+        }
+    }
+
+    @Test
+    public void testBug55843e() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet = wb.createSheet("test");
+            Row row = sheet.createRow(0);
+            Row row2 = sheet.createRow(1);
+            Cell cellA2 = row2.createCell(0, CellType.FORMULA);
+            Cell cellB1 = row.createCell(1, CellType.NUMERIC);
+            cellB1.setCellValue(10);
+            FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
+
+            cellA2.setCellFormula("IF(B1=0,\"\",((ROW())))");
+            CellValue evaluate = formulaEvaluator.evaluate(cellA2);
+            assertEquals("2.0", evaluate.formatAsString());
+        }
+    }
+
+    @Test
+    public void testBug55843f() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet = wb.createSheet("test");
+            Row row = sheet.createRow(0);
+            Row row2 = sheet.createRow(1);
+            Cell cellA2 = row2.createCell(0, CellType.FORMULA);
+            Cell cellB1 = row.createCell(1, CellType.NUMERIC);
+            cellB1.setCellValue(10);
+            FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
+
+            cellA2.setCellFormula("IF(B1=0,\"\",IF(B1=10,3,4))");
+            CellValue evaluate = formulaEvaluator.evaluate(cellA2);
+            assertEquals("3.0", evaluate.formatAsString());
+        }
+    }
+
+    @Test
+    public void testBug56655() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet = wb.createSheet();
+
+            setCellFormula(sheet, 0, 0, "#VALUE!");
+            setCellFormula(sheet, 0, 1, "SUMIFS(A:A,A:A,#VALUE!)");
+
+            wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
+
+            assertEquals(CellType.ERROR, getCell(sheet, 0, 0).getCachedFormulaResultType());
+            assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 0).getErrorCellValue());
+            assertEquals(CellType.ERROR, getCell(sheet, 0, 1).getCachedFormulaResultType());
+            assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 1).getErrorCellValue());
+        }
+    }
+
+    @Test
+    public void testBug56655a() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet = wb.createSheet();
+
+            setCellFormula(sheet, 0, 0, "B1*C1");
+            sheet.getRow(0).createCell(1).setCellValue("A");
+            setCellFormula(sheet, 1, 0, "B1*C1");
+            sheet.getRow(1).createCell(1).setCellValue("A");
+            setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)");
+
+            wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
+
+            assertEquals(CellType.ERROR, getCell(sheet, 0, 0).getCachedFormulaResultType());
+            assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 0).getErrorCellValue());
+            assertEquals(CellType.ERROR, getCell(sheet, 1, 0).getCachedFormulaResultType());
+            assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 1, 0).getErrorCellValue());
+            assertEquals(CellType.ERROR, getCell(sheet, 0, 3).getCachedFormulaResultType());
+            assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 3).getErrorCellValue());
+        }
+    }
+
+    /**
+     * @param row 0-based
+     * @param column 0-based
+     */
+    private void setCellFormula(Sheet sheet, int row, int column, String formula) {
+        Row r = sheet.getRow(row);
+        if (r == null) {
+            r = sheet.createRow(row);
+        }
+        Cell cell = r.getCell(column);
+        if (cell == null) {
+            cell = r.createCell(column);
+        }
+        cell.setCellType(CellType.FORMULA);
+        cell.setCellFormula(formula);
+    }
+
+    /**
+     * @param rowNo 0-based
+     * @param column 0-based
+     */
+    private Cell getCell(Sheet sheet, int rowNo, int column) {
+        return sheet.getRow(rowNo).getCell(column);
     }
-}
\ No newline at end of file
+}