diff options
Diffstat (limited to 'src')
4 files changed, 76 insertions, 7 deletions
diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index 17a02ee598..e8b0f419c4 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -37,6 +37,7 @@ <!-- Don't forget to update status.xml too! --> <release version="3.5-beta5" date="2008-??-??"> + <action dev="POI-DEVELOPERS" type="fix">46647 - Fixed COUNTIF NE operator and other special cases involving type conversion</action> <action dev="POI-DEVELOPERS" type="add">46635 - Added a method to remove slides</action> <action dev="POI-DEVELOPERS" type="fix">46520 - Fixed HSSFFont.applyFont() to properly apply font to overlapping regions</action> <action dev="POI-DEVELOPERS" type="fix">46545 - Fixed ObjRecord to ignore excessive padding written by previous POI versions</action> diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index c4c22452d3..c96bd23f6a 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ <!-- Don't forget to update changes.xml too! --> <changes> <release version="3.5-beta5" date="2008-??-??"> + <action dev="POI-DEVELOPERS" type="fix">46647 - Fixed COUNTIF NE operator and other special cases involving type conversion</action> <action dev="POI-DEVELOPERS" type="add">46635 - Added a method to remove slides</action> <action dev="POI-DEVELOPERS" type="fix">46520 - Fixed HSSFFont.applyFont() to properly apply font to overlapping regions</action> <action dev="POI-DEVELOPERS" type="fix">46545 - Fixed ObjRecord to ignore excessive padding written by previous POI versions</action> diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java b/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java index db973ec21b..babedab61d 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Countif.java @@ -128,7 +128,7 @@ public final class Countif implements Function { case NONE: case EQ: return cmpResult == 0; - case NE: return cmpResult == 0; + case NE: return cmpResult != 0; case LT: return cmpResult < 0; case LE: return cmpResult <= 0; case GT: return cmpResult > 0; @@ -160,14 +160,27 @@ public final class Countif implements Function { double testValue; if(x instanceof StringEval) { // if the target(x) is a string, but parses as a number - // it may still count as a match + // it may still count as a match, only for the equality operator + switch (_operator.getCode()) { + case CmpOp.EQ: + case CmpOp.NONE: + break; + case CmpOp.NE: + // Always matches (inconsistent with above two cases). + // for example '<>123' matches '123', '4', 'abc', etc + return true; + default: + // never matches (also inconsistent with above three cases). + // for example '>5' does not match '6', + return false; + } StringEval se = (StringEval)x; Double val = OperandResolver.parseDouble(se.getStringValue()); if(val == null) { // x is text that is not a number return false; } - testValue = val.doubleValue(); + return _value == val.doubleValue(); } else if((x instanceof NumberEval)) { NumberEval ne = (NumberEval) x; testValue = ne.getNumberValue(); @@ -249,6 +262,7 @@ public final class Countif implements Function { _pattern = getWildCardPattern(value); break; default: + // pattern matching is never used for < > <= => _pattern = null; } } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java index 279ae51068..f11749030b 100755 --- a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java @@ -108,11 +108,64 @@ public final class TestCountFuncs extends TestCase { // note - same results when criteria is a string that parses as the number with the same value confirmCountIf(3, range, new StringEval("2.00")); - if (false) { // not supported yet: - // when criteria is an expression (starting with a comparison operator) - confirmCountIf(4, range, new StringEval(">1")); + // when criteria is an expression (starting with a comparison operator) + confirmCountIf(2, range, new StringEval(">1")); + // when criteria is an expression (starting with a comparison operator) + confirmCountIf(2, range, new StringEval(">0.5")); + } + + public void testCriteriaPredicateNe_Bug46647() { + I_MatchPredicate mp = Countif.createCriteriaPredicate(new StringEval("<>aa"), 0, 0); + StringEval seA = new StringEval("aa"); // this should not match the criteria '<>aa' + StringEval seB = new StringEval("bb"); // this should match + if (mp.matches(seA) && !mp.matches(seB)) { + throw new AssertionFailedError("Identified bug 46647"); } + assertFalse(mp.matches(seA)); + assertTrue(mp.matches(seB)); + + // general tests for not-equal (<>) operator + AreaEval range; + ValueEval[] values; + + values = new ValueEval[] { + new StringEval("aa"), + new StringEval("def"), + new StringEval("aa"), + new StringEval("ghi"), + new StringEval("aa"), + new StringEval("aa"), + }; + + range = EvalFactory.createAreaEval("A1:A6", values); + confirmCountIf(2, range, new StringEval("<>aa")); + + values = new ValueEval[] { + new StringEval("ab"), + new StringEval("aabb"), + new StringEval("aa"), // match + new StringEval("abb"), + new StringEval("aab"), + new StringEval("ba"), // match + }; + + range = EvalFactory.createAreaEval("A1:A6", values); + confirmCountIf(2, range, new StringEval("<>a*b")); + + + values = new ValueEval[] { + new NumberEval(222), + new NumberEval(222), + new NumberEval(111), + new StringEval("aa"), + new StringEval("111"), + }; + + range = EvalFactory.createAreaEval("A1:A5", values); + confirmCountIf(4, range, new StringEval("<>111")); + } + /** * special case where the criteria argument is a cell reference */ @@ -219,7 +272,7 @@ public final class TestCountFuncs extends TestCase { confirmPredicate(true, mp, 4); confirmPredicate(true, mp, 5); confirmPredicate(false, mp, 6); - confirmPredicate(true, mp, "4.9"); + confirmPredicate(false, mp, "4.9"); confirmPredicate(false, mp, "4.9t"); confirmPredicate(false, mp, "5.1"); confirmPredicate(false, mp, NULL); |