org.apache.poi.ss.formula.functions.Countif Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of aem-sdk-api Show documentation
Show all versions of aem-sdk-api Show documentation
The Adobe Experience Manager SDK
/* ====================================================================
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.functions;
import java.util.regex.Pattern;
import org.apache.poi.ss.formula.ThreeDEval;
import org.apache.poi.ss.formula.eval.BlankEval;
import org.apache.poi.ss.formula.eval.BoolEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.RefEval;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate;
import org.apache.poi.ss.usermodel.FormulaError;
/**
* Implementation for the function COUNTIF
*
* Syntax: COUNTIF ( range, criteria )
*
* Parameter descriptions
* range is the range of cells to be counted based on the criteria criteria is used to determine which cells to count
*/
public final class Countif extends Fixed2ArgFunction {
private static final class CmpOp {
public static final int NONE = 0;
public static final int EQ = 1;
public static final int NE = 2;
public static final int LE = 3;
public static final int LT = 4;
public static final int GT = 5;
public static final int GE = 6;
public static final CmpOp OP_NONE = op("", NONE);
public static final CmpOp OP_EQ = op("=", EQ);
public static final CmpOp OP_NE = op("<>", NE);
public static final CmpOp OP_LE = op("<=", LE);
public static final CmpOp OP_LT = op("<", LT);
public static final CmpOp OP_GT = op(">", GT);
public static final CmpOp OP_GE = op(">=", GE);
private final String _representation;
private final int _code;
private static CmpOp op(String rep, int code) {
return new CmpOp(rep, code);
}
private CmpOp(String representation, int code) {
_representation = representation;
_code = code;
}
/**
* @return number of characters used to represent this operator
*/
public int getLength() {
return _representation.length();
}
public int getCode() {
return _code;
}
public static CmpOp getOperator(String value) {
int len = value.length();
if (len < 1) {
return OP_NONE;
}
char firstChar = value.charAt(0);
switch(firstChar) {
case '=':
return OP_EQ;
case '>':
if (len > 1) {
switch(value.charAt(1)) {
case '=':
return OP_GE;
}
}
return OP_GT;
case '<':
if (len > 1) {
switch(value.charAt(1)) {
case '=':
return OP_LE;
case '>':
return OP_NE;
}
}
return OP_LT;
}
return OP_NONE;
}
public boolean evaluate(boolean cmpResult) {
switch (_code) {
case NONE:
case EQ:
return cmpResult;
case NE:
return !cmpResult;
}
throw new RuntimeException("Cannot call boolean evaluate on non-equality operator '"
+ _representation + "'");
}
public boolean evaluate(int cmpResult) {
switch (_code) {
case NONE:
case EQ:
return cmpResult == 0;
case NE: return cmpResult != 0;
case LT: return cmpResult < 0;
case LE: return cmpResult <= 0;
case GT: return cmpResult > 0;
case GE: return cmpResult >= 0;
}
throw new RuntimeException("Cannot call boolean evaluate on non-equality operator '"
+ _representation + "'");
}
@Override
public String toString() {
return getClass().getName() + " [" + _representation + "]";
}
public String getRepresentation() {
return _representation;
}
}
private static abstract class MatcherBase implements I_MatchPredicate {
private final CmpOp _operator;
MatcherBase(CmpOp operator) {
_operator = operator;
}
protected final int getCode() {
return _operator.getCode();
}
protected final boolean evaluate(int cmpResult) {
return _operator.evaluate(cmpResult);
}
protected final boolean evaluate(boolean cmpResult) {
return _operator.evaluate(cmpResult);
}
@Override
public final String toString() {
return getClass().getName() + " [" + _operator.getRepresentation() + getValueText() + "]";
}
protected abstract String getValueText();
}
private static final class NumberMatcher extends MatcherBase {
private final double _value;
public NumberMatcher(double value, CmpOp operator) {
super(operator);
_value = value;
}
@Override
protected String getValueText() {
return String.valueOf(_value);
}
@Override
public boolean matches(ValueEval x) {
double testValue;
if(x instanceof StringEval) {
// if the target(x) is a string, but parses as a number
// it may still count as a match, only for the equality operator
switch (getCode()) {
case CmpOp.EQ:
case CmpOp.NONE:
break;
case CmpOp.NE:
// Always matches (inconsistent with above two cases).
// for example '<>123' matches '123', '4', 'abc', etc
return true;
default:
// never matches (also inconsistent with above three cases).
// for example '>5' does not match '6',
return false;
}
StringEval se = (StringEval)x;
Double val = OperandResolver.parseDouble(se.getStringValue());
if(val == null) {
// x is text that is not a number
return false;
}
return _value == val;
} else if((x instanceof NumberEval)) {
NumberEval ne = (NumberEval) x;
testValue = ne.getNumberValue();
} else if((x instanceof BlankEval)) {
switch (getCode()) {
case CmpOp.NE:
// Excel counts blank values in range as not equal to any value. See Bugzilla 51498
return true;
default:
return false;
}
} else {
return false;
}
return evaluate(Double.compare(testValue, _value));
}
}
private static final class BooleanMatcher extends MatcherBase {
private final int _value;
public BooleanMatcher(boolean value, CmpOp operator) {
super(operator);
_value = boolToInt(value);
}
@Override
protected String getValueText() {
return _value == 1 ? "TRUE" : "FALSE";
}
private static int boolToInt(boolean value) {
return value ? 1 : 0;
}
@Override
public boolean matches(ValueEval x) {
int testValue;
if(x instanceof StringEval) {
// Note - Unlike with numbers, it seems that COUNTIF never matches
// boolean values when the target(x) is a string
return false;
// uncomment to observe more intuitive behaviour
// StringEval se = (StringEval)x;
// Boolean val = parseBoolean(se.getStringValue());
// if(val == null) {
// // x is text that is not a boolean
// return false;
// }
// testValue = boolToInt(val.booleanValue());
} else if((x instanceof BoolEval)) {
BoolEval be = (BoolEval) x;
testValue = boolToInt(be.getBooleanValue());
} else if((x instanceof BlankEval)) {
switch (getCode()) {
case CmpOp.NE:
// Excel counts blank values in range as not equal to any value. See Bugzilla 51498
return true;
default:
return false;
}
} else if((x instanceof NumberEval)) {
switch (getCode()) {
case CmpOp.NE:
// not-equals comparison of a number to boolean always returnes false
return true;
default:
return false;
}
} else {
return false;
}
return evaluate(testValue - _value);
}
}
public static final class ErrorMatcher extends MatcherBase {
private final int _value;
public ErrorMatcher(int errorCode, CmpOp operator) {
super(operator);
_value = errorCode;
}
@Override
protected String getValueText() {
return FormulaError.forInt(_value).getString();
}
@Override
public boolean matches(ValueEval x) {
if(x instanceof ErrorEval) {
int testValue = ((ErrorEval)x).getErrorCode();
return evaluate(testValue - _value);
}
return false;
}
public int getValue() {
return _value;
}
}
public static final class StringMatcher extends MatcherBase {
private final String _value;
private final Pattern _pattern;
public StringMatcher(String value, CmpOp operator) {
super(operator);
_value = value;
switch(operator.getCode()) {
case CmpOp.NONE:
case CmpOp.EQ:
case CmpOp.NE:
_pattern = getWildCardPattern(value);
break;
default:
// pattern matching is never used for < > <= =>
_pattern = null;
}
}
@Override
protected String getValueText() {
if (_pattern == null) {
return _value;
}
return _pattern.pattern();
}
@Override
public boolean matches(ValueEval x) {
if (x instanceof BlankEval) {
switch(getCode()) {
case CmpOp.NONE:
case CmpOp.EQ:
return _value.length() == 0;
case CmpOp.NE:
// pred '<>' matches empty string but not blank cell
// pred '<>ABC' matches blank and 'not ABC'
return _value.length() != 0;
}
// no other criteria matches a blank cell
return false;
}
if(!(x instanceof StringEval)) {
// must always be string
// even if match str is wild, but contains only digits
// e.g. '4*7', NumberEval(4567) does not match
return false;
}
String testedValue = ((StringEval) x).getStringValue();
if (testedValue.length() < 1 && _value.length() < 1) {
// odd case: criteria '=' behaves differently to criteria ''
switch(getCode()) {
case CmpOp.NONE: return true;
case CmpOp.EQ: return false;
case CmpOp.NE: return true;
}
return false;
}
if (_pattern != null) {
return evaluate(_pattern.matcher(testedValue).matches());
}
// String criteria in COUNTIF are case insensitive:
// for example, the string "apples" and the string "APPLES" will match the same cells.
return evaluate(testedValue.compareToIgnoreCase(_value));
}
/**
* Translates Excel countif wildcard strings into java regex strings
* @return {@code null} if the specified value contains no special wildcard characters.
*/
public static Pattern getWildCardPattern(String value) {
int len = value.length();
StringBuilder sb = new StringBuilder(len);
boolean hasWildCard = false;
for(int i=0; i© 2015 - 2024 Weber Informatics LLC | Privacy Policy