You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2014/01/23 17:44:00 UTC
svn commit: r1560736 - in /poi/trunk/src:
java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
java/org/apache/poi/ss/formula/functions/Countifs.java
testcases/org/apache/poi/ss/formula/functions/CountifsTests.java
Author: nick
Date: Thu Jan 23 16:43:59 2014
New Revision: 1560736
URL: http://svn.apache.org/r1560736
Log:
Patch from Detlef Brendle from bug #55873 - Support for COUNTIFS function
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countifs.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/CountifsTests.java
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1560736&r1=1560735&r2=1560736&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java Thu Jan 23 16:43:59 2014
@@ -164,6 +164,7 @@ public final class AnalysisToolPak imple
r(m, "YIELD", null);
r(m, "YIELDDISC", null);
r(m, "YIELDMAT", null);
+ r(m, "COUNTIFS", Countifs.instance);
return m;
}
Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countifs.java?rev=1560736&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countifs.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countifs.java Thu Jan 23 16:43:59 2014
@@ -0,0 +1,56 @@
+/* ====================================================================
+ 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.OperationEvaluationContext;
+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 function COUNTIFS
+ * <p>
+ * Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2])
+ * </p>
+ */
+
+public class Countifs implements FreeRefFunction {
+ public static final FreeRefFunction instance = new Countifs();
+
+ @Override
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+ Double result = null;
+ if (args.length == 0 || args.length % 2 > 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ for (int i = 0; i < args.length; ) {
+ ValueEval firstArg = args[i];
+ ValueEval secondArg = args[i + 1];
+ i += 2;
+ NumberEval evaluate = (NumberEval) new Countif().evaluate(new ValueEval[]{firstArg, secondArg}, ec.getRowIndex(), ec.getColumnIndex());
+ if (result == null) {
+ result = evaluate.getNumberValue();
+ } else if (evaluate.getNumberValue() < result) {
+ result = evaluate.getNumberValue();
+ }
+ }
+ return new NumberEval(result == null ? 0 : result);
+ }
+}
+
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/CountifsTests.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/CountifsTests.java?rev=1560736&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/CountifsTests.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/CountifsTests.java Thu Jan 23 16:43:59 2014
@@ -0,0 +1,66 @@
+/* ====================================================================
+ 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 junit.framework.TestCase;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.formula.atp.AnalysisToolPak;
+import org.apache.poi.ss.usermodel.*;
+
+public class CountifsTests extends TestCase {
+
+ public void testCallFunction() {
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ Sheet sheet = workbook.createSheet("test");
+ Row row1 = sheet.createRow(0);
+ Cell cellA1 = row1.createCell(0, Cell.CELL_TYPE_FORMULA);
+ Cell cellB1 = row1.createCell(1, Cell.CELL_TYPE_NUMERIC);
+ Cell cellC1 = row1.createCell(2, Cell.CELL_TYPE_NUMERIC);
+ Cell cellD1 = row1.createCell(3, Cell.CELL_TYPE_NUMERIC);
+ Cell cellE1 = row1.createCell(4, Cell.CELL_TYPE_NUMERIC);
+ cellB1.setCellValue(1);
+ cellC1.setCellValue(1);
+ cellD1.setCellValue(2);
+ cellE1.setCellValue(4);
+
+ cellA1.setCellFormula("COUNTIFS(B1:C1,1, D1:E1,2)");
+ FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
+ CellValue evaluate = evaluator.evaluate(cellA1);
+ assertEquals(1.0d, evaluate.getNumberValue());
+ }
+
+ public void testCallFunction_invalidArgs() {
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ Sheet sheet = workbook.createSheet("test");
+ Row row1 = sheet.createRow(0);
+ Cell cellA1 = row1.createCell(0, Cell.CELL_TYPE_FORMULA);
+ cellA1.setCellFormula("COUNTIFS()");
+ FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
+ CellValue evaluate = evaluator.evaluate(cellA1);
+ assertEquals(15, evaluate.getErrorValue());
+ cellA1.setCellFormula("COUNTIFS(A1:C1)");
+ evaluator = workbook.getCreationHelper().createFormulaEvaluator();
+ evaluate = evaluator.evaluate(cellA1);
+ assertEquals(15, evaluate.getErrorValue());
+ cellA1.setCellFormula("COUNTIFS(A1:C1,2,2)");
+ evaluator = workbook.getCreationHelper().createFormulaEvaluator();
+ evaluate = evaluator.evaluate(cellA1);
+ assertEquals(15, evaluate.getErrorValue());
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org