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/26 10:25:32 UTC
svn commit: r1901277 - 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: Thu May 26 10:25:32 2022
New Revision: 1901277
URL: http://svn.apache.org/viewvc?rev=1901277&view=rev
Log:
add DPRODUCT function support
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DProduct.java
- copied, changed from r1901275, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVar.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java
- copied, changed from r1901274, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java
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=1901277&r1=1901276&r2=1901277&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 Thu May 26 10:25:32 2022
@@ -201,7 +201,7 @@ public final class FunctionEval {
retval[183] = AggregateFunction.PRODUCT;
retval[184] = NumericFunction.FACT;
- // 189: DPRODUCT
+ retval[189] = new DStarRunner(DStarRunner.DStarAlgorithmEnum.DPRODUCT);
retval[190] = LogicalFunction.ISNONTEXT;
retval[194] = AggregateFunction.VARP;
Copied: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DProduct.java (from r1901275, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVar.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DProduct.java?p2=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DProduct.java&p1=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVar.java&r1=1901275&r2=1901277&rev=1901277&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DVar.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DProduct.java Thu May 26 10:25:32 2022
@@ -26,28 +26,28 @@ import java.math.BigDecimal;
import java.util.ArrayList;
/**
- * Implementation of the DVar function:
- * Gets the variance value of a column in an area with given conditions.
+ * Implementation of the DProduct function:
+ * Gets the product value of a column in an area with given conditions.
*/
-public final class DVar implements IDStarAlgorithm {
- private final ArrayList<NumericValueEval> values = new ArrayList<>();
+public final class DProduct implements IDStarAlgorithm {
+ private double product;
+ private boolean initDone = false;
@Override
public boolean processMatch(ValueEval eval) {
if (eval instanceof NumericValueEval) {
- values.add((NumericValueEval) eval);
+ if (initDone) {
+ product *= ((NumericValueEval) eval).getNumberValue();
+ } else {
+ product = ((NumericValueEval) eval).getNumberValue();
+ initDone = true;
+ }
}
return true;
}
@Override
public ValueEval getResult() {
- final double[] array = new double[values.size()];
- int pos = 0;
- for (NumericValueEval d : values) {
- array[pos++] = d.getNumberValue();
- }
- final double var = StatsLib.var(array);
- return new NumberEval(new BigDecimal(NumberToTextConverter.toText(var)).doubleValue());
+ return new NumberEval(product);
}
}
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=1901277&r1=1901276&r2=1901277&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 Thu May 26 10:25:32 2022
@@ -65,6 +65,8 @@ public final class DStarRunner implement
DSTDEV(DStdev::new),
/** @see DVar */
DVAR(DVar::new),
+ /** @see DProduct */
+ DPRODUCT(DProduct::new),
;
private final Supplier<IDStarAlgorithm> implSupplier;
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java (from r1901274, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java&r1=1901274&r2=1901277&rev=1901277&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDVar.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDProduct.java Thu May 26 10:25:32 2022
@@ -29,33 +29,49 @@ import static org.apache.poi.ss.util.Uti
import static org.apache.poi.ss.util.Utils.assertDouble;
/**
- * Testcase for function DVAR()
+ * Testcase for function DPRODUCT()
*/
-public class TestDVar {
+public class TestDProduct {
- //https://support.microsoft.com/en-us/office/dvar-function-d6747ca9-99c7-48bb-996e-9d7af00f3ed1
+ //https://support.microsoft.com/en-us/office/dproduct-function-4f96b13e-d49c-47a7-b769-22f6d017cb31
@Test
void testMicrosoftExample1() throws IOException {
- try (HSSFWorkbook wb = initWorkbook1()) {
+ try (HSSFWorkbook wb = initWorkbook1(false)) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12);
- assertDouble(fe, cell, "DVAR(A4:E10, \"Yield\", A1:A3)", 8.8, 0.0000000001);
+ assertDouble(fe, cell, "DPRODUCT(A5:E11, \"Yield\", A1:F3)", 800, 0.0000000001);
}
}
- private HSSFWorkbook initWorkbook1() {
+ @Test
+ void testNoMatch() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook1(true)) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12);
+ assertDouble(fe, cell, "DPRODUCT(A5:E11, \"Yield\", A1:A2)", 0);
+ assertDouble(fe, cell, "DPRODUCT(A5:E11, \"Yield\", A1:A3)", 604800);
+ }
+ }
+
+ private HSSFWorkbook initWorkbook1(boolean noMatch) {
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");
- addRow(sheet, 3, "Tree", "Height", "Age", "Yield", "Profit");
- addRow(sheet, 4, "Apple", 18, 20, 14, 105);
- addRow(sheet, 5, "Pear", 12, 12, 10, 96);
- addRow(sheet, 6, "Cherry", 13, 14, 9, 105);
- addRow(sheet, 7, "Apple", 14, 15, 10, 75);
- addRow(sheet, 8, "Pear", 9, 8, 8, 77);
- addRow(sheet, 9, "Apple", 8, 9, 6, 45);
+ if (noMatch) {
+ addRow(sheet, 1, "=NoMatch");
+ addRow(sheet, 2);
+ } else {
+ 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, 15, 10, 75);
+ addRow(sheet, 9, "Pear", 9, 8, 8, 77);
+ addRow(sheet, 10, "Apple", 8, 9, 6, 45);
return wb;
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org