fix TextJoin use case that was not handled

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

3 files changed, 74 insertions, 7 deletions

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/IfNa.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/IfNa.java
index a3885601f4..7d9659c173 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/atp/IfNa.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/IfNa.java
@@ -28,7 +28,7 @@ import org.apache.poi.ss.formula.functions.FreeRefFunction;
  * Implementation of 'Analysis Toolpak' the Excel function IFNA()
  *
  * <b>Syntax</b>:<br>
- * <b>IFNA</b>(<b>test_value</b>,<b>default_value</b>)<p>
+ * <b>IFNA</b>(<b>test_value</b>, <b>default_value</b>)<p>
  *
  * <b>test_value</b>  The value to be tested<br>
  * <b>default_value</b>  The value to be tested<br>
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
index 16289a8776..1148267b63 100644
--- 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
@@ -22,10 +22,22 @@ import org.apache.poi.ss.formula.eval.*;
 import org.apache.poi.ss.formula.functions.FreeRefFunction;
 
 import java.util.ArrayList;
+import java.util.Collections;
+import java.util.List;
 
 /**
  * Implementation of Excel function TEXTJOIN()
  *
+ * <b>Syntax</b><br>
+ * <b>TEXTJOIN</b>(<b>delimiter</b>, <b>ignore_empty</b>, <b>text1</b>, <b>[text2]<b>, …)<p>
+ *
+ * <b>delimiter</b> A text string, either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string.
+ * If a number is supplied, it will be treated as text.<br>
+ * <b>ignore_empty</b> If TRUE, ignores empty cells.<br>
+ * <b>text1</b> Text item to be joined. A text string, or array of strings, such as a range of cells.<br>
+ * <b>text2 ...</b> Optional. Additional text items to be joined. There can be a maximum of 252 text arguments for the text items, including text1.
+ * Each can be a text string, or array of strings, such as a range of cells.<br>
+ *
  * @since POI 5.0.1
  */
 final class TextJoinFunction implements FreeRefFunction {
@@ -70,12 +82,14 @@ final class TextJoinFunction implements FreeRefFunction {
             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);
+                List<ValueEval> textArgs = getValues(args[i], srcRowIndex, srcColumnIndex);
+                for (ValueEval textArg : textArgs) {
+                    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);
+                    }
                 }
             }
 
@@ -86,4 +100,19 @@ final class TextJoinFunction implements FreeRefFunction {
         }
     }
 
+    private List<ValueEval> getValues(ValueEval eval, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
+        if (eval instanceof AreaEval) {
+            AreaEval ae = (AreaEval)eval;
+            List<ValueEval> list = new ArrayList<>();
+            for (int r = 0; r < ae.getHeight(); r++) {
+                for (int c = 0; c < ae.getWidth(); c++) {
+                    list.add(ae.getRelativeValue(r, c));
+                }
+            }
+            return list;
+        } else {
+            return Collections.singletonList(OperandResolver.getSingleValue(eval, srcRowIndex, srcColumnIndex));
+        }
+    }
+
 }
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 598542efa0..00792985ac 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,12 +17,18 @@
 ==================================================================== */
 package org.apache.poi.ss.formula.atp;
 
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
 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.io.IOException;
+
+import static org.apache.poi.ss.util.Utils.addRow;
 import static org.junit.jupiter.api.Assertions.assertEquals;
 
 /**
@@ -165,4 +171,36 @@ public class TestTextJoinFunction {
         assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), formulaCell.getErrorCellValue());
     }
 
+    //https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
+    @Test
+    void testMicrosoftExample1() throws IOException {
+        try (HSSFWorkbook wb = initWorkbook1()) {
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+            confirmResult(fe, cell, "TEXTJOIN(\", \", TRUE, A2:A8)",
+                    "US Dollar, Australian Dollar, Chinese Yuan, Hong Kong Dollar, Israeli Shekel, South Korean Won, Russian Ruble");
+        }
+    }
+
+    private HSSFWorkbook initWorkbook1() {
+        HSSFWorkbook wb = new HSSFWorkbook();
+        HSSFSheet sheet = wb.createSheet();
+        addRow(sheet, 0, "Currency");
+        addRow(sheet, 1, "US Dollar");
+        addRow(sheet, 2, "Australian Dollar");
+        addRow(sheet, 3, "Chinese Yuan");
+        addRow(sheet, 4, "Hong Kong Dollar");
+        addRow(sheet, 5, "Israeli Shekel");
+        addRow(sheet, 6, "South Korean Won");
+        addRow(sheet, 7, "Russian Ruble");
+        return wb;
+    }
+
+    private static void confirmResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, String expectedResult) {
+        cell.setCellFormula(formulaText);
+        fe.notifyUpdateCell(cell);
+        CellValue result = fe.evaluate(cell);
+        assertEquals(result.getCellType(), CellType.STRING);
+        assertEquals(expectedResult, result.getStringValue());
+    }
 }