From f00456e38d5be4c268a78dae383df6f93e7f8005 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sat, 7 Aug 2021 11:16:34 +0000 Subject: [PATCH] fix TextJoin use case that was not handled git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1892067 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/atp/TextJoinFunction.java | 37 ++++++++++++++++--- .../ss/formula/atp/TestTextJoinFunction.java | 24 ++++++++++++ 2 files changed, 55 insertions(+), 6 deletions(-) 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 5c64fd8bcd..8cd588715a 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 @@ -71,8 +71,7 @@ final class TextJoinFunction implements FreeRefFunction { try { // Get the delimiter argument - ValueEval delimiterArg = OperandResolver.getSingleValue(args[0], srcRowIndex, srcColumnIndex); - String delimiter = OperandResolver.coerceValueToString(delimiterArg); + List delimiterArgs = getValues(args[0], srcRowIndex, srcColumnIndex, true); // Get the boolean ignoreEmpty argument ValueEval ignoreEmptyArg = OperandResolver.getSingleValue(args[1], srcRowIndex, srcColumnIndex); @@ -82,7 +81,7 @@ final class TextJoinFunction implements FreeRefFunction { ArrayList textValues = new ArrayList<>(); for (int i = 2; i < args.length; i++) { - List textArgs = getValues(args[i], srcRowIndex, srcColumnIndex); + List textArgs = getValues(args[i], srcRowIndex, srcColumnIndex, false); for (ValueEval textArg : textArgs) { String textValue = OperandResolver.coerceValueToString(textArg); @@ -94,17 +93,43 @@ final class TextJoinFunction implements FreeRefFunction { } // Join the list of values with the specified delimiter and return - return new StringEval(String.join(delimiter, textValues)); + if (delimiterArgs.size() == 0) { + return new StringEval(String.join("", textValues)); + } else if (delimiterArgs.size() == 1) { + String delimiter = OperandResolver.coerceValueToString(delimiterArgs.get(0)); + return new StringEval(String.join(delimiter, textValues)); + } else { + //https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c + //see example 3 to see why this is needed + List delimiters = new ArrayList<>(); + for (ValueEval delimiterArg: delimiterArgs) { + delimiters.add(OperandResolver.coerceValueToString(delimiterArg)); + } + StringBuilder sb = new StringBuilder(); + for (int i = 0; i < textValues.size(); i++) { + if (i > 0) { + int delimiterIndex = (i - 1) % delimiters.size(); + sb.append(delimiters.get(delimiterIndex)); + } + sb.append(textValues.get(i)); + } + return new StringEval(sb.toString()); + } } catch (EvaluationException e){ return e.getErrorEval(); } } - private List getValues(ValueEval eval, int srcRowIndex, int srcColumnIndex) throws EvaluationException { + //https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c + //in example 3, the delimiter is defined by a large area but only the last row of that area seems to be used + //this is why lastRowOnly is supported + private List getValues(ValueEval eval, int srcRowIndex, int srcColumnIndex, + boolean lastRowOnly) throws EvaluationException { if (eval instanceof AreaEval) { AreaEval ae = (AreaEval)eval; List list = new ArrayList<>(); - for (int r = ae.getFirstRow(); r <= ae.getLastRow(); r++) { + int startRow = lastRowOnly ? ae.getLastRow() : ae.getFirstRow(); + for (int r = startRow; r <= ae.getLastRow(); r++) { for (int c = ae.getFirstColumn(); c <= ae.getLastColumn(); c++) { list.add(OperandResolver.getSingleValue(ae.getAbsoluteValue(r, c), r, c)); } 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 29d716bcec..6ece0eae71 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 @@ -194,6 +194,16 @@ public class TestTextJoinFunction { } } + @Test + void testMicrosoftExample3() throws IOException { + try (HSSFWorkbook wb = initWorkbook3()) { + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); + confirmResult(fe, cell, "TEXTJOIN(A8:D8, TRUE, A2:D7)", + "Tulsa,OK,74133,US;Seattle,WA,98109,US;Iselin,NJ,08830,US;Fort Lauderdale,FL,33309,US;Tempe,AZ,85285,US;end"); + } + } + private HSSFWorkbook initWorkbook1() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); @@ -220,6 +230,20 @@ public class TestTextJoinFunction { return wb; } + private HSSFWorkbook initWorkbook3() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + addRow(sheet, 0, "City", "State", "Postcode", "Country"); + addRow(sheet, 1, "Tulsa", "OK", "74133", "US"); + addRow(sheet, 2, "Seattle", "WA", "98109", "US"); + addRow(sheet, 3, "Iselin", "NJ", "08830", "US"); + addRow(sheet, 4, "Fort Lauderdale", "FL", "33309", "US"); + addRow(sheet, 5, "Tempe", "AZ", "85285", "US"); + addRow(sheet, 6, "end"); + addRow(sheet, 7, ",", ",", ",", ";"); + return wb; + } + private static void confirmResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, String expectedResult) { cell.setCellFormula(formulaText); fe.notifyUpdateCell(cell); -- 2.39.5