From 3d0f69744c434aed7d180598ea77daa3b4d5a046 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sun, 5 Jun 2022 19:12:45 +0000 Subject: [PATCH] support blank cells in correl function git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1901683 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/functions/Correl.java | 78 +++++++++++++------ .../poi/ss/formula/functions/DoubleList.java | 4 + .../poi/ss/formula/functions/TestCorrel.java | 23 +++++- 3 files changed, 76 insertions(+), 29 deletions(-) diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java index 34c871bdb3..6ba00cd055 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java @@ -29,6 +29,9 @@ import org.apache.poi.ss.formula.eval.RefEval; import org.apache.poi.ss.formula.eval.StringValueEval; import org.apache.poi.ss.formula.eval.ValueEval; +import java.util.Arrays; +import java.util.List; + /** * Implementation for Excel CORREL() function. *

@@ -50,22 +53,34 @@ public class Correl extends Fixed2ArgFunction { @Override public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { try { + final List arrays = getNumberArrays(arg0, arg1); final PearsonsCorrelation pc = new PearsonsCorrelation(); final double correl = pc.correlation( - getNumberArray(arg0), getNumberArray(arg1)); + arrays.get(0).toArray(), arrays.get(1).toArray()); return new NumberEval(correl); } catch (EvaluationException e) { return e.getErrorEval(); } } - private double[] getNumberArray(ValueEval operand) throws EvaluationException { - DoubleList retval = new DoubleList(); - collectValues(operand, retval); - return retval.toArray(); + private List getNumberArrays(ValueEval operand0, ValueEval operand1) throws EvaluationException { + double[] retval0 = collectValuesWithBlanks(operand0).toArray(); + double[] retval1 = collectValuesWithBlanks(operand1).toArray(); + DoubleList filtered0 = new DoubleList(); + DoubleList filtered1 = new DoubleList(); + for (int i = 0; i < retval0.length; i++) { + if (Double.isNaN(retval0[i]) || Double.isNaN(retval1[i])) { + //ignore + } else { + filtered0.add(retval0[i]); + filtered1.add(retval1[i]); + } + } + return Arrays.asList(filtered0, filtered1); } - private void collectValues(ValueEval operand, DoubleList temp) throws EvaluationException { + private DoubleList collectValuesWithBlanks(ValueEval operand) throws EvaluationException { + DoubleList doubleList = new DoubleList(); if (operand instanceof ThreeDEval) { ThreeDEval ae = (ThreeDEval) operand; for (int sIx = ae.getFirstSheetIndex(); sIx <= ae.getLastSheetIndex(); sIx++) { @@ -74,11 +89,16 @@ public class Correl extends Fixed2ArgFunction { for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve = ae.getValue(sIx, rrIx, rcIx); - collectValue(ve, temp); + Double d = collectValue(ve); + if (d == null) { + doubleList.add(Double.NaN); + } else { + doubleList.add(d.doubleValue()); + } } } } - return; + return doubleList; } if (operand instanceof TwoDEval) { TwoDEval ae = (TwoDEval) operand; @@ -87,46 +107,54 @@ public class Correl extends Fixed2ArgFunction { for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve = ae.getValue(rrIx, rcIx); - collectValue(ve, temp); + Double d = collectValue(ve); + if (d == null) { + doubleList.add(Double.NaN); + } else { + doubleList.add(d.doubleValue()); + } } } - return; + return doubleList; } if (operand instanceof RefEval) { RefEval re = (RefEval) operand; for (int sIx = re.getFirstSheetIndex(); sIx <= re.getLastSheetIndex(); sIx++) { - collectValue(re.getInnerValueEval(sIx), temp); + Double d = collectValue(re.getInnerValueEval(sIx)); + if (d == null) { + doubleList.add(Double.NaN); + } else { + doubleList.add(d.doubleValue()); + } } - return; + return doubleList; + } + Double d = collectValue(operand); + if (d == null) { + doubleList.add(Double.NaN); + } else { + doubleList.add(d.doubleValue()); } - collectValue(operand, temp); + return doubleList; } - private void collectValue(ValueEval ve, DoubleList temp) throws EvaluationException { + private Double collectValue(ValueEval ve) throws EvaluationException { if (ve == null) { throw new IllegalArgumentException("ve must not be null"); } if (ve instanceof NumericValueEval) { NumericValueEval ne = (NumericValueEval) ve; - temp.add(ne.getNumberValue()); - return; + return ne.getNumberValue(); } if (ve instanceof StringValueEval) { 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()); - } - return; + return OperandResolver.parseDouble(s); } if (ve instanceof ErrorEval) { throw new EvaluationException((ErrorEval) ve); } if (ve == BlankEval.instance) { - temp.add(0.0); - return; + return null; } throw new RuntimeException("Invalid ValueEval type passed for conversion: (" + ve.getClass() + ")"); diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java index 2d124695ce..3278f10c05 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java @@ -46,4 +46,8 @@ final class DoubleList { _array[_count] = value; _count++; } + + public int getLength() { + return _count; + } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java index dc18477a39..b161dc9e76 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java @@ -36,8 +36,8 @@ final class TestCorrel { //https://support.microsoft.com/en-us/office/correl-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92 @Test - void testMicrosoftExample() throws IOException { - try (HSSFWorkbook wb = initWorkbook1()) { + void testMicrosoftExample1() throws IOException { + try (HSSFWorkbook wb = initWorkbook1(false)) { HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(0); HSSFCell cell = row.createCell(100); @@ -46,14 +46,29 @@ final class TestCorrel { } } - private HSSFWorkbook initWorkbook1() { + @Test + void testBlankValue() throws IOException { + try (HSSFWorkbook wb = initWorkbook1(true)) { + HSSFSheet sheet = wb.getSheetAt(0); + HSSFRow row = sheet.getRow(0); + HSSFCell cell = row.createCell(100); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + assertDouble(fe, cell, "CORREL(A2:A6,B2:B6)", 0.9984884738, 0.0000000005); + } + } + + private HSSFWorkbook initWorkbook1(boolean blankRow4) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); addRow(sheet, 0, "Data1", "Data2"); addRow(sheet, 1, 3, 9); addRow(sheet, 2, 2, 7); addRow(sheet, 3, 4, 12); - addRow(sheet, 4, 5, 15); + if (blankRow4) { + addRow(sheet, 4, 5); + } else { + addRow(sheet, 4, 5, 15); + } addRow(sheet, 5, 6, 17); return wb; } -- 2.39.5