[Bug 61764] New: Conditional formatting rules don't evaluate properly for some multi-range rule definitions

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 61764] New: Conditional formatting rules don't evaluate properly for some multi-range rule definitions

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61764

            Bug ID: 61764
           Summary: Conditional formatting rules don't evaluate properly
                    for some multi-range rule definitions
           Product: POI
           Version: 3.17-FINAL
          Hardware: All
                OS: All
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61764] Conditional formatting rules don't evaluate properly for some multi-range rule definitions

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61764

--- Comment #1 from Greg Woolsey <[hidden email]> ---
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

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61764] Conditional formatting rules don't evaluate properly for some multi-range rule definitions

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61764

--- Comment #2 from Greg Woolsey <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61764] Conditional formatting rules don't evaluate properly for some multi-range rule definitions

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61764

Greg Woolsey <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|---                         |FIXED

--- Comment #3 from Greg Woolsey <[hidden email]> ---
Fixed in r1815298

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]