Bug 61532: Fix setting values/types during formula evaluation for SXSSF

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

3 files changed, 162 insertions, 41 deletions

diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java
index 2b40d37577..77e3ef80b5 100644
--- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java
+++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java
@@ -393,7 +393,7 @@ public abstract class BaseTestBugzillaIssues {
         cell0.setCellValue(longValue);
 
         // autoSize will fail if required fonts are not installed, skip this test then
-        Font font = wb.getFontAt(cell0.getCellStyle().getFontIndex());
+        Font font = wb.getFontAt(cell0.getCellStyle().getFontIndexAsInt());
         Assume.assumeTrue("Cannot verify autoSizeColumn() because the necessary Fonts are not installed on this machine: " + font,
                 SheetUtil.canComputeColumnWidth(font));
 
@@ -745,79 +745,79 @@ public abstract class BaseTestBugzillaIssues {
         // First up, check that TRUE and ISLOGICAL both behave
         cf.setCellFormula("TRUE()");
         cf = evaluateCell(wb, cf);
-        assertEquals(true, cf.getBooleanCellValue());
+        assertTrue(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISLOGICAL(TRUE())");
         cf = evaluateCell(wb, cf);
-        assertEquals(true, cf.getBooleanCellValue());
+        assertTrue(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISLOGICAL(4)");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
 
         // Now, check ISNUMBER / ISTEXT / ISNONTEXT
         cf.setCellFormula("ISNUMBER(A1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(true, cf.getBooleanCellValue());
+        assertTrue(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNUMBER(B1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNUMBER(C1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNUMBER(D1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNUMBER(E1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
 
         cf.setCellFormula("ISTEXT(A1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISTEXT(B1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(true, cf.getBooleanCellValue());
+        assertTrue(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISTEXT(C1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(true, cf.getBooleanCellValue());
+        assertTrue(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISTEXT(D1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISTEXT(E1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
 
         cf.setCellFormula("ISNONTEXT(A1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(true, cf.getBooleanCellValue());
+        assertTrue(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNONTEXT(B1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNONTEXT(C1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNONTEXT(D1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(true, cf.getBooleanCellValue());
+        assertTrue(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNONTEXT(E1)");
         cf = evaluateCell(wb, cf);
-        assertEquals(true, cf.getBooleanCellValue()); // Blank and Null the same
+        assertTrue(cf.getBooleanCellValue()); // Blank and Null the same
 
 
         // Next up, SEARCH on its own
@@ -841,23 +841,23 @@ public abstract class BaseTestBugzillaIssues {
         // Finally, bring it all together
         cf.setCellFormula("ISNUMBER(SEARCH(\"am\", A1))");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNUMBER(SEARCH(\"am\", B1))");
         cf = evaluateCell(wb, cf);
-        assertEquals(true, cf.getBooleanCellValue());
+        assertTrue(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNUMBER(SEARCH(\"am\", C1))");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNUMBER(SEARCH(\"am\", D1))");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
         cf.setCellFormula("ISNUMBER(SEARCH(\"am\", E1))");
         cf = evaluateCell(wb, cf);
-        assertEquals(false, cf.getBooleanCellValue());
+        assertFalse(cf.getBooleanCellValue());
 
         wb.close();
     }
@@ -1230,8 +1230,7 @@ public abstract class BaseTestBugzillaIssues {
         cell.setCellValue("somevalue");
 
         value = cell.getStringCellValue();
-        assertTrue("can set value afterwards: " + value,
-                value.equals("somevalue"));
+        assertEquals("can set value afterwards: " + value, "somevalue", value);
 
         // verify that the null-value is actually set even if there was some value in the cell before
         cell.setCellValue((String)null);
@@ -1282,18 +1281,30 @@ public abstract class BaseTestBugzillaIssues {
         // Check read ok, and re-evaluate fine
         cell = row.getCell(5);
         assertEquals("ab", cell.getStringCellValue());
+        assertEquals(CellType.FORMULA, cell.getCellType());
+        assertEquals("IF(A1<>\"\",MID(A1,1,2),\" \")", cell.getCellFormula());
         ev.evaluateFormulaCell(cell);
         assertEquals("ab", cell.getStringCellValue());
+        assertEquals(CellType.FORMULA, cell.getCellType());
+        assertEquals("IF(A1<>\"\",MID(A1,1,2),\" \")", cell.getCellFormula());
 
         cell = row.getCell(6);
         assertEquals("empty", cell.getStringCellValue());
+        assertEquals(CellType.FORMULA, cell.getCellType());
+        assertEquals("IF(B1<>\"\",MID(A1,1,2),\"empty\")", cell.getCellFormula());
         ev.evaluateFormulaCell(cell);
         assertEquals("empty", cell.getStringCellValue());
+        assertEquals(CellType.FORMULA, cell.getCellType());
+        assertEquals("IF(B1<>\"\",MID(A1,1,2),\"empty\")", cell.getCellFormula());
 
         cell = row.getCell(7);
         assertEquals("ab", cell.getStringCellValue());
+        assertEquals(CellType.FORMULA, cell.getCellType());
+        assertEquals("IF(A1<>\"\",IF(C1<>\"\",MID(A1,1,2),\"c1\"),\"c2\")", cell.getCellFormula());
         ev.evaluateFormulaCell(cell);
         assertEquals("ab", cell.getStringCellValue());
+        assertEquals(CellType.FORMULA, cell.getCellType());
+        assertEquals("IF(A1<>\"\",IF(C1<>\"\",MID(A1,1,2),\"c1\"),\"c2\")", cell.getCellFormula());
         wb2.close();
     }
 
diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java
index acf0999922..3228bbd0a1 100644
--- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java
+++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java
@@ -27,6 +27,10 @@ import static org.junit.Assert.fail;
 
 import java.io.IOException;
 import java.util.Calendar;
+import java.util.Date;
+import java.util.GregorianCalendar;
+import java.util.Locale;
+import java.util.TimeZone;
 
 import org.apache.poi.common.usermodel.HyperlinkType;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
@@ -66,10 +70,10 @@ public abstract class BaseTestCell {
                 CellType.FORMULA, CellType.ERROR);
 
         cell.setCellValue(false);
-        assertEquals(false, cell.getBooleanCellValue());
+        assertFalse(cell.getBooleanCellValue());
         assertEquals(CellType.BOOLEAN, cell.getCellType());
         cell.setCellValue(true);
-        assertEquals(true, cell.getBooleanCellValue());
+        assertTrue(cell.getBooleanCellValue());
         assertProhibitedValueAccess(cell, CellType.NUMERIC, CellType.STRING,
                 CellType.FORMULA, CellType.ERROR);
 
@@ -156,14 +160,14 @@ public abstract class BaseTestCell {
         assertEquals(0, c.getRowIndex());
         assertEquals(1, c.getColumnIndex());
         c.setCellValue(true);
-        assertEquals("B1 value", true, c.getBooleanCellValue());
+        assertTrue("B1 value", c.getBooleanCellValue());
 
         // C1
         c=r.createCell(2);
         assertEquals(0, c.getRowIndex());
         assertEquals(2, c.getColumnIndex());
         c.setCellValue(false);
-        assertEquals("C1 value", false, c.getBooleanCellValue());
+        assertFalse("C1 value", c.getBooleanCellValue());
 
         // Make sure values are saved and re-read correctly.
         Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1);
@@ -177,13 +181,13 @@ public abstract class BaseTestCell {
         assertEquals(0, c.getRowIndex());
         assertEquals(1, c.getColumnIndex());
         assertEquals(CellType.BOOLEAN, c.getCellType());
-        assertEquals("B1 value", true, c.getBooleanCellValue());
+        assertTrue("B1 value", c.getBooleanCellValue());
         
         c = r.getCell(2);
         assertEquals(0, c.getRowIndex());
         assertEquals(2, c.getColumnIndex());
         assertEquals(CellType.BOOLEAN, c.getCellType());
-        assertEquals("C1 value", false, c.getBooleanCellValue());
+        assertFalse("C1 value", c.getBooleanCellValue());
         
         wb2.close();
     }
@@ -276,7 +280,7 @@ public abstract class BaseTestCell {
         cs = c.getCellStyle();
 
         assertNotNull("Formula Cell Style", cs);
-        assertEquals("Font Index Matches", f.getIndexAsInt(), cs.getFontIndex());
+        assertEquals("Font Index Matches", f.getIndexAsInt(), cs.getFontIndexAsInt());
         assertEquals("Top Border", BorderStyle.THIN, cs.getBorderTop());
         assertEquals("Left Border", BorderStyle.THIN, cs.getBorderLeft());
         assertEquals("Right Border", BorderStyle.THIN, cs.getBorderRight());
@@ -301,7 +305,7 @@ public abstract class BaseTestCell {
 
         // create date-formatted cell
         Calendar c = LocaleUtil.getLocaleCalendar();
-        c.set(2010, 01, 02, 00, 00, 00);
+        c.set(2010, Calendar.FEBRUARY, 2, 0, 0, 0);
         r.createCell(7).setCellValue(c);
         CellStyle dateStyle = wb1.createCellStyle();
         short formatId = wb1.getCreationHelper().createDataFormat().getFormat("m/d/yy h:mm"); // any date format will do
@@ -426,7 +430,7 @@ public abstract class BaseTestCell {
         cell.setCellType(CellType.BOOLEAN);
 
         assertEquals(CellType.BOOLEAN, cell.getCellType());
-        assertEquals(true, cell.getBooleanCellValue());
+        assertTrue(cell.getBooleanCellValue());
         cell.setCellType(CellType.STRING);
         assertEquals("TRUE", cell.getRichStringCellValue().getString());
 
@@ -434,7 +438,7 @@ public abstract class BaseTestCell {
         cell.setCellValue("FALSE");
         cell.setCellType(CellType.BOOLEAN);
         assertEquals(CellType.BOOLEAN, cell.getCellType());
-        assertEquals(false, cell.getBooleanCellValue());
+        assertFalse(cell.getBooleanCellValue());
         cell.setCellType(CellType.STRING);
         assertEquals("FALSE", cell.getRichStringCellValue().getString());
         
@@ -479,7 +483,7 @@ public abstract class BaseTestCell {
         cell.setCellErrorValue(FormulaError.NAME.getCode());
         cell.setCellValue(true);
         // Identify bug 46479c
-        assertEquals(true, cell.getBooleanCellValue());
+        assertTrue(cell.getBooleanCellValue());
         
         wb.close();
     }
@@ -541,7 +545,7 @@ public abstract class BaseTestCell {
         fe.clearAllCachedResultValues();
         fe.evaluateFormulaCell(cellA1);
         confirmCannotReadString(cellA1);
-        assertEquals(true, cellA1.getBooleanCellValue());
+        assertTrue(cellA1.getBooleanCellValue());
         cellA1.setCellType(CellType.STRING);
         assertEquals("TRUE", cellA1.getStringCellValue());
 
@@ -572,7 +576,7 @@ public abstract class BaseTestCell {
         cell.setCellValue(true);
         cell.setCellType(CellType.BOOLEAN);
         assertTrue("Identified bug 46479d", cell.getBooleanCellValue());
-        assertEquals(true, cell.getBooleanCellValue());
+        assertTrue(cell.getBooleanCellValue());
         
         wb.close();
     }
@@ -626,7 +630,9 @@ public abstract class BaseTestCell {
         Workbook wb = _testDataProvider.createWorkbook();
         Cell cell = wb.createSheet("Sheet1").createRow(0).createCell(0);
         cell.setCellFormula("B1&C1");
+        assertEquals(CellType.FORMULA, cell.getCellType());
         cell.setCellValue(wb.getCreationHelper().createRichTextString("hello"));
+        assertEquals(CellType.FORMULA, cell.getCellType());
         wb.close();
     }
 
@@ -714,10 +720,10 @@ public abstract class BaseTestCell {
         assertTrue(style.getLocked());
         assertFalse(style.getHidden());
         assertEquals(0, style.getIndention());
-        assertEquals(0, style.getFontIndex());
+        assertEquals(0, style.getFontIndexAsInt());
         assertEquals(HorizontalAlignment.GENERAL, style.getAlignment());
         assertEquals(0, style.getDataFormat());
-        assertEquals(false, style.getWrapText());
+        assertFalse(style.getWrapText());
 
         CellStyle style2 = wb1.createCellStyle();
         assertTrue(style2.getLocked());
@@ -1054,4 +1060,34 @@ public abstract class BaseTestCell {
             assertNull(cell.getHyperlink());
         }
     }
+
+    @Test
+    public void testFormulaSetValueDoesNotChangeType() throws IOException {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            Sheet sheet = wb.createSheet();
+            Row row = sheet.createRow(0);
+            Cell cell = row.createCell(0);
+            cell.setCellFormula("SQRT(-1)");
+
+            assertEquals(CellType.FORMULA, cell.getCellType());
+
+            cell.setCellValue(new Date());
+            assertEquals(CellType.FORMULA, cell.getCellType());
+
+            cell.setCellValue(GregorianCalendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT));
+            assertEquals(CellType.FORMULA, cell.getCellType());
+
+            cell.setCellValue(1.0);
+            assertEquals(CellType.FORMULA, cell.getCellType());
+
+            cell.setCellValue("test");
+            assertEquals(CellType.FORMULA, cell.getCellType());
+
+            cell.setCellValue(wb.getCreationHelper().createRichTextString("test"));
+            assertEquals(CellType.FORMULA, cell.getCellType());
+
+            cell.setCellValue(false);
+            assertEquals(CellType.FORMULA, cell.getCellType());
+        }
+    }
 }
diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java
index eda716b82c..309da9472c 100644
--- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java
+++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestFormulaEvaluator.java
@@ -18,8 +18,11 @@
 package org.apache.poi.ss.usermodel;
 
 import java.io.IOException;
+import java.util.Locale;
 
 import org.apache.poi.ss.ITestDataProvider;
+import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.util.CellRangeAddressList;
 import org.junit.Test;
 
 import static org.junit.Assert.*;
@@ -624,4 +627,75 @@ public abstract class BaseTestFormulaEvaluator {
             assertEquals(3.0, cell.getNumericCellValue(), 0.01);
         }
     }
+
+    @Test
+    public void testFormulaEvaluatorEvaluateSimpleFormulaCell() throws Exception {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            final Row row = wb.createSheet().createRow(0);
+            final Cell a1 = row.createCell(0, CellType.NUMERIC);
+            a1.setCellValue(1.0);
+            final Cell a2 = row.createCell(1, CellType.NUMERIC);
+            a2.setCellValue(2.0);
+            final Cell a3 = row.createCell(2, CellType.FORMULA);
+            final String formula = "SUM(A1:B1)";
+            a3.setCellFormula(formula);
+            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+            CellType resultType = evaluator.evaluateFormulaCell(a3);
+            assertEquals(CellType.NUMERIC, resultType);
+
+            double result = a3.getNumericCellValue();
+            // result is correct
+            assertTrue(String.format(Locale.ROOT, "Expected %f to be greater than %f", result, 2.0), result > 2.0);
+            assertTrue(String.format(Locale.ROOT, "Expected %f to be less than %f", result, 4.0), result < 4.0);
+
+            // ensure that this works for SUM
+            assertEquals(CellType.FORMULA, a3.getCellType());
+            assertEquals(formula, a3.getCellFormula());
+        }
+    }
+
+    @Test
+    public void testFormulaEvaluatorEvaluateVlookupFormulaCell() throws Exception {
+        try (Workbook wb = _testDataProvider.createWorkbook()) {
+            final Sheet mainSheet = wb.createSheet("main");
+            final Sheet otherSheet = wb.createSheet("other");
+            final Row otherRow1 = otherSheet.createRow(0);
+            final Cell label1 = otherRow1.createCell(0, CellType.STRING);
+            label1.setCellValue("Thing One");
+            final Cell id1 = otherRow1.createCell(1, CellType.STRING);
+            id1.setCellValue("1");
+            final Row otherRow2 = otherSheet.createRow(1);
+            final Cell label2 = otherRow2.createCell(0, CellType.STRING);
+            label2.setCellValue("Thing Two");
+            final Cell id2 = otherRow2.createCell(1, CellType.STRING);
+            id2.setCellValue("2");
+            final DataValidationHelper dvHelper = mainSheet.getDataValidationHelper();
+            final int maxRows = SpreadsheetVersion.EXCEL2007.getMaxRows() - 1;
+            final CellRangeAddressList addressList = new CellRangeAddressList(0, maxRows, 0, 0);
+            final String constraint = "'other'!$A:$A";
+            final DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(constraint);
+            final DataValidation dataValidation = dvHelper.createValidation(dvConstraint, addressList);
+            dataValidation.setShowErrorBox(true);
+            mainSheet.addValidationData(dataValidation);
+            wb.setSheetHidden(wb.getSheetIndex(otherSheet), true);
+            final Row row = mainSheet.createRow(0);
+            final Cell a1 = row.createCell(0, CellType.STRING);
+            a1.setCellValue("Thing Two");
+            final Cell a2 = row.createCell(1, CellType.FORMULA);
+            final String formula = "VLOOKUP(A1,other!A:B,2,FALSE)";
+            a2.setCellFormula(formula);
+
+            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+            CellType resultType = evaluator.evaluateFormulaCell(a2);
+            assertEquals(CellType.STRING, resultType);
+
+            // result is correct
+            String result = a2.getStringCellValue();
+            assertEquals("2", result);
+
+            // ensure that this works for vlookup as well
+            assertEquals(CellType.FORMULA, a2.getCellType());
+            assertEquals(formula, a2.getCellFormula());
+        }
+    }
 }