From 39dff4479dbf203ef6c89cdf9d288c554b31af33 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Wed, 25 May 2022 15:54:29 +0000 Subject: [PATCH] DStar functions need to support field params provided as numbers as well as field names git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1901250 13f79535-47bb-0310-9956-ffa450edef68 --- .../org/apache/poi/ss/formula/functions/DCount.java | 4 ++-- .../org/apache/poi/ss/formula/functions/DGet.java | 2 +- .../org/apache/poi/ss/formula/functions/DMax.java | 2 +- .../org/apache/poi/ss/formula/functions/DMin.java | 2 +- .../apache/poi/ss/formula/functions/DStarRunner.java | 11 ++++++----- .../org/apache/poi/ss/formula/functions/DSum.java | 2 +- .../poi/ss/formula/functions/IDStarAlgorithm.java | 4 ++-- .../apache/poi/ss/formula/functions/TestDCount.java | 1 + .../org/apache/poi/ss/formula/functions/TestDGet.java | 2 ++ 9 files changed, 17 insertions(+), 13 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 c925652e7f..a367eca814 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 @@ -29,8 +29,8 @@ public final class DCount implements IDStarAlgorithm { private int count; @Override - public boolean processMatch(ValueEval eval, String field) { - if (field == null || eval instanceof NumericValueEval) { + public boolean processMatch(ValueEval eval, int fieldNumber) { + if (fieldNumber < 0 || eval instanceof NumericValueEval) { count++; } 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 606e13d7cf..34c2900462 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, String field) { + public boolean processMatch(ValueEval eval, int fieldNumber) { 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 0960e8c667..77b4624406 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, String field) { + public boolean processMatch(ValueEval eval, int fieldNumber) { 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 8919dc9426..216d194ca5 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, String field) { + public boolean processMatch(ValueEval eval, int fieldNumber) { 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 2af4de1867..5c014802aa 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 @@ -104,12 +104,13 @@ public final class DStarRunner implements Function3Arg { final IDStarAlgorithm algorithm = algoType.newInstance(); int fc = -1; - String field = null; try { filterColumn = OperandResolver.getSingleValue(filterColumn, srcRowIndex, srcColumnIndex); - fc = getColumnForName(filterColumn, db); - if (filterColumn instanceof StringEval) { - field = ((StringEval)filterColumn).getStringValue(); + if (filterColumn instanceof NumericValueEval) { + //fc is zero based while Excel uses 1 based column numbering + fc = (int) Math.round(((NumericValueEval)filterColumn).getNumberValue()) - 1; + } else { + fc = getColumnForName(filterColumn, db); } if(fc == -1 && !algorithm.allowEmptyMatchField()) { // column not found @@ -140,7 +141,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, field); + boolean shouldContinue = algorithm.processMatch(currentValueEval, fc); 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 df60f7063a..758b2481c3 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, String field) { + public boolean processMatch(ValueEval eval, int fieldNumber) { 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 e9fbd6c7be..dff5eb227f 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,10 +27,10 @@ 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) + * @param fieldNumber the field number (added in POI 5.2.3) * @return Whether we should continue iterating through the database. */ - boolean processMatch(ValueEval eval, String field); + boolean processMatch(ValueEval eval, int fieldNumber); /** * Return a result ValueEval that will be the result of the calculation. 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 dcd9b83bae..31888498ee 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 @@ -42,6 +42,7 @@ public class TestDCount { assertDouble(fe, cell, "DCOUNT(A5:E11,,A1:A2)", 3); assertDouble(fe, cell, "DCOUNT(A5:E11, \"Age\", A1:A2)", 2); assertDouble(fe, cell, "DCOUNT(A5:E11, \"Age\", A1:F2)", 1); + assertDouble(fe, cell, "DCOUNT(A5:E11, 3, A1:F2)", 1); } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java index cfbf0b832a..349f1b5e51 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java @@ -43,6 +43,7 @@ public class TestDGet { 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); } } @@ -52,6 +53,7 @@ public class TestDGet { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); assertDouble(fe, cell, "DGET(A5:E11, \"Yield\", A1:F3)", 6); + assertDouble(fe, cell, "DGET(A5:E11, 4, A1:F3)", 6); } } -- 2.39.5