[bug-64633] add excel function: TEXTJOIN - thanks to David Gordon

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

3 files changed, 261 insertions, 0 deletions

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
index 096f6b2ed2..d1ae07cf67 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
@@ -184,6 +184,7 @@ public final class AnalysisToolPak implements UDFFinder {
         r(m, "TBILLEQ", null);
         r(m, "TBILLPRICE", null);
         r(m, "TBILLYIELD", null);
+        r(m, "TEXTJOIN", TextJoinFunction.instance);
         r(m, "WEEKNUM", WeekNum.instance);
         r(m, "WORKDAY", WorkdayFunction.instance);
         r(m, "XIRR", null);
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java
new file mode 100644
index 0000000000..16289a8776
--- /dev/null
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java
@@ -0,0 +1,89 @@
+/* ====================================================================
+   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.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.*;
+import org.apache.poi.ss.formula.functions.FreeRefFunction;
+
+import java.util.ArrayList;
+
+/**
+ * Implementation of Excel function TEXTJOIN()
+ *
+ * @since POI 5.0.1
+ */
+final class TextJoinFunction implements FreeRefFunction {
+
+    public static final FreeRefFunction instance = new TextJoinFunction(ArgumentsEvaluator.instance);
+
+    private ArgumentsEvaluator evaluator;
+
+    private TextJoinFunction(ArgumentsEvaluator anEvaluator) {
+        // enforces singleton
+        this.evaluator = anEvaluator;
+    }
+
+    public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+        /*
+         * Must be at least three arguments:
+         *  - delimiter    Delimiter for joining text arguments
+         *  - ignoreEmpty  If true, empty strings will be ignored in the join
+         *  - text1		   First value to be evaluated as text and joined
+         *  - text2, etc.  Optional additional values to be evaluated and joined
+         */
+
+        // Make sure we have at least one text value, and at most 252 text values, as documented at:
+        // https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c?ui=en-us&rs=en-us&ad=us
+        if (args.length < 3 || args.length > 254) {
+            return ErrorEval.VALUE_INVALID;
+        }
+
+        int srcRowIndex = ec.getRowIndex();
+        int srcColumnIndex = ec.getColumnIndex();
+
+        try {
+            // Get the delimiter argument
+            ValueEval delimiterArg = OperandResolver.getSingleValue(args[0], srcRowIndex, srcColumnIndex);
+            String delimiter = OperandResolver.coerceValueToString(delimiterArg);
+
+            // Get the boolean ignoreEmpty argument
+            ValueEval ignoreEmptyArg = OperandResolver.getSingleValue(args[1], srcRowIndex, srcColumnIndex);
+            boolean ignoreEmpty = OperandResolver.coerceValueToBoolean(ignoreEmptyArg, false);
+
+            // Get a list of string values for each text argument
+            ArrayList<String> textValues = new ArrayList<>();
+
+            for (int i = 2; i < args.length; i++) {
+                ValueEval textArg = OperandResolver.getSingleValue(args[i], srcRowIndex, srcColumnIndex);
+                String textValue = OperandResolver.coerceValueToString(textArg);
+
+                // If we're not ignoring empty values or if our value is not empty, add it to the list
+                if (!ignoreEmpty || (textValue != null && textValue.length() > 0)) {
+                    textValues.add(textValue);
+                }
+            }
+
+            // Join the list of values with the specified delimiter and return
+            return new StringEval(String.join(delimiter, textValues));
+        } catch (EvaluationException e){
+            return e.getErrorEval();
+        }
+    }
+
+}
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
new file mode 100644
index 0000000000..a82521c464
--- /dev/null
+++ b/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java
@@ -0,0 +1,171 @@
+
+/* ====================================================================
+   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.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;
+
+/**
+ * Testcase for function TEXTJOIN()
+ */
+public class TestTextJoinFunction {
+
+    private Workbook wb;
+    private Sheet sheet;
+    private FormulaEvaluator evaluator;
+    private Cell textCell1;
+    private Cell textCell2;
+    private Cell textCell3;
+    private Cell numericCell1;
+    private Cell numericCell2;
+    private Cell blankCell;
+    private Cell emptyCell;
+    private Cell formulaCell;
+
+    @BeforeEach
+    public void setUp() throws Exception {
+        wb = new HSSFWorkbook();
+        evaluator = wb.getCreationHelper().createFormulaEvaluator();
+
+        sheet = wb.createSheet("TextJoin");
+        Row row = sheet.createRow(0);
+
+        textCell1 = row.createCell(0);
+        textCell1.setCellValue("One");
+
+        textCell2 = row.createCell(1);
+        textCell2.setCellValue("Two");
+
+        blankCell = row.createCell(2);
+        blankCell.setBlank();
+
+        numericCell1 = row.createCell(3);
+        numericCell1.setCellValue(1);
+
+        numericCell2 = row.createCell(4);
+        numericCell2.setCellValue(2);
+
+        formulaCell = row.createCell(100, CellType.FORMULA);
+    }
+
+    @Test
+    public void testJoinSingleLiteralText() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", true, \"Text\")");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals("Text", formulaCell.getStringCellValue());
+    }
+
+    @Test
+    public void testJoinMultipleLiteralText() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", true, \"One\", \"Two\", \"Three\")");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals("One,Two,Three", formulaCell.getStringCellValue());
+    }
+
+    @Test
+    public void testJoinLiteralTextAndNumber() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", true, \"Text\", 1)");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals("Text,1", formulaCell.getStringCellValue());
+    }
+
+    @Test
+    public void testJoinEmptyStringIncludeEmpty() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", false, \"A\", \"\", \"B\")");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals("A,,B", formulaCell.getStringCellValue());
+    }
+
+    @Test
+    public void testJoinEmptyStringIgnoreEmpty() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", true, \"A\", \"\", \"B\")");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals("A,B", formulaCell.getStringCellValue());
+    }
+
+    @Test
+    public void testJoinEmptyStringsIncludeEmpty() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", false, \"\", \"\")");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals(",", formulaCell.getStringCellValue());
+    }
+
+    @Test
+    public void testJoinEmptyStringsIgnoreEmpty() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", true, \"\", \"\")");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals("", formulaCell.getStringCellValue());
+    }
+
+    @Test
+    public void testJoinTextCellValues() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", true, A1, B1)");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals("One,Two", formulaCell.getStringCellValue());
+    }
+
+    @Test
+    public void testJoinNumericCellValues() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", true, D1, E1)");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals("1,2", formulaCell.getStringCellValue());
+    }
+
+    @Test
+    public void testJoinBlankCellIncludeEmpty() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", false, A1, C1, B1)");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals("One,,Two", formulaCell.getStringCellValue());
+    }
+
+    @Test
+    public void testJoinBlankCellIgnoreEmpty() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", true, A1, C1, B1)");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals("One,Two", formulaCell.getStringCellValue());
+    }
+
+    @Test
+    public void testNoTextArgument() {
+        evaluator.clearAllCachedResultValues();
+        formulaCell.setCellFormula("TEXTJOIN(\",\", true)");
+        evaluator.evaluateFormulaCell(formulaCell);
+        assertEquals(CellType.ERROR, formulaCell.getCachedFormulaResultType());
+        assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), formulaCell.getErrorCellValue());
+    }
+
+}