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/06/05 19:12:45 UTC
svn commit: r1901683 - in /poi/trunk/poi/src: main/java/org/apache/poi/ss/formula/functions/Correl.java main/java/org/apache/poi/ss/formula/functions/DoubleList.java test/java/org/apache/poi/ss/formula/functions/TestCorrel.java
Author: fanningpj
Date: Sun Jun 5 19:12:45 2022
New Revision: 1901683
URL: http://svn.apache.org/viewvc?rev=1901683&view=rev
Log:
support blank cells in correl function
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java?rev=1901683&r1=1901682&r2=1901683&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java Sun Jun 5 19:12:45 2022
@@ -29,6 +29,9 @@ import org.apache.poi.ss.formula.eval.Re
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.
* <p>
@@ -50,22 +53,34 @@ public class Correl extends Fixed2ArgFun
@Override
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
try {
+ final List<DoubleList> 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<DoubleList> 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 Fixed2ArgFun
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 Fixed2ArgFun
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() + ")");
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java?rev=1901683&r1=1901682&r2=1901683&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java Sun Jun 5 19:12:45 2022
@@ -46,4 +46,8 @@ final class DoubleList {
_array[_count] = value;
_count++;
}
+
+ public int getLength() {
+ return _count;
+ }
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java?rev=1901683&r1=1901682&r2=1901683&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java Sun Jun 5 19:12:45 2022
@@ -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;
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org