https://bz.apache.org/bugzilla/show_bug.cgi?id=61764
--- Comment #2 from Greg Woolsey <[email protected]> --- 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: [email protected] For additional commands, e-mail: [email protected]
