From 93bba050ac4ff92c7bbe20b7709e73960ec0e558 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sat, 7 Aug 2021 10:25:24 +0000 Subject: [PATCH] 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 --- .../org/apache/poi/ss/formula/atp/IfNa.java | 2 +- .../poi/ss/formula/atp/TextJoinFunction.java | 41 ++++++++++++++++--- .../ss/formula/atp/TestTextJoinFunction.java | 38 +++++++++++++++++ 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() * * Syntax:
- * IFNA(test_value,default_value)

+ * IFNA(test_value, default_value)

* * test_value The value to be tested
* default_value The value to be tested
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() * + * Syntax
+ * TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

+ * + * delimiter 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.
+ * ignore_empty If TRUE, ignores empty cells.
+ * text1 Text item to be joined. A text string, or array of strings, such as a range of cells.
+ * text2 ... 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.
+ * * @since POI 5.0.1 */ final class TextJoinFunction implements FreeRefFunction { @@ -70,12 +82,14 @@ final class TextJoinFunction implements FreeRefFunction { ArrayList 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 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 getValues(ValueEval eval, int srcRowIndex, int srcColumnIndex) throws EvaluationException { + if (eval instanceof AreaEval) { + AreaEval ae = (AreaEval)eval; + List 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()); + } } -- 2.39.5