poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 61764] Conditional formatting rules don't evaluate properly for some multi-range rule definitions
Date Wed, 15 Nov 2017 07:39:23 GMT

--- Comment #2 from Greg Woolsey <gwoolsey@apache.org> ---
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",

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

You are receiving this mail because:
You are the assignee for the bug.
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

View raw message