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 21:46:00 UTC
svn commit: r1901689 - in /poi/trunk/poi/src: main/java/org/apache/poi/ss/formula/atp/ 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 21:45:59 2022
New Revision: 1901689
URL: http://svn.apache.org/viewvc?rev=1901689&view=rev
Log:
add FORECAST and FORECAST.LINEAR functions
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/ArrayFunctionUtils.java
- copied, changed from r1901688, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/TwoArrayFunction.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Forecast.java
- copied, changed from r1901687, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestForecast.java
- copied, changed from r1901688, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java
Removed:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/TwoArrayFunction.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
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/Correl.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Covar.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/atp/AnalysisToolPak.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1901689&r1=1901688&r2=1901689&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java Sun Jun 5 21:45:59 2022
@@ -118,6 +118,7 @@ public final class AnalysisToolPak imple
r(m, "FACTDOUBLE", FactDouble.instance);
r(m, "FLOOR.MATH", FloorMath.instance);
r(m, "FLOOR.PRECISE", FloorPrecise.instance);
+ r(m, "FORECAST.LINEAR", Forecast.instance);
r(m, "FVSCHEDULE", null);
r(m, "GCD", Gcd.instance);
r(m, "GESTEP", null);
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=1901689&r1=1901688&r2=1901689&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 21:45:59 2022
@@ -289,7 +289,7 @@ public final class FunctionEval {
// 306: CHITEST
retval[307] = Correl.instance;
retval[308] = Covar.instanceP;
- // 309: FORECAST
+ retval[309] = Forecast.instance;
// 310: FTEST
retval[311] = new Intercept();
retval[312] = Correl.instance;
Copied: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/ArrayFunctionUtils.java (from r1901688, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/TwoArrayFunction.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/ArrayFunctionUtils.java?p2=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/ArrayFunctionUtils.java&p1=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/TwoArrayFunction.java&r1=1901688&r2=1901689&rev=1901689&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/TwoArrayFunction.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/ArrayFunctionUtils.java Sun Jun 5 21:45:59 2022
@@ -30,9 +30,9 @@ import org.apache.poi.ss.formula.eval.Va
import java.util.Arrays;
import java.util.List;
-abstract class TwoArrayFunction extends Fixed2ArgFunction {
+final class ArrayFunctionUtils {
- protected List<DoubleList> getNumberArrays(ValueEval operand0, ValueEval operand1) throws EvaluationException {
+ static List<DoubleList> getNumberArrays(ValueEval operand0, ValueEval operand1) throws EvaluationException {
double[] retval0 = collectValuesWithBlanks(operand0).toArray();
double[] retval1 = collectValuesWithBlanks(operand1).toArray();
if (retval0.length != retval1.length) {
@@ -52,7 +52,7 @@ abstract class TwoArrayFunction extends
return Arrays.asList(filtered0, filtered1);
}
- private DoubleList collectValuesWithBlanks(ValueEval operand) throws EvaluationException {
+ private static DoubleList collectValuesWithBlanks(ValueEval operand) throws EvaluationException {
DoubleList doubleList = new DoubleList();
if (operand instanceof ThreeDEval) {
ThreeDEval ae = (ThreeDEval) operand;
@@ -111,7 +111,7 @@ abstract class TwoArrayFunction extends
return doubleList;
}
- private Double collectValue(ValueEval ve) throws EvaluationException {
+ private static Double collectValue(ValueEval ve) throws EvaluationException {
if (ve == null) {
throw new IllegalArgumentException("ve must not be null");
}
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=1901689&r1=1901688&r2=1901689&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 21:45:59 2022
@@ -24,6 +24,8 @@ import org.apache.poi.ss.formula.eval.Va
import java.util.List;
+import static org.apache.poi.ss.formula.functions.ArrayFunctionUtils.getNumberArrays;
+
/**
* Implementation for Excel CORREL() function.
* <p>
@@ -38,7 +40,7 @@ import java.util.List;
* See https://support.microsoft.com/en-us/office/correl-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92
* </p>
*/
-public class Correl extends TwoArrayFunction {
+public class Correl extends Fixed2ArgFunction {
public static final Correl instance = new Correl();
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Covar.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Covar.java?rev=1901689&r1=1901688&r2=1901689&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Covar.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Covar.java Sun Jun 5 21:45:59 2022
@@ -26,6 +26,8 @@ import org.apache.poi.ss.formula.eval.Va
import java.util.List;
+import static org.apache.poi.ss.formula.functions.ArrayFunctionUtils.getNumberArrays;
+
/**
* Implementation for Excel COVAR() and COVARIANCE.P() functions.
* <p>
@@ -35,7 +37,7 @@ import java.util.List;
* @see <a href="https://support.microsoft.com/en-us/office/covariance-p-function-6f0e1e6d-956d-4e4b-9943-cfef0bf9edfc">COVARIANCE.P</a>
* </p>
*/
-public class Covar extends TwoArrayFunction implements FreeRefFunction {
+public class Covar extends Fixed2ArgFunction implements FreeRefFunction {
public static final Covar instanceP = new Covar(false);
public static final Covar instanceS = new Covar(true);
Copied: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Forecast.java (from r1901687, 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/Forecast.java?p2=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Forecast.java&p1=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Correl.java&r1=1901687&r2=1901689&rev=1901689&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/Forecast.java Sun Jun 5 21:45:59 2022
@@ -16,46 +16,78 @@
==================================================================== */
package org.apache.poi.ss.formula.functions;
-import org.apache.commons.math3.stat.correlation.PearsonsCorrelation;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
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.OperandResolver;
import org.apache.poi.ss.formula.eval.ValueEval;
import java.util.List;
+import static org.apache.poi.ss.formula.functions.ArrayFunctionUtils.getNumberArrays;
+
/**
- * Implementation for Excel CORREL() function.
- * <p>
- * <b>Syntax</b>:<br> <b>CORREL </b>(<b>array1</b>, <b>array2</b>)<br>
- * </p>
+ * Implementation for Excel FORECAST() and FORECAST.LINEAR() functions.
* <p>
- * 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.
+ * <b>Syntax</b>:<br> <b>FORECAST </b>(<b>number</b>, <b>array1</b>, <b>array2</b>)<br>
* </p>
* <p>
- * See https://support.microsoft.com/en-us/office/correl-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92
+ * See https://support.microsoft.com/en-us/office/forecast-and-forecast-linear-functions-50ca49c9-7b40-4892-94e4-7ad38bbeda99
* </p>
*/
-public class Correl extends TwoArrayFunction {
+public class Forecast extends Fixed3ArgFunction implements FreeRefFunction {
- public static final Correl instance = new Correl();
+ public static final Forecast instance = new Forecast();
- private Correl() {}
+ private Forecast() {}
@Override
- public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2) {
try {
- final List<DoubleList> arrays = getNumberArrays(arg0, arg1);
- final PearsonsCorrelation pc = new PearsonsCorrelation();
- final double correl = pc.correlation(
- arrays.get(0).toArray(), arrays.get(1).toArray());
- return new NumberEval(correl);
+ final Double x = evaluateValue(arg0, srcRowIndex, srcColumnIndex);
+ if (x == null || x.isNaN() || x.isInfinite()) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ final List<DoubleList> arrays = getNumberArrays(arg1, arg2);
+ final double[] arrY = arrays.get(0).toArray();
+ final double[] arrX = arrays.get(1).toArray();
+ final double averageY = MathX.average(arrY);
+ final double averageX = MathX.average(arrX);
+ double bnum = 0;
+ double bdem = 0;
+ final int len = arrY.length;
+ for (int i = 0; i < len; i++) {
+ double diff0 = arrX[i] - averageX;
+ bnum += diff0 * (arrY[i] - averageY);
+ bdem += Math.pow(diff0, 2);
+ }
+ if (bdem == 0) {
+ return ErrorEval.DIV_ZERO;
+ }
+
+ final double b = bnum / bdem;
+ final double a = averageY - (b * averageX);
+ final double res = a + (b * x);
+ return new NumberEval(res);
} catch (EvaluationException e) {
return e.getErrorEval();
} catch (Exception e) {
return ErrorEval.NA;
}
}
+
+ @Override
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+ if (args.length != 3) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ return evaluate(ec.getRowIndex(), ec.getColumnIndex(), args[0], args[1], args[2]);
+ }
+
+ private static Double evaluateValue(ValueEval arg, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
+ ValueEval veText = OperandResolver.getSingleValue(arg, srcRowIndex, srcColumnIndex);
+ String strText1 = OperandResolver.coerceValueToString(veText);
+ return OperandResolver.parseDouble(strText1);
+ }
}
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=1901689&r1=1901688&r2=1901689&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 21:45:59 2022
@@ -60,6 +60,7 @@ final class TestCorrel {
assertDouble(fe, cell, "PEARSON(A2:A6,B2:B6)", 0.699379, 0.0000005);
}
}
+
@Test
void testBlankValue() throws IOException {
try (HSSFWorkbook wb = initWorkbook1(null)) {
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestForecast.java (from r1901688, 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/TestForecast.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestForecast.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCorrel.java&r1=1901688&r2=1901689&rev=1901689&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/TestForecast.java Sun Jun 5 21:45:59 2022
@@ -32,11 +32,11 @@ import static org.apache.poi.ss.util.Uti
import static org.apache.poi.ss.util.Utils.assertError;
/**
- * Tests for {@link Correl}
+ * Tests for {@link Forecast}
*/
-final class TestCorrel {
+final class TestForecast {
- //https://support.microsoft.com/en-us/office/correl-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92
+ //https://support.microsoft.com/en-us/office/forecast-and-forecast-linear-functions-50ca49c9-7b40-4892-94e4-7ad38bbeda99
@Test
void testMicrosoftExample1() throws IOException {
try (HSSFWorkbook wb = initWorkbook1()) {
@@ -44,81 +44,20 @@ final class TestCorrel {
HSSFRow row = sheet.getRow(0);
HSSFCell cell = row.createCell(100);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
- assertDouble(fe, cell, "CORREL(A2:A6,B2:B6)", 0.997054486, 0.0000000005);
- }
- }
-
- //https://support.microsoft.com/en-us/office/pearson-function-0c3e30fc-e5af-49c4-808a-3ef66e034c18
- @Test
- void testPearsonExample1() throws IOException {
- try (HSSFWorkbook wb = initWorkbook2()) {
- 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.699379, 0.0000005);
- assertDouble(fe, cell, "PEARSON(A2:A6,B2:B6)", 0.699379, 0.0000005);
- }
- }
- @Test
- void testBlankValue() throws IOException {
- try (HSSFWorkbook wb = initWorkbook1(null)) {
- 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);
- }
- }
-
- @Test
- void testStringValue() throws IOException {
- try (HSSFWorkbook wb = initWorkbook1("string")) {
- 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);
- }
- }
-
- @Test
- void testMismatch() 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);
- assertError(fe, cell, "CORREL(A2:A6,B2:B5)", FormulaError.NA);
- assertError(fe, cell, "CORREL(A2:B6,B2:B6)", FormulaError.NA);
+ assertDouble(fe, cell, "FORECAST(30,A2:A6,B2:B6)", 10.607253, 0.0000001);
+ assertDouble(fe, cell, "FORECAST.LINEAR(30,A2:A6,B2:B6)", 10.607253, 0.0000001);
}
}
private HSSFWorkbook initWorkbook1() {
- return initWorkbook1(Double.valueOf(15));
- }
-
- private HSSFWorkbook initWorkbook1(Object row4Data2) {
- 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, row4Data2);
- addRow(sheet, 5, 6, 17);
- return wb;
- }
-
- private HSSFWorkbook initWorkbook2() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
- addRow(sheet, 0, "Independent values", "Dependent values");
- addRow(sheet, 1, 9, 10);
- addRow(sheet, 2, 7, 6);
- addRow(sheet, 3, 5, 1);
- addRow(sheet, 4, 3, 5);
- addRow(sheet, 5, 1, 3);
+ addRow(sheet, 0, "Known X", "Known Y");
+ addRow(sheet, 1, 6, 20);
+ addRow(sheet, 2, 7, 28);
+ addRow(sheet, 3, 9, 31);
+ addRow(sheet, 4, 15, 38);
+ addRow(sheet, 5, 21, 40);
return wb;
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org