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: [email protected]
For additional commands, e-mail: [email protected]
