org.apache.poi.ss.formula.atp.TextJoinFunction Maven / Gradle / Ivy
/* ====================================================================
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;
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.1.0
*/
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
List delimiterArgs = getValues(args[0], srcRowIndex, srcColumnIndex, true);
// 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++) {
List textArgs = getValues(args[i], srcRowIndex, srcColumnIndex, false);
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);
}
}
}
// Join the list of values with the specified delimiter and return
if (delimiterArgs.isEmpty()) {
return new StringEval(String.join("", textValues));
} else if (delimiterArgs.size() == 1) {
String delimiter = laxValueToString(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(laxValueToString(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 String laxValueToString(ValueEval eval) {
return (eval instanceof MissingArgEval) ? "" : OperandResolver.coerceValueToString(eval);
}
//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<>();
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));
}
}
return list;
} else {
return Collections.singletonList(OperandResolver.getSingleValue(eval, srcRowIndex, srcColumnIndex));
}
}
}