in poi/src/main/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java [422:534]
private boolean checkFilter(Cell cell, CellReference ref, CellRangeAddress region) {
final ConditionFilterType filterType = rule.getConditionFilterType();
if (filterType == null) {
return false;
}
final ValueAndFormat cv = getCellValue(cell);
// TODO: this could/should be delegated to the Enum type, but that's in the usermodel package,
// we may not want evaluation code there. Of course, maybe the enum should go here in formula,
// and not be returned by the SS model, but then we need the XSSF rule to expose the raw OOXML
// type value, which isn't ideal either.
switch (filterType) {
case FILTER:
return false; // we don't evaluate HSSF filters yet
case TOP_10:
// from testing, Excel only operates on numbers and dates (which are stored as numbers) in the range.
// numbers stored as text are ignored, but numbers formatted as text are treated as numbers.
if (! cv.isNumber()) {
return false;
}
return getMeaningfulValues(region, false, this::evaluateTop10).contains(cv);
case UNIQUE_VALUES:
// Per Excel help, "duplicate" means matching value AND format
// https://support.office.com/en-us/article/Filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2
return getMeaningfulValues(region, true, this::evaluateUniqueValues).contains(cv);
case DUPLICATE_VALUES:
// Per Excel help, "duplicate" means matching value AND format
// https://support.office.com/en-us/article/Filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2
return getMeaningfulValues(region, true, this::evaluateDuplicateValues).contains(cv);
case ABOVE_AVERAGE:
// from testing, Excel only operates on numbers and dates (which are stored as numbers) in the range.
// numbers stored as text are ignored, but numbers formatted as text are treated as numbers.
final ConditionFilterData conf = rule.getFilterConfiguration();
// actually ordered, so iteration order is predictable
List<ValueAndFormat> values = new ArrayList<>(getMeaningfulValues(region, false, this::evaluateAboveAverage));
Double val = cv.isNumber() ? cv.getValue() : null;
if (val == null) {
return false;
}
double avg = values.get(0).value;
double stdDev = values.get(1).value;
/*
* use StdDev, aboveAverage, equalAverage to find:
* comparison value
* operator type
*/
Double comp = conf.getStdDev() > 0 ? (avg + (conf.getAboveAverage() ? 1 : -1) * stdDev * conf.getStdDev()) : avg;
final OperatorEnum op;
if (conf.getAboveAverage()) {
if (conf.getEqualAverage()) {
op = OperatorEnum.GREATER_OR_EQUAL;
} else {
op = OperatorEnum.GREATER_THAN;
}
} else {
if (conf.getEqualAverage()) {
op = OperatorEnum.LESS_OR_EQUAL;
} else {
op = OperatorEnum.LESS_THAN;
}
}
return op.isValid(val, comp, null);
case CONTAINS_TEXT:
// implemented both by a cfRule "text" attribute and a formula. Use the text.
return text != null && cv.toString().toLowerCase(LocaleUtil.getUserLocale()).contains(lowerText);
case NOT_CONTAINS_TEXT:
// implemented both by a cfRule "text" attribute and a formula. Use the text.
return text == null || !cv.toString().toLowerCase(LocaleUtil.getUserLocale()).contains(lowerText);
case BEGINS_WITH:
// implemented both by a cfRule "text" attribute and a formula. Use the text.
return cv.toString().toLowerCase(LocaleUtil.getUserLocale()).startsWith(lowerText);
case ENDS_WITH:
// implemented both by a cfRule "text" attribute and a formula. Use the text.
return cv.toString().toLowerCase(LocaleUtil.getUserLocale()).endsWith(lowerText);
case CONTAINS_BLANKS:
try {
String v = cv.getString();
// see TextFunction.TRIM for implementation
return StringUtil.isBlank(v);
} catch (Exception e) {
// not a valid string value, and not a blank cell (that's checked earlier)
return false;
}
case NOT_CONTAINS_BLANKS:
try {
String v = cv.getString();
// see TextFunction.TRIM for implementation
return StringUtil.isNotBlank(v);
} catch (Exception e) {
// not a valid string value, but not blank
return true;
}
case CONTAINS_ERRORS:
return cell != null && DataValidationEvaluator.isType(cell, CellType.ERROR);
case NOT_CONTAINS_ERRORS:
return cell == null || ! DataValidationEvaluator.isType(cell, CellType.ERROR);
case TIME_PERIOD:
// implemented both by a cfRule "text" attribute and a formula. Use the formula.
return checkFormula(ref, region);
default:
return false;
}
}