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/28 13:53:31 UTC
svn commit: r1901351 - in /poi/trunk/poi/src: main/java/org/apache/poi/ss/formula/functions/ test/java/org/apache/poi/ss/formula/functions/
Author: fanningpj
Date: Sat May 28 13:53:30 2022
New Revision: 1901351
URL: http://svn.apache.org/viewvc?rev=1901351&view=rev
Log:
fix issue with AVERAGEA function and how it handles string cells
Modified:
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
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java
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=1901351&r1=1901350&r2=1901351&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 Sat May 28 13:53:30 2022
@@ -206,7 +206,7 @@ public abstract class AggregateFunction
}
@Override
- protected boolean handleLogicalValues() {
+ protected boolean treatStringsAsZero() {
return true;
}
}
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=1901351&r1=1901350&r2=1901351&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 Sat May 28 13:53:30 2022
@@ -98,12 +98,12 @@ public abstract class MultiOperandNumeri
/**
* 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.
+ * AVERAGEA treats booleans as 1.0 (true) and 0.0 (false). String cells are treated as 0.0
+ * (AVERAGE() ignores the cell altogether).
*
* @return whether to parse non-numeric cells
*/
- protected boolean handleLogicalValues() {
+ protected boolean treatStringsAsZero() {
return false;
}
@@ -180,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, !handleLogicalValues(), temp);
+ collectValue(ve, !treatStringsAsZero(), temp);
}
}
}
@@ -194,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, !handleLogicalValues(), temp);
+ collectValue(ve, !treatStringsAsZero(), temp);
}
}
return;
@@ -202,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), !handleLogicalValues(), temp);
+ collectValue(re.getInnerValueEval(sIx), !treatStringsAsZero(), temp);
}
return;
}
@@ -232,16 +232,16 @@ public abstract class MultiOperandNumeri
// ignore all ref strings
return;
}
- String s = ((StringValueEval) ve).getStringValue().trim();
- Double d = OperandResolver.parseDouble(s);
- if (d == null) {
- if (handleLogicalValues()) {
- temp.add(0.0);
- } else {
+ if (treatStringsAsZero()) {
+ temp.add(0.0);
+ } else {
+ String s = ((StringValueEval) ve).getStringValue().trim();
+ Double d = OperandResolver.parseDouble(s);
+ if (d == null) {
throw new EvaluationException(ErrorEval.VALUE_INVALID);
+ } else {
+ temp.add(d.doubleValue());
}
- } else {
- temp.add(d.doubleValue());
}
return;
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java?rev=1901351&r1=1901350&r2=1901351&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java Sat May 28 13:53:30 2022
@@ -55,6 +55,16 @@ public class TestAverageA {
}
}
+ @Test
+ void testStringsWithNums() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook3()) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(12);
+ assertDouble(fe, cell, "AVERAGEA(A2:A7)", 4.666666666666667, 0.00000000001);
+ assertDouble(fe, cell, "AVERAGE(A2:A7)", 7, 0.00000000001);
+ }
+ }
+
private HSSFWorkbook initWorkbook1() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
@@ -80,4 +90,17 @@ public class TestAverageA {
addRow(sheet, 6, false);
return wb;
}
+
+ private HSSFWorkbook initWorkbook3() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet();
+ addRow(sheet, 0, "Data");
+ addRow(sheet, 1, 10);
+ addRow(sheet, 2, 7);
+ addRow(sheet, 3, 9);
+ addRow(sheet, 4, 2);
+ addRow(sheet, 5, "4.5");
+ addRow(sheet, 6, "14");
+ return wb;
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org