From 0f385a0440547dc4800bcd0da054dc8b340c88cf Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Wed, 25 May 2022 14:53:42 +0000 Subject: [PATCH] support empty param for DCount git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1901242 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/functions/DCount.java | 12 +++++- .../apache/poi/ss/formula/functions/DGet.java | 2 +- .../apache/poi/ss/formula/functions/DMax.java | 2 +- .../apache/poi/ss/formula/functions/DMin.java | 2 +- .../poi/ss/formula/functions/DStarRunner.java | 38 +++++++++++-------- .../apache/poi/ss/formula/functions/DSum.java | 2 +- .../ss/formula/functions/IDStarAlgorithm.java | 13 ++++++- .../poi/ss/formula/functions/TestDCount.java | 3 +- 8 files changed, 50 insertions(+), 24 deletions(-) diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java index a8c2ca22e1..c925652e7f 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java @@ -18,6 +18,7 @@ package org.apache.poi.ss.formula.functions; import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.NumericValueEval; import org.apache.poi.ss.formula.eval.ValueEval; /** @@ -28,8 +29,10 @@ public final class DCount implements IDStarAlgorithm { private int count; @Override - public boolean processMatch(ValueEval eval) { - count++; + public boolean processMatch(ValueEval eval, String field) { + if (field == null || eval instanceof NumericValueEval) { + count++; + } return true; } @@ -37,4 +40,9 @@ public final class DCount implements IDStarAlgorithm { public ValueEval getResult() { return new NumberEval(count); } + + @Override + public boolean allowEmptyMatchField() { + return true; + } } diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DGet.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DGet.java index 78b1b9235e..606e13d7cf 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/DGet.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DGet.java @@ -31,7 +31,7 @@ public final class DGet implements IDStarAlgorithm { private ValueEval result; @Override - public boolean processMatch(ValueEval eval) { + public boolean processMatch(ValueEval eval, String field) { if(result == null) // First match, just set the value. { result = eval; diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DMax.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DMax.java index 4d442f0415..0960e8c667 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/DMax.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DMax.java @@ -33,7 +33,7 @@ public final class DMax implements IDStarAlgorithm { private ValueEval maximumValue; @Override - public boolean processMatch(ValueEval eval) { + public boolean processMatch(ValueEval eval, String field) { if(eval instanceof NumericValueEval) { if(maximumValue == null) { // First match, just set the value. maximumValue = eval; diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DMin.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DMin.java index 61b75768c7..8919dc9426 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/DMin.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DMin.java @@ -33,7 +33,7 @@ public final class DMin implements IDStarAlgorithm { private ValueEval minimumValue; @Override - public boolean processMatch(ValueEval eval) { + public boolean processMatch(ValueEval eval, String field) { if(eval instanceof NumericValueEval) { if(minimumValue == null) { // First match, just set the value. minimumValue = eval; 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 369288d1fb..fc2e61b01a 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 @@ -80,7 +80,7 @@ public final class DStarRunner implements Function3Arg { this.algoType = algorithm; } - public final ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { if(args.length == 3) { return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2]); } @@ -98,25 +98,31 @@ public final class DStarRunner implements Function3Arg { AreaEval db = (AreaEval)database; AreaEval cdb = (AreaEval)conditionDatabase; - try { - filterColumn = OperandResolver.getSingleValue(filterColumn, srcRowIndex, srcColumnIndex); - } catch (EvaluationException e) { - return e.getErrorEval(); - } + // Create an algorithm runner. + final IDStarAlgorithm algorithm = algoType.newInstance(); - int fc; + int fc = -1; + String field = null; try { + filterColumn = OperandResolver.getSingleValue(filterColumn, srcRowIndex, srcColumnIndex); fc = getColumnForName(filterColumn, db); - } - catch (EvaluationException e) { - return ErrorEval.VALUE_INVALID; - } - if(fc == -1) { // column not found - return ErrorEval.VALUE_INVALID; + if (filterColumn instanceof StringEval) { + field = ((StringEval)filterColumn).getStringValue(); + } + if(fc == -1 && !algorithm.allowEmptyMatchField()) { + // column not found + return ErrorEval.VALUE_INVALID; + } + } catch (EvaluationException e) { + if (!algorithm.allowEmptyMatchField()) { + return e.getErrorEval(); + } + } catch (Exception e) { + if (!algorithm.allowEmptyMatchField()) { + return ErrorEval.VALUE_INVALID; + } } - // Create an algorithm runner. - IDStarAlgorithm algorithm = algoType.newInstance(); // Iterate over all DB entries. final int height = db.getHeight(); @@ -132,7 +138,7 @@ public final class DStarRunner implements Function3Arg { if(matches) { ValueEval currentValueEval = resolveReference(db, row, fc); // Pass the match to the algorithm and conditionally abort the search. - boolean shouldContinue = algorithm.processMatch(currentValueEval); + boolean shouldContinue = algorithm.processMatch(currentValueEval, field); if(! shouldContinue) { break; } diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DSum.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DSum.java index 26604677de..df60f7063a 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/DSum.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DSum.java @@ -33,7 +33,7 @@ public final class DSum implements IDStarAlgorithm { private double totalValue = 0; @Override - public boolean processMatch(ValueEval eval) { + public boolean processMatch(ValueEval eval, String field) { if(eval instanceof NumericValueEval) { double currentValue = ((NumericValueEval)eval).getNumberValue(); totalValue += currentValue; diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/IDStarAlgorithm.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/IDStarAlgorithm.java index 111964a4c4..e9fbd6c7be 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/IDStarAlgorithm.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/IDStarAlgorithm.java @@ -27,13 +27,24 @@ public interface IDStarAlgorithm { /** * Process a match that is found during a run through a database. * @param eval ValueEval of the cell in the matching row. References will already be resolved. + * @param field the field name (added in POI 5.2.3) * @return Whether we should continue iterating through the database. */ - boolean processMatch(ValueEval eval); + boolean processMatch(ValueEval eval, String field); + /** * Return a result ValueEval that will be the result of the calculation. * This is always called at the end of a run through the database. * @return a ValueEval */ ValueEval getResult(); + + /** + * Whether the field value (the 2nd param in DCOUNT, DGET, etc.) can evaluate to empty. It + * is allowed to evaluate to empty for DCOUNT. + * @return whether the field value can evaluate to empty + */ + default boolean allowEmptyMatchField() { + return false; + } } 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 65af098221..c970db1638 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 @@ -39,7 +39,8 @@ public class TestDCount { try (HSSFWorkbook wb = initWorkbook1()) { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); - assertDouble(fe, cell, "DCOUNT(A5:E11, \"Age\", A1:A2)", 3); + assertDouble(fe, cell, "DCOUNT(A5:E11,,A1:A2)", 3); + assertDouble(fe, cell, "DCOUNT(A5:E11, \"Age\", A1:A2)", 2); //next one returns 0 in error //assertDouble(fe, cell, "DCOUNT(A5:E11, \"Age\", A1:F2)", 1); } -- 2.39.5