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 2021/08/16 11:29:33 UTC
svn commit: r1892366 - in /poi/trunk/poi/src:
main/java/org/apache/poi/ss/formula/atp/
main/java/org/apache/poi/ss/formula/functions/
test/java/org/apache/poi/ss/formula/functions/
Author: fanningpj
Date: Mon Aug 16 11:29:33 2021
New Revision: 1892366
URL: http://svn.apache.org/viewvc?rev=1892366&view=rev
Log:
[github-242] add support for MAXIFS, MINIFS, AVERAGEIFS functions. Thanks to jniewerth. This closes #242
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Averageifs.java (with props)
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Maxifs.java (with props)
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Minifs.java (with props)
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageifs.java (with props)
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMaxifs.java (with props)
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMinifs.java (with props)
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Countifs.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sumifs.java
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1892366&r1=1892365&r2=1892366&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java Mon Aug 16 11:29:33 2021
@@ -69,7 +69,7 @@ public final class AnalysisToolPak imple
r(m, "AMORDEGRC", null);
r(m, "AMORLINC", null);
r(m, "AVERAGEIF", null);
- r(m, "AVERAGEIFS", null);
+ r(m, "AVERAGEIFS", Averageifs.instance);
r(m, "BAHTTEXT", null);
r(m, "BESSELI", null);
r(m, "BESSELJ", null);
@@ -142,7 +142,9 @@ public final class AnalysisToolPak imple
r(m, "ISODD", ParityFunction.IS_ODD);
r(m, "JIS", null);
r(m, "LCM", null);
+ r(m, "MAXIFS", Maxifs.instance);
r(m, "MDURATION", null);
+ r(m, "MINIFS", Minifs.instance);
r(m, "MROUND", MRound.instance);
r(m, "MULTINOMIAL", null);
r(m, "NETWORKDAYS", NetworkdaysFunction.instance);
@@ -239,7 +241,7 @@ public final class AnalysisToolPak imple
* @throws IllegalArgumentException if the function is unknown or already registered.
* @since 3.8 beta6
*/
- public static void registerFunction(String name, FreeRefFunction func){
+ public static void registerFunction(String name, FreeRefFunction func){
AnalysisToolPak inst = (AnalysisToolPak)instance;
if(!isATPFunction(name)) {
FunctionMetadata metaData = FunctionMetadataRegistry.getFunctionByName(name);
Added: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Averageifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Averageifs.java?rev=1892366&view=auto
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Averageifs.java (added)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Averageifs.java Mon Aug 16 11:29:33 2021
@@ -0,0 +1,82 @@
+/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
+/**
+ * Implementation for the Excel function AVERAGEIFS<p>
+ *
+ * Syntax : <p>
+ * AVERAGEIFS ( <b>average_range</b>, <b>criteria_range1</b>, <b>criteria1</b>,
+ * [<b>criteria_range2</b>, <b>criteria2</b>], ...)
+ * <ul>
+ * <li><b>min_range</b> Required. One or more cells to average, including numbers or names, ranges,
+ * or cell references that contain numbers. Blank and text values are ignored.</li>
+ * <li><b>criteria1_range</b> Required. The first range in which
+ * to evaluate the associated criteria.</li>
+ * <li><b>criteria1</b> Required. The criteria in the form of a number, expression,
+ * cell reference, or text that define which cells in the criteria_range1
+ * argument will be added</li>
+ * <li><b> criteria_range2, criteria2, ...</b> Optional. Additional ranges and their associated criteria.
+ * Up to 127 range/criteria pairs are allowed.
+ * </ul>
+ */
+public final class Averageifs extends Baseifs {
+ /**
+ * Singleton
+ */
+ public static final FreeRefFunction instance = new Averageifs();
+
+ /**
+ * https://support.microsoft.com/en-us/office/averageifs-function-48910c45-1fc0-4389-a028-f7c5c3001690
+ * AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
+ * need at least 3 arguments and need to have an odd number of arguments (average_range plus x*(criteria_range, criteria))
+ */
+ @Override
+ protected boolean hasInitialRange() {
+ return true;
+ }
+
+ @Override
+ protected Aggregator createAggregator() {
+ return new Aggregator() {
+ Double sum = 0.0;
+ Integer count = 0;
+
+ @Override
+ public void addValue(ValueEval value) {
+ if(!(value instanceof NumberEval)) return;
+
+ double d = ((NumberEval) value).getNumberValue();;
+ sum += d;
+ count++;
+
+ }
+
+ @Override
+ public ValueEval getResult() {
+ return count == 0 ? ErrorEval.DIV_ZERO : new NumberEval(sum / count);
+ }
+ };
+ }
+}
Propchange: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Averageifs.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java?rev=1892366&r1=1892365&r2=1892366&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java Mon Aug 16 11:29:33 2021
@@ -40,11 +40,21 @@ import org.apache.poi.ss.formula.functio
* @return true if there should be a range argument before the criteria pairs
*/
protected abstract boolean hasInitialRange();
-
+
+ /**
+ * Implements the details of a specific aggregation function
+ */
+ protected static interface Aggregator {
+ void addValue(ValueEval d);
+ ValueEval getResult();
+ }
+
+ protected abstract Aggregator createAggregator();
+
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
final boolean hasInitialRange = hasInitialRange();
final int firstCriteria = hasInitialRange ? 1 : 0;
-
+
if( args.length < (2+firstCriteria) || args.length % 2 != firstCriteria ) {
return ErrorEval.VALUE_INVALID;
}
@@ -54,21 +64,20 @@ import org.apache.poi.ss.formula.functio
if (hasInitialRange) {
sumRange = convertRangeArg(args[0]);
}
-
+
// collect pairs of ranges and criteria
AreaEval[] ae = new AreaEval[(args.length - firstCriteria)/2];
I_MatchPredicate[] mp = new I_MatchPredicate[ae.length];
for(int i = firstCriteria, k=0; i < args.length; i += 2, k++){
ae[k] = convertRangeArg(args[i]);
-
+
mp[k] = Countif.createCriteriaPredicate(args[i+1], ec.getRowIndex(), ec.getColumnIndex());
}
validateCriteriaRanges(sumRange, ae);
validateCriteria(mp);
- double result = aggregateMatchingCells(sumRange, ae, mp);
- return new NumberEval(result);
+ return aggregateMatchingCells(createAggregator(), sumRange, ae, mp);
} catch (EvaluationException e) {
return e.getErrorEval();
}
@@ -84,16 +93,16 @@ import org.apache.poi.ss.formula.functio
private static void validateCriteriaRanges(AreaEval sumRange, AreaEval[] criteriaRanges) throws EvaluationException {
int h = criteriaRanges[0].getHeight();
int w = criteriaRanges[0].getWidth();
-
- if (sumRange != null
- && (sumRange.getHeight() != h
- || sumRange.getWidth() != w) ) {
+
+ if (sumRange != null
+ && (sumRange.getHeight() != h
+ || sumRange.getWidth() != w) ) {
throw EvaluationException.invalidValue();
}
-
+
for(AreaEval r : criteriaRanges){
if(r.getHeight() != h ||
- r.getWidth() != w ) {
+ r.getWidth() != w ) {
throw EvaluationException.invalidValue();
}
}
@@ -107,7 +116,7 @@ import org.apache.poi.ss.formula.functio
*/
private static void validateCriteria(I_MatchPredicate[] criteria) throws EvaluationException {
for(I_MatchPredicate predicate : criteria) {
-
+
// check for errors in predicate and return immediately using this error code
if(predicate instanceof ErrorMatcher) {
throw new EvaluationException(ErrorEval.valueOf(((ErrorMatcher)predicate).getValue()));
@@ -123,12 +132,11 @@ import org.apache.poi.ss.formula.functio
* @return the computed value
* @throws EvaluationException if there is an issue with eval
*/
- private static double aggregateMatchingCells(AreaEval sumRange, AreaEval[] ranges, I_MatchPredicate[] predicates)
+ private static ValueEval aggregateMatchingCells(Aggregator aggregator, AreaEval sumRange, AreaEval[] ranges, I_MatchPredicate[] predicates)
throws EvaluationException {
int height = ranges[0].getHeight();
int width = ranges[0].getWidth();
- double result = 0.0;
for (int r = 0; r < height; r++) {
for (int c = 0; c < width; c++) {
@@ -142,38 +150,22 @@ import org.apache.poi.ss.formula.functio
matches = false;
break;
}
-
}
- if(matches) { // sum only if all of the corresponding criteria specified are true for that cell.
- result += accumulate(sumRange, r, c);
+ if(matches) { // aggregate only if all of the corresponding criteria specified are true for that cell.
+ if(sumRange != null) {
+ ValueEval value = sumRange.getRelativeValue(r, c);
+ if (value instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval)value);
+ }
+ aggregator.addValue(value);
+ } else {
+ aggregator.addValue(null);
+ }
}
}
}
- return result;
- }
-
- /**
- * For counts, this would return 1, for sums it returns a cell value or zero.
- * This is only called after all the criteria are confirmed true for the coordinates.
- * @param sumRange if used
- * @param relRowIndex
- * @param relColIndex
- * @return the aggregate input value corresponding to the given range coordinates
- * @throws EvaluationException if there is an issue with eval
- */
- private static double accumulate(AreaEval sumRange, int relRowIndex, int relColIndex) throws EvaluationException {
- if (sumRange == null) return 1.0; // count
-
- ValueEval addend = sumRange.getRelativeValue(relRowIndex, relColIndex);
- if (addend instanceof NumberEval) {
- return ((NumberEval) addend).getNumberValue();
- } else if (addend instanceof ErrorEval) {
- throw new EvaluationException((ErrorEval)addend);
- } else {
- // everything else (including string and boolean values) counts as zero
- return 0.0;
- }
+ return aggregator.getResult();
}
protected static AreaEval convertRangeArg(ValueEval eval) throws EvaluationException {
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Countifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Countifs.java?rev=1892366&r1=1892365&r2=1892366&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Countifs.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Countifs.java Mon Aug 16 11:29:33 2021
@@ -18,6 +18,9 @@
package org.apache.poi.ss.formula.functions;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
/**
* Implementation for the function COUNTIFS
* <p>
@@ -41,5 +44,21 @@ public class Countifs extends Baseifs {
protected boolean hasInitialRange() {
return false;
}
-}
+ @Override
+ protected Aggregator createAggregator() {
+ return new Aggregator() {
+ double accumulator = 0.0;
+
+ @Override
+ public void addValue(ValueEval value) {
+ accumulator += 1.0;
+ }
+
+ @Override
+ public ValueEval getResult() {
+ return new NumberEval(accumulator);
+ }
+ };
+ }
+}
Added: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Maxifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Maxifs.java?rev=1892366&view=auto
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Maxifs.java (added)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Maxifs.java Mon Aug 16 11:29:33 2021
@@ -0,0 +1,78 @@
+/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
+/**
+ * Implementation for the Excel function MAXIFS<p>
+ *
+ * Syntax : <p>
+ * MAXIFS ( <b>max_range</b>, <b>criteria_range1</b>, <b>criteria1</b>,
+ * [<b>criteria_range2</b>, <b>criteria2</b>], ...)
+ * <ul>
+ * <li><b>min_range</b> Required. One or more cells to determine the maximum value of, including numbers or names, ranges,
+ * or cell references that contain numbers. Blank and text values are ignored.</li>
+ * <li><b>criteria1_range</b> Required. The first range in which
+ * to evaluate the associated criteria.</li>
+ * <li><b>criteria1</b> Required. The criteria in the form of a number, expression,
+ * cell reference, or text that define which cells in the criteria_range1
+ * argument will be added</li>
+ * <li><b> criteria_range2, criteria2, ...</b> Optional. Additional ranges and their associated criteria.
+ * Up to 127 range/criteria pairs are allowed.
+ * </ul>
+ */
+public final class Maxifs extends Baseifs {
+ /**
+ * Singleton
+ */
+ public static final FreeRefFunction instance = new Maxifs();
+
+ /**
+ * https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883
+ * MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
+ * need at least 3 arguments and need to have an odd number of arguments (max-range plus x*(criteria_range, criteria))
+ */
+ @Override
+ protected boolean hasInitialRange() {
+ return true;
+ }
+
+ @Override
+ protected Aggregator createAggregator() {
+ return new Aggregator() {
+ Double accumulator = null;
+
+ @Override
+ public void addValue(ValueEval value) {
+ double d = (value instanceof NumberEval) ? ((NumberEval) value).getNumberValue() : 0.0;
+ if(accumulator == null || accumulator < d) {
+ accumulator = d;
+ }
+ }
+
+ @Override
+ public ValueEval getResult() {
+ return new NumberEval(accumulator == null ? 0.0 : accumulator);
+ }
+ };
+ }
+}
Propchange: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Maxifs.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Minifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Minifs.java?rev=1892366&view=auto
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Minifs.java (added)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Minifs.java Mon Aug 16 11:29:33 2021
@@ -0,0 +1,78 @@
+/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
+/**
+ * Implementation for the Excel function MINIFS<p>
+ *
+ * Syntax : <p>
+ * MINIFS ( <b>min_range</b>, <b>criteria_range1</b>, <b>criteria1</b>,
+ * [<b>criteria_range2</b>, <b>criteria2</b>], ...)
+ * <ul>
+ * <li><b>min_range</b> Required. One or more cells to determine the minimum value of, including numbers or names, ranges,
+ * or cell references that contain numbers. Blank and text values are ignored.</li>
+ * <li><b>criteria1_range</b> Required. The first range in which
+ * to evaluate the associated criteria.</li>
+ * <li><b>criteria1</b> Required. The criteria in the form of a number, expression,
+ * cell reference, or text that define which cells in the criteria_range1
+ * argument will be added</li>
+ * <li><b> criteria_range2, criteria2, ...</b> Optional. Additional ranges and their associated criteria.
+ * Up to 127 range/criteria pairs are allowed.
+ * </ul>
+ */
+public final class Minifs extends Baseifs {
+ /**
+ * Singleton
+ */
+ public static final FreeRefFunction instance = new Minifs();
+
+ /**
+ * https://support.microsoft.com/en-us/office/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599
+ * MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...
+ * need at least 3 arguments and need to have an odd number of arguments (min-range plus x*(criteria_range, criteria))
+ */
+ @Override
+ protected boolean hasInitialRange() {
+ return true;
+ }
+
+ @Override
+ protected Aggregator createAggregator() {
+ return new Aggregator() {
+ Double accumulator = null;
+
+ @Override
+ public void addValue(ValueEval value) {
+ double d = (value instanceof NumberEval) ? ((NumberEval) value).getNumberValue() : 0.0;
+ if(accumulator == null || accumulator > d) {
+ accumulator = d;
+ }
+ }
+
+ @Override
+ public ValueEval getResult() {
+ return new NumberEval(accumulator == null ? 0.0 : accumulator);
+ }
+ };
+ }
+}
Propchange: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Minifs.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sumifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sumifs.java?rev=1892366&r1=1892365&r2=1892366&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sumifs.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sumifs.java Mon Aug 16 11:29:33 2021
@@ -19,6 +19,9 @@
package org.apache.poi.ss.formula.functions;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
/**
* Implementation for the Excel function SUMIFS<p>
*
@@ -52,4 +55,21 @@ public final class Sumifs extends Baseif
protected boolean hasInitialRange() {
return true;
}
+
+ @Override
+ protected Aggregator createAggregator() {
+ return new Aggregator() {
+ double accumulator = 0.0;
+
+ @Override
+ public void addValue(ValueEval value) {
+ accumulator += (value instanceof NumberEval) ? ((NumberEval) value).getNumberValue() : 0.0;
+ }
+
+ @Override
+ public ValueEval getResult() {
+ return new NumberEval(accumulator);
+ }
+ };
+ }
}
Added: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageifs.java?rev=1892366&view=auto
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageifs.java (added)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageifs.java Mon Aug 16 11:29:33 2021
@@ -0,0 +1,108 @@
+/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.formula.functions;
+
+import static org.apache.poi.ss.util.Utils.*;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.NumericValueEval;
+import org.apache.poi.ss.formula.eval.StringEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.usermodel.FormulaError;
+import org.junit.jupiter.api.Test;
+
+import java.io.IOException;
+
+/**
+ * Test cases for AVERAGEIFS()
+ */
+final class TestAverageifs {
+
+ private static final OperationEvaluationContext EC = new OperationEvaluationContext(null, null, 0, 1, 0, null);
+
+ private static ValueEval invokeAverageifs(ValueEval[] args) {
+ return new Averageifs().evaluate(args, EC);
+ }
+
+ private static void confirmDouble(double expected, ValueEval actualEval) {
+ assertTrue(actualEval instanceof NumericValueEval, "Expected numeric result");
+ NumericValueEval nve = (NumericValueEval)actualEval;
+ assertEquals(expected, nve.getNumberValue(), 0);
+ }
+
+ private static void confirm(double expectedResult, ValueEval[] args) {
+ confirmDouble(expectedResult, invokeAverageifs(args));
+ }
+
+ private static void confirmError(ErrorEval errorEval, ValueEval[] args) {
+ ValueEval actualEval = invokeAverageifs(args);
+ assertEquals(errorEval, actualEval);
+ }
+
+ /**
+ * Example 1 from
+ * https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883
+ */
+ @Test
+ void testExample1() {
+ ValueEval[] b2b5 = new ValueEval[] {
+ new StringEval("Quiz"),
+ new StringEval("Grade"),
+ new NumberEval(75),
+ new NumberEval(94)
+ };
+
+ ValueEval[] args;
+ // "=AVERAGEIFS(B2:B5, B2:B5, ">70", B2:B5, "<90")"
+ args = new ValueEval[]{
+ EvalFactory.createAreaEval("B2:B5", b2b5),
+ EvalFactory.createAreaEval("B2:B5", b2b5),
+ new StringEval(">70"),
+ EvalFactory.createAreaEval("B2:B5", b2b5),
+ new StringEval("<90")
+ };
+ confirm(75.0, args);
+
+ ValueEval[] c2c5 = new ValueEval[] {
+ new StringEval("Quiz"),
+ new StringEval("Grade"),
+ new NumberEval(85),
+ new NumberEval(80)
+ };
+ // "=AVERAGEIFS(C2:C5, C2:C5, ">95")"
+ args = new ValueEval[]{
+ EvalFactory.createAreaEval("C2:C5", c2c5),
+ EvalFactory.createAreaEval("C2:C5", c2c5),
+ new StringEval(">95")
+ };
+ confirmError(ErrorEval.DIV_ZERO, args);
+
+ }
+
+}
Propchange: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageifs.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMaxifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMaxifs.java?rev=1892366&view=auto
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMaxifs.java (added)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMaxifs.java Mon Aug 16 11:29:33 2021
@@ -0,0 +1,129 @@
+/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.formula.functions;
+
+import static org.apache.poi.ss.util.Utils.*;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.NumericValueEval;
+import org.apache.poi.ss.formula.eval.StringEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.usermodel.FormulaError;
+import org.junit.jupiter.api.Test;
+
+import java.io.IOException;
+
+/**
+ * Test cases for MAXIFS()
+ */
+final class TestMaxifs {
+
+ private static final OperationEvaluationContext EC = new OperationEvaluationContext(null, null, 0, 1, 0, null);
+
+ private static ValueEval invokeMaxifs(ValueEval[] args) {
+ return new Maxifs().evaluate(args, EC);
+ }
+
+ private static void confirmDouble(double expected, ValueEval actualEval) {
+ assertTrue(actualEval instanceof NumericValueEval, "Expected numeric result");
+ NumericValueEval nve = (NumericValueEval)actualEval;
+ assertEquals(expected, nve.getNumberValue(), 0);
+ }
+
+ private static void confirm(double expectedResult, ValueEval[] args) {
+ confirmDouble(expectedResult, invokeMaxifs(args));
+ }
+
+ /**
+ * Example 1 from
+ * https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883
+ */
+ @Test
+ void testExample1() {
+ ValueEval[] a2a7 = new ValueEval[] {
+ new NumberEval(89),
+ new NumberEval(93),
+ new NumberEval(96),
+ new NumberEval(85),
+ new NumberEval(91),
+ new NumberEval(88)
+ };
+
+ ValueEval[] b2b7 = new ValueEval[] {
+ new NumberEval(1),
+ new NumberEval(2),
+ new NumberEval(2),
+ new NumberEval(3),
+ new NumberEval(1),
+ new NumberEval(1)
+ };
+
+ ValueEval[] args;
+ // "=MAXIFS(A2:A7,B2:B7,1)"
+ args = new ValueEval[]{
+ EvalFactory.createAreaEval("A2:A7", a2a7),
+ EvalFactory.createAreaEval("B2:B7", b2b7),
+ new NumberEval(1)
+ };
+ confirm(91.0, args);
+
+ }
+
+ /**
+ * Example 2 from
+ * https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883
+ */
+ @Test
+ void testExample2() {
+ ValueEval[] a2a5 = new ValueEval[] {
+ new NumberEval(10),
+ new NumberEval(11),
+ new NumberEval(100),
+ new NumberEval(111)
+ };
+
+ ValueEval[] b3b6 = new ValueEval[] {
+ new StringEval("a"),
+ new StringEval("a"),
+ new StringEval("b"),
+ new StringEval("a")
+ };
+
+ ValueEval[] args;
+
+ // "=MAXIFS(A2:A5,B3:B6,"a")"
+ args = new ValueEval[]{
+ EvalFactory.createAreaEval("A2:A5", a2a5),
+ EvalFactory.createAreaEval("B3:B6", b3b6),
+ new StringEval("a")
+ };
+ confirm(111.0, args); // the support article wrongly states 10.0
+ }
+
+}
Propchange: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMaxifs.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMinifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMinifs.java?rev=1892366&view=auto
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMinifs.java (added)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMinifs.java Mon Aug 16 11:29:33 2021
@@ -0,0 +1,129 @@
+/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.formula.functions;
+
+import static org.apache.poi.ss.util.Utils.*;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.NumericValueEval;
+import org.apache.poi.ss.formula.eval.StringEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.usermodel.FormulaError;
+import org.junit.jupiter.api.Test;
+
+import java.io.IOException;
+
+/**
+ * Test cases for MINIFS()
+ */
+final class TestMinifs {
+
+ private static final OperationEvaluationContext EC = new OperationEvaluationContext(null, null, 0, 1, 0, null);
+
+ private static ValueEval invokeMinifs(ValueEval[] args) {
+ return new Minifs().evaluate(args, EC);
+ }
+
+ private static void confirmDouble(double expected, ValueEval actualEval) {
+ assertTrue(actualEval instanceof NumericValueEval, "Expected numeric result");
+ NumericValueEval nve = (NumericValueEval)actualEval;
+ assertEquals(expected, nve.getNumberValue(), 0);
+ }
+
+ private static void confirm(double expectedResult, ValueEval[] args) {
+ confirmDouble(expectedResult, invokeMinifs(args));
+ }
+
+ /**
+ * Example 1 from
+ * https://support.microsoft.com/en-us/office/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599
+ */
+ @Test
+ void testExample1() {
+ ValueEval[] a2a7 = new ValueEval[] {
+ new NumberEval(89),
+ new NumberEval(93),
+ new NumberEval(96),
+ new NumberEval(85),
+ new NumberEval(91),
+ new NumberEval(88)
+ };
+
+ ValueEval[] b2b7 = new ValueEval[] {
+ new NumberEval(1),
+ new NumberEval(2),
+ new NumberEval(2),
+ new NumberEval(3),
+ new NumberEval(1),
+ new NumberEval(1)
+ };
+
+ ValueEval[] args;
+ // "=MINIFS(A2:A7,B2:B7,1)"
+ args = new ValueEval[]{
+ EvalFactory.createAreaEval("A2:A7", a2a7),
+ EvalFactory.createAreaEval("B2:B7", b2b7),
+ new NumberEval(1)
+ };
+ confirm(88.0, args);
+
+ }
+
+ /**
+ * Example 2 from
+ * https://support.microsoft.com/en-us/office/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599
+ */
+ @Test
+ void testExample2() {
+ ValueEval[] a2a5 = new ValueEval[] {
+ new NumberEval(10),
+ new NumberEval(11),
+ new NumberEval(100),
+ new NumberEval(111)
+ };
+
+ ValueEval[] b3b6 = new ValueEval[] {
+ new StringEval("a"),
+ new StringEval("a"),
+ new StringEval("b"),
+ new StringEval("a")
+ };
+
+ ValueEval[] args;
+
+ // "=MINIFS(A2:A5,B3:B6,"a")"
+ args = new ValueEval[]{
+ EvalFactory.createAreaEval("A2:A5", a2a5),
+ EvalFactory.createAreaEval("B3:B6", b3b6),
+ new StringEval("a")
+ };
+ confirm(10.0, args);
+ }
+
+}
Propchange: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestMinifs.java
------------------------------------------------------------------------------
svn:eol-style = native
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org