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 13:27:01 UTC
svn commit: r1901670 - 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: Sun Jun 5 13:27:01 2022
New Revision: 1901670
URL: http://svn.apache.org/viewvc?rev=1901670&view=rev
Log:
[bug-66105] support CORREL function
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java
- copied, changed from r1901612, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sqrtpi.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java (with props)
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java
- copied, changed from r1901612, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCeilingMath.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/MultiOperandNumericFunction.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sqrtpi.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=1901670&r1=1901669&r2=1901670&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 Sun Jun 5 13:27:01 2022
@@ -287,7 +287,7 @@ public final class FunctionEval {
retval[304] = new Sumx2my2();
retval[305] = new Sumx2py2();
// 306: CHITEST
- // 307: CORREL
+ retval[307] = Correl.instance;
// 308: COVAR
// 309: FORECAST
// 310: FTEST
Copied: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java (from r1901612, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sqrtpi.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java?p2=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java&p1=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sqrtpi.java&r1=1901612&r2=1901670&rev=1901670&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sqrtpi.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java Sun Jun 5 13:27:01 2022
@@ -16,55 +16,120 @@
==================================================================== */
package org.apache.poi.ss.formula.functions;
-import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.commons.math3.stat.correlation.PearsonsCorrelation;
+import org.apache.poi.ss.formula.ThreeDEval;
+import org.apache.poi.ss.formula.TwoDEval;
+import org.apache.poi.ss.formula.eval.BlankEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.NumericValueEval;
import org.apache.poi.ss.formula.eval.OperandResolver;
+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 org.apache.poi.ss.util.NumberToTextConverter;
/**
- * Implementation for Excel SQRTPI() function.
+ * Implementation for Excel CORREL() function.
* <p>
- * <b>Syntax</b>:<br> <b>SQRT </b>(<b>number</b>)<br>
+ * <b>Syntax</b>:<br> <b>CORREL </b>(<b>array1</b>, <b>array2</b>)<br>
* </p>
* <p>
- * Returns the square root of (number * pi).
+ * The CORREL function returns the correlation coefficient of two cell ranges.
+ * Use the correlation coefficient to determine the relationship between two properties.
+ * For example, you can examine the relationship between a location's average temperature and the use of air conditioners.
* </p>
* <p>
- * See https://support.microsoft.com/en-us/office/sqrtpi-function-1fb4e63f-9b51-46d6-ad68-b3e7a8b519b4
+ * See https://support.microsoft.com/en-us/office/correl-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92
* </p>
*/
-public class Sqrtpi implements FreeRefFunction {
+public class Correl extends Fixed2ArgFunction {
- public static final Sqrtpi instance = new Sqrtpi();
+ public static final Correl instance = new Correl();
@Override
- public ValueEval evaluate(final ValueEval[] args, final OperationEvaluationContext ec) {
- if (args.length != 1) {
- return ErrorEval.VALUE_INVALID;
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
+ try {
+ final PearsonsCorrelation pc = new PearsonsCorrelation();
+ final double correl = pc.correlation(
+ getNumberArray(arg0), getNumberArray(arg1));
+ return new NumberEval(correl);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
}
- return evaluate(ec.getRowIndex(), ec.getColumnIndex(), args[0]);
}
- private ValueEval evaluate(final int srcRowIndex, final int srcColumnIndex, final ValueEval arg0) {
- try {
- final ValueEval v1 = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
- final double d = OperandResolver.coerceValueToDouble(v1);
- if (isInvalidInput(d)) {
- return ErrorEval.NUM_ERROR;
+ private double[] getNumberArray(ValueEval operand) throws EvaluationException {
+ DoubleList retval = new DoubleList();
+ collectValues(operand, retval);
+ return retval.toArray();
+ }
+
+ private void collectValues(ValueEval operand, DoubleList temp) throws EvaluationException {
+ if (operand instanceof ThreeDEval) {
+ ThreeDEval ae = (ThreeDEval) operand;
+ for (int sIx = ae.getFirstSheetIndex(); sIx <= ae.getLastSheetIndex(); sIx++) {
+ int width = ae.getWidth();
+ int height = ae.getHeight();
+ for (int rrIx = 0; rrIx < height; rrIx++) {
+ for (int rcIx = 0; rcIx < width; rcIx++) {
+ ValueEval ve = ae.getValue(sIx, rrIx, rcIx);
+ collectValue(ve, temp);
+ }
+ }
}
- final double result = Math.sqrt(Math.PI * d);
- //NumberToTextConverter reduces the precision to what Excel uses internally
- //without this conversion, `result` is too precise
- return new NumberEval(Double.parseDouble(NumberToTextConverter.toText(result)));
- } catch (EvaluationException e) {
- return e.getErrorEval();
+ return;
+ }
+ if (operand instanceof TwoDEval) {
+ TwoDEval ae = (TwoDEval) operand;
+ int width = ae.getWidth();
+ int height = ae.getHeight();
+ for (int rrIx = 0; rrIx < height; rrIx++) {
+ for (int rcIx = 0; rcIx < width; rcIx++) {
+ ValueEval ve = ae.getValue(rrIx, rcIx);
+ collectValue(ve, temp);
+ }
+ }
+ return;
}
+ if (operand instanceof RefEval) {
+ RefEval re = (RefEval) operand;
+ for (int sIx = re.getFirstSheetIndex(); sIx <= re.getLastSheetIndex(); sIx++) {
+ collectValue(re.getInnerValueEval(sIx), temp);
+ }
+ return;
+ }
+ collectValue(operand, temp);
}
- private boolean isInvalidInput(double d) {
- return (d < 0);
+ private void collectValue(ValueEval ve, DoubleList temp) 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;
+ }
+ 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;
+ }
+ if (ve instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval) ve);
+ }
+ if (ve == BlankEval.instance) {
+ temp.add(0.0);
+ return;
+ }
+ throw new RuntimeException("Invalid ValueEval type passed for conversion: ("
+ + ve.getClass() + ")");
}
+
}
Added: 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=1901670&view=auto
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java (added)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java Sun Jun 5 13:27:01 2022
@@ -0,0 +1,32 @@
+package org.apache.poi.ss.formula.functions;
+
+import java.util.Arrays;
+
+final class DoubleList {
+ static final double[] EMPTY_DOUBLE_ARRAY = {};
+
+ private double[] _array;
+ private int _count;
+
+ public DoubleList() {
+ _array = new double[8];
+ _count = 0;
+ }
+
+ public double[] toArray() {
+ return _count < 1 ? EMPTY_DOUBLE_ARRAY : Arrays.copyOf(_array, _count);
+ }
+
+ private void ensureCapacity(int reqSize) {
+ if (reqSize > _array.length) {
+ int newSize = reqSize * 3 / 2; // grow with 50% extra
+ _array = Arrays.copyOf(_array, newSize);
+ }
+ }
+
+ public void add(double value) {
+ ensureCapacity(_count + 1);
+ _array[_count] = value;
+ _count++;
+ }
+}
Propchange: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DoubleList.java
------------------------------------------------------------------------------
svn:eol-style = native
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=1901670&r1=1901669&r2=1901670&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 Sun Jun 5 13:27:01 2022
@@ -17,8 +17,6 @@
package org.apache.poi.ss.formula.functions;
-import java.util.Arrays;
-
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.ThreeDEval;
import org.apache.poi.ss.formula.TwoDEval;
@@ -57,35 +55,6 @@ public abstract class MultiOperandNumeri
blankConsumer = ConsumerFactory.createForBlank(isBlankCounted ? Policy.COERCE : Policy.SKIP);
}
- static final double[] EMPTY_DOUBLE_ARRAY = {};
-
- private static class DoubleList {
- private double[] _array;
- private int _count;
-
- public DoubleList() {
- _array = new double[8];
- _count = 0;
- }
-
- public double[] toArray() {
- return _count < 1 ? EMPTY_DOUBLE_ARRAY : Arrays.copyOf(_array, _count);
- }
-
- private void ensureCapacity(int reqSize) {
- if (reqSize > _array.length) {
- int newSize = reqSize * 3 / 2; // grow with 50% extra
- _array = Arrays.copyOf(_array, newSize);
- }
- }
-
- public void add(double value) {
- ensureCapacity(_count + 1);
- _array[_count] = value;
- _count++;
- }
- }
-
private static final int DEFAULT_MAX_NUM_OPERANDS = SpreadsheetVersion.EXCEL2007.getMaxFunctionArgs();
public void setMissingArgPolicy(Policy policy) {
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sqrtpi.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sqrtpi.java?rev=1901670&r1=1901669&r2=1901670&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sqrtpi.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sqrtpi.java Sun Jun 5 13:27:01 2022
@@ -27,7 +27,7 @@ import org.apache.poi.ss.util.NumberToTe
/**
* Implementation for Excel SQRTPI() function.
* <p>
- * <b>Syntax</b>:<br> <b>SQRT </b>(<b>number</b>)<br>
+ * <b>Syntax</b>:<br> <b>SQRTPI </b>(<b>number</b>)<br>
* </p>
* <p>
* Returns the square root of (number * pi).
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java (from r1901612, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCeilingMath.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCeilingMath.java&r1=1901612&r2=1901670&rev=1901670&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCeilingMath.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java Sun Jun 5 13:27:01 2022
@@ -22,58 +22,39 @@ import org.apache.poi.hssf.usermodel.HSS
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
-import org.apache.poi.ss.usermodel.FormulaError;
import org.junit.jupiter.api.Test;
import java.io.IOException;
+import static org.apache.poi.ss.util.Utils.addRow;
import static org.apache.poi.ss.util.Utils.assertDouble;
-import static org.apache.poi.ss.util.Utils.assertError;
/**
- * Tests for {@link CeilingMath}
+ * Tests for {@link Correl}
*/
-final class TestCeilingMath {
+final class TestCorrel {
- //https://support.microsoft.com/en-us/office/ceiling-math-function-80f95d2f-b499-4eee-9f16-f795a8e306c8
+ //https://support.microsoft.com/en-us/office/correl-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92
@Test
- void testMicrosoftExamples() throws IOException {
- try (HSSFWorkbook wb = new HSSFWorkbook()) {
- HSSFSheet sheet = wb.createSheet();
- HSSFRow row = sheet.createRow(0);
- HSSFCell cell = row.createCell(0);
+ void testMicrosoftExample() throws IOException {
+ try (HSSFWorkbook wb = initWorkbook1()) {
+ HSSFSheet sheet = wb.getSheetAt(0);
+ HSSFRow row = sheet.getRow(0);
+ HSSFCell cell = row.createCell(100);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
- assertDouble(fe, cell, "CEILING.MATH(24.3,5)", 25.0, 0.00000000000001);
- assertDouble(fe, cell, "CEILING.MATH(6.7)", 7.0, 0.00000000000001);
- assertDouble(fe, cell, "CEILING.MATH(-8.1,2)", -8.0, 0.00000000000001);
- assertDouble(fe, cell, "CEILING.MATH(-5.5,2,-1)", -6.0, 0.00000000000001);
-
- assertDouble(fe, cell, "CEILING.MATH(2.5,-2)", 4.0, 0.00000000000001);
- assertDouble(fe, cell, "CEILING.MATH(-2.5,-2)", -2.0, 0.00000000000001);
- assertDouble(fe, cell, "CEILING.MATH(-2.5,-2,-1)", -4.0, 0.00000000000001);
- assertDouble(fe, cell, "CEILING.MATH(0.234, 0.01)", 0.24, 0.00000000000001);
+ assertDouble(fe, cell, "CORREL(A2:A6,B2:B6)", 0.997054486, 0.0000000005);
}
}
- @Test
- void testInvalid() throws IOException {
- try (HSSFWorkbook wb = new HSSFWorkbook()) {
- HSSFSheet sheet = wb.createSheet();
- HSSFRow row = sheet.createRow(0);
- HSSFCell cell = row.createCell(0);
- HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
- assertError(fe, cell, "CEILING.MATH()", FormulaError.VALUE);
- }
- }
-
- @Test
- void testNumError() throws IOException {
- try (HSSFWorkbook wb = new HSSFWorkbook()) {
- HSSFSheet sheet = wb.createSheet();
- HSSFRow row = sheet.createRow(0);
- HSSFCell cell = row.createCell(0);
- HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
- assertError(fe, cell, "CEILING.MATH(\"abc\")", FormulaError.VALUE);
- }
+ private HSSFWorkbook initWorkbook1() {
+ 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);
+ 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