Author: gwoolsey
Date: Mon Feb 13 22:51:30 2017
New Revision: 1782894
URL: http://svn.apache.org/viewvc?rev=1782894&view=rev
Log:
Add evaluation of data validation rules and conditional formatting
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/ConditionalFormattingEvaluator.java
(with props)
poi/trunk/src/java/org/apache/poi/ss/formula/DataValidationEvaluator.java
(with props)
poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java
(with props)
poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionFilterData.java
(with props)
poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionFilterType.java
(with props)
poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java.svntmp
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionFilterData.java
(with props)
poi/trunk/src/ooxml/testcases/org/apache/poi/ss/usermodel/ConditionalFormattingEvalTest.java
(with props)
poi/trunk/test-data/spreadsheet/ConditionalFormattingSamples.xls (with
props)
poi/trunk/test-data/spreadsheet/ConditionalFormattingSamples.xlsx (with
props)
poi/trunk/test-data/spreadsheet/DataValidationEvaluations.xlsx (with
props)
Modified:
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java
poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java
poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java
poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java
poi/trunk/src/java/org/apache/poi/ss/util/CellRangeAddressBase.java
poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
Modified:
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java?rev=1782894&r1=1782893&r2=1782894&view=diff
==============================================================================
---
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java
(original)
+++
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java
Mon Feb 13 22:51:30 2017
@@ -29,6 +29,8 @@ import org.apache.poi.hssf.record.cf.Fon
import org.apache.poi.hssf.record.cf.IconMultiStateFormatting;
import org.apache.poi.hssf.record.cf.PatternFormatting;
import org.apache.poi.ss.formula.ptg.Ptg;
+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.ConditionalFormattingRule;
@@ -57,6 +59,22 @@ public final class HSSFConditionalFormat
cfRuleRecord = pRuleRecord;
}
+ /**
+ * we don't know priority for these, other than definition/model order,
which appears to be what Excel uses.
+ * @see org.apache.poi.ss.usermodel.ConditionalFormattingRule#getPriority()
+ */
+ public int getPriority() {
+ return 0;
+ }
+
+ /**
+ * Always true for HSSF files, per Microsoft Excel documentation
+ * @see
org.apache.poi.ss.usermodel.ConditionalFormattingRule#getStopIfTrue()
+ */
+ public boolean getStopIfTrue() {
+ return true;
+ }
+
CFRuleBase getCfRuleRecord() {
return cfRuleRecord;
}
@@ -237,6 +255,18 @@ public final class HSSFConditionalFormat
}
/**
+ * always null (not a filter condition) or {@link
ConditionFilterType#FILTER} if it is.
+ * @see
org.apache.poi.ss.usermodel.ConditionalFormattingRule#getConditionFilterType()
+ */
+ public ConditionFilterType getConditionFilterType() {
+ return getConditionType() == ConditionType.FILTER ?
ConditionFilterType.FILTER : null;
+ }
+
+ public ConditionFilterData getFilterConfiguration() {
+ return null;
+ }
+
+ /**
* @return - the comparisionoperatation for the cfrule
*/
@Override
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/ConditionalFormattingEvaluator.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/ConditionalFormattingEvaluator.java?rev=1782894&view=auto
==============================================================================
---
poi/trunk/src/java/org/apache/poi/ss/formula/ConditionalFormattingEvaluator.java
(added)
+++
poi/trunk/src/java/org/apache/poi/ss/formula/ConditionalFormattingEvaluator.java
Mon Feb 13 22:51:30 2017
@@ -0,0 +1,282 @@
+/* ====================================================================
+ 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.util.ArrayList;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.ConditionalFormatting;
+import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.ss.util.CellRangeAddressBase;
+import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.ss.util.SheetUtil;
+
+/**
+ * Evaluates Conditional Formatting constraints.<p/>
+ *
+ * For performance reasons, this class keeps a cache of all previously
evaluated rules and cells.
+ * Be sure to call {@link #clearAllCachedFormats()} if any conditional formats
are modified, added, or deleted,
+ * and {@link #clearAllCachedValues()} whenever cell values change.
+ * <p/>
+ *
+ */
+public class ConditionalFormattingEvaluator {
+
+ private final WorkbookEvaluator workbookEvaluator;
+ private final Workbook workbook;
+
+ /**
+ * All the underlying structures, for both HSSF and XSSF, repeatedly go to
the raw bytes/XML for the
+ * different pieces used in the ConditionalFormatting* structures. That's
highly inefficient,
+ * and can cause significant lag when checking formats for large workbooks.
+ * <p/>
+ * Instead we need a cached version that is discarded when definitions
change.
+ * <p/>
+ * Sheets don't implement equals, and since its an interface,
+ * there's no guarantee instances won't be recreated on the fly by some
implementation.
+ * So we use sheet name.
+ */
+ private final Map<String, List<EvaluationConditionalFormatRule>> formats =
new HashMap<String, List<EvaluationConditionalFormatRule>>();
+
+ /**
+ * Evaluating rules for cells in their region(s) is expensive, so we want
to cache them,
+ * and empty/reevaluate the cache when values change.
+ * <p/>
+ * Rule lists are in priority order, as evaluated by Excel (smallest
priority # for XSSF, definition order for HSSF)
+ * <p/>
+ * CellReference implements equals().
+ */
+ private final Map<CellReference, List<EvaluationConditionalFormatRule>>
values = new HashMap<CellReference, List<EvaluationConditionalFormatRule>>();
+
+ public ConditionalFormattingEvaluator(Workbook wb,
WorkbookEvaluatorProvider provider) {
+ this.workbook = wb;
+ this.workbookEvaluator = provider._getWorkbookEvaluator();
+ }
+
+ protected WorkbookEvaluator getWorkbookEvaluator() {
+ return workbookEvaluator;
+ }
+
+ /**
+ * Call this whenever rules are added, reordered, or removed, or a rule
formula is changed
+ * (not the formula inputs but the formula expression itself)
+ */
+ public void clearAllCachedFormats() {
+ formats.clear();
+ }
+
+ /**
+ * Call this whenever cell values change in the workbook, so condional
formats are re-evaluated
+ * for all cells.
+ * <p/>
+ * TODO: eventually this should work like {@link
EvaluationCache#notifyUpdateCell(int, int, EvaluationCell)}
+ * and only clear values that need recalculation based on the formula
dependency tree.
+ */
+ public void clearAllCachedValues() {
+ values.clear();
+ }
+
+ /**
+ * lazy load by sheet since reading can be expensive
+ *
+ * @param sheet
+ * @return unmodifiable list of rules
+ */
+ protected List<EvaluationConditionalFormatRule> getRules(Sheet sheet) {
+ final String sheetName = sheet.getSheetName();
+ List<EvaluationConditionalFormatRule> rules = formats.get(sheetName);
+ if (rules == null && ! formats.containsKey(sheetName)) {
+ final SheetConditionalFormatting scf =
sheet.getSheetConditionalFormatting();
+ final int count = scf.getNumConditionalFormattings();
+ rules = new ArrayList<EvaluationConditionalFormatRule>(count);
+ formats.put(sheetName, rules);
+ for (int i=0; i < count; i++) {
+ ConditionalFormatting f = scf.getConditionalFormattingAt(i);
+ //optimization, as this may be expensive for lots of ranges
+ final CellRangeAddress[] regions = f.getFormattingRanges();
+ for (int r=0; r < f.getNumberOfRules(); r++) {
+ ConditionalFormattingRule rule = f.getRule(r);
+ rules.add(new
EvaluationConditionalFormatRule(workbookEvaluator, sheet, f, i, rule, r,
regions));
+ }
+ }
+ // need them in formatting and priority order so logic works right
+ Collections.sort(rules);
+ }
+ return Collections.unmodifiableList(rules);
+ }
+
+ /**
+ * This checks all applicable {@link ConditionalFormattingRule}s for the
cell's sheet,
+ * in defined "priority" order, returning the matches if any. This is a
property currently
+ * not exposed from <code>CTCfRule</code> in
<code>XSSFConditionalFormattingRule</code>.
+ * <p/>
+ * Most cells will have zero or one applied rule, but it is possible to
define multiple rules
+ * that apply at the same time to the same cell, thus the List result.
+ * <p/>
+ * Note that to properly apply conditional rules, care must be taken to
offset the base
+ * formula by the relative position of the current cell, or the wrong
value is checked.
+ * This is handled by {@link WorkbookEvaluator#evaluate(String,
CellReference, CellRangeAddressBase)}.
+ *
+ * @param cell NOTE: if no sheet name is specified, this uses the workbook
active sheet
+ * @return Unmodifiable List of {@link
EvaluationConditionalFormattingRule}s that apply to the current cell value,
+ * in priority order, as evaluated by Excel (smallest priority #
for XSSF, definition order for HSSF),
+ * or null if none apply
+ */
+ public List<EvaluationConditionalFormatRule>
getConditionalFormattingForCell(final CellReference cellRef) {
+ String sheetName = cellRef.getSheetName();
+ Sheet sheet = null;
+ if (sheetName == null) sheet =
workbook.getSheetAt(workbook.getActiveSheetIndex());
+ else sheet = workbook.getSheet(sheetName);
+
+ final Cell cell = SheetUtil.getCell(sheet, cellRef.getRow(),
cellRef.getCol());
+
+ if (cell == null) return Collections.emptyList();
+
+ return getConditionalFormattingForCell(cell, cellRef);
+ }
+
+ /**
+ * This checks all applicable {@link ConditionalFormattingRule}s for the
cell's sheet,
+ * in defined "priority" order, returning the matches if any. This is a
property currently
+ * not exposed from <code>CTCfRule</code> in
<code>XSSFConditionalFormattingRule</code>.
+ * <p/>
+ * Most cells will have zero or one applied rule, but it is possible to
define multiple rules
+ * that apply at the same time to the same cell, thus the List result.
+ * <p/>
+ * Note that to properly apply conditional rules, care must be taken to
offset the base
+ * formula by the relative position of the current cell, or the wrong
value is checked.
+ * This is handled by {@link WorkbookEvaluator#evaluate(String,
CellReference, CellRangeAddressBase)}.
+ *
+ * @param cell
+ * @return Unmodifiable List of {@link
EvaluationConditionalFormattingRule}s that apply to the current cell value,
+ * in priority order, as evaluated by Excel (smallest priority #
for XSSF, definition order for HSSF),
+ * or null if none apply
+ */
+ public List<EvaluationConditionalFormatRule>
getConditionalFormattingForCell(Cell cell) {
+ return getConditionalFormattingForCell(cell, getRef(cell));
+ }
+
+ /**
+ * We need both, and can derive one from the other, but this is to avoid
duplicate work
+ *
+ * @param cell
+ * @param ref
+ * @return unmodifiable list of matching rules
+ */
+ private List<EvaluationConditionalFormatRule>
getConditionalFormattingForCell(Cell cell, CellReference ref) {
+ List<EvaluationConditionalFormatRule> rules = values.get(ref);
+
+ if (rules == null) {
+ // compute and cache them
+ rules = new ArrayList<EvaluationConditionalFormatRule>();
+ /*
+ * Per Excel help:
+ *
https://support.office.com/en-us/article/Manage-conditional-formatting-rule-precedence-e09711a3-48df-4bcb-b82c-9d8b8b22463d#__toc269129417
+ * stopIfTrue is true for all rules from HSSF files, and an
explicit value for XSSF files.
+ * thus the explicit ordering of the rule lists in
#getFormattingRulesForSheet(Sheet)
+ */
+ boolean stopIfTrue = false;
+ for (EvaluationConditionalFormatRule rule :
getRules(cell.getSheet())) {
+
+ if (stopIfTrue) continue; // a previous rule matched and wants
no more evaluations
+
+ if (rule.matches(cell)) {
+ rules.add(rule);
+ stopIfTrue = rule.getRule().getStopIfTrue();
+ }
+ }
+ Collections.sort(rules);
+ values.put(ref, rules);
+ }
+
+ return Collections.unmodifiableList(rules);
+ }
+
+ public static CellReference getRef(Cell cell) {
+ return new CellReference(cell.getSheet().getSheetName(),
cell.getRowIndex(), cell.getColumnIndex(), false, false);
+ }
+
+ /**
+ * @param sheetName
+ * @return unmodifiable list of all Conditional format rules for the given
sheet, if any
+ */
+ public List<EvaluationConditionalFormatRule> getFormatRulesForSheet(String
sheetName) {
+ return getFormatRulesForSheet(workbook.getSheet(sheetName));
+ }
+
+ /**
+ * @param sheet
+ * @return unmodifiable list of all Conditional format rules for the given
sheet, if any
+ */
+ public List<EvaluationConditionalFormatRule> getFormatRulesForSheet(Sheet
sheet) {
+ return getRules(sheet);
+ }
+
+ /**
+ * Conditional formatting rules can apply only to cells in the sheet to
which they are attached.
+ * The POI data model does not have a back-reference to the owning sheet,
so it must be passed in separately.
+ * <p/>
+ * We could overload this with convenience methods taking a sheet name and
sheet index as well.
+ * <p/>
+ * @param sheet containing the rule
+ * @param index of the {@link ConditionalFormatting} instance in the
sheet's array
+ * @return unmodifiable List of all cells in the rule's region matching
the rule's condition
+ */
+ public List<Cell> getMatchingCells(Sheet sheet, int
conditionalFormattingIndex, int ruleIndex) {
+ for (EvaluationConditionalFormatRule rule : getRules(sheet)) {
+ if (rule.getSheet().equals(sheet) && rule.getFormattingIndex() ==
conditionalFormattingIndex && rule.getRuleIndex() == ruleIndex) {
+ return getMatchingCells(rule);
+ }
+ }
+ return Collections.emptyList();
+ }
+
+ /**
+ *
+ * @param rule
+ * @return unmodifiable List of all cells in the rule's region matching
the rule's condition
+ */
+ public List<Cell> getMatchingCells(EvaluationConditionalFormatRule rule) {
+ final List<Cell> cells = new ArrayList<Cell>();
+ final Sheet sheet = rule.getSheet();
+
+ for (CellRangeAddress region : rule.getRegions()) {
+ for (int r = region.getFirstRow(); r <= region.getLastRow(); r++) {
+ final Row row = sheet.getRow(r);
+ if (row == null) continue; // no cells to check
+ for (int c = region.getFirstColumn(); c <=
region.getLastColumn(); c++) {
+ final Cell cell = row.getCell(c);
+ if (cell == null) continue;
+
+ List<EvaluationConditionalFormatRule> cellRules =
getConditionalFormattingForCell(cell);
+ if (cellRules.contains(rule)) cells.add(cell);
+ }
+ }
+ }
+ return Collections.unmodifiableList(cells);
+ }
+}
Propchange:
poi/trunk/src/java/org/apache/poi/ss/formula/ConditionalFormattingEvaluator.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/src/java/org/apache/poi/ss/formula/DataValidationEvaluator.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/DataValidationEvaluator.java?rev=1782894&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/DataValidationEvaluator.java
(added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/DataValidationEvaluator.java
Mon Feb 13 22:51:30 2017
@@ -0,0 +1,563 @@
+/* ====================================================================
+ 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.util.ArrayList;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+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.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.DataValidation;
+import org.apache.poi.ss.usermodel.DataValidationConstraint;
+import org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType;
+import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.util.CellRangeAddressBase;
+import org.apache.poi.ss.util.CellRangeAddressList;
+import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.ss.util.SheetUtil;
+
+/**
+ * Evaluates Data Validation constraints.<p/>
+ *
+ * For performance reasons, this class keeps a cache of all previously
retrieved {@link DataValidation} instances.
+ * Be sure to call {@link #clearAllCachedValues()} if any workbook validation
definitions are
+ * added, modified, or deleted.
+ * <p/>
+ * Changing cell values should be fine, as long as the corresponding {@link
WorkbookEvaluator#clearAllCachedResultValues()}
+ * is called as well.
+ *
+ */
+public class DataValidationEvaluator {
+
+ /**
+ * Expensive to compute, so cache them as they are retrieved.
+ * <p/>
+ * Sheets don't implement equals, and since its an interface,
+ * there's no guarantee instances won't be recreated on the fly by some
implementation.
+ * So we use sheet name.
+ */
+ private final Map<String, List<? extends DataValidation>> validations =
new HashMap<String, List<? extends DataValidation>>();
+
+ private final Workbook workbook;
+ private final WorkbookEvaluator workbookEvaluator;
+
+ public DataValidationEvaluator(Workbook wb, WorkbookEvaluatorProvider
provider) {
+ this.workbook = wb;
+ this.workbookEvaluator = provider._getWorkbookEvaluator();
+ }
+
+ protected WorkbookEvaluator getWorkbookEvaluator() {
+ return workbookEvaluator;
+ }
+
+ public void clearAllCachedValues() {
+ validations.clear();
+ }
+
+ /**
+ * lazy load validations by sheet, since reading the CT* types is expensive
+ * @param sheet
+ * @return
+ */
+ private List<? extends DataValidation> getValidations(Sheet sheet) {
+ List<? extends DataValidation> dvs =
validations.get(sheet.getSheetName());
+ if (dvs == null && !validations.containsKey(sheet.getSheetName())) {
+ dvs = sheet.getDataValidations();
+ validations.put(sheet.getSheetName(), dvs);
+ }
+ return dvs;
+ }
+
+ /**
+ * Finds and returns the {@link DataValidation} for the cell, if there is
+ * one. Lookup is based on the first match from
+ * {@link DataValidation#getRegions()} for the cell's sheet. DataValidation
+ * regions must be in the same sheet as the DataValidation. Allowed values
+ * expressions may reference other sheets, however.
+ *
+ * @param cell reference to check - use this in case the cell does not
actually exist yet
+ * @return the DataValidation applicable to the given cell, or null if no
+ * validation applies
+ */
+ public DataValidation getValidationForCell(CellReference cell) {
+ return getValidationContextForCell(cell).getValidation();
+ }
+
+ public DataValidationContext getValidationContextForCell(CellReference
cell) {
+ // TODO
+ final Sheet sheet = workbook.getSheet(cell.getSheetName());
+ if (sheet == null) return null;
+ final List<? extends DataValidation> dataValidations =
getValidations(sheet);
+ if (dataValidations == null) return null;
+ for (DataValidation dv : dataValidations) {
+ final CellRangeAddressList regions = dv.getRegions();
+ if (regions == null) return null;
+ // current implementation can't return null
+ for (CellRangeAddressBase range : regions.getCellRangeAddresses())
{
+ if (range.isInRange(cell)) {
+ return new DataValidationContext(dv, this, range, cell);
+ }
+ }
+ }
+ return null;
+ }
+
+ /**
+ * If {@link #getValidationForCell(Cell)} returns an instance, and the
+ * {@link ValidationType} is {@link ValidationType#LIST}, return the valid
+ * values, whether they are from a static list or cell range.
+ * <p/>
+ * For all other validation types, or no validation at all, this method
+ * returns null.
+ * <p/>
+ * This method could throw an exception if the validation type is not LIST,
+ * but since this method is mostly useful in UI contexts, null seems the
+ * easier path.
+ *
+ * @param cell reference to check - use this in case the cell does not
actually exist yet
+ * @return returns an unmodifiable {@link List} of {@link ValueEval}s if
applicable, or
+ * null
+ */
+ public List<ValueEval> getValidationValuesForCell(CellReference cell) {
+ DataValidationContext context = getValidationContextForCell(cell);
+
+ if (context == null) return null;
+
+ return getValidationValuesForConstraint(context);
+ }
+
+ /**
+ * static so enums can reference it without creating a whole instance
+ * @param cell
+ * @param val
+ * @return returns an unmodifiable {@link List} of {@link ValueEval}s,
which may be empty
+ */
+ protected static List<ValueEval>
getValidationValuesForConstraint(DataValidationContext context) {
+ final DataValidationConstraint val =
context.getValidation().getValidationConstraint();
+ if (val.getValidationType() != ValidationType.LIST) return null;
+
+ String formula = val.getFormula1();
+
+ final List<ValueEval> values = new ArrayList<ValueEval>();
+
+ if (val.getExplicitListValues() != null &&
val.getExplicitListValues().length > 0) {
+ // assumes parsing interprets the overloaded property right for
XSSF
+ for (String s : val.getExplicitListValues()) {
+ if (s != null) values.add(new StringEval(s)); // constructor
throws exception on null
+ }
+ } else if (formula != null) {
+ // evaluate formula for cell refs then get their values
+ ValueEval eval =
context.getEvaluator().getWorkbookEvaluator().evaluate(formula,
context.getTarget(), context.getRegion());
+ // formula is a StringEval if the validation is by a fixed list.
Use the explicit list later.
+ // there is no way from the model to tell if the list is fixed
values or formula based.
+ if (eval instanceof TwoDEval) {
+ TwoDEval twod = (TwoDEval) eval;
+ for (int i=0; i < twod.getHeight(); i++) {
+ final ValueEval cellValue = twod.getValue(i, 0);
+ values.add(cellValue);
+ }
+ }
+ }
+ return Collections.unmodifiableList(values);
+ }
+
+ /**
+ * Use the validation returned by {@link #getValidationForCell(Cell)} if
you
+ * want the error display details. This is the validation checked by this
+ * method, which attempts to replicate Excel's data validation rules.
+ * <p/>
+ * Note that to properly apply some validations, care must be taken to
+ * offset the base validation formula by the relative position of the
+ * current cell, or the wrong value is checked.
+ *
+ * @param cell
+ * @return true if the cell has no validation or the cell value passes the
+ * defined validation, false if it fails
+ */
+ public boolean isValidCell(CellReference cellRef) {
+ final DataValidationContext context =
getValidationContextForCell(cellRef);
+
+ if (context == null) return true;
+
+ final Cell cell =
SheetUtil.getCell(workbook.getSheet(cellRef.getSheetName()), cellRef.getRow(),
cellRef.getCol());
+
+ // now we can validate the cell
+
+ // if empty, return not allowed flag
+ if ( cell == null
+ || isType(cell, CellType.BLANK)
+ || (isType(cell,CellType.STRING)
+ && (cell.getStringCellValue() == null ||
cell.getStringCellValue().isEmpty())
+ )
+ ) {
+ return context.getValidation().getEmptyCellAllowed();
+ }
+
+ // cell has a value
+
+ return ValidationEnum.isValid(cell, context);
+ }
+
+ /**
+ * Note that this assumes the cell cached value is up to date and in sync
with data edits
+ * @param cell
+ * @param type
+ * @return true if the cell or cached cell formula result type match the
given type
+ */
+ public static boolean isType(Cell cell, CellType type) {
+ final CellType cellType = cell.getCellTypeEnum();
+ return cellType == type
+ || (cellType == CellType.FORMULA
+ && cell.getCachedFormulaResultTypeEnum() == type
+ );
+ }
+
+
+ /**
+ * Not calling it ValidationType to avoid confusion for now with
DataValidationConstraint.ValidationType.
+ * Definition order matches OOXML type ID indexes
+ */
+ public static enum ValidationEnum {
+ ANY {
+ public boolean isValidValue(Cell cell, DataValidationContext
context) {
+ return true;
+ }
+ },
+ INTEGER {
+ public boolean isValidValue(Cell cell, DataValidationContext
context) {
+ if (super.isValidValue(cell, context)) {
+ // we know it is a number in the proper range, now check
if it is an int
+ final double value = cell.getNumericCellValue(); // can't
get here without a valid numeric value
+ return
Double.valueOf(value).compareTo(Double.valueOf((int) value)) == 0;
+ }
+ return false;
+ }
+ },
+ DECIMAL,
+ LIST {
+ public boolean isValidValue(Cell cell, DataValidationContext
context) {
+ final List<ValueEval> valueList =
getValidationValuesForConstraint(context);
+ if (valueList == null) return true; // special case
+
+ // compare cell value to each item
+ for (ValueEval listVal : valueList) {
+ ValueEval comp = listVal instanceof RefEval ? ((RefEval)
listVal).getInnerValueEval(context.getSheetIndex()) : listVal;
+
+ // any value is valid if the list contains a blank value
per Excel help
+ if (comp instanceof BlankEval) return true;
+ if (comp instanceof ErrorEval) continue; // nothing to
check
+ if (comp instanceof BoolEval) {
+ if (isType(cell, CellType.BOOLEAN) && ((BoolEval)
comp).getBooleanValue() == cell.getBooleanCellValue() ) {
+ return true;
+ } else {
+ continue; // check the rest
+ }
+ }
+ if (comp instanceof NumberEval) {
+ // could this have trouble with double
precision/rounding errors and date/time values?
+ // do we need to allow a "close enough" double
fractional range?
+ // I see 17 digits after the decimal separator in XSSF
files, and for time values,
+ // there are sometimes discrepancies in the final
decimal place.
+ // I don't have a validation test case yet though. - GW
+ if (isType(cell, CellType.NUMERIC) && ((NumberEval)
comp).getNumberValue() == cell.getNumericCellValue()) {
+ return true;
+ } else {
+ continue; // check the rest
+ }
+ }
+ if (comp instanceof StringEval) {
+ // interestingly, in testing, a validation value of
the string "TRUE" or "true"
+ // did not match a boolean cell value of TRUE - so
apparently cell type matters
+ // also, Excel validation is case insensitive - "true"
is valid for the list value "TRUE"
+ if (isType(cell, CellType.STRING) && ((StringEval)
comp).getStringValue().equalsIgnoreCase(cell.getStringCellValue())) {
+ return true;
+ } else {
+ continue; // check the rest;
+ }
+ }
+ }
+ return false; // no matches
+ }
+ },
+ DATE,
+ TIME,
+ TEXT_LENGTH {
+ public boolean isValidValue(Cell cell, DataValidationContext
context) {
+ if (! isType(cell, CellType.STRING)) return false;
+ String v = cell.getStringCellValue();
+ return isValidNumericValue(Double.valueOf(v.length()),
context);
+ }
+ },
+ FORMULA {
+ /**
+ * Note the formula result must either be a boolean result, or
anything not in error.
+ * If boolean, value must be true to pass, anything else valid is
also passing, errors fail.
+ * @see
org.apache.poi.ss.formula.DataValidationEvaluator.ValidationEnum#isValidValue(org.apache.poi.ss.usermodel.Cell,
org.apache.poi.ss.usermodel.DataValidationConstraint,
org.apache.poi.ss.formula.WorkbookEvaluator)
+ */
+ public boolean isValidValue(Cell cell, DataValidationContext
context) {
+ ValueEval comp =
context.getEvaluator().getWorkbookEvaluator().evaluate(context.getFormula1(),
context.getTarget(), context.getRegion());
+ if (comp instanceof RefEval) {
+ comp = ((RefEval) comp).getInnerValueEval(((RefEval)
comp).getFirstSheetIndex());
+ }
+
+ 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
+ }
+ },
+ ;
+
+ public boolean isValidValue(Cell cell, DataValidationContext context) {
+ return isValidNumericCell(cell, context);
+ }
+
+ /**
+ * Uses the cell value, which may be the cached formula result value.
+ * We won't re-evaluate cells here. This validation would be after
the cell value was updated externally.
+ * Excel allows invalid values through methods like copy/paste, and
only validates them when the user
+ * interactively edits the cell.
+ * @param cell
+ * @param dvc
+ * @param wbe
+ * @return
+ */
+ protected boolean isValidNumericCell(Cell cell, DataValidationContext
context) {
+ if ( ! isType(cell, CellType.NUMERIC)) return false;
+
+ Double value = Double.valueOf(cell.getNumericCellValue());
+ return isValidNumericValue(value, context);
+ }
+
+ /**
+ *
+ * @param value
+ * @param context
+ * @return
+ */
+ protected boolean isValidNumericValue(Double value, final
DataValidationContext context) {
+ try {
+ Double t1 = evalOrConstant(context.getFormula1(), context);
+ // per Excel, a blank value for a numeric validation
constraint formula validates true
+ if (t1 == null) return true;
+ Double t2 = null;
+ if (context.getOperator() == OperatorType.BETWEEN ||
context.getOperator() == OperatorType.NOT_BETWEEN) {
+ t2 = evalOrConstant(context.getFormula2(), context);
+ // per Excel, a blank value for a numeric validation
constraint formula validates true
+ if (t2 == null) return true;
+ }
+ return
OperatorEnum.values()[context.getOperator()].isValid(value, t1, t2);
+ } catch (NumberFormatException e) {
+ // one or both formulas are in error, not evaluating to a
number, so the validation is false per Excel's behavior.
+ return false;
+ }
+ }
+
+ /**
+ * Evaluate a numeric formula value as either a constant or numeric
expression.
+ * Note that Excel treats validations with constraint formulas that
evaluate to null as valid,
+ * but evaluations in error or non-numeric are marked invalid.
+ * @param formula
+ * @param context
+ * @return numeric value or null if not defined or the formula
evaluates to an empty/missing cell.
+ * @throws NumberFormatException if the formula is non-numeric when it
should be
+ */
+ private Double evalOrConstant(String formula, DataValidationContext
context) throws NumberFormatException {
+ if (formula == null || formula.trim().isEmpty()) return null; //
shouldn't happen, but just in case
+ try {
+ return Double.valueOf(formula);
+ } catch (NumberFormatException e) {
+ // must be an expression, then. Overloading by Excel in the
file formats.
+ }
+ ValueEval eval =
context.getEvaluator().getWorkbookEvaluator().evaluate(formula,
context.getTarget(), context.getRegion());
+ if (eval instanceof RefEval) {
+ eval = ((RefEval) eval).getInnerValueEval(((RefEval)
eval).getFirstSheetIndex());
+ }
+ if (eval instanceof BlankEval) return null;
+ if (eval instanceof NumberEval) return
Double.valueOf(((NumberEval) eval).getNumberValue());
+ if (eval instanceof StringEval) {
+ final String value = ((StringEval) eval).getStringValue();
+ if (value == null || value.trim().isEmpty()) return null;
+ // try to parse the cell value as a double and return it
+ return Double.valueOf(value);
+ }
+ throw new NumberFormatException("Formula '" + formula + "'
evaluates to something other than a number");
+ }
+
+ /**
+ * Validates against the type defined in dvc, as an index of the enum
values array.
+ * @param cell
+ * @param dvc
+ * @param wbe
+ * @return true if validation passes
+ * @throws ArrayIndexOutOfBoundsException if the constraint type is an
invalid index
+ */
+ public static boolean isValid(Cell cell, DataValidationContext
context) {
+ return
values()[context.getValidation().getValidationConstraint().getValidationType()].isValidValue(cell,
context);
+ }
+
+ }
+
+ /**
+ * Not calling it OperatorType to avoid confusion for now with
DataValidationConstraint.OperatorType.
+ * Definition order matches OOXML type ID indexes
+ */
+ public static enum OperatorEnum {
+ BETWEEN {
+ public boolean isValid(Double cellValue, Double v1, Double v2) {
+ return cellValue.compareTo(v1) >= 0 && cellValue.compareTo(v2)
<= 0;
+ }
+ },
+ NOT_BETWEEN {
+ public boolean isValid(Double cellValue, Double v1, Double v2) {
+ return cellValue.compareTo(v1) < 0 || cellValue.compareTo(v2)
> 0;
+ }
+ },
+ EQUAL {
+ public boolean isValid(Double cellValue, Double v1, Double v2) {
+ return cellValue.compareTo(v1) == 0;
+ }
+ },
+ NOT_EQUAL {
+ public boolean isValid(Double cellValue, Double v1, Double v2) {
+ return cellValue.compareTo(v1) != 0;
+ }
+ },
+ GREATER_THAN {
+ public boolean isValid(Double cellValue, Double v1, Double v2) {
+ return cellValue.compareTo(v1) > 0;
+ }
+ },
+ LESS_THAN {
+ public boolean isValid(Double cellValue, Double v1, Double v2) {
+ return cellValue.compareTo(v1) < 0;
+ }
+ },
+ GREATER_OR_EQUAL {
+ public boolean isValid(Double cellValue, Double v1, Double v2) {
+ return cellValue.compareTo(v1) >= 0;
+ }
+ },
+ LESS_OR_EQUAL {
+ public boolean isValid(Double cellValue, Double v1, Double v2) {
+ return cellValue.compareTo(v1) <= 0;
+ }
+ },
+ ;
+
+ public static final OperatorEnum IGNORED = BETWEEN;
+
+ /**
+ * Evaluates comparison using operator instance rules
+ * @param cellValue won't be null, assumption is previous checks
handled that
+ * @param v1 if null, value assumed invalid, anything passes, per
Excel behavior
+ * @param v2 null if not needed. If null when needed, assume anything
passes, per Excel behavior
+ * @return true if the comparison is valid
+ */
+ public abstract boolean isValid(Double cellValue, Double v1, Double
v2);
+ }
+
+ public static class DataValidationContext {
+ private final DataValidation dv;
+ private final DataValidationEvaluator dve;
+ private final CellRangeAddressBase region;
+ private final CellReference target;
+
+ /**
+ *
+ * @param dv
+ * @param dve
+ * @param region
+ * @param target
+ */
+ public DataValidationContext(DataValidation dv,
DataValidationEvaluator dve, CellRangeAddressBase region, CellReference target)
{
+ this.dv = dv;
+ this.dve = dve;
+ this.region = region;
+ this.target = target;
+ }
+ /**
+ * @return the dv
+ */
+ public DataValidation getValidation() {
+ return dv;
+ }
+ /**
+ * @return the dve
+ */
+ public DataValidationEvaluator getEvaluator() {
+ return dve;
+ }
+ /**
+ * @return the region
+ */
+ public CellRangeAddressBase getRegion() {
+ return region;
+ }
+ /**
+ * @return the target
+ */
+ public CellReference getTarget() {
+ return target;
+ }
+
+ public int getOffsetColumns() {
+ return target.getCol() - region.getFirstColumn();
+ }
+
+ public int getOffsetRows() {
+ return target.getRow() - region.getFirstRow();
+ }
+
+ public int getSheetIndex() {
+ return
dve.getWorkbookEvaluator().getSheetIndex(target.getSheetName());
+ }
+
+ public String getFormula1() {
+ return dv.getValidationConstraint().getFormula1();
+ }
+
+ public String getFormula2() {
+ return dv.getValidationConstraint().getFormula2();
+ }
+
+ public int getOperator() {
+ return dv.getValidationConstraint().getOperator();
+ }
+
+ }
+}
Propchange:
poi/trunk/src/java/org/apache/poi/ss/formula/DataValidationEvaluator.java
------------------------------------------------------------------------------
svn:eol-style = native
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java?rev=1782894&view=auto
==============================================================================
---
poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java
(added)
+++
poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java
Mon Feb 13 22:51:30 2017
@@ -0,0 +1,700 @@
+/* ====================================================================
+ 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.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.Map;
+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.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+/**
+ * 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;
+ /**
+ * Depending on the rule type, it may want to know about certain values in
the region when evaluating {@link #matches(Cell)},
+ * 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<CellRangeAddress, Set<ValueAndFormat>>();
+
+ private final int priority;
+ private final int formattingIndex;
+ private final int ruleIndex;
+ private final String formula1;
+ private final String formula2;
+ private final OperatorEnum operator;
+ private final ConditionType type;
+
+ /**
+ *
+ * @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;
+ formula1 = rule.getFormula1();
+ formula2 = rule.getFormula2();
+
+ operator = OperatorEnum.values()[rule.getComparisonOperation()];
+ type = rule.getConditionType();
+ }
+
+ public Sheet getSheet() {
+ return sheet;
+ }
+
+ /**
+ * @return the formatting
+ */
+ public ConditionalFormatting getFormatting() {
+ return formatting;
+ }
+
+ public int getFormattingIndex() {
+ return formattingIndex;
+ }
+
+ /**
+ * @return the rule
+ */
+ public ConditionalFormattingRule getRule() {
+ return rule;
+ }
+
+ 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 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)
+ */
+ 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
+ */
+ 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 = (x < y) ? -1 : ((x == y) ? 0 : 1);
+ if (cmp != 0) return cmp;
+
+ cmp = Integer.compare(getFormattingIndex(), o.getFormattingIndex());
+ if (cmp != 0) return cmp;
+ return Integer.compare(getRuleIndex(), o.getRuleIndex());
+ }
+
+ public int hashCode() {
+ int hash = sheet.getSheetName().hashCode();
+ hash = 31 * hash + formattingIndex;
+ hash = 31 * hash + ruleIndex;
+ return hash;
+ }
+
+ /**
+ * @param cell
+ * @return true if this rule evaluates to true for the given cell
+ */
+ /* package */ boolean matches(Cell cell) {
+ // first check that it is in one of the regions defined for this format
+ CellRangeAddress region = null;
+ for (CellRangeAddress r : regions) {
+ if (r.isInRange(cell)) {
+ region = r;
+ break;
+ }
+ }
+
+ if (region == null) return false; // cell not in range of this rule
+
+ 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;
+ }
+
+ if (ruleType.equals(ConditionType.CELL_VALUE_IS)) {
+ return checkValue(cell, region);
+ }
+ if (ruleType.equals(ConditionType.FORMULA)) {
+ return checkFormula(cell, region);
+ }
+ if (ruleType.equals(ConditionType.FILTER)) {
+ return checkFilter(cell, region);
+ }
+
+ // TODO: anything else, we don't handle yet, such as top 10
+ return false;
+ }
+
+ /**
+ * @param cell
+ * @param region for adjusting relative formulas
+ * @return
+ */
+ 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 = null;
+ 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)) {
+ if (eval instanceof BoolEval && (eval2 == null || eval2 instanceof
BoolEval) ) {
+ return operator.isValid(cell.getBooleanCellValue(),
((BoolEval) eval).getBooleanValue(), eval2 == null ? null : ((BoolEval)
eval2).getBooleanValue());
+ }
+ return false; // wrong types
+ }
+ if (DataValidationEvaluator.isType(cell, CellType.NUMERIC)) {
+ if (eval instanceof NumberEval && (eval2 == null || eval2
instanceof NumberEval) ) {
+ return operator.isValid(cell.getNumericCellValue(),
((NumberEval) eval).getNumberValue(), eval2 == null ? null : ((NumberEval)
eval2).getNumberValue());
+ }
+ return false; // wrong types
+ }
+ if (DataValidationEvaluator.isType(cell, CellType.STRING)) {
+ if (eval instanceof StringEval && (eval2 == null || eval2
instanceof StringEval) ) {
+ return operator.isValid(cell.getStringCellValue(),
((StringEval) eval).getStringValue(), eval2 == null ? null : ((StringEval)
eval2).getStringValue());
+ }
+ return false; // wrong types
+ }
+
+ // should not get here, but in case...
+ return false;
+ }
+
+ private ValueEval unwrapEval(ValueEval eval) {
+ ValueEval comp = eval;
+
+ while (comp instanceof RefEval) {
+ RefEval ref = (RefEval) comp;
+ comp = ref.getInnerValueEval(ref.getFirstSheetIndex());
+ }
+ return comp;
+ }
+ /**
+ * @param cell needed for offsets from region anchor
+ * @param region for adjusting relative formulas
+ * @return true/false using the same rules as Data Validation evaluations
+ */
+ private boolean checkFormula(Cell cell, CellRangeAddress region) {
+ ValueEval comp =
unwrapEval(workbookEvaluator.evaluate(rule.getFormula1(),
ConditionalFormattingEvaluator.getRef(cell), 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, CellRangeAddress region) {
+ final ConditionFilterType filterType = rule.getConditionFilterType();
+ if (filterType == null) return false;
+
+ // 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.
+
+ final ValueAndFormat cv10 = getCellValue(cell);
+ if (! cv10.isNumber()) return false;
+
+ return getMeaningfulValues(region, false, new ValueFunction() {
+ public Set<ValueAndFormat> evaluate(List<ValueAndFormat>
allValues) {
+ List<ValueAndFormat> values = allValues;
+ final ConditionFilterData conf =
rule.getFilterConfiguration();
+
+ if (! conf.getBottom()) Collections.sort(values,
Collections.reverseOrder());
+ else Collections.sort(values);
+
+ int limit = (int) conf.getRank();
+ if (conf.getPercent()) limit = allValues.size() * limit /
100;
+ if (allValues.size() <= limit) return new
HashSet<ValueAndFormat>(allValues);
+
+ return new HashSet<ValueAndFormat>(allValues.subList(0,
limit));
+ }
+ }).contains(cv10);
+ 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() {
+ public Set<ValueAndFormat> evaluate(List<ValueAndFormat>
allValues) {
+ List<ValueAndFormat> values = allValues;
+ Collections.sort(values);
+
+ final Set<ValueAndFormat> unique = new
HashSet<ValueAndFormat>();
+
+ for (int i=0; i < values.size(); i++) {
+ final ValueAndFormat v = values.get(i);
+ // skip this if the current value matches the next
one, or is the last one and matches the previous one
+ if ( (i < values.size()-1 && v.equals(values.get(i+1))
) || ( i > 0 && i == values.size()-1 && v.equals(values.get(i-1)) ) ) {
+ // current value matches next value, skip both
+ i++;
+ continue;
+ }
+ unique.add(v);
+ }
+
+ return unique;
+ }
+ }).contains(getCellValue(cell));
+ 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() {
+ public Set<ValueAndFormat> evaluate(List<ValueAndFormat>
allValues) {
+ List<ValueAndFormat> values = allValues;
+ Collections.sort(values);
+
+ final Set<ValueAndFormat> dup = new
HashSet<ValueAndFormat>();
+
+ for (int i=0; i < values.size(); i++) {
+ final ValueAndFormat v = values.get(i);
+ // skip this if the current value matches the next
one, or is the last one and matches the previous one
+ if ( (i < values.size()-1 && v.equals(values.get(i+1))
) || ( i > 0 && i == values.size()-1 && v.equals(values.get(i-1)) ) ) {
+ // current value matches next value, add one
+ dup.add(v);
+ i++;
+ }
+ }
+ return dup;
+ }
+ }).contains(getCellValue(cell));
+ 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<ValueAndFormat>(getMeaningfulValues(region, false, new
ValueFunction() {
+ public Set<ValueAndFormat> evaluate(List<ValueAndFormat>
allValues) {
+ List<ValueAndFormat> values = allValues;
+ double total = 0;
+ ValueEval[] pop = new ValueEval[values.size()];
+ for (int i=0; i < values.size(); i++) {
+ ValueAndFormat v = values.get(i);
+ total += v.value.doubleValue();
+ pop[i] = new NumberEval(v.value.doubleValue());
+ }
+
+ final Set<ValueAndFormat> avgSet = new
LinkedHashSet<ValueAndFormat>(1);
+ avgSet.add(new ValueAndFormat(new Double(values.size() ==
0 ? 0 : total / values.size()), null));
+
+ final double stdDev = values.size() <= 1 ? 0 :
((NumberEval) AggregateFunction.STDEV.evaluate(pop, 0, 0)).getNumberValue();
+ avgSet.add(new ValueAndFormat(new Double(stdDev), null));
+ return avgSet;
+ }
+ }));
+
+ final ValueAndFormat cv = getCellValue(cell);
+ 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 = new Double(conf.getStdDev() > 0 ? (avg +
(conf.getAboveAverage() ? 1 : -1) * stdDev * conf.getStdDev()) : avg) ;
+
+ OperatorEnum op = null;
+ 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 != null && op.isValid(val, comp, null);
+ case CONTAINS_TEXT:
+ // implemented both by a cfRule "text" attribute and a formula.
Use the formula.
+ return checkFormula(cell, region);
+ case NOT_CONTAINS_TEXT:
+ // implemented both by a cfRule "text" attribute and a formula.
Use the formula.
+ return checkFormula(cell, region);
+ case BEGINS_WITH:
+ // implemented both by a cfRule "text" attribute and a formula.
Use the formula.
+ return checkFormula(cell, region);
+ case ENDS_WITH:
+ // implemented both by a cfRule "text" attribute and a formula.
Use the formula.
+ return checkFormula(cell, region);
+ case CONTAINS_BLANKS:
+ try {
+ String v = cell.getStringCellValue();
+ // 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 = cell.getStringCellValue();
+ // 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(cell, 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
+ */
+ 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<ValueAndFormat>((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 (cv != null && (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 CellType type = cell.getCellTypeEnum();
+ if (type == CellType.NUMERIC || (type == CellType.FORMULA &&
cell.getCachedFormulaResultTypeEnum() == CellType.NUMERIC) ) {
+ return new ValueAndFormat(new
Double(cell.getNumericCellValue()), cell.getCellStyle().getDataFormatString());
+ } else if (type == CellType.STRING || (type == CellType.FORMULA &&
cell.getCachedFormulaResultTypeEnum() == CellType.STRING) ) {
+ return new ValueAndFormat(cell.getStringCellValue(),
cell.getCellStyle().getDataFormatString());
+ } else if (type == CellType.BOOLEAN || (type == CellType.FORMULA
&& cell.getCachedFormulaResultTypeEnum() == CellType.BOOLEAN) ) {
+ return new ValueAndFormat(cell.getStringCellValue(),
cell.getCellStyle().getDataFormatString());
+ }
+ }
+ return null;
+ }
+ /**
+ * 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 */
+ public <C extends Comparable<C>> boolean isValid(C cellValue, C
v1, C v2) {
+ return false;
+ }
+ },
+ BETWEEN {
+ public <C extends Comparable<C>> boolean isValid(C cellValue, C
v1, C v2) {
+ return cellValue.compareTo(v1) >= 0 && cellValue.compareTo(v2)
<= 0;
+ }
+ },
+ NOT_BETWEEN {
+ public <C extends Comparable<C>> boolean isValid(C cellValue, C
v1, C v2) {
+ return cellValue.compareTo(v1) < 0 || cellValue.compareTo(v2)
> 0;
+ }
+ },
+ EQUAL {
+ public <C extends Comparable<C>> boolean isValid(C cellValue, C
v1, C v2) {
+ if (cellValue instanceof String) {
+ return ((String) cellValue).compareToIgnoreCase((String)
v1) == 0;
+ }
+ return cellValue.compareTo(v1) == 0;
+ }
+ },
+ NOT_EQUAL {
+ public <C extends Comparable<C>> boolean isValid(C cellValue, C
v1, C v2) {
+ if (cellValue instanceof String) {
+ return ((String) cellValue).compareToIgnoreCase((String)
v1) != 0;
+ }
+ return cellValue.compareTo(v1) != 0;
+ }
+ },
+ GREATER_THAN {
+ public <C extends Comparable<C>> boolean isValid(C cellValue, C
v1, C v2) {
+ return cellValue.compareTo(v1) > 0;
+ }
+ },
+ LESS_THAN {
+ public <C extends Comparable<C>> boolean isValid(C cellValue, C
v1, C v2) {
+ return cellValue.compareTo(v1) < 0;
+ }
+ },
+ GREATER_OR_EQUAL {
+ public <C extends Comparable<C>> boolean isValid(C cellValue, C
v1, C v2) {
+ return cellValue.compareTo(v1) >= 0;
+ }
+ },
+ LESS_OR_EQUAL {
+ public <C extends Comparable<C>> boolean isValid(C cellValue, C
v1, C v2) {
+ 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, value assumed invalid, anything passes, per
Excel behavior
+ * @param v2 null if not needed. If null when needed, assume anything
passes, per Excel behavior
+ * @return true if the comparison is valid
+ */
+ public abstract <C extends Comparable<C>> boolean isValid(C cellValue,
C v1, C v2);
+ }
+
+ /**
+ * Note: this class has a natural ordering that is inconsistent with
equals.
+ */
+ protected class ValueAndFormat implements Comparable<ValueAndFormat> {
+
+ private final Double value;
+ private final String string;
+ private final String format;
+
+ public ValueAndFormat(Double value, String format) {
+ this.value = value;
+ this.format = format;
+ string = null;
+ }
+
+ public ValueAndFormat(String value, String format) {
+ this.value = null;
+ this.format = format;
+ string = value;
+ }
+
+ public boolean isNumber() {
+ return value != null;
+ }
+
+ public Double getValue() {
+ return value;
+ }
+
+ public boolean equals(Object obj) {
+ ValueAndFormat o = (ValueAndFormat) obj;
+ return ( value == o.value || value.equals(o.value))
+ && ( format == o.format || format.equals(o.format))
+ && (string == o.string || string.equals(o.string));
+ }
+
+ /**
+ * Note: this class has a natural ordering that is inconsistent with
equals.
+ * @param o
+ * @return value comparison
+ */
+ 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);
+ }
+
+ public int hashCode() {
+ return (string == null ? 0 : string.hashCode()) * 37 * 37 + 37 *
(value == null ? 0 : value.hashCode()) + (format == null ? 0 :
format.hashCode());
+ }
+ }
+}
Propchange:
poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java?rev=1782894&r1=1782893&r2=1782894&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java Mon
Feb 13 22:51:30 2017
@@ -17,6 +17,7 @@
package org.apache.poi.ss.formula;
+import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.ptg.NamePtg;
import org.apache.poi.ss.formula.ptg.NameXPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
@@ -75,6 +76,7 @@ public interface EvaluationWorkbook {
String resolveNameXText(NameXPtg ptg);
Ptg[] getFormulaTokens(EvaluationCell cell);
UDFFinder getUDFFinder();
+ SpreadsheetVersion getSpreadsheetVersion();
/**
* Propagated from {@link WorkbookEvaluator#clearAllCachedResultValues()}
to clear locally cached data.
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java?rev=1782894&r1=1782893&r2=1782894&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java Mon Feb 13
22:51:30 2017
@@ -785,6 +785,7 @@ public final class FormulaParser {
actualEndRow = _rowIndex;
} else { // Really no special quantifiers
actualStartRow++;
+ if (tbl.isHasTotalsRow()) actualEndRow--;
}
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java?rev=1782894&r1=1782893&r2=1782894&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java Mon Feb
13 22:51:30 2017
@@ -25,6 +25,7 @@ import java.util.Map;
import java.util.Stack;
import java.util.TreeSet;
+import org.apache.poi.ss.SpreadsheetVersion;
import
org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException;
import org.apache.poi.ss.formula.atp.AnalysisToolPak;
import org.apache.poi.ss.formula.eval.BlankEval;
@@ -45,39 +46,11 @@ import org.apache.poi.ss.formula.functio
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.formula.functions.Function;
import org.apache.poi.ss.formula.functions.IfFunc;
-import org.apache.poi.ss.formula.ptg.Area3DPtg;
-import org.apache.poi.ss.formula.ptg.Area3DPxg;
-import org.apache.poi.ss.formula.ptg.AreaErrPtg;
-import org.apache.poi.ss.formula.ptg.AreaPtg;
-import org.apache.poi.ss.formula.ptg.AttrPtg;
-import org.apache.poi.ss.formula.ptg.BoolPtg;
-import org.apache.poi.ss.formula.ptg.ControlPtg;
-import org.apache.poi.ss.formula.ptg.DeletedArea3DPtg;
-import org.apache.poi.ss.formula.ptg.DeletedRef3DPtg;
-import org.apache.poi.ss.formula.ptg.ErrPtg;
-import org.apache.poi.ss.formula.ptg.ExpPtg;
-import org.apache.poi.ss.formula.ptg.FuncVarPtg;
-import org.apache.poi.ss.formula.ptg.IntPtg;
-import org.apache.poi.ss.formula.ptg.MemAreaPtg;
-import org.apache.poi.ss.formula.ptg.MemErrPtg;
-import org.apache.poi.ss.formula.ptg.MemFuncPtg;
-import org.apache.poi.ss.formula.ptg.MissingArgPtg;
-import org.apache.poi.ss.formula.ptg.NamePtg;
-import org.apache.poi.ss.formula.ptg.NameXPtg;
-import org.apache.poi.ss.formula.ptg.NameXPxg;
-import org.apache.poi.ss.formula.ptg.NumberPtg;
-import org.apache.poi.ss.formula.ptg.OperationPtg;
-import org.apache.poi.ss.formula.ptg.Ptg;
-import org.apache.poi.ss.formula.ptg.Ref3DPtg;
-import org.apache.poi.ss.formula.ptg.Ref3DPxg;
-import org.apache.poi.ss.formula.ptg.RefErrorPtg;
-import org.apache.poi.ss.formula.ptg.RefPtg;
-import org.apache.poi.ss.formula.ptg.StringPtg;
-import org.apache.poi.ss.formula.ptg.UnionPtg;
-import org.apache.poi.ss.formula.ptg.UnknownPtg;
+import org.apache.poi.ss.formula.ptg.*;
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.util.CellRangeAddressBase;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.Internal;
import org.apache.poi.util.POILogFactory;
@@ -754,6 +727,107 @@ public final class WorkbookEvaluator {
}
/**
+ * Evaluate a formula outside a cell value, e.g. conditional format rules
or data validation expressions
+ *
+ * @param formula to evaluate
+ * @param ref defines the sheet and optionally row/column base for the
formula, if it is relative
+ * @param formulaType used in some contexts to define branches of logic
+ * @return value
+ * @throws IllegalArgumentException if ref does not define a sheet name to
evaluate the formula on.
+ */
+ public ValueEval evaluate(String formula, CellReference ref) {
+ final String sheetName = ref == null ? null : ref.getSheetName();
+ if (sheetName == null) throw new IllegalArgumentException("Sheet name
is required");
+ final int sheetIndex = getWorkbook().getSheetIndex(sheetName);
+ final OperationEvaluationContext ec = new
OperationEvaluationContext(this, getWorkbook(), sheetIndex, ref.getRow(),
ref.getCol(), new EvaluationTracker(_cache));
+ Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook)
getWorkbook(), FormulaType.CELL, sheetIndex, ref.getRow());
+ return evaluateNameFormula(ptgs, ec);
+ }
+
+ /**
+ * Some expressions need to be evaluated in terms of an offset from the
top left corner of a region,
+ * such as some data validation and conditional format expressions, when
those constraints apply
+ * to contiguous cells. When a relative formula is used, it must be
evaluated by shifting by the target
+ * offset position relative to the top left of the range.
+ *
+ * @param formula
+ * @param target cell context for the operation
+ * @param region containing the cell
+ * @return value
+ * @throws IllegalArgumentException if target does not define a sheet name
to evaluate the formula on.
+ */
+ public ValueEval evaluate(String formula, CellReference target,
CellRangeAddressBase region) {
+ final String sheetName = target == null ? null : target.getSheetName();
+ if (sheetName == null) throw new IllegalArgumentException("Sheet name
is required");
+
+ final int sheetIndex = getWorkbook().getSheetIndex(sheetName);
+ Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook)
getWorkbook(), FormulaType.CELL, sheetIndex, target.getRow());
+
+ adjustRegionRelativeReference(ptgs, target, region);
+
+ final OperationEvaluationContext ec = new
OperationEvaluationContext(this, getWorkbook(), sheetIndex, target.getRow(),
target.getCol(), new EvaluationTracker(_cache));
+ return evaluateNameFormula(ptgs, ec);
+ }
+
+ /**
+ * Adjust formula relative references by the offset between the start of
the given region and the given target cell.
+ * @param ptgs
+ * @param target cell within the region to use.
+ * @param region containing the cell
+ * @return true if any Ptg references were shifted
+ * @throws IndexOutOfBoundsException if the resulting shifted row/column
indexes are over the document format limits
+ * @throws IllegalArgumentException if target is not within region.
+ */
+ protected boolean adjustRegionRelativeReference(Ptg[] ptgs, CellReference
target, CellRangeAddressBase region) {
+ if (! region.isInRange(target)) {
+ throw new IllegalArgumentException(target + " is not within " +
region);
+ }
+
+ return adjustRegionRelativeReference(ptgs, target.getRow() -
region.getFirstRow(), target.getCol() - region.getFirstColumn());
+ }
+
+ /**
+ * Adjust the formula relative cell references by a given delta
+ * @param ptgs
+ * @param deltaRow target row offset from the top left cell of a region
+ * @param deltaColumn target column offset from the top left cell of a
region
+ * @return true if any Ptg references were shifted
+ * @throws IndexOutOfBoundsException if the resulting shifted row/column
indexes are over the document format limits
+ * @throws IllegalArgumentException if either of the deltas are negative,
as the assumption is we are shifting formulas
+ * relative to the top left cell of a region.
+ */
+ protected boolean adjustRegionRelativeReference(Ptg[] ptgs, int deltaRow,
int deltaColumn) {
+ if (deltaRow < 0) throw new IllegalArgumentException("offset row must
be positive");
+ if (deltaColumn < 0) throw new IllegalArgumentException("offset column
must be positive");
+ boolean shifted = false;
+ for (Ptg ptg : ptgs) {
+ // base class for cell reference "things"
+ if (ptg instanceof RefPtgBase) {
+ RefPtgBase ref = (RefPtgBase) ptg;
+ // re-calculate cell references
+ final SpreadsheetVersion version =
_workbook.getSpreadsheetVersion();
+ if (ref.isRowRelative()) {
+ final int rowIndex = ref.getRow() + deltaRow;
+ if (rowIndex > version.getMaxRows()) {
+ throw new IndexOutOfBoundsException(version.name() + "
files can only have " + version.getMaxRows() + " rows, but row " + rowIndex + "
was requested.");
+ }
+ ref.setRow(rowIndex);
+ shifted = true;
+ }
+ if (ref.isColRelative()) {
+ final int colIndex = ref.getColumn() + deltaColumn;
+ if (colIndex > version.getMaxColumns()) {
+ throw new IndexOutOfBoundsException(version.name() + "
files can only have " + version.getMaxColumns() + " columns, but column " +
colIndex + " was requested.");
+ }
+ ref.setColumn(colIndex);
+ shifted = true;
+ }
+ }
+ }
+ return shifted;
+ }
+
+ /**
* Whether to ignore missing references to external workbooks and
* use cached formula results in the main workbook instead.
* <p>
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java?rev=1782894&r1=1782893&r2=1782894&view=diff
==============================================================================
---
poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java
(original)
+++
poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java
Mon Feb 13 22:51:30 2017
@@ -20,6 +20,7 @@ package org.apache.poi.ss.formula.eval.f
import java.util.HashMap;
import java.util.Map;
+import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.EvaluationCell;
import org.apache.poi.ss.formula.EvaluationName;
import org.apache.poi.ss.formula.EvaluationSheet;
@@ -155,6 +156,10 @@ final class ForkedEvaluationWorkbook imp
return _masterBook.getUDFFinder();
}
+ public SpreadsheetVersion getSpreadsheetVersion() {
+ return _masterBook.getSpreadsheetVersion();
+ }
+
/* (non-Javadoc)
* leave the map alone, if it needs resetting, reusing this class is
probably a bad idea.
* @see
org.apache.poi.ss.formula.EvaluationSheet#clearAllCachedResultValues()
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
