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/25 12:36:42 UTC
svn commit: r1901237 - in /poi/trunk/poi/src: main/java/org/apache/poi/ss/formula/eval/ main/java/org/apache/poi/ss/formula/functions/ test/java/org/apache/poi/ss/formula/functions/
Author: fanningpj
Date: Wed May 25 12:36:42 2022
New Revision: 1901237
URL: http://svn.apache.org/viewvc?rev=1901237&view=rev
Log:
add initial version of DCount and broken test
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java
- copied, changed from r1901195, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DGet.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java
- copied, changed from r1901195, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java (with props)
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DStarRunner.java
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java?rev=1901237&r1=1901236&r2=1901237&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java Wed May 25 12:36:42 2022
@@ -105,7 +105,7 @@ public final class FunctionEval {
retval[37] = BooleanFunction.OR;
retval[38] = BooleanFunction.NOT;
retval[39] = NumericFunction.MOD;
- // 40: DCOUNT
+ retval[40] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DCOUNT);
retval[41] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DSUM);
// 42: DAVERAGE
retval[43] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DMIN);
Copied: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java (from r1901195, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DGet.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java?p2=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java&p1=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DGet.java&r1=1901195&r2=1901237&rev=1901237&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DGet.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DCount.java Wed May 25 12:36:42 2022
@@ -17,58 +17,24 @@
package org.apache.poi.ss.formula.functions;
-import org.apache.poi.ss.formula.eval.BlankEval;
-import org.apache.poi.ss.formula.eval.ErrorEval;
-import org.apache.poi.ss.formula.eval.EvaluationException;
-import org.apache.poi.ss.formula.eval.OperandResolver;
+import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
/**
- * Implementation of the DGet function:
- * Finds the value of a column in an area with given conditions.
+ * Implementation of the DCount function:
+ * Counts the value of a column in an area with given conditions.
*/
-public final class DGet implements IDStarAlgorithm {
- private ValueEval result;
+public final class DCount implements IDStarAlgorithm {
+ private int count;
@Override
public boolean processMatch(ValueEval eval) {
- if(result == null) // First match, just set the value.
- {
- result = eval;
- }
- else // There was a previous match. Only one non-blank result is allowed. #NUM! on multiple values.
- {
- if(result instanceof BlankEval) {
- result = eval;
- }
- else {
- // We have a previous filled result.
- if(!(eval instanceof BlankEval)) {
- result = ErrorEval.NUM_ERROR;
- return false;
- }
- }
- }
-
+ count++;
return true;
}
@Override
public ValueEval getResult() {
- if(result == null) {
- return ErrorEval.VALUE_INVALID;
- } else if(result instanceof BlankEval) {
- return ErrorEval.VALUE_INVALID;
- } else
- try {
- if(OperandResolver.coerceValueToString(OperandResolver.getSingleValue(result, 0, 0)).isEmpty()) {
- return ErrorEval.VALUE_INVALID;
- }
- else {
- return result;
- }
- } catch (EvaluationException e) {
- return e.getErrorEval();
- }
+ return new NumberEval(count);
}
}
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=1901237&r1=1901236&r2=1901237&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 Wed May 25 12:36:42 2022
@@ -17,7 +17,6 @@
package org.apache.poi.ss.formula.functions;
-import java.util.Locale;
import java.util.function.Supplier;
import org.apache.poi.ss.formula.eval.AreaEval;
@@ -55,6 +54,8 @@ public final class DStarRunner implement
DMAX(DMax::new),
/** @see DSum */
DSUM(DSum::new),
+ /** @see DCount */
+ DCOUNT(DCount::new),
;
private final Supplier<IDStarAlgorithm> implSupplier;
@@ -122,7 +123,7 @@ public final class DStarRunner implement
for(int row = 1; row < height; ++row) {
boolean matches;
try {
- matches = fullfillsConditions(db, row, cdb);
+ matches = fulfillsConditions(db, row, cdb);
}
catch (EvaluationException e) {
return ErrorEval.VALUE_INVALID;
@@ -181,7 +182,7 @@ public final class DStarRunner implement
* @param name Column heading.
* @return Corresponding column number.
*/
- private static int getColumnForString(AreaEval db,String name) {
+ private static int getColumnForString(AreaEval db, String name) {
int resultColumn = -1;
final int width = db.getWidth();
for(int column = 0; column < width; ++column) {
@@ -211,7 +212,7 @@ public final class DStarRunner implement
* @throws EvaluationException If references could not be resolved or comparison
* operators and operands didn't match.
*/
- private static boolean fullfillsConditions(AreaEval db, int row, AreaEval cdb)
+ private static boolean fulfillsConditions(AreaEval db, int row, AreaEval cdb)
throws EvaluationException {
// Only one row must match to accept the input, so rows are ORed.
// Each row is made up of cells where each cell is a condition,
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java (from r1901195, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java&r1=1901195&r2=1901237&rev=1901237&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDCount.java Wed May 25 12:36:42 2022
@@ -21,70 +21,44 @@ import org.apache.poi.hssf.usermodel.HSS
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.usermodel.*;
+import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
import java.io.IOException;
import static org.apache.poi.ss.util.Utils.addRow;
import static org.apache.poi.ss.util.Utils.assertDouble;
-import static org.junit.jupiter.api.Assertions.assertEquals;
/**
- * Testcase for function PERCENTRANK()
+ * Testcase for function DCOUNT()
*/
-public class TestPercentRank {
+public class TestDCount {
- //https://support.microsoft.com/en-us/office/percentrank-function-f1b5836c-9619-4847-9fc9-080ec9024442
+ //https://support.microsoft.com/en-us/office/dcount-function-c1fc7b93-fb0d-4d8d-97db-8d5f076eaeb1
+ @Disabled
@Test
void testMicrosoftExample1() throws IOException {
try (HSSFWorkbook wb = initWorkbook1()) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
- assertDouble(fe, cell, "PERCENTRANK(A2:A11,2)", 0.333);
- assertDouble(fe, cell, "PERCENTRANK(A2:A11,4)", 0.555);
- assertDouble(fe, cell, "PERCENTRANK(A2:A11,8)", 0.666);
- assertDouble(fe, cell, "PERCENTRANK(A2:A11,8,2)", 0.66);
- assertDouble(fe, cell, "PERCENTRANK(A2:A11,8,4)", 0.6666);
- assertDouble(fe, cell, "PERCENTRANK(A2:A11,5)", 0.583);
- assertDouble(fe, cell, "PERCENTRANK(A2:A11,1)", 0);
- assertDouble(fe, cell, "PERCENTRANK(A2:A11,13)", 1);
- }
- }
-
- @Test
- void testErrorCases() throws IOException {
- try (HSSFWorkbook wb = initWorkbook1()) {
- HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
- HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
- confirmErrorResult(fe, cell, "PERCENTRANK(A2:A11,0)", FormulaError.NA);
- confirmErrorResult(fe, cell, "PERCENTRANK(A2:A11,100)", FormulaError.NA);
- confirmErrorResult(fe, cell, "PERCENTRANK(B2:B11,100)", FormulaError.NUM);
- confirmErrorResult(fe, cell, "PERCENTRANK(A2:A11,8,0)", FormulaError.NUM);
+ assertDouble(fe, cell, "DCOUNT(A5:E11, \"Age\", A1:F2)", 1);
}
}
private HSSFWorkbook initWorkbook1() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
- addRow(sheet, 0, "Data");
- addRow(sheet, 1, 13);
- addRow(sheet, 2, 12);
- addRow(sheet, 3, 11);
- addRow(sheet, 4, 8);
- addRow(sheet, 5, 4);
- addRow(sheet, 6, 3);
- addRow(sheet, 7, 2);
- addRow(sheet, 8, 1);
- addRow(sheet, 9, 1);
- addRow(sheet, 10, 1);
+ addRow(sheet, 0, "Tree", "Height", "Age", "Yield", "Profit", "Height");
+ addRow(sheet, 1, "=\"=Apple\"", ">10", null, null, null, "<16");
+ addRow(sheet, 2, "=\"=Pear\"");
+ addRow(sheet, 3);
+ addRow(sheet, 4, "Tree", "Height", "Age", "Yield", "Profit");
+ addRow(sheet, 5, "Apple", 18, 20, 14, 105);
+ addRow(sheet, 6, "Pear", 12, 12, 10, 96);
+ addRow(sheet, 7, "Cherry", 13, 14, 9, 105);
+ addRow(sheet, 8, "Apple", 14, "N/A", 10, 75);
+ addRow(sheet, 9, "Pear", 9, 8, 8, 77);
+ addRow(sheet, 10, "Apple", 12, 11, 6, 45);
return wb;
}
-
- private static void confirmErrorResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, FormulaError expectedResult) {
- cell.setCellFormula(formulaText);
- fe.notifyUpdateCell(cell);
- CellValue result = fe.evaluate(cell);
- assertEquals(expectedResult.getCode(), result.getErrorValue());
- }
}
Added: 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=1901237&view=auto
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java (added)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java Wed May 25 12:36:42 2022
@@ -0,0 +1,64 @@
+
+/* ====================================================================
+ 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.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.junit.jupiter.api.Disabled;
+import org.junit.jupiter.api.Test;
+
+import java.io.IOException;
+
+import static org.apache.poi.ss.util.Utils.addRow;
+import static org.apache.poi.ss.util.Utils.assertDouble;
+
+/**
+ * Testcase for function DGET()
+ */
+public class TestDGet {
+
+ //https://support.microsoft.com/en-us/office/dget-function-455568bf-4eef-45f7-90f0-ec250d00892e
+ @Disabled
+ @Test
+ void testMicrosoftExample1() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook1()) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+ assertDouble(fe, cell, "DGET(A5:E11, \"Yield\", A1:F3)", 10);
+ }
+ }
+
+ private HSSFWorkbook initWorkbook1() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet();
+ addRow(sheet, 0, "Tree", "Height", "Age", "Yield", "Profit", "Height");
+ addRow(sheet, 1, "=\"=Apple\"", ">10", null, null, null, "<16");
+ addRow(sheet, 2, "=\"=Pear\"", ">12");
+ addRow(sheet, 3);
+ addRow(sheet, 4, "Tree", "Height", "Age", "Yield", "Profit");
+ addRow(sheet, 5, "Apple", 18, 20, 14, 105);
+ addRow(sheet, 6, "Pear", 12, 12, 10, 96);
+ addRow(sheet, 7, "Cherry", 13, 14, 9, 105);
+ addRow(sheet, 8, "Apple", 14, "N/A", 10, 75);
+ addRow(sheet, 9, "Pear", 9, 8, 8, 77);
+ addRow(sheet, 10, "Apple", 12, 11, 6, 45);
+ return wb;
+ }
+}
Propchange: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDGet.java
------------------------------------------------------------------------------
svn:eol-style = native
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org