[bug-60924] add support for Excel IFS and SWITCH functions. Thanks to Pieter Degraeuwe

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

3 files changed, 200 insertions, 3 deletions

diff --git a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestIfs.java b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestIfs.java
new file mode 100644
index 0000000000..35e1254cae
--- /dev/null
+++ b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestIfs.java
@@ -0,0 +1,117 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.formula.atp;
+
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.util.CellReference;
+import org.junit.jupiter.api.Test;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+/**
+ * Testcase for 'Analysis Toolpak' function IFS()
+ *
+ * @author Pieter Degraeuwe
+ */
+public class TestIfs  {
+
+    /**
+     * =IFS(A1="A", "Value for A" , A1="B", "Value for B")
+     */
+    @Test
+    public void testEvaluate() {
+        Workbook wb = new HSSFWorkbook();
+        Sheet sh = wb.createSheet();
+        Row row1 = sh.createRow(0);
+
+        // Create cells
+        row1.createCell(0, CellType.STRING);
+
+        // Create references
+        CellReference a1Ref = new CellReference("A1");
+
+        // Set values
+        final Cell cellA1 = sh.getRow(a1Ref.getRow()).getCell(a1Ref.getCol());
+
+
+        Cell cell1 = row1.createCell(1);
+        cell1.setCellFormula("IFS(A1=\"A\", \"Value for A\", A1=\"B\",\"Value for B\")");
+
+        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+
+        cellA1.setCellValue("A");
+        assertEquals(CellType.STRING, evaluator.evaluate(cell1).getCellType());
+        assertEquals("Value for A", evaluator.evaluate(cell1).getStringValue(),
+                "IFS should return 'Value for B'");
+
+        cellA1.setCellValue("B");
+        evaluator.clearAllCachedResultValues();
+        assertEquals(CellType.STRING, evaluator.evaluate(cell1).getCellType());
+        assertEquals("Value for B", evaluator.evaluate(cell1).getStringValue(),
+                "IFS should return 'Value for B'");
+    }
+
+    /**
+     * where D1 contains a string "A"
+     * =IFS(A1=D1, "Value for A" , A1="B", "Value for B")
+     */
+    @Test
+    public  void testEvaluateForReferenced() {
+        Workbook wb = new HSSFWorkbook();
+        Sheet sh = wb.createSheet();
+        Row row1 = sh.createRow(0);
+
+        // Create cells
+//        row1.createCell(0, CellType.STRING);
+
+        // Create references
+        CellReference a1Ref = new CellReference("A1");
+        CellReference d1Ref = new CellReference("D1");
+
+        // Set values
+        final Cell cellA1 = sh.getRow(a1Ref.getRow()).createCell(a1Ref.getCol());
+        cellA1.setCellFormula("D1");
+
+        final Cell cellD1 = sh.getRow(d1Ref.getRow()).createCell(d1Ref.getCol());
+        cellD1.setCellValue("A");
+
+
+        Cell cell1 = row1.createCell(1);
+        cell1.setCellFormula("IFS(A1=\"A\", \"Value for A\", A1=\"B\",\"Value for B\")");
+
+        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+
+        cellD1.setCellValue("A");
+        assertEquals(CellType.STRING, evaluator.evaluate(cell1).getCellType());
+        assertEquals( "Value for A", evaluator.evaluate(cell1).getStringValue(),
+                "IFS should return 'Value for B'");
+
+        cellD1.setCellValue("B");
+        evaluator.clearAllCachedResultValues();
+        assertEquals(CellType.STRING, evaluator.evaluate(cell1).getCellType());
+        assertEquals("Value for B", evaluator.evaluate(cell1).getStringValue(),
+                "IFS should return 'Value for B'");
+
+    }
+
+}
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestSwitch.java b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestSwitch.java
new file mode 100644
index 0000000000..b53d57035c
--- /dev/null
+++ b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestSwitch.java
@@ -0,0 +1,83 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.formula.atp;
+
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.util.CellReference;
+import org.junit.jupiter.api.Test;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+/**
+ * Testcase for 'Analysis Toolpak' function SWITCH()
+ *
+ * @author Pieter Degraeuwe
+ */
+public class TestSwitch {
+
+    /**
+     * =SWITCH(A1, "A", "Value for A", "B", "Value for B" )
+     * =SWITCH(A1, "A", "Value for A", "B", "Value for B", "Something else" )
+     */
+    @Test
+    public void testEvaluate() {
+        Workbook wb = new HSSFWorkbook();
+        Sheet sh = wb.createSheet();
+        Row row1 = sh.createRow(0);
+
+        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+
+        // Create cells
+        row1.createCell(0, CellType.STRING);
+
+        // Create references
+        CellReference a1Ref = new CellReference("A1");
+
+        // Set values
+        final Cell cellA1 = sh.getRow(a1Ref.getRow()).getCell(a1Ref.getCol());
+
+
+        Cell cell1 = row1.createCell(1);
+        cell1.setCellFormula("SWITCH(A1, \"A\",\"Value for A\", \"B\",\"Value for B\", \"Something else\")");
+
+
+        cellA1.setCellValue("A");
+        assertEquals(CellType.STRING, evaluator.evaluate(cell1).getCellType());
+        assertEquals("Value for A", evaluator.evaluate(cell1).getStringValue(),
+                "SWITCH should return 'Value for A'");
+
+        cellA1.setCellValue("B");
+        evaluator.clearAllCachedResultValues();
+        assertEquals(CellType.STRING, evaluator.evaluate(cell1).getCellType());
+        assertEquals("Value for B", evaluator.evaluate(cell1).getStringValue(),
+                "SWITCH should return 'Value for B'");
+
+        cellA1.setCellValue("");
+        evaluator.clearAllCachedResultValues();
+        assertEquals(CellType.STRING, evaluator.evaluate(cell1).getCellType());
+        assertEquals("Something else", evaluator.evaluate(cell1).getStringValue(),
+                "SWITCH should return 'Something else'");
+
+    }
+
+}
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java
index a82521c464..598542efa0 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java
@@ -17,15 +17,12 @@
 ==================================================================== */
 package org.apache.poi.ss.formula.atp;
 
-import org.apache.poi.hssf.HSSFTestDataSamples;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.ss.formula.eval.ErrorEval;
 import org.apache.poi.ss.usermodel.*;
 import org.junit.jupiter.api.BeforeEach;
 import org.junit.jupiter.api.Test;
 
-import java.util.Arrays;
-
 import static org.junit.jupiter.api.Assertions.assertEquals;
 
 /**