poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From gwool...@apache.org
Subject svn commit: r1815298 - in /poi/trunk: src/java/org/apache/poi/hssf/usermodel/ src/java/org/apache/poi/ss/formula/ src/java/org/apache/poi/ss/usermodel/ src/ooxml/java/org/apache/poi/xssf/usermodel/ src/ooxml/testcases/org/apache/poi/ss/usermodel/ src/o...
Date Wed, 15 Nov 2017 08:35:17 GMT
Author: gwoolsey
Date: Wed Nov 15 08:35:17 2017
New Revision: 1815298

URL: http://svn.apache.org/viewvc?rev=1815298&view=rev
Log:
Fixes Bug 61764 Conditional formatting rules don't evaluate properly for some multi-range
rule definitions

Fixes Bug 61761 Conditional formatting rule evaluation doesn't like comparing cells of different
types

fixed, with unit tests.

Added:
    poi/trunk/test-data/spreadsheet/61495-test.xlsm   (with props)
    poi/trunk/test-data/spreadsheet/conditional_formatting_cell_is.xlsx   (with props)
    poi/trunk/test-data/spreadsheet/conditional_formatting_multiple_ranges.xlsx   (with props)
    poi/trunk/test-data/spreadsheet/test_conditional_formatting.xlsx   (with props)
Modified:
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java
    poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java
    poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
    poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java
    poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java
    poi/trunk/src/ooxml/testcases/org/apache/poi/ss/usermodel/ConditionalFormattingEvalTest.java
    poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java?rev=1815298&r1=1815297&r2=1815298&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java Wed
Nov 15 08:35:17 2017
@@ -305,6 +305,10 @@ public final class HSSFConditionalFormat
         return null;
     }
 
+    public String getText() {
+        return null; // not available here, unless it exists and is unimplemented in cfRuleRecord
+    }
+    
     protected String toFormulaString(Ptg[] parsedExpression) {
         return toFormulaString(parsedExpression, workbook);
     }

Modified: poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java?rev=1815298&r1=1815297&r2=1815298&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java Wed
Nov 15 08:35:17 2017
@@ -17,12 +17,15 @@
 
 package org.apache.poi.ss.formula;
 
+import java.text.DecimalFormat;
+import java.text.DecimalFormatSymbols;
 import java.util.ArrayList;
 import java.util.Collections;
 import java.util.HashMap;
 import java.util.HashSet;
 import java.util.LinkedHashSet;
 import java.util.List;
+import java.util.Locale;
 import java.util.Map;
 import java.util.Set;
 
@@ -82,10 +85,13 @@ public class EvaluationConditionalFormat
     private final int ruleIndex;
     private final String formula1;
     private final String formula2;
+    private final String text;
     private final OperatorEnum operator;
     private final ConditionType type;
     // cached for performance, to avoid reading the XMLBean every time a conditionally formatted
cell is rendered
     private final ExcelNumberFormat numberFormat;
+    // cached for performance, used to format numeric cells for string comparisons.  See
Bug #61764 for explanation
+    private final DecimalFormat decimalTextFormat;
     
     /**
      *
@@ -112,10 +118,14 @@ public class EvaluationConditionalFormat
         this.regions = regions;
         formula1 = rule.getFormula1();
         formula2 = rule.getFormula2();
+        text = rule.getText();
         numberFormat = rule.getNumberFormat();
         
         operator = OperatorEnum.values()[rule.getComparisonOperation()];
         type = rule.getConditionType();
+        
+        decimalTextFormat = new DecimalFormat("0", DecimalFormatSymbols.getInstance(Locale.ENGLISH));
+        decimalTextFormat.setMaximumFractionDigits(340); // DecimalFormat.DOUBLE_FRACTION_DIGITS,
which is default scoped
     }
 
     /**
@@ -189,6 +199,13 @@ public class EvaluationConditionalFormat
     }
     
     /**
+     * @return condition text if any, or null
+     */
+    public String getText() {
+        return text;
+    }
+    
+    /**
      * @return the operator
      */
     public OperatorEnum getOperator() {
@@ -328,33 +345,32 @@ public class EvaluationConditionalFormat
         ValueEval eval = unwrapEval(workbookEvaluator.evaluate(rule.getFormula1(), ConditionalFormattingEvaluator.getRef(cell),
region));
         
         String f2 = rule.getFormula2();
-        ValueEval eval2 = null;
+        ValueEval eval2 = BlankEval.instance;
         if (f2 != null && f2.length() > 0) {
             eval2 = unwrapEval(workbookEvaluator.evaluate(f2, ConditionalFormattingEvaluator.getRef(cell),
region));
         }
         
         // we assume the cell has been evaluated, and the current formula value stored
-        if (DataValidationEvaluator.isType(cell, CellType.BOOLEAN)) {
-            if (eval instanceof BoolEval && (eval2 == null || eval2 instanceof BoolEval)
) {
-                return operator.isValid(cell.getBooleanCellValue(), ((BoolEval) eval).getBooleanValue(),
eval2 == null ? null : ((BoolEval) eval2).getBooleanValue());
-            }
-            return false; // wrong types
+        if (DataValidationEvaluator.isType(cell, CellType.BOOLEAN) 
+                && (eval == BlankEval.instance || eval instanceof BoolEval) 
+                && (eval2 == BlankEval.instance || eval2 instanceof BoolEval) 
+           ) {
+            return operator.isValid(cell.getBooleanCellValue(), eval == BlankEval.instance
? null : ((BoolEval) eval).getBooleanValue(), eval2 == BlankEval.instance ? null : ((BoolEval)
eval2).getBooleanValue());
         }
-        if (DataValidationEvaluator.isType(cell, CellType.NUMERIC)) {
-            if (eval instanceof NumberEval && (eval2 == null || eval2 instanceof
NumberEval) ) {
-                return operator.isValid(cell.getNumericCellValue(), ((NumberEval) eval).getNumberValue(),
eval2 == null ? null : ((NumberEval) eval2).getNumberValue());
-            }
-            return false; // wrong types
+        if (DataValidationEvaluator.isType(cell, CellType.NUMERIC) 
+                && (eval == BlankEval.instance || eval instanceof NumberEval )
+                && (eval2 == BlankEval.instance || eval2 instanceof NumberEval) 
+           ) {
+            return operator.isValid(cell.getNumericCellValue(), eval == BlankEval.instance
? null : ((NumberEval) eval).getNumberValue(), eval2 == BlankEval.instance ? null : ((NumberEval)
eval2).getNumberValue());
         }
-        if (DataValidationEvaluator.isType(cell, CellType.STRING)) {
-            if (eval instanceof StringEval && (eval2 == null || eval2 instanceof
StringEval) ) {
-                return operator.isValid(cell.getStringCellValue(), ((StringEval) eval).getStringValue(),
eval2 == null ? null : ((StringEval) eval2).getStringValue());
-            }
-            return false; // wrong types
+        if (DataValidationEvaluator.isType(cell, CellType.STRING)
+                && (eval == BlankEval.instance || eval instanceof StringEval )
+                && (eval2 == BlankEval.instance || eval2 instanceof StringEval) 
+           ) {
+                return operator.isValid(cell.getStringCellValue(), eval == BlankEval.instance
? null : ((StringEval) eval).getStringValue(), eval2 == BlankEval.instance ? null : ((StringEval)
eval2).getStringValue());
         }
         
-        // should not get here, but in case...
-        return false;
+        return operator.isValidForIncompatibleTypes();
     }
     
     private ValueEval unwrapEval(ValueEval eval) {
@@ -399,7 +415,7 @@ public class EvaluationConditionalFormat
         }
 
         final ValueAndFormat cv = getCellValue(cell);
-        
+
         // TODO: this could/should be delegated to the Enum type, but that's in the usermodel
package,
         // we may not want evaluation code there.  Of course, maybe the enum should go here
in formula,
         // and not be returned by the SS model, but then we need the XSSF rule to expose
the raw OOXML
@@ -502,10 +518,10 @@ public class EvaluationConditionalFormat
                     }
 
                     final Set<ValueAndFormat> avgSet = new LinkedHashSet<>(1);
-                    avgSet.add(new ValueAndFormat(Double.valueOf(allValues.size() == 0 ?
0 : total / allValues.size()), null));
+                    avgSet.add(new ValueAndFormat(Double.valueOf(allValues.size() == 0 ?
0 : total / allValues.size()), null, decimalTextFormat));
 
                     final double stdDev = allValues.size() <= 1 ? 0 : ((NumberEval) AggregateFunction.STDEV.evaluate(pop,
0, 0)).getNumberValue();
-                    avgSet.add(new ValueAndFormat(Double.valueOf(stdDev), null));
+                    avgSet.add(new ValueAndFormat(Double.valueOf(stdDev), null, decimalTextFormat));
                     return avgSet;
                 }
             }));
@@ -542,17 +558,17 @@ public class EvaluationConditionalFormat
             }
             return op.isValid(val, comp, null);
         case CONTAINS_TEXT:
-            // implemented both by a cfRule "text" attribute and a formula.  Use the formula.
-            return checkFormula(ref, region);
+            // implemented both by a cfRule "text" attribute and a formula.  Use the text.
+            return cv.toString().toLowerCase().contains(text.toLowerCase());
         case NOT_CONTAINS_TEXT:
-            // implemented both by a cfRule "text" attribute and a formula.  Use the formula.
-            return checkFormula(ref, region);
+            // implemented both by a cfRule "text" attribute and a formula.  Use the text.
+            return ! cv.toString().toLowerCase().contains(text.toLowerCase());
         case BEGINS_WITH:
-            // implemented both by a cfRule "text" attribute and a formula.  Use the formula.
-            return checkFormula(ref, region);
+            // implemented both by a cfRule "text" attribute and a formula.  Use the text.
+            return cv.toString().toLowerCase().startsWith(text.toLowerCase());
         case ENDS_WITH:
-            // implemented both by a cfRule "text" attribute and a formula.  Use the formula.
-            return checkFormula(ref, region);
+            // implemented both by a cfRule "text" attribute and a formula.  Use the text.
+            return cv.toString().toLowerCase().endsWith(text.toLowerCase());
         case CONTAINS_BLANKS:
             try {
                 String v = cv.getString();
@@ -622,7 +638,7 @@ public class EvaluationConditionalFormat
         if (cell != null) {
             final CellType type = cell.getCellType();
             if (type == CellType.NUMERIC || (type == CellType.FORMULA && cell.getCachedFormulaResultType()
== CellType.NUMERIC) ) {
-                return new ValueAndFormat(Double.valueOf(cell.getNumericCellValue()), cell.getCellStyle().getDataFormatString());
+                return new ValueAndFormat(Double.valueOf(cell.getNumericCellValue()), cell.getCellStyle().getDataFormatString(),
decimalTextFormat);
             } else if (type == CellType.STRING || (type == CellType.FORMULA && cell.getCachedFormulaResultType()
== CellType.STRING) ) {
                 return new ValueAndFormat(cell.getStringCellValue(), cell.getCellStyle().getDataFormatString());
             } else if (type == CellType.BOOLEAN || (type == CellType.FORMULA && cell.getCachedFormulaResultType()
== CellType.BOOLEAN) ) {
@@ -662,18 +678,57 @@ public class EvaluationConditionalFormat
         BETWEEN {
             @Override
             public <C extends Comparable<C>> boolean isValid(C cellValue, C v1,
C v2) {
+                if (v1 == null) {
+                    if (cellValue instanceof Number) {
+                        // use zero for null
+                        double n1 = 0;
+                        double n2 = v2 == null ? 0 : ((Number) v2).doubleValue();
+                        return Double.compare( ((Number) cellValue).doubleValue(), n1) >=
0 && Double.compare(((Number) cellValue).doubleValue(), n2) <= 0;
+                    } else if (cellValue instanceof String) {
+                        String n1 = "";
+                        String n2 = v2 == null ? "" : (String) v2;
+                        return ((String) cellValue).compareToIgnoreCase(n1) >= 0 &&
((String) cellValue).compareToIgnoreCase(n2) <= 0;
+                    } else if (cellValue instanceof Boolean) return false;
+                    return false; // just in case - not a typical possibility
+                }
                 return cellValue.compareTo(v1) >= 0 && cellValue.compareTo(v2)
<= 0;
             }
         },
         NOT_BETWEEN {
             @Override
             public <C extends Comparable<C>> boolean isValid(C cellValue, C v1,
C v2) {
+                if (v1 == null) {
+                    if (cellValue instanceof Number) {
+                        // use zero for null
+                        double n1 = 0;
+                        double n2 = v2 == null ? 0 : ((Number) v2).doubleValue();
+                        return Double.compare( ((Number) cellValue).doubleValue(), n1) <
0 || Double.compare(((Number) cellValue).doubleValue(), n2) > 0;
+                    } else if (cellValue instanceof String) {
+                        String n1 = "";
+                        String n2 = v2 == null ? "" : (String) v2;
+                        return ((String) cellValue).compareToIgnoreCase(n1) < 0 || ((String)
cellValue).compareToIgnoreCase(n2) > 0;
+                    } else if (cellValue instanceof Boolean) return true;
+                    return false; // just in case - not a typical possibility
+                }
                 return cellValue.compareTo(v1) < 0 || cellValue.compareTo(v2) > 0;
             }
+            
+            public boolean isValidForIncompatibleTypes() {
+                return true;
+            }
         },
         EQUAL {
             @Override
             public <C extends Comparable<C>> boolean isValid(C cellValue, C v1,
C v2) {
+                if (v1 == null) {
+                    if (cellValue instanceof Number) {
+                        // use zero for null
+                        return Double.compare( ((Number) cellValue).doubleValue(), 0) ==
0;
+                    } else if (cellValue instanceof String) {
+                        return false; // even an empty string is not equal the empty cell,
only another empty cell is, handled higher up
+                    } else if (cellValue instanceof Boolean) return false;
+                    return false; // just in case - not a typical possibility
+                }
                 // need to avoid instanceof, to work around a 1.6 compiler bug
                 if (cellValue.getClass() == String.class) {
                     return cellValue.toString().compareToIgnoreCase(v1.toString()) == 0;
@@ -684,34 +739,77 @@ public class EvaluationConditionalFormat
         NOT_EQUAL {
             @Override
             public <C extends Comparable<C>> boolean isValid(C cellValue, C v1,
C v2) {
+                if (v1 == null) {
+                    return true; // non-null not equal null, returns true
+                }
                 // need to avoid instanceof, to work around a 1.6 compiler bug
                 if (cellValue.getClass() == String.class) {
                     return cellValue.toString().compareToIgnoreCase(v1.toString()) == 0;
                 }
                 return cellValue.compareTo(v1) != 0;
             }
+            
+            public boolean isValidForIncompatibleTypes() {
+                return true;
+            }
         },
         GREATER_THAN {
             @Override
             public <C extends Comparable<C>> boolean isValid(C cellValue, C v1,
C v2) {
+                if (v1 == null) {
+                    if (cellValue instanceof Number) {
+                        // use zero for null
+                        return Double.compare( ((Number) cellValue).doubleValue(), 0) >
0;
+                    } else if (cellValue instanceof String) {
+                        return true; // non-null string greater than empty cell
+                    } else if (cellValue instanceof Boolean) return true;
+                    return false; // just in case - not a typical possibility
+                }
                 return cellValue.compareTo(v1) > 0;
             }
         },
         LESS_THAN {
             @Override
             public <C extends Comparable<C>> boolean isValid(C cellValue, C v1,
C v2) {
+                if (v1 == null) {
+                    if (cellValue instanceof Number) {
+                        // use zero for null
+                        return Double.compare( ((Number) cellValue).doubleValue(), 0) <
0;
+                    } else if (cellValue instanceof String) {
+                        return false; // non-null string greater than empty cell
+                    } else if (cellValue instanceof Boolean) return false;
+                    return false; // just in case - not a typical possibility
+                }
                 return cellValue.compareTo(v1) < 0;
             }
         },
         GREATER_OR_EQUAL {
             @Override
             public <C extends Comparable<C>> boolean isValid(C cellValue, C v1,
C v2) {
+                if (v1 == null) {
+                    if (cellValue instanceof Number) {
+                        // use zero for null
+                        return Double.compare( ((Number) cellValue).doubleValue(), 0) >=
0;
+                    } else if (cellValue instanceof String) {
+                        return true; // non-null string greater than empty cell
+                    } else if (cellValue instanceof Boolean) return true;
+                    return false; // just in case - not a typical possibility
+                }
                 return cellValue.compareTo(v1) >= 0;
             }
         },
         LESS_OR_EQUAL {
             @Override
             public <C extends Comparable<C>> boolean isValid(C cellValue, C v1,
C v2) {
+                if (v1 == null) {
+                    if (cellValue instanceof Number) {
+                        // use zero for null
+                        return Double.compare( ((Number) cellValue).doubleValue(), 0) <=
0;
+                    } else if (cellValue instanceof String) {
+                        return false; // non-null string not less than empty cell
+                    } else if (cellValue instanceof Boolean) return false; // for completeness
+                    return false; // just in case - not a typical possibility
+                }
                 return cellValue.compareTo(v1) <= 0;
             }
         },
@@ -720,11 +818,20 @@ public class EvaluationConditionalFormat
         /**
          * Evaluates comparison using operator instance rules
          * @param cellValue won't be null, assumption is previous checks handled that
-         * @param v1 if null, value assumed invalid, anything passes, per Excel behavior
-         * @param v2 null if not needed.  If null when needed, assume anything passes, per
Excel behavior
+         * @param v1 if null, per Excel behavior various results depending on the type of
cellValue and the specific enum instance
+         * @param v2 null if not needed.  If null when needed, various results, per Excel
behavior
          * @return true if the comparison is valid
          */
         public abstract <C extends Comparable<C>> boolean isValid(C cellValue,
C v1, C v2);
+        
+        /**
+         * Called when the cell and comparison values are of different data types
+         * Needed for negation operators, which should return true.
+         * @return true if this comparison is true when the types to compare are different
+         */
+        public boolean isValidForIncompatibleTypes() {
+            return false;
+        }
     }
     
     /**
@@ -735,17 +842,20 @@ public class EvaluationConditionalFormat
         private final Double value;
         private final String string;
         private final String format;
+        private final DecimalFormat decimalTextFormat;
         
-        public ValueAndFormat(Double value, String format) {
+        public ValueAndFormat(Double value, String format, DecimalFormat df) {
             this.value = value;
             this.format = format;
             string = null;
+            decimalTextFormat = df;
         }
         
         public ValueAndFormat(String value, String format) {
             this.value = null;
             this.format = format;
             string = value;
+            decimalTextFormat = null;
         }
         
         public boolean isNumber() {
@@ -760,6 +870,14 @@ public class EvaluationConditionalFormat
             return string;
         }
         
+        public String toString() {
+            if(isNumber()) {
+                return decimalTextFormat.format(getValue().doubleValue());
+            } else {
+                return getString();
+            }
+        }
+        
         @Override
         public boolean equals(Object obj) {
             if (!(obj instanceof ValueAndFormat)) {

Modified: poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java?rev=1815298&r1=1815297&r2=1815298&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java Wed Nov 15 08:35:17
2017
@@ -842,6 +842,11 @@ public final class WorkbookEvaluator {
     
     /**
      * Adjust formula relative references by the offset between the start of the given region
and the given target cell.
+     * That is, treat the region top-left cell as "A1" for the purposes of evaluating relative
reference components (row and/or column),
+     * and further move references by the position of the target within the region.
+     * <p><pre>formula ref + range top-left + current cell range offset </pre></p>
+     * which simplifies to
+     * <p><pre>formula ref + current cell ref</pre></p>
      * @param ptgs
      * @param target cell within the region to use.
      * @param region containing the cell
@@ -854,22 +859,11 @@ public final class WorkbookEvaluator {
             throw new IllegalArgumentException(target + " is not within " + region);
         }
         
-        return adjustRegionRelativeReference(ptgs, target.getRow() - region.getFirstRow(),
target.getCol() - region.getFirstColumn());
-    }
-    
-    /**
-     * Adjust the formula relative cell references by a given delta
-     * @param ptgs
-     * @param deltaRow target row offset from the top left cell of a region
-     * @param deltaColumn target column offset from the top left cell of a region
-     * @return true if any Ptg references were shifted
-     * @throws IndexOutOfBoundsException if the resulting shifted row/column indexes are
over the document format limits
-     * @throws IllegalArgumentException if either of the deltas are negative, as the assumption
is we are shifting formulas
-     * relative to the top left cell of a region.
-     */
-    protected boolean adjustRegionRelativeReference(Ptg[] ptgs, int deltaRow, int deltaColumn)
{
-        if (deltaRow < 0) throw new IllegalArgumentException("offset row must be positive");
-        if (deltaColumn < 0) throw new IllegalArgumentException("offset column must be
positive");
+        //return adjustRegionRelativeReference(ptgs, target.getRow() - region.getFirstRow(),
target.getCol() - region.getFirstColumn());
+        
+        int deltaRow = target.getRow();
+        int deltaColumn = target.getCol();
+        
         boolean shifted = false;
         for (Ptg ptg : ptgs) {
             // base class for cell reference "things"

Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java?rev=1815298&r1=1815297&r2=1815298&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java Wed Nov
15 08:35:17 2017
@@ -153,6 +153,13 @@ public interface ConditionalFormattingRu
     String getFormula2();
 
     /**
+     * XSSF rules store textual condition values as an attribute and also as a formula that
needs shifting.  Using the attribute is simpler/faster.
+     * HSSF rules don't have this and return null.  We can fall back on the formula for those
(AFAIK).
+     * @return condition text if it exists, or null
+     */
+    String getText();
+    
+    /**
      * The priority of the rule, if defined, otherwise 0.
      * <p>
      * If priority is 0, just use definition order, as that's how older HSSF rules 

Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java?rev=1815298&r1=1815297&r2=1815298&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java
(original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java
Wed Nov 15 08:35:17 2017
@@ -417,6 +417,10 @@ public class XSSFConditionalFormattingRu
         return _cfRule.sizeOfFormulaArray() == 2 ? _cfRule.getFormulaArray(1) : null;
     }
     
+    public String getText() {
+        return _cfRule.getText();
+    }
+    
     /**
      * Conditional format rules don't define stripes, so always 0
      * @see org.apache.poi.ss.usermodel.DifferentialStyleProvider#getStripeSize()

Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/ss/usermodel/ConditionalFormattingEvalTest.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/ss/usermodel/ConditionalFormattingEvalTest.java?rev=1815298&r1=1815297&r2=1815298&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/ss/usermodel/ConditionalFormattingEvalTest.java
(original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/ss/usermodel/ConditionalFormattingEvalTest.java
Wed Nov 15 08:35:17 2017
@@ -23,6 +23,7 @@ import java.util.List;
 
 import org.apache.poi.ss.formula.ConditionalFormattingEvaluator;
 import org.apache.poi.ss.formula.EvaluationConditionalFormatRule;
+import org.apache.poi.ss.formula.eval.NotImplementedException;
 import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.xssf.XSSFTestDataSamples;
 import org.apache.poi.xssf.usermodel.XSSFColor;
@@ -130,6 +131,64 @@ public class ConditionalFormattingEvalTe
         assertEquals("wrong bg color for " + ref, "FFFFFF00", getColor(rules.get(0).getRule().getPatternFormatting().getFillBackgroundColorColor()));
     }
     
+    @Test
+    public void testRepeatedEval() throws Exception {
+        wb = XSSFTestDataSamples.openSampleWorkbook("test_conditional_formatting.xlsx");
+        formulaEval = new XSSFFormulaEvaluator(wb);
+        cfe = new ConditionalFormattingEvaluator(wb, formulaEval);
+
+        sheet = wb.getSheetAt(0);
+        try {
+            getRulesFor(2, 1);
+            fail("Got rules when an unsupported function error was expected.");
+        } catch (NotImplementedException e) {
+            // expected
+        }
+
+        try {
+            getRulesFor(2, 1);
+            fail("Got rules the second time when an unsupported function error was expected.");
+        } catch (NotImplementedException e) {
+            // expected
+        }
+        
+    }
+    
+    @Test
+    public void testCellValueIsWrongType() throws Exception {
+        wb = XSSFTestDataSamples.openSampleWorkbook("conditional_formatting_cell_is.xlsx");
+        formulaEval = new XSSFFormulaEvaluator(wb);
+        cfe = new ConditionalFormattingEvaluator(wb, formulaEval);
+
+        sheet = wb.getSheetAt(1);
+        
+        assertEquals("wrong # of matching rules", 1, getRulesFor(3, 1).size());
+    }
+    
+    @Test
+    public void testRangeCondition() throws Exception {
+        wb = XSSFTestDataSamples.openSampleWorkbook("conditional_formatting_multiple_ranges.xlsx");
+        formulaEval = new XSSFFormulaEvaluator(wb);
+        cfe = new ConditionalFormattingEvaluator(wb, formulaEval);
+        
+        sheet = wb.getSheetAt(0);
+        
+        assertEquals("wrong # of matching rules", 0, getRulesFor(0, 0).size());
+        assertEquals("wrong # of matching rules", 0, getRulesFor(1, 0).size());
+        assertEquals("wrong # of matching rules", 0, getRulesFor(2, 0).size());
+        assertEquals("wrong # of matching rules", 1, getRulesFor(3, 0).size());
+        assertEquals("wrong # of matching rules", 0, getRulesFor(0, 1).size());
+        assertEquals("wrong # of matching rules", 0, getRulesFor(1, 1).size());
+        assertEquals("wrong # of matching rules", 1, getRulesFor(2, 1).size());
+        assertEquals("wrong # of matching rules", 1, getRulesFor(3, 1).size());
+        assertEquals("wrong # of matching rules", 1, getRulesFor(0, 3).size());
+        assertEquals("wrong # of matching rules", 0, getRulesFor(1, 3).size());
+        assertEquals("wrong # of matching rules", 1, getRulesFor(2, 3).size());
+        assertEquals("wrong # of matching rules", 0, getRulesFor(0, 6).size());
+        assertEquals("wrong # of matching rules", 0, getRulesFor(3, 6).size());
+        assertEquals("wrong # of matching rules", 0, getRulesFor(2, 6).size());
+    }
+    
     private List<EvaluationConditionalFormatRule> getRulesFor(int row, int col) {
         ref = new CellReference(sheet.getSheetName(), row, col, false, false);
         return rules = cfe.getConditionalFormattingForCell(ref);

Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java?rev=1815298&r1=1815297&r2=1815298&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
(original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
Wed Nov 15 08:35:17 2017
@@ -428,4 +428,17 @@ public final class TestXSSFFormulaEvalua
         CellValue value = evaluator.evaluate(cell);
         assertEquals(3750, value.getNumberValue(), 0.001);
     }
+    
+    @Test
+    public void testBug61495() {
+        Workbook wb = XSSFTestDataSamples.openSampleWorkbook("61495-test.xlsm");
+        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+        Cell cell = wb.getSheetAt(0).getRow(0).getCell(1);
+//        assertEquals("D 67.10", cell.getStringCellValue());
+        
+        CellValue value = evaluator.evaluate(cell);
+        assertEquals("D 67.10", value.getStringValue());
+        
+        assertEquals("D 0,068", evaluator.evaluate(wb.getSheetAt(0).getRow(1).getCell(1)));
+    }
 }

Added: poi/trunk/test-data/spreadsheet/61495-test.xlsm
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/61495-test.xlsm?rev=1815298&view=auto
==============================================================================
Binary file - no diff available.

Propchange: poi/trunk/test-data/spreadsheet/61495-test.xlsm
------------------------------------------------------------------------------
    svn:mime-type = application/octet-stream

Added: poi/trunk/test-data/spreadsheet/conditional_formatting_cell_is.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/conditional_formatting_cell_is.xlsx?rev=1815298&view=auto
==============================================================================
Binary file - no diff available.

Propchange: poi/trunk/test-data/spreadsheet/conditional_formatting_cell_is.xlsx
------------------------------------------------------------------------------
    svn:mime-type = application/octet-stream

Added: poi/trunk/test-data/spreadsheet/conditional_formatting_multiple_ranges.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/conditional_formatting_multiple_ranges.xlsx?rev=1815298&view=auto
==============================================================================
Binary file - no diff available.

Propchange: poi/trunk/test-data/spreadsheet/conditional_formatting_multiple_ranges.xlsx
------------------------------------------------------------------------------
    svn:mime-type = application/octet-stream

Added: poi/trunk/test-data/spreadsheet/test_conditional_formatting.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/test_conditional_formatting.xlsx?rev=1815298&view=auto
==============================================================================
Binary file - no diff available.

Propchange: poi/trunk/test-data/spreadsheet/test_conditional_formatting.xlsx
------------------------------------------------------------------------------
    svn:mime-type = application/octet-stream



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message