org.apache.poi.ss.formula.ConditionalFormattingEvaluator Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of poi Show documentation
Show all versions of poi Show documentation
Apache POI - Java API To Access Microsoft Format Files
/* ====================================================================
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.
*
* 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.
*
*
*/
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.
*
* Instead we need a cached version that is discarded when definitions change.
*
* 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> formats = new HashMap>();
/**
* Evaluating rules for cells in their region(s) is expensive, so we want to cache them,
* and empty/reevaluate the cache when values change.
*
* Rule lists are in priority order, as evaluated by Excel (smallest priority # for XSSF, definition order for HSSF)
*
* CellReference implements equals().
*/
private final Map> values = new HashMap>();
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.
*
* 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 getRules(Sheet sheet) {
final String sheetName = sheet.getSheetName();
List rules = formats.get(sheetName);
if (rules == null) {
if (formats.containsKey(sheetName)) {
return Collections.emptyList();
}
final SheetConditionalFormatting scf = sheet.getSheetConditionalFormatting();
final int count = scf.getNumConditionalFormattings();
rules = new ArrayList(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 CTCfRule in XSSFConditionalFormattingRule.
*
* 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.
*
* 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 cellRef NOTE: if no sheet name is specified, this uses the workbook active sheet
* @return Unmodifiable List of {@link EvaluationConditionalFormatRule}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 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 CTCfRule in XSSFConditionalFormattingRule.
*
* 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.
*
* 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 EvaluationConditionalFormatRule}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 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 getConditionalFormattingForCell(Cell cell, CellReference ref) {
List rules = values.get(ref);
if (rules == null) {
// compute and cache them
rules = new ArrayList();
/*
* 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 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 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.
*
* We could overload this with convenience methods taking a sheet name and sheet index as well.
*
* @param sheet containing the rule
* @param conditionalFormattingIndex of the {@link ConditionalFormatting} instance in the sheet's array
* @param ruleIndex of the {@link ConditionalFormattingRule} instance within the {@link ConditionalFormatting}
* @return unmodifiable List of all cells in the rule's region matching the rule's condition
*/
public List 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 getMatchingCells(EvaluationConditionalFormatRule rule) {
final List cells = new ArrayList();
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 cellRules = getConditionalFormattingForCell(cell);
if (cellRules.contains(rule)) {
cells.add(cell);
}
}
}
}
return Collections.unmodifiableList(cells);
}
}
| | | |