From c93d9508ad1c15948b3b3428d1e04cfc6698a7b2 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Wed, 28 Jul 2021 13:48:55 +0000 Subject: [PATCH] [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 --- .../poi/ss/formula/atp/AnalysisToolPak.java | 1 + .../poi/ss/formula/atp/TextJoinFunction.java | 89 +++++++++ .../ss/formula/atp/TestTextJoinFunction.java | 171 ++++++++++++++++++ 3 files changed, 261 insertions(+) create mode 100644 poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java create mode 100644 poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java 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 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()); + } + +} -- 2.39.5