From 1b0fac992cf2f9128707d278f0e01999ab79b378 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sat, 28 May 2022 13:53:30 +0000 Subject: [PATCH] fix issue with AVERAGEA function and how it handles string cells git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1901351 13f79535-47bb-0310-9956-ffa450edef68 --- .../formula/functions/AggregateFunction.java | 2 +- .../MultiOperandNumericFunction.java | 28 +++++++++---------- .../ss/formula/functions/TestAverageA.java | 23 +++++++++++++++ 3 files changed, 38 insertions(+), 15 deletions(-) diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java index 41ba43cb6a..8b76ac161a 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/AggregateFunction.java @@ -206,7 +206,7 @@ public abstract class AggregateFunction extends MultiOperandNumericFunction { } @Override - protected boolean handleLogicalValues() { + protected boolean treatStringsAsZero() { return true; } } diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java index 23213caf7c..86a2741fff 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/MultiOperandNumericFunction.java @@ -98,12 +98,12 @@ public abstract class MultiOperandNumericFunction implements Function { /** * 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 MultiOperandNumericFunction implements Function { 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 MultiOperandNumericFunction implements Function { 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 MultiOperandNumericFunction implements Function { 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 MultiOperandNumericFunction implements Function { // 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; } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java index 75bf4718af..b78eb6ce7c 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAverageA.java @@ -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; + } } -- 2.39.5