Bug 61764 - Conditional formatting rules don't evaluate properly for some multi-range rule definitions
Summary: Conditional formatting rules don't evaluate properly for some multi-range rul...
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.17-FINAL
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-11-15 00:28 UTC by Greg Woolsey
Modified: 2017-11-15 08:40 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Greg Woolsey 2017-11-15 00:28:12 UTC
When evaluating a single rule defined over a series of disjoint cell ranges using a comparison to a relative cell reference, POI gets confused about what applies where and the reference shifting needed to evaluate the rule in the context of cells not in the top left of the range.
Comment 1 Greg Woolsey 2017-11-15 02:24:50 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
Comment 2 Greg Woolsey 2017-11-15 07:39:23 UTC
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.
Comment 3 Greg Woolsey 2017-11-15 08:40:53 UTC
Fixed in r1815298