diff options
8 files changed, 92 insertions, 17 deletions
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java index 8007ad06e0..16b0b2c259 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java @@ -175,7 +175,8 @@ public final class DStarRunner implements Function3Arg { largerEqualThan, smallerThan, smallerEqualThan, - equal + equal, + notEqual } /** @@ -295,7 +296,7 @@ public final class DStarRunner implements Function3Arg { } /** - * Test a value against a simple (< > <= >= = starts-with) condition string. + * Test a value against a simple (< > <= >= = <> starts-with) condition string. * * @param value The value to check. * @param condition The condition to check for. @@ -307,11 +308,19 @@ public final class DStarRunner implements Function3Arg { if(condition instanceof StringEval) { String conditionString = ((StringEval)condition).getStringValue(); - if(conditionString.startsWith("<")) { // It's a </<= condition. + if(conditionString.startsWith("<")) { // It's a </<=/<> condition. String number = conditionString.substring(1); if(number.startsWith("=")) { number = number.substring(1); return testNumericCondition(value, operator.smallerEqualThan, number); + } else if (number.startsWith(">")) { + number = number.substring(1); + boolean itsANumber = isNumber(number); + if (itsANumber) { + return testNumericCondition(value, operator.notEqual, number); + } else { + return testStringCondition(value, operator.notEqual, number); + } } else { return testNumericCondition(value, operator.smallerThan, number); } @@ -330,23 +339,11 @@ public final class DStarRunner implements Function3Arg { return value instanceof BlankEval; } // Distinguish between string and number. - boolean itsANumber; - try { - Integer.parseInt(stringOrNumber); - itsANumber = true; - } catch (NumberFormatException e) { // It's not an int. - try { - Double.parseDouble(stringOrNumber); - itsANumber = true; - } catch (NumberFormatException e2) { // It's a string. - itsANumber = false; - } - } + boolean itsANumber = isNumber(stringOrNumber); if(itsANumber) { return testNumericCondition(value, operator.equal, stringOrNumber); } else { // It's a string. - String valueString = value instanceof BlankEval ? "" : OperandResolver.coerceValueToString(value); - return stringOrNumber.equalsIgnoreCase(valueString); + return testStringCondition(value, operator.equal, stringOrNumber); } } else { // It's a text starts-with condition. if(conditionString.isEmpty()) { @@ -418,6 +415,28 @@ public final class DStarRunner implements Function3Arg { return result <= 0; case equal: return result == 0; + case notEqual: + return result != 0; + } + return false; // Can not be reached. + } + + /** + * Test whether a value matches a text condition. + * @param valueEval Value to check. + * @param op Comparator to use. + * @param condition Value to check against. + * @return whether the condition holds. + */ + private static boolean testStringCondition( + ValueEval valueEval, operator op, String condition) { + + String valueString = valueEval instanceof BlankEval ? "" : OperandResolver.coerceValueToString(valueEval); + switch(op) { + case equal: + return valueString.equalsIgnoreCase(condition); + case notEqual: + return !valueString.equalsIgnoreCase(condition); } return false; // Can not be reached. } @@ -454,4 +473,27 @@ public final class DStarRunner implements Function3Arg { return e.getErrorEval(); } } + + /** + * Determines whether a given string represents a valid number. + * + * @param value The string to be checked if it represents a number. + * @return {@code true} if the string can be parsed as either an integer or + * a double; {@code false} otherwise. + */ + private static boolean isNumber(String value) { + boolean itsANumber; + try { + Integer.parseInt(value); + itsANumber = true; + } catch (NumberFormatException e) { // It's not an int. + try { + Double.parseDouble(value); + itsANumber = true; + } catch (NumberFormatException e2) { // It's a string. + itsANumber = false; + } + } + return itsANumber; + } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDAverage.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDAverage.java index 6e3028492d..a41630c4d4 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDAverage.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDAverage.java @@ -41,6 +41,8 @@ public class TestDAverage { HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12); assertDouble(fe, cell, "DAVERAGE(A4:E10, \"Yield\", A1:B2)", 12); assertDouble(fe, cell, "DAVERAGE(A4:E10, 3, A4:E10)", 13); + assertDouble(fe, cell, "DAVERAGE(A4:E10, \"Profit\", A12:A13)", 92.6); + assertDouble(fe, cell, "DAVERAGE(A4:E10, \"Profit\", B12:C13)", 82.5); } } @@ -57,6 +59,9 @@ public class TestDAverage { addRow(sheet, 7, "Apple", 14, 15, 10, 75); addRow(sheet, 8, "Pear", 9, 8, 8, 76.8); addRow(sheet, 9, "Apple", 8, 9, 6, 45); + addRow(sheet, 10); + addRow(sheet, 11, "Tree", "Height", "Height"); + addRow(sheet, 12, "<>Apple", "<>12", "<>9"); return wb; } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java index a75d1ca22c..ea4d79e5fe 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java @@ -46,6 +46,8 @@ public class TestDCount { assertDouble(fe, cell, "DCOUNT(A5:E11, 2, A1:F3)", 4); assertDouble(fe, cell, "DCOUNT(A5:E11, 3, A1:F3)", 3); assertDouble(fe, cell, "DCOUNT(A5:E11, 5, A1:F3)", 3); + assertDouble(fe, cell, "DCOUNT(A5:E11, 5, A13:A14)", 2); + assertDouble(fe, cell, "DCOUNT(A5:E11, 5, B13:B14)", 3); } } @@ -63,6 +65,9 @@ public class TestDCount { addRow(sheet, 8, "Apple", 14, null, 10, 75); addRow(sheet, 9, "Pear", 9, 8, 8, "$77"); addRow(sheet, 10, "Apple", 12, 11, 6, 45); + addRow(sheet, 11); + addRow(sheet, 12, "Tree", "Height"); + addRow(sheet, 13, "<>Apple", "<>12"); return wb; } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java index 85ee0ff510..8c531b733b 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java @@ -45,6 +45,8 @@ public class TestDCountA { assertDouble(fe, cell, "DCOUNTA(A4:E10, \"Profit\", A1:F2)", 1); assertDouble(fe, cell, "DCOUNTA(A4:E10, \"Profit\", A1:F3)", 3); assertDouble(fe, cell, "DCOUNTA(A4:E10, \"Age\", A1:F3)", 2); + assertDouble(fe, cell, "DCOUNTA(A4:E10, \"Profit\", A12:A13)", 3); + assertDouble(fe, cell, "DCOUNTA(A4:E10, \"Age\", B12:B13)", 4); } } @@ -61,6 +63,9 @@ public class TestDCountA { addRow(sheet, 7, "Apple", 14, null, 10, 75); addRow(sheet, 8, "Pear", 9, 8, 8, "$77"); addRow(sheet, 9, "Apple", 8, 9, 6, 45); + addRow(sheet, 10); + addRow(sheet, 11, "Tree", "Height"); + addRow(sheet, 12, "<>Apple", "<>12"); return wb; } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java index 80cef52f85..a654d84d65 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java @@ -40,6 +40,8 @@ public class TestDProduct { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12); assertDouble(fe, cell, "DPRODUCT(A5:E11, \"Yield\", A1:F3)", 800, 0.0000000001); + assertDouble(fe, cell, "DPRODUCT(A5:E11, \"Yield\", A13:A14)", 720, 0.0000000001); + assertDouble(fe, cell, "DPRODUCT(A5:E11, \"Yield\", B13:C14)", 7560, 0.0000000001); } } @@ -72,6 +74,9 @@ public class TestDProduct { addRow(sheet, 8, "Apple", 14, 15, 10, 75); addRow(sheet, 9, "Pear", 9, 8, 8, 77); addRow(sheet, 10, "Apple", 8, 9, 6, 45); + addRow(sheet, 11); + addRow(sheet, 12, "Tree", "Height", "Height"); + addRow(sheet, 13, "<>Apple", "<>12", "<>9"); return wb; } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java index df4eb99d5c..b19d06bb38 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java @@ -40,6 +40,7 @@ public class TestDStdev { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12); assertDouble(fe, cell, "DSTDEV(A5:E11, \"Yield\", A1:A3)", 2.96647939483827, 0.0000000001); + assertDouble(fe, cell, "DSTDEV(A5:E11, \"Yield\", B12:C14)", 2.66458251889485, 0.0000000001); } } @@ -50,6 +51,8 @@ public class TestDStdev { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12); assertDouble(fe, cell, "DSTDEVP(A5:E11, \"Yield\", A1:A3)", 2.65329983228432, 0.0000000001); + assertDouble(fe, cell, "DSTDEVP(A5:E11, \"Yield\", A12:A13)", 0.816496580927726, 0.0000000001); + assertDouble(fe, cell, "DSTDEVP(A5:E11, \"Yield\", B12:C14)", 2.43241991988774, 0.0000000001); } } @@ -67,6 +70,9 @@ public class TestDStdev { addRow(sheet, 8, "Apple", 14, 15, 10, 75); addRow(sheet, 9, "Pear", 9, 8, 8, 77); addRow(sheet, 10, "Apple", 8, 9, 6, 45); + addRow(sheet, 11); + addRow(sheet, 11, "Tree", "Height", "Height"); + addRow(sheet, 12, "<>Apple", "<>12", "<>9"); return wb; } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java index 6c8a86bf28..46262eca04 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java @@ -40,6 +40,8 @@ public class TestDVar { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12); assertDouble(fe, cell, "DVAR(A4:E10, \"Yield\", A1:A3)", 8.8, 0.0000000001); + assertDouble(fe, cell, "DVAR(A4:E10, \"Yield\", A12:A13)", 1.0, 0.0000000001); + assertDouble(fe, cell, "DVAR(A4:E10, \"Yield\", B12:C13)", 10.9166666667, 0.0000000001); } } @@ -50,6 +52,8 @@ public class TestDVar { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12); assertDouble(fe, cell, "DVARP(A4:E10, \"Yield\", A1:A3)", 7.04, 0.0000000001); + assertDouble(fe, cell, "DVARP(A4:E10, \"Yield\", A12:A13)", 0.666666666666667, 0.0000000001); + assertDouble(fe, cell, "DVARP(A4:E10, \"Yield\", B12:C13)", 8.1875, 0.0000000001); } } @@ -66,6 +70,9 @@ public class TestDVar { addRow(sheet, 7, "Apple", 14, 15, 10, 75); addRow(sheet, 8, "Pear", 9, 8, 8, 77); addRow(sheet, 9, "Apple", 8, 9, 6, 45); + addRow(sheet, 10); + addRow(sheet, 11, "Tree", "Height", "Height"); + addRow(sheet, 12, "<>Apple", "<>12", "<>9"); return wb; } } diff --git a/test-data/spreadsheet/DStar.xls b/test-data/spreadsheet/DStar.xls Binary files differindex 8a5a45d7ef..e7c29bd63c 100644 --- a/test-data/spreadsheet/DStar.xls +++ b/test-data/spreadsheet/DStar.xls |