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 22:23:48 UTC
svn commit: r1901299 - 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 22:23:48 2022
New Revision: 1901299
URL: http://svn.apache.org/viewvc?rev=1901299&view=rev
Log:
add AVERAGEA function
Added:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java
- copied, changed from r1901291, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestVar.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/AggregateFunction.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.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=1901299&r1=1901298&r2=1901299&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 22:23:48 2022
@@ -333,13 +333,13 @@ public final class FunctionEval {
// 358: GETPIVOTDATA
retval[359] = new Hyperlink();
// 360: PHONETIC
- // 361: AVERAGEA
+ retval[361] = AggregateFunction.AVERAGEA;
retval[362] = MinaMaxa.MAXA;
retval[363] = MinaMaxa.MINA;
// 364: STDEVPA
// 365: VARPA
- // 366: STDEVA
- // 367: VARA
+ retval[366] = AggregateFunction.STDEVA;
+ retval[367] = AggregateFunction.VARA;
for (int i = 0; i < retval.length; i++) {
Function f = retval[i];
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java?rev=1901299&r1=1901298&r2=1901299&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java Thu May 26 22:23:48 2022
@@ -187,6 +187,21 @@ public abstract class AggregateFunction
}
};
public static final Function AVERAGE = new AggregateFunction() {
+ @Override
+ protected double evaluate(double[] values) throws EvaluationException {
+ if (values.length < 1) {
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+ return MathX.average(values);
+ }
+ };
+ public static final Function AVERAGEA = new AggregateFunction() {
+ @Override
+ protected boolean handleLogicalValues() {
+ return true;
+ }
+
+ @Override
protected double evaluate(double[] values) throws EvaluationException {
if (values.length < 1) {
throw new EvaluationException(ErrorEval.DIV_ZERO);
@@ -236,6 +251,20 @@ public abstract class AggregateFunction
return StatsLib.stdevp(values);
}
};
+ public static final Function STDEVA = new AggregateFunction() {
+ @Override
+ protected boolean handleLogicalValues() {
+ return true;
+ }
+
+ @Override
+ protected double evaluate(double[] values) throws EvaluationException {
+ if (values.length < 1) {
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+ return StatsLib.stdev(values);
+ }
+ };
public static final Function SUM = new AggregateFunction() {
protected double evaluate(double[] values) {
return MathX.sum(values);
@@ -262,6 +291,20 @@ public abstract class AggregateFunction
return StatsLib.varp(values);
}
};
+ public static final Function VARA = new AggregateFunction() {
+ @Override
+ protected boolean handleLogicalValues() {
+ return true;
+ }
+
+ @Override
+ protected double evaluate(double[] values) throws EvaluationException {
+ if (values.length < 1) {
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+ return StatsLib.var(values);
+ }
+ };
public static final Function GEOMEAN = new Geomean();
private static class Product extends AggregateFunction {
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java?rev=1901299&r1=1901298&r2=1901299&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java Thu May 26 22:23:48 2022
@@ -96,6 +96,17 @@ public abstract class MultiOperandNumeri
blankConsumer = ConsumerFactory.createForBlank(policy);
}
+ /**
+ * Functions like AVERAGEA() differ from AVERAGE() in the way they handle non-numeric cells.
+ * AVERAGEA treats booleans as 1.0 (true) and 0.0 (false). For strings, they should be parsed as numbers.
+ * When the string is not a number, treat it as 0.0.
+ *
+ * @return whether to parse non-numeric cells
+ */
+ protected boolean handleLogicalValues() {
+ return false;
+ }
+
public final ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) {
try {
double[] values = getNumberArray(args);
@@ -169,7 +180,7 @@ public abstract class MultiOperandNumeri
ValueEval ve = ae.getValue(sIx, rrIx, rcIx);
if (!isSubtotalCounted() && ae.isSubTotal(rrIx, rcIx)) continue;
if (!isHiddenRowCounted() && ae.isRowHidden(rrIx)) continue;
- collectValue(ve, true, temp);
+ collectValue(ve, !handleLogicalValues(), temp);
}
}
}
@@ -183,7 +194,7 @@ public abstract class MultiOperandNumeri
for (int rcIx = 0; rcIx < width; rcIx++) {
ValueEval ve = ae.getValue(rrIx, rcIx);
if (!isSubtotalCounted() && ae.isSubTotal(rrIx, rcIx)) continue;
- collectValue(ve, true, temp);
+ collectValue(ve, !handleLogicalValues(), temp);
}
}
return;
@@ -191,7 +202,7 @@ public abstract class MultiOperandNumeri
if (operand instanceof RefEval) {
RefEval re = (RefEval) operand;
for (int sIx = re.getFirstSheetIndex(); sIx <= re.getLastSheetIndex(); sIx++) {
- collectValue(re.getInnerValueEval(sIx), true, temp);
+ collectValue(re.getInnerValueEval(sIx), !handleLogicalValues(), temp);
}
return;
}
@@ -221,12 +232,17 @@ public abstract class MultiOperandNumeri
// ignore all ref strings
return;
}
- String s = ((StringValueEval) ve).getStringValue();
+ String s = ((StringValueEval) ve).getStringValue().trim();
Double d = OperandResolver.parseDouble(s);
if (d == null) {
- throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ if (handleLogicalValues()) {
+ temp.add(0.0);
+ } else {
+ throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+ } else {
+ temp.add(d.doubleValue());
}
- temp.add(d.doubleValue());
return;
}
if (ve instanceof ErrorEval) {
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java (from r1901291, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestVar.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestVar.java&r1=1901291&r2=1901299&rev=1901299&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestVar.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java Thu May 26 22:23:48 2022
@@ -29,21 +29,19 @@ import static org.apache.poi.ss.util.Uti
import static org.apache.poi.ss.util.Utils.assertDouble;
/**
- * Testcase for functions: VAR.S(), VAR.P()
+ * Testcase for AVERAGEA() functions
*/
-public class TestVar {
+public class TestAverageA {
- //https://support.microsoft.com/en-us/office/var-s-function-913633de-136b-449d-813e-65a00b2b990b
- //https://support.microsoft.com/en-us/office/var-p-function-73d1285c-108c-4843-ba5d-a51f90656f3a
+ //https://support.microsoft.com/en-us/office/averagea-function-f5f84098-d453-4f4c-bbba-3d2c66356091
@Test
void testMicrosoftExample1() throws IOException {
try (HSSFWorkbook wb = initWorkbook1()) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12);
- assertDouble(fe, cell, "VARP(A3:A12)", 678.84, 0.00000000001);
- assertDouble(fe, cell, "VAR.P(A3:A12)", 678.84, 0.00000000001);
- assertDouble(fe, cell, "VAR(A3:A12)", 754.27, 0.005);
- assertDouble(fe, cell, "VAR.S(A3:A12)", 754.27, 0.005);
+ assertDouble(fe, cell, "AVERAGEA(A2:A6)", 5.6, 0.00000000001);
+ assertDouble(fe, cell, "AVERAGEA(A2:A5,A7)", 5.6, 0.00000000001);
+ assertDouble(fe, cell, "AVERAGE(A2:A6)", 7, 0.00000000001);
}
}
@@ -51,17 +49,12 @@ public class TestVar {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
addRow(sheet, 0, "Data");
- addRow(sheet, 1, "Strength");
- addRow(sheet, 2, 1345);
- addRow(sheet, 3, 1301);
- addRow(sheet, 4, 1368);
- addRow(sheet, 5, 1322);
- addRow(sheet, 6, 1310);
- addRow(sheet, 7, 1370);
- addRow(sheet, 8, 1318);
- addRow(sheet, 9, 1350);
- addRow(sheet, 10, 1303);
- addRow(sheet, 11, 1299);
+ addRow(sheet, 1, 10);
+ addRow(sheet, 2, 7);
+ addRow(sheet, 3, 9);
+ addRow(sheet, 4, 2);
+ addRow(sheet, 5, "Not available");
+ addRow(sheet, 6, "Formula");
return wb;
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org