Author: fanningpj
Date: Fri Sep 20 20:20:34 2024
New Revision: 1920817
URL: http://svn.apache.org/viewvc?rev=1920817&view=rev
Log:
[github-692] D* functions are incompatible with the diamond operator. Thanks to
Luk Spiewak. This closes #692
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDAverage.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java
poi/trunk/test-data/spreadsheet/DStar.xls
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java?rev=1920817&r1=1920816&r2=1920817&view=diff
==============================================================================
---
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java
(original)
+++
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java
Fri Sep 20 20:20:34 2024
@@ -175,7 +175,8 @@ public final class DStarRunner implement
largerEqualThan,
smallerThan,
smallerEqualThan,
- equal
+ equal,
+ notEqual
}
/**
@@ -295,7 +296,7 @@ public final class DStarRunner implement
}
/**
- * 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 implement
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 implement
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 implement
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 implement
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;
+ }
}
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDAverage.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDAverage.java?rev=1920817&r1=1920816&r2=1920817&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDAverage.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDAverage.java
Fri Sep 20 20:20:34 2024
@@ -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;
}
}
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java?rev=1920817&r1=1920816&r2=1920817&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java
Fri Sep 20 20:20:34 2024
@@ -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;
}
}
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java?rev=1920817&r1=1920816&r2=1920817&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCountA.java
Fri Sep 20 20:20:34 2024
@@ -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;
}
}
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java?rev=1920817&r1=1920816&r2=1920817&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java
Fri Sep 20 20:20:34 2024
@@ -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;
}
}
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java?rev=1920817&r1=1920816&r2=1920817&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDStdev.java
Fri Sep 20 20:20:34 2024
@@ -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;
}
}
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java?rev=1920817&r1=1920816&r2=1920817&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java
Fri Sep 20 20:20:34 2024
@@ -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;
}
}
Modified: poi/trunk/test-data/spreadsheet/DStar.xls
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/DStar.xls?rev=1920817&r1=1920816&r2=1920817&view=diff
==============================================================================
Binary files poi/trunk/test-data/spreadsheet/DStar.xls (original) and
poi/trunk/test-data/spreadsheet/DStar.xls Fri Sep 20 20:20:34 2024 differ
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
