| /* ==================================================================== |
| 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; |
| |
| import java.text.DecimalFormat; |
| import java.text.DecimalFormatSymbols; |
| import java.util.ArrayList; |
| import java.util.Collections; |
| import java.util.HashMap; |
| import java.util.HashSet; |
| import java.util.LinkedHashSet; |
| import java.util.List; |
| import java.util.Locale; |
| import java.util.Map; |
| import java.util.Objects; |
| import java.util.Set; |
| |
| 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.NumberEval; |
| 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.AggregateFunction; |
| import org.apache.poi.ss.usermodel.Cell; |
| import org.apache.poi.ss.usermodel.CellType; |
| import org.apache.poi.ss.usermodel.ConditionFilterData; |
| import org.apache.poi.ss.usermodel.ConditionFilterType; |
| import org.apache.poi.ss.usermodel.ConditionType; |
| import org.apache.poi.ss.usermodel.ConditionalFormatting; |
| import org.apache.poi.ss.usermodel.ConditionalFormattingRule; |
| import org.apache.poi.ss.usermodel.ExcelNumberFormat; |
| import org.apache.poi.ss.usermodel.Row; |
| import org.apache.poi.ss.usermodel.Sheet; |
| import org.apache.poi.ss.util.CellRangeAddress; |
| import org.apache.poi.ss.util.CellReference; |
| import org.apache.poi.util.LocaleUtil; |
| |
| /** |
| * Abstracted and cached version of a Conditional Format rule for use with a |
| * {@link ConditionalFormattingEvaluator}. This references a rule, its owning |
| * {@link ConditionalFormatting}, its priority order (lower index = higher priority in Excel), |
| * and the information needed to evaluate the rule for a given cell. |
| * <p> |
| * Having this all combined and cached avoids repeated access calls to the |
| * underlying structural objects, XSSF CT* objects and HSSF raw byte structures. |
| * Those objects can be referenced from here. This object will be out of sync if |
| * anything modifies the referenced structures' evaluation properties. |
| * <p> |
| * The assumption is that consuming applications will read the display properties once and |
| * create whatever style objects they need, caching those at the application level. |
| * Thus this class only caches values needed for evaluation, not display. |
| */ |
| public class EvaluationConditionalFormatRule implements Comparable<EvaluationConditionalFormatRule> { |
| |
| private final WorkbookEvaluator workbookEvaluator; |
| private final Sheet sheet; |
| private final ConditionalFormatting formatting; |
| private final ConditionalFormattingRule rule; |
| |
| /* cached values */ |
| private final CellRangeAddress[] regions; |
| |
| private CellRangeAddress topLeftRegion; |
| |
| /** |
| * Depending on the rule type, it may want to know about certain values in the region when evaluating {@link #matches(CellReference)}, |
| * such as top 10, unique, duplicate, average, etc. This collection stores those if needed so they are not repeatedly calculated |
| */ |
| private final Map<CellRangeAddress, Set<ValueAndFormat>> meaningfulRegionValues = new HashMap<>(); |
| |
| private final int priority; |
| private final int formattingIndex; |
| private final int ruleIndex; |
| private final String formula1; |
| private final String formula2; |
| private final String text; |
| // cached for performance, used with cell text comparisons, which are case insensitive and need to be Locale aware (contains, starts with, etc.) |
| private final String lowerText; |
| |
| private final OperatorEnum operator; |
| private final ConditionType type; |
| // cached for performance, to avoid reading the XMLBean every time a conditionally formatted cell is rendered |
| private final ExcelNumberFormat numberFormat; |
| // cached for performance, used to format numeric cells for string comparisons. See Bug #61764 for explanation |
| private final DecimalFormat decimalTextFormat; |
| |
| /** |
| * |
| * @param workbookEvaluator |
| * @param sheet |
| * @param formatting |
| * @param formattingIndex for priority, zero based |
| * @param rule |
| * @param ruleIndex for priority, zero based, if this is an HSSF rule. Unused for XSSF rules |
| * @param regions could be read from formatting, but every call creates new objects in a new array. |
| * this allows calling it once per formatting instance, and re-using the array. |
| */ |
| public EvaluationConditionalFormatRule(WorkbookEvaluator workbookEvaluator, Sheet sheet, ConditionalFormatting formatting, int formattingIndex, ConditionalFormattingRule rule, int ruleIndex, CellRangeAddress[] regions) { |
| super(); |
| this.workbookEvaluator = workbookEvaluator; |
| this.sheet = sheet; |
| this.formatting = formatting; |
| this.rule = rule; |
| this.formattingIndex = formattingIndex; |
| this.ruleIndex = ruleIndex; |
| |
| this.priority = rule.getPriority(); |
| |
| this.regions = regions; |
| |
| for (CellRangeAddress region : regions) { |
| if (topLeftRegion == null) topLeftRegion = region; |
| else if (region.getFirstColumn() < topLeftRegion.getFirstColumn() |
| || region.getFirstRow() < topLeftRegion.getFirstRow()) { |
| topLeftRegion = region; |
| } |
| } |
| formula1 = rule.getFormula1(); |
| formula2 = rule.getFormula2(); |
| |
| text = rule.getText(); |
| lowerText = text == null ? null : text.toLowerCase(LocaleUtil.getUserLocale()); |
| |
| numberFormat = rule.getNumberFormat(); |
| |
| operator = OperatorEnum.values()[rule.getComparisonOperation()]; |
| type = rule.getConditionType(); |
| |
| // Excel uses the stored text representation from the XML apparently, in tests done so far |
| decimalTextFormat = new DecimalFormat("0", DecimalFormatSymbols.getInstance(Locale.ENGLISH)); |
| decimalTextFormat.setMaximumFractionDigits(340); // DecimalFormat.DOUBLE_FRACTION_DIGITS, which is default scoped |
| } |
| |
| /** |
| * @return sheet |
| */ |
| public Sheet getSheet() { |
| return sheet; |
| } |
| |
| /** |
| * @return the formatting |
| */ |
| public ConditionalFormatting getFormatting() { |
| return formatting; |
| } |
| |
| /** |
| * @return conditional formatting index |
| */ |
| public int getFormattingIndex() { |
| return formattingIndex; |
| } |
| |
| /** |
| * @return Excel number format string to apply to matching cells, or null to keep the cell default |
| */ |
| public ExcelNumberFormat getNumberFormat() { |
| return numberFormat; |
| } |
| |
| /** |
| * @return the rule |
| */ |
| public ConditionalFormattingRule getRule() { |
| return rule; |
| } |
| |
| /** |
| * @return rule index |
| */ |
| public int getRuleIndex() { |
| return ruleIndex; |
| } |
| |
| /** |
| * @return the regions |
| */ |
| public CellRangeAddress[] getRegions() { |
| return regions; |
| } |
| |
| /** |
| * @return the priority |
| */ |
| public int getPriority() { |
| return priority; |
| } |
| |
| /** |
| * @return the formula1 |
| */ |
| public String getFormula1() { |
| return formula1; |
| } |
| |
| /** |
| * @return the formula2 |
| */ |
| public String getFormula2() { |
| return formula2; |
| } |
| |
| /** |
| * @return condition text if any, or null |
| */ |
| public String getText() { |
| return text; |
| } |
| |
| /** |
| * @return the operator |
| */ |
| public OperatorEnum getOperator() { |
| return operator; |
| } |
| |
| /** |
| * @return the type |
| */ |
| public ConditionType getType() { |
| return type; |
| } |
| |
| /** |
| * Defined as equal sheet name and formatting and rule indexes |
| * @see java.lang.Object#equals(java.lang.Object) |
| */ |
| @Override |
| public boolean equals(Object obj) { |
| if (obj == null) { |
| return false; |
| } |
| if (! obj.getClass().equals(this.getClass())) { |
| return false; |
| } |
| final EvaluationConditionalFormatRule r = (EvaluationConditionalFormatRule) obj; |
| return getSheet().getSheetName().equalsIgnoreCase(r.getSheet().getSheetName()) |
| && getFormattingIndex() == r.getFormattingIndex() |
| && getRuleIndex() == r.getRuleIndex(); |
| } |
| |
| /** |
| * Per Excel Help, XSSF rule priority is sheet-wide, not just within the owning ConditionalFormatting object. |
| * This can be seen by creating 4 rules applying to two different ranges and examining the XML. |
| * <p> |
| * HSSF priority is based on definition/persistence order. |
| * |
| * @param o |
| * @return comparison based on sheet name, formatting index, and rule priority |
| */ |
| @Override |
| public int compareTo(EvaluationConditionalFormatRule o) { |
| int cmp = getSheet().getSheetName().compareToIgnoreCase(o.getSheet().getSheetName()); |
| if (cmp != 0) { |
| return cmp; |
| } |
| |
| final int x = getPriority(); |
| final int y = o.getPriority(); |
| // logic from Integer.compare() |
| cmp = Integer.compare(x, y); |
| if (cmp != 0) { |
| return cmp; |
| } |
| |
| cmp = Integer.compare(getFormattingIndex(), o.getFormattingIndex()); |
| if (cmp != 0) { |
| return cmp; |
| } |
| return Integer.compare(getRuleIndex(), o.getRuleIndex()); |
| } |
| |
| @Override |
| public int hashCode() { |
| return Objects.hash(sheet.getSheetName(),formattingIndex,ruleIndex); |
| } |
| |
| /** |
| * @param ref |
| * @return true if this rule evaluates to true for the given cell |
| */ |
| /* package */ boolean matches(CellReference ref) { |
| // first check that it is in one of the regions defined for this format |
| CellRangeAddress region = null; |
| for (CellRangeAddress r : regions) { |
| if (r.isInRange(ref)) { |
| region = r; |
| break; |
| } |
| } |
| |
| if (region == null) { |
| // cell not in range of this rule |
| return false; |
| } |
| |
| final ConditionType ruleType = getRule().getConditionType(); |
| |
| // these rules apply to all cells in a region. Specific condition criteria |
| // may specify no special formatting for that value partition, but that's display logic |
| if (ruleType.equals(ConditionType.COLOR_SCALE) |
| || ruleType.equals(ConditionType.DATA_BAR) |
| || ruleType.equals(ConditionType.ICON_SET)) { |
| return true; |
| } |
| |
| Cell cell = null; |
| final Row row = sheet.getRow(ref.getRow()); |
| if (row != null) { |
| cell = row.getCell(ref.getCol()); |
| } |
| |
| if (ruleType.equals(ConditionType.CELL_VALUE_IS)) { |
| // undefined cells never match a VALUE_IS condition |
| if (cell == null) return false; |
| return checkValue(cell, topLeftRegion); |
| } |
| if (ruleType.equals(ConditionType.FORMULA)) { |
| return checkFormula(ref, topLeftRegion); |
| } |
| if (ruleType.equals(ConditionType.FILTER)) { |
| return checkFilter(cell, ref, topLeftRegion); |
| } |
| |
| // TODO: anything else, we don't handle yet, such as top 10 |
| return false; |
| } |
| |
| /** |
| * @param cell the cell to check for |
| * @param region for adjusting relative formulas |
| * @return if the value of the cell is valid or not for the formatting rule |
| */ |
| private boolean checkValue(Cell cell, CellRangeAddress region) { |
| if (cell == null || DataValidationEvaluator.isType(cell, CellType.BLANK) |
| || DataValidationEvaluator.isType(cell,CellType.ERROR) |
| || (DataValidationEvaluator.isType(cell,CellType.STRING) |
| && (cell.getStringCellValue() == null || cell.getStringCellValue().isEmpty()) |
| ) |
| ) { |
| return false; |
| } |
| |
| ValueEval eval = unwrapEval(workbookEvaluator.evaluate(rule.getFormula1(), ConditionalFormattingEvaluator.getRef(cell), region)); |
| |
| String f2 = rule.getFormula2(); |
| ValueEval eval2 = BlankEval.instance; |
| if (f2 != null && f2.length() > 0) { |
| eval2 = unwrapEval(workbookEvaluator.evaluate(f2, ConditionalFormattingEvaluator.getRef(cell), region)); |
| } |
| |
| // we assume the cell has been evaluated, and the current formula value stored |
| if (DataValidationEvaluator.isType(cell, CellType.BOOLEAN) |
| && (eval == BlankEval.instance || eval instanceof BoolEval) |
| && (eval2 == BlankEval.instance || eval2 instanceof BoolEval) |
| ) { |
| return operator.isValid(cell.getBooleanCellValue(), eval == BlankEval.instance ? null : ((BoolEval) eval).getBooleanValue(), eval2 == BlankEval.instance ? null : ((BoolEval) eval2).getBooleanValue()); |
| } |
| if (DataValidationEvaluator.isType(cell, CellType.NUMERIC) |
| && (eval == BlankEval.instance || eval instanceof NumberEval ) |
| && (eval2 == BlankEval.instance || eval2 instanceof NumberEval) |
| ) { |
| return operator.isValid(cell.getNumericCellValue(), eval == BlankEval.instance ? null : ((NumberEval) eval).getNumberValue(), eval2 == BlankEval.instance ? null : ((NumberEval) eval2).getNumberValue()); |
| } |
| if (DataValidationEvaluator.isType(cell, CellType.STRING) |
| && (eval == BlankEval.instance || eval instanceof StringEval ) |
| && (eval2 == BlankEval.instance || eval2 instanceof StringEval) |
| ) { |
| return operator.isValid(cell.getStringCellValue(), eval == BlankEval.instance ? null : ((StringEval) eval).getStringValue(), eval2 == BlankEval.instance ? null : ((StringEval) eval2).getStringValue()); |
| } |
| |
| return operator.isValidForIncompatibleTypes(); |
| } |
| |
| private ValueEval unwrapEval(ValueEval eval) { |
| ValueEval comp = eval; |
| |
| while (comp instanceof RefEval) { |
| RefEval ref = (RefEval) comp; |
| comp = ref.getInnerValueEval(ref.getFirstSheetIndex()); |
| } |
| return comp; |
| } |
| /** |
| * @param ref needed for offsets from region anchor - may be null! |
| * @param region for adjusting relative formulas |
| * @return true/false using the same rules as Data Validation evaluations |
| */ |
| private boolean checkFormula(CellReference ref, CellRangeAddress region) { |
| ValueEval comp = unwrapEval(workbookEvaluator.evaluate(rule.getFormula1(), ref, region)); |
| |
| // Copied for now from DataValidationEvaluator.ValidationEnum.FORMULA#isValidValue() |
| if (comp instanceof BlankEval) { |
| return true; |
| } |
| if (comp instanceof ErrorEval) { |
| return false; |
| } |
| if (comp instanceof BoolEval) { |
| return ((BoolEval) comp).getBooleanValue(); |
| } |
| // empirically tested in Excel - 0=false, any other number = true/valid |
| // see test file DataValidationEvaluations.xlsx |
| if (comp instanceof NumberEval) { |
| return ((NumberEval) comp).getNumberValue() != 0; |
| } |
| return false; // anything else is false, such as text |
| } |
| |
| 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, new ValueFunction() { |
| @Override |
| public Set<ValueAndFormat> evaluate(List<ValueAndFormat> allValues) { |
| final ConditionFilterData conf = rule.getFilterConfiguration(); |
| |
| if (! conf.getBottom()) { |
| allValues.sort(Collections.reverseOrder()); |
| } else { |
| Collections.sort(allValues); |
| } |
| |
| int limit = Math.toIntExact(conf.getRank()); |
| if (conf.getPercent()) { |
| limit = allValues.size() * limit / 100; |
| } |
| if (allValues.size() <= limit) { |
| return new HashSet<>(allValues); |
| } |
| |
| return new HashSet<>(allValues.subList(0, limit)); |
| } |
| }).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, new ValueFunction() { |
| @Override |
| public Set<ValueAndFormat> evaluate(List<ValueAndFormat> allValues) { |
| Collections.sort(allValues); |
| |
| final Set<ValueAndFormat> unique = new HashSet<>(); |
| |
| for (int i = 0; i < allValues.size(); i++) { |
| final ValueAndFormat v = allValues.get(i); |
| // skip this if the current value matches the next one, or is the last one and matches the previous one |
| if ( (i < allValues.size()-1 && v.equals(allValues.get(i+1)) ) || ( i > 0 && i == allValues.size()-1 && v.equals(allValues.get(i-1)) ) ) { |
| // current value matches next value, skip both |
| i++; |
| continue; |
| } |
| unique.add(v); |
| } |
| |
| return unique; |
| } |
| }).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, new ValueFunction() { |
| @Override |
| public Set<ValueAndFormat> evaluate(List<ValueAndFormat> allValues) { |
| Collections.sort(allValues); |
| |
| final Set<ValueAndFormat> dup = new HashSet<>(); |
| |
| for (int i = 0; i < allValues.size(); i++) { |
| final ValueAndFormat v = allValues.get(i); |
| // skip this if the current value matches the next one, or is the last one and matches the previous one |
| if ( (i < allValues.size()-1 && v.equals(allValues.get(i+1)) ) || ( i > 0 && i == allValues.size()-1 && v.equals(allValues.get(i-1)) ) ) { |
| // current value matches next value, add one |
| dup.add(v); |
| i++; |
| } |
| } |
| return dup; |
| } |
| }).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, new ValueFunction() { |
| @Override |
| public Set<ValueAndFormat> evaluate(List<ValueAndFormat> allValues) { |
| double total = 0; |
| ValueEval[] pop = new ValueEval[allValues.size()]; |
| for (int i = 0; i < allValues.size(); i++) { |
| ValueAndFormat v = allValues.get(i); |
| total += v.value.doubleValue(); |
| pop[i] = new NumberEval(v.value.doubleValue()); |
| } |
| |
| final Set<ValueAndFormat> avgSet = new LinkedHashSet<>(1); |
| avgSet.add(new ValueAndFormat(Double.valueOf(allValues.size() == 0 ? 0 : total / allValues.size()), null, decimalTextFormat)); |
| |
| final double stdDev = allValues.size() <= 1 ? 0 : ((NumberEval) AggregateFunction.STDEV.evaluate(pop, 0, 0)).getNumberValue(); |
| avgSet.add(new ValueAndFormat(Double.valueOf(stdDev), null, decimalTextFormat)); |
| return avgSet; |
| } |
| })); |
| |
| Double val = cv.isNumber() ? cv.getValue() : null; |
| if (val == null) { |
| return false; |
| } |
| |
| double avg = values.get(0).value.doubleValue(); |
| double stdDev = values.get(1).value.doubleValue(); |
| |
| /* |
| * use StdDev, aboveAverage, equalAverage to find: |
| * comparison value |
| * operator type |
| */ |
| |
| Double comp = Double.valueOf(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 ? false : 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 ? true : ! 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 v == null || v.trim().length() == 0; |
| } 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 v != null && v.trim().length() > 0; |
| } 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; |
| } |
| } |
| |
| /** |
| * 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. |
| * |
| * @param region |
| * @return the meaningful values in the range of cells specified |
| */ |
| private Set<ValueAndFormat> getMeaningfulValues(CellRangeAddress region, boolean withText, ValueFunction func) { |
| Set<ValueAndFormat> values = meaningfulRegionValues.get(region); |
| if (values != null) { |
| return values; |
| } |
| |
| List<ValueAndFormat> allValues = new ArrayList<>((region.getLastColumn() - region.getFirstColumn() + 1) * (region.getLastRow() - region.getFirstRow() + 1)); |
| |
| for (int r=region.getFirstRow(); r <= region.getLastRow(); r++) { |
| final Row row = sheet.getRow(r); |
| if (row == null) { |
| continue; |
| } |
| for (int c = region.getFirstColumn(); c <= region.getLastColumn(); c++) { |
| Cell cell = row.getCell(c); |
| final ValueAndFormat cv = getCellValue(cell); |
| if (withText || cv.isNumber()) { |
| allValues.add(cv); |
| } |
| } |
| } |
| |
| values = func.evaluate(allValues); |
| meaningfulRegionValues.put(region, values); |
| |
| return values; |
| } |
| |
| private ValueAndFormat getCellValue(Cell cell) { |
| if (cell != null) { |
| final String format = cell.getCellStyle().getDataFormatString(); |
| CellType type = cell.getCellType(); |
| if (type == CellType.FORMULA) { |
| type = cell.getCachedFormulaResultType(); |
| } |
| switch (type) { |
| case NUMERIC: |
| return new ValueAndFormat(Double.valueOf(cell.getNumericCellValue()), format, decimalTextFormat); |
| case STRING: |
| case BOOLEAN: |
| return new ValueAndFormat(cell.getStringCellValue(), format); |
| default: |
| break; |
| } |
| } |
| return new ValueAndFormat("", ""); |
| } |
| /** |
| * instances evaluate the values for a region and return the positive matches for the function type. |
| * TODO: when we get to use Java 8, this is obviously a Lambda Function. |
| */ |
| protected interface ValueFunction { |
| |
| /** |
| * |
| * @param values |
| * @return the desired values for the rules implemented by the current instance |
| */ |
| Set<ValueAndFormat> evaluate(List<ValueAndFormat> values); |
| } |
| |
| /** |
| * Not calling it OperatorType to avoid confusion for now with other classes. |
| * Definition order matches OOXML type ID indexes. |
| * Note that this has NO_COMPARISON as the first item, unlike the similar |
| * DataValidation operator enum. Thanks, Microsoft. |
| */ |
| public static enum OperatorEnum { |
| NO_COMPARISON { |
| /** always false/invalid */ |
| @Override |
| public <C extends Comparable<C>> boolean isValid(C cellValue, C v1, C v2) { |
| return false; |
| } |
| }, |
| BETWEEN { |
| @Override |
| public <C extends Comparable<C>> boolean isValid(C cellValue, C v1, C v2) { |
| if (v1 == null) { |
| if (cellValue instanceof Number) { |
| // use zero for null |
| double n1 = 0; |
| double n2 = v2 == null ? 0 : ((Number) v2).doubleValue(); |
| return Double.compare( ((Number) cellValue).doubleValue(), n1) >= 0 && Double.compare(((Number) cellValue).doubleValue(), n2) <= 0; |
| } else if (cellValue instanceof String) { |
| String n1 = ""; |
| String n2 = v2 == null ? "" : (String) v2; |
| return ((String) cellValue).compareToIgnoreCase(n1) >= 0 && ((String) cellValue).compareToIgnoreCase(n2) <= 0; |
| } else if (cellValue instanceof Boolean) return false; |
| return false; // just in case - not a typical possibility |
| } |
| return cellValue.compareTo(v1) >= 0 && cellValue.compareTo(v2) <= 0; |
| } |
| }, |
| NOT_BETWEEN { |
| @Override |
| public <C extends Comparable<C>> boolean isValid(C cellValue, C v1, C v2) { |
| if (v1 == null) { |
| if (cellValue instanceof Number) { |
| // use zero for null |
| double n1 = 0; |
| double n2 = v2 == null ? 0 : ((Number) v2).doubleValue(); |
| return Double.compare( ((Number) cellValue).doubleValue(), n1) < 0 || Double.compare(((Number) cellValue).doubleValue(), n2) > 0; |
| } else if (cellValue instanceof String) { |
| String n1 = ""; |
| String n2 = v2 == null ? "" : (String) v2; |
| return ((String) cellValue).compareToIgnoreCase(n1) < 0 || ((String) cellValue).compareToIgnoreCase(n2) > 0; |
| } else if (cellValue instanceof Boolean) return true; |
| return false; // just in case - not a typical possibility |
| } |
| return cellValue.compareTo(v1) < 0 || cellValue.compareTo(v2) > 0; |
| } |
| |
| public boolean isValidForIncompatibleTypes() { |
| return true; |
| } |
| }, |
| EQUAL { |
| @Override |
| public <C extends Comparable<C>> boolean isValid(C cellValue, C v1, C v2) { |
| if (v1 == null) { |
| if (cellValue instanceof Number) { |
| // use zero for null |
| return Double.compare( ((Number) cellValue).doubleValue(), 0) == 0; |
| } else if (cellValue instanceof String) { |
| return false; // even an empty string is not equal the empty cell, only another empty cell is, handled higher up |
| } else if (cellValue instanceof Boolean) return false; |
| return false; // just in case - not a typical possibility |
| } |
| // need to avoid instanceof, to work around a 1.6 compiler bug |
| if (cellValue.getClass() == String.class) { |
| return cellValue.toString().compareToIgnoreCase(v1.toString()) == 0; |
| } |
| return cellValue.compareTo(v1) == 0; |
| } |
| }, |
| NOT_EQUAL { |
| @Override |
| public <C extends Comparable<C>> boolean isValid(C cellValue, C v1, C v2) { |
| if (v1 == null) { |
| return true; // non-null not equal null, returns true |
| } |
| // need to avoid instanceof, to work around a 1.6 compiler bug |
| if (cellValue.getClass() == String.class) { |
| return cellValue.toString().compareToIgnoreCase(v1.toString()) == 0; |
| } |
| return cellValue.compareTo(v1) != 0; |
| } |
| |
| public boolean isValidForIncompatibleTypes() { |
| return true; |
| } |
| }, |
| GREATER_THAN { |
| @Override |
| public <C extends Comparable<C>> boolean isValid(C cellValue, C v1, C v2) { |
| if (v1 == null) { |
| if (cellValue instanceof Number) { |
| // use zero for null |
| return Double.compare( ((Number) cellValue).doubleValue(), 0) > 0; |
| } else if (cellValue instanceof String) { |
| return true; // non-null string greater than empty cell |
| } else if (cellValue instanceof Boolean) return true; |
| return false; // just in case - not a typical possibility |
| } |
| return cellValue.compareTo(v1) > 0; |
| } |
| }, |
| LESS_THAN { |
| @Override |
| public <C extends Comparable<C>> boolean isValid(C cellValue, C v1, C v2) { |
| if (v1 == null) { |
| if (cellValue instanceof Number) { |
| // use zero for null |
| return Double.compare( ((Number) cellValue).doubleValue(), 0) < 0; |
| } else if (cellValue instanceof String) { |
| return false; // non-null string greater than empty cell |
| } else if (cellValue instanceof Boolean) return false; |
| return false; // just in case - not a typical possibility |
| } |
| return cellValue.compareTo(v1) < 0; |
| } |
| }, |
| GREATER_OR_EQUAL { |
| @Override |
| public <C extends Comparable<C>> boolean isValid(C cellValue, C v1, C v2) { |
| if (v1 == null) { |
| if (cellValue instanceof Number) { |
| // use zero for null |
| return Double.compare( ((Number) cellValue).doubleValue(), 0) >= 0; |
| } else if (cellValue instanceof String) { |
| return true; // non-null string greater than empty cell |
| } else if (cellValue instanceof Boolean) return true; |
| return false; // just in case - not a typical possibility |
| } |
| return cellValue.compareTo(v1) >= 0; |
| } |
| }, |
| LESS_OR_EQUAL { |
| @Override |
| public <C extends Comparable<C>> boolean isValid(C cellValue, C v1, C v2) { |
| if (v1 == null) { |
| if (cellValue instanceof Number) { |
| // use zero for null |
| return Double.compare( ((Number) cellValue).doubleValue(), 0) <= 0; |
| } else if (cellValue instanceof String) { |
| return false; // non-null string not less than empty cell |
| } else if (cellValue instanceof Boolean) return false; // for completeness |
| return false; // just in case - not a typical possibility |
| } |
| return cellValue.compareTo(v1) <= 0; |
| } |
| }, |
| ; |
| |
| /** |
| * Evaluates comparison using operator instance rules |
| * @param cellValue won't be null, assumption is previous checks handled that |
| * @param v1 if null, per Excel behavior various results depending on the type of cellValue and the specific enum instance |
| * @param v2 null if not needed. If null when needed, various results, per Excel behavior |
| * @return true if the comparison is valid |
| */ |
| public abstract <C extends Comparable<C>> boolean isValid(C cellValue, C v1, C v2); |
| |
| /** |
| * Called when the cell and comparison values are of different data types |
| * Needed for negation operators, which should return true. |
| * @return true if this comparison is true when the types to compare are different |
| */ |
| public boolean isValidForIncompatibleTypes() { |
| return false; |
| } |
| } |
| |
| /** |
| * Note: this class has a natural ordering that is inconsistent with equals. |
| */ |
| protected static class ValueAndFormat implements Comparable<ValueAndFormat> { |
| |
| private final Double value; |
| private final String string; |
| private final String format; |
| private final DecimalFormat decimalTextFormat; |
| |
| public ValueAndFormat(Double value, String format, DecimalFormat df) { |
| this.value = value; |
| this.format = format; |
| string = null; |
| decimalTextFormat = df; |
| } |
| |
| public ValueAndFormat(String value, String format) { |
| this.value = null; |
| this.format = format; |
| string = value; |
| decimalTextFormat = null; |
| } |
| |
| public boolean isNumber() { |
| return value != null; |
| } |
| |
| public Double getValue() { |
| return value; |
| } |
| |
| public String getString() { |
| return string; |
| } |
| |
| public String toString() { |
| if(isNumber()) { |
| return decimalTextFormat.format(getValue().doubleValue()); |
| } else { |
| return getString(); |
| } |
| } |
| |
| @Override |
| public boolean equals(Object obj) { |
| if (!(obj instanceof ValueAndFormat)) { |
| return false; |
| } |
| ValueAndFormat o = (ValueAndFormat) obj; |
| return (Objects.equals(value, o.value) |
| && Objects.equals(format, o.format) |
| && Objects.equals(string, o.string)); |
| } |
| |
| /** |
| * Note: this class has a natural ordering that is inconsistent with equals. |
| * @param o |
| * @return value comparison |
| */ |
| @Override |
| public int compareTo(ValueAndFormat o) { |
| if (value == null && o.value != null) { |
| return 1; |
| } |
| if (o.value == null && value != null) { |
| return -1; |
| } |
| int cmp = value == null ? 0 : value.compareTo(o.value); |
| if (cmp != 0) { |
| return cmp; |
| } |
| |
| if (string == null && o.string != null) { |
| return 1; |
| } |
| if (o.string == null && string != null) { |
| return -1; |
| } |
| |
| return string == null ? 0 : string.compareTo(o.string); |
| } |
| |
| @Override |
| public int hashCode() { |
| return Objects.hash(string,value,format); |
| } |
| } |
| } |
