| Summary: | Conditional formatting rules don't evaluate properly for some multi-range rule definitions | ||
|---|---|---|---|
| Product: | POI | Reporter: | Greg Woolsey <gwoolsey> |
| Component: | SS Common | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | ||
| Priority: | P2 | ||
| Version: | 3.17-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | All | ||
| OS: | All | ||
|
Description
Greg Woolsey
2017-11-15 00:28:12 UTC
It turns out Excel evaluates relative references in the conditional formula relative to the range the formula applies to by treating the top left cell of the applicable range as "A1". POI code currently shifts those references by starting from the formula-referenced cell and adding the current range cell's offsets. So offsets need to change to be formula ref + range top-left + current cell range offset rather than the current formula ref + current cell range offset Turns out this logic doesn't work for the formulas generated internally by Excel for string comparison rules. Different logic for adjusting the relative references in that case. Fortunately HSSF doesn't implement these types of rules, and XSSF has a "text" attribute that is the text to key the condition by. Using that also avoids some formula evaluation, which saves time.
Interesting Excel conditional formatting twist: I tried a "contains text" rule on cells with numbers.
When looking for values containing "0". Regardless of display formatting, Excel matched cells with significant zeros only. i.e. a cell with a value of 424 and a currency format that displays as "$424.00" does not match the rule, but a value of "$424.01" does. "$424.10" also does not match.
Similarly looking for a period (".") matches cells with fractional values only. This implies the matching is likely using the value stored in the OOXML which uses the canonical decimal separator.
The evaluation logic will need to support this via a double to String conversion that doesn't fall into scientific notation or include extraneous trailing zeros or periods. This rules out:
* direct Double.toString() (trailing ".0" for integers)
* BigDecimal.toPlainString() (converts the full floating point representation of double values, which doesn't match expected values)
* String.format() (have to hard-code decimal precision)
leaving us with:
DecimalFormat df = new DecimalFormat("0", DecimalFormatSymbols.getInstance(Locale.ENGLISH));
df.setMaximumFractionDigits(DecimalFormat.DOUBLE_FRACTION_DIGITS);
since POI rule objects are assumed not thread safe, an instance field scoped format instance in EvaluationConditionalFormatRule is a decent place to cache a DecimalFormat instance for balancing performance and memory. Most workbooks won't have very many rule definitions, and this way it is kept close to it's callers.
|