You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2022/05/29 12:30:33 UTC
svn commit: r1901385 - in /poi/trunk/poi/src: main/java/org/apache/poi/ss/formula/functions/ test/java/org/apache/poi/ss/formula/functions/
Author: fanningpj
Date: Sun May 29 12:30:33 2022
New Revision: 1901385
URL: http://svn.apache.org/viewvc?rev=1901385&view=rev
Log:
[bug-66098] support wildcard matches in D* functions
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DMax.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DMin.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DSum.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DMax.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DMax.java?rev=1901385&r1=1901384&r2=1901385&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DMax.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DMax.java Sun May 29 12:30:33 2022
@@ -26,7 +26,6 @@ import org.apache.poi.ss.formula.eval.Va
* Finds the maximum value of a column in an area with given conditions.
*
* TODO:
- * - wildcards ? and * in string conditions
* - functions as conditions
*/
public final class DMax implements IDStarAlgorithm {
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DMin.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DMin.java?rev=1901385&r1=1901384&r2=1901385&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DMin.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DMin.java Sun May 29 12:30:33 2022
@@ -26,7 +26,6 @@ import org.apache.poi.ss.formula.eval.Va
* Finds the minimum value of a column in an area with given conditions.
*
* TODO:
- * - wildcards ? and * in string conditions
* - functions as conditions
*/
public final class DMin implements IDStarAlgorithm {
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=1901385&r1=1901384&r2=1901385&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 Sun May 29 12:30:33 2022
@@ -18,6 +18,7 @@
package org.apache.poi.ss.formula.functions;
import java.util.function.Supplier;
+import java.util.regex.Pattern;
import org.apache.poi.ss.formula.eval.AreaEval;
import org.apache.poi.ss.formula.eval.BlankEval;
@@ -40,7 +41,6 @@ import org.apache.poi.util.LocaleUtil;
* entries against the set of conditions is done here.
*
* TODO:
- * - wildcards ? and * in string conditions
* - functions as conditions
*/
@Internal
@@ -351,10 +351,16 @@ public final class DStarRunner implement
} else { // It's a text starts-with condition.
if(conditionString.isEmpty()) {
return value instanceof StringEval;
- }
- else {
+ } else {
String valueString = value instanceof BlankEval ? "" : OperandResolver.coerceValueToString(value);
- return valueString.toLowerCase(LocaleUtil.getUserLocale()).startsWith(conditionString.toLowerCase(LocaleUtil.getUserLocale()));
+ final String lowerValue = valueString.toLowerCase(LocaleUtil.getUserLocale());
+ final String lowerCondition = conditionString.toLowerCase(LocaleUtil.getUserLocale());
+ final Pattern pattern = Countif.StringMatcher.getWildCardPattern(lowerCondition);
+ if (pattern == null) {
+ return lowerValue.startsWith(lowerCondition);
+ } else {
+ return pattern.matcher(lowerValue).matches();
+ }
}
}
} else if(condition instanceof NumericValueEval) {
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DSum.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DSum.java?rev=1901385&r1=1901384&r2=1901385&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DSum.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DSum.java Sun May 29 12:30:33 2022
@@ -26,7 +26,6 @@ import org.apache.poi.ss.formula.eval.Va
* Finds the total value of matching values in a column in an area with given conditions.
*
* TODO:
- * - wildcards ? and * in string conditions
* - functions as conditions
*/
public final class DSum implements IDStarAlgorithm {
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java?rev=1901385&r1=1901384&r2=1901385&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java Sun May 29 12:30:33 2022
@@ -38,7 +38,7 @@ public class TestDGet {
//https://support.microsoft.com/en-us/office/dget-function-455568bf-4eef-45f7-90f0-ec250d00892e
@Test
void testMicrosoftExample1() throws IOException {
- try (HSSFWorkbook wb = initWorkbook1(false, false)) {
+ try (HSSFWorkbook wb = initWorkbook1(false, "=Apple")) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
assertError(fe, cell, "DGET(A5:E11, \"Yield\", A1:A3)", FormulaError.NUM);
@@ -49,7 +49,7 @@ public class TestDGet {
@Test
void testMicrosoftExample1CaseInsensitive() throws IOException {
- try (HSSFWorkbook wb = initWorkbook1(false, true)) {
+ try (HSSFWorkbook wb = initWorkbook1(false, "=apple")) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
assertError(fe, cell, "DGET(A5:E11, \"Yield\", A1:A3)", FormulaError.NUM);
@@ -59,8 +59,62 @@ public class TestDGet {
}
@Test
+ void testMicrosoftExample1StartsWith() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook1(false, "App")) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+ assertError(fe, cell, "DGET(A5:E11, \"Yield\", A1:A3)", FormulaError.NUM);
+ assertDouble(fe, cell, "DGET(A5:E11, \"Yield\", A1:F3)", 10);
+ assertDouble(fe, cell, "DGET(A5:E11, 4, A1:F3)", 10);
+ }
+ }
+
+ @Test
+ void testMicrosoftExample1StartsWithLowercase() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook1(false, "app")) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+ assertError(fe, cell, "DGET(A5:E11, \"Yield\", A1:A3)", FormulaError.NUM);
+ assertDouble(fe, cell, "DGET(A5:E11, \"Yield\", A1:F3)", 10);
+ assertDouble(fe, cell, "DGET(A5:E11, 4, A1:F3)", 10);
+ }
+ }
+
+ @Test
+ void testMicrosoftExample1Wildcard() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook1(false, "A*le")) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+ assertError(fe, cell, "DGET(A5:E11, \"Yield\", A1:A3)", FormulaError.NUM);
+ assertDouble(fe, cell, "DGET(A5:E11, \"Yield\", A1:F3)", 10);
+ assertDouble(fe, cell, "DGET(A5:E11, 4, A1:F3)", 10);
+ }
+ }
+
+ @Test
+ void testMicrosoftExample1WildcardLowercase() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook1(false, "a*le")) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+ assertError(fe, cell, "DGET(A5:E11, \"Yield\", A1:A3)", FormulaError.NUM);
+ assertDouble(fe, cell, "DGET(A5:E11, \"Yield\", A1:F3)", 10);
+ assertDouble(fe, cell, "DGET(A5:E11, 4, A1:F3)", 10);
+ }
+ }
+
+ @Test
+ void testMicrosoftExample1AppleWildcardNoMatch() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook1(false, "A*x")) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+ assertError(fe, cell, "DGET(A5:E11, \"Yield\", A1:A3)", FormulaError.NUM);
+ assertError(fe, cell, "DGET(A5:E11, \"Yield\", A1:F3)", FormulaError.VALUE);
+ }
+ }
+
+ @Test
void testMicrosoftExample1Variant() throws IOException {
- try (HSSFWorkbook wb = initWorkbook1(true, false)) {
+ try (HSSFWorkbook wb = initWorkbook1(true, "=Apple")) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
assertDouble(fe, cell, "DGET(A5:E11, \"Yield\", A1:F3)", 6);
@@ -68,11 +122,10 @@ public class TestDGet {
}
}
- private HSSFWorkbook initWorkbook1(boolean adjustAppleCondition, boolean lowercase) {
+ private HSSFWorkbook initWorkbook1(boolean adjustAppleCondition, String appleCondition) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
addRow(sheet, 0, "Tree", "Height", "Age", "Yield", "Profit", "Height");
- String appleCondition = lowercase ? "=apple" : "=Apple";
if (adjustAppleCondition) {
addRow(sheet, 1, appleCondition, ">=8", null, null, null, "<12");
} else {
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org