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 2021/08/07 14:42:47 UTC
svn commit: r1892081 - in /poi/trunk/poi/src:
main/java/org/apache/poi/ss/formula/atp/
test/java/org/apache/poi/ss/formula/atp/
test/java/org/apache/poi/ss/formula/functions/
Author: fanningpj
Date: Sat Aug 7 14:42:47 2021
New Revision: 1892081
URL: http://svn.apache.org/viewvc?rev=1892081&view=rev
Log:
[bug-49202] add PERCENTRANK.INC function
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java
- copied, changed from r1892070, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankIncFunction.java
- copied, changed from r1892078, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.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=1892081&r1=1892080&r2=1892081&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 Sat Aug 7 14:42:47 2021
@@ -22,25 +22,7 @@ import org.apache.poi.ss.formula.eval.No
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.function.FunctionMetadata;
import org.apache.poi.ss.formula.function.FunctionMetadataRegistry;
-import org.apache.poi.ss.formula.functions.Bin2Dec;
-import org.apache.poi.ss.formula.functions.Complex;
-import org.apache.poi.ss.formula.functions.Countifs;
-import org.apache.poi.ss.formula.functions.Dec2Bin;
-import org.apache.poi.ss.formula.functions.Dec2Hex;
-import org.apache.poi.ss.formula.functions.Delta;
-import org.apache.poi.ss.formula.functions.EDate;
-import org.apache.poi.ss.formula.functions.EOMonth;
-import org.apache.poi.ss.formula.functions.FactDouble;
-import org.apache.poi.ss.formula.functions.FreeRefFunction;
-import org.apache.poi.ss.formula.functions.Hex2Dec;
-import org.apache.poi.ss.formula.functions.ImReal;
-import org.apache.poi.ss.formula.functions.Imaginary;
-import org.apache.poi.ss.formula.functions.Oct2Dec;
-import org.apache.poi.ss.formula.functions.Quotient;
-import org.apache.poi.ss.formula.functions.Single;
-import org.apache.poi.ss.formula.functions.Sumifs;
-import org.apache.poi.ss.formula.functions.TextFunction;
-import org.apache.poi.ss.formula.functions.WeekNum;
+import org.apache.poi.ss.formula.functions.*;
import org.apache.poi.ss.formula.udf.UDFFinder;
/**
@@ -173,6 +155,7 @@ public final class AnalysisToolPak imple
r(m, "ODDLPRICE", null);
r(m, "ODDLYIELD", null);
r(m, "PRICE", null);
+ r(m, "PERCENTRANK.INC", PercentRankIncFunction.instance);
r(m, "PRICEDISC", null);
r(m, "PRICEMAT", null);
r(m, "QUOTIENT", Quotient.instance);
Copied: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java (from r1892070, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java?p2=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java&p1=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java&r1=1892070&r2=1892081&rev=1892081&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/PercentRankIncFunction.java Sat Aug 7 14:42:47 2021
@@ -20,128 +20,36 @@ package org.apache.poi.ss.formula.atp;
import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.*;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
-
-import java.util.ArrayList;
-import java.util.Collections;
-import java.util.List;
+import org.apache.poi.ss.formula.functions.PercentRank;
/**
- * Implementation of Excel function TEXTJOIN()
+ * Implementation of 'Analysis Toolpak' the Excel function PERCENTRANK.INC()
*
- * <b>Syntax</b><br>
- * <b>TEXTJOIN</b>(<b>delimiter</b>, <b>ignore_empty</b>, <b>text1</b>, <b>[text2]<b>, â¦)<p>
+ * <b>Syntax</b>:<br>
+ * <b>PERCENTRANK.INC</b>(<b>array</b>, <b>X</b>, <b>[significance]</b>)<p>
*
- * <b>delimiter</b> A text string, either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string.
- * If a number is supplied, it will be treated as text.<br>
- * <b>ignore_empty</b> If TRUE, ignores empty cells.<br>
- * <b>text1</b> Text item to be joined. A text string, or array of strings, such as a range of cells.<br>
- * <b>text2 ...</b> Optional. Additional text items to be joined. There can be a maximum of 252 text arguments for the text items, including text1.
- * Each can be a text string, or array of strings, such as a range of cells.<br>
+ * <b>array</b> The array or range of data with numeric values that defines relative standing.<br>
+ * <b>X</b> The value for which you want to know the rank.<br>
+ * <b>significance</b> Optional. A value that identifies the number of significant digits for the returned percentage value.
+ * If omitted, PERCENTRANK.INC uses three digits (0.xxx).<br>
+ * <br>
+ * Returns a number between 0 and 1 representing a percentage.
*
+ * @see PercentRank
* @since POI 5.0.1
*/
-final class TextJoinFunction implements FreeRefFunction {
+final class PercentRankIncFunction implements FreeRefFunction {
- public static final FreeRefFunction instance = new TextJoinFunction(ArgumentsEvaluator.instance);
+ public static final FreeRefFunction instance = new PercentRankIncFunction(ArgumentsEvaluator.instance);
private ArgumentsEvaluator evaluator;
- private TextJoinFunction(ArgumentsEvaluator anEvaluator) {
+ private PercentRankIncFunction(ArgumentsEvaluator anEvaluator) {
// enforces singleton
this.evaluator = anEvaluator;
}
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
- /*
- * Must be at least three arguments:
- * - delimiter Delimiter for joining text arguments
- * - ignoreEmpty If true, empty strings will be ignored in the join
- * - text1 First value to be evaluated as text and joined
- * - text2, etc. Optional additional values to be evaluated and joined
- */
-
- // Make sure we have at least one text value, and at most 252 text values, as documented at:
- // https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c?ui=en-us&rs=en-us&ad=us
- if (args.length < 3 || args.length > 254) {
- return ErrorEval.VALUE_INVALID;
- }
-
- int srcRowIndex = ec.getRowIndex();
- int srcColumnIndex = ec.getColumnIndex();
-
- try {
- // Get the delimiter argument
- List<ValueEval> delimiterArgs = getValues(args[0], srcRowIndex, srcColumnIndex, true);
-
- // Get the boolean ignoreEmpty argument
- ValueEval ignoreEmptyArg = OperandResolver.getSingleValue(args[1], srcRowIndex, srcColumnIndex);
- boolean ignoreEmpty = OperandResolver.coerceValueToBoolean(ignoreEmptyArg, false);
-
- // Get a list of string values for each text argument
- ArrayList<String> textValues = new ArrayList<>();
-
- for (int i = 2; i < args.length; i++) {
- List<ValueEval> textArgs = getValues(args[i], srcRowIndex, srcColumnIndex, false);
- for (ValueEval textArg : textArgs) {
- String textValue = OperandResolver.coerceValueToString(textArg);
-
- // If we're not ignoring empty values or if our value is not empty, add it to the list
- if (!ignoreEmpty || (textValue != null && textValue.length() > 0)) {
- textValues.add(textValue);
- }
- }
- }
-
- // Join the list of values with the specified delimiter and return
- if (delimiterArgs.size() == 0) {
- return new StringEval(String.join("", textValues));
- } else if (delimiterArgs.size() == 1) {
- String delimiter = coerceValueToString(delimiterArgs.get(0));
- return new StringEval(String.join(delimiter, textValues));
- } else {
- //https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
- //see example 3 to see why this is needed
- List<String> delimiters = new ArrayList<>();
- for (ValueEval delimiterArg: delimiterArgs) {
- delimiters.add(coerceValueToString(delimiterArg));
- }
- StringBuilder sb = new StringBuilder();
- for (int i = 0; i < textValues.size(); i++) {
- if (i > 0) {
- int delimiterIndex = (i - 1) % delimiters.size();
- sb.append(delimiters.get(delimiterIndex));
- }
- sb.append(textValues.get(i));
- }
- return new StringEval(sb.toString());
- }
- } catch (EvaluationException e){
- return e.getErrorEval();
- }
- }
-
- private String coerceValueToString(ValueEval eval) {
- return (eval instanceof MissingArgEval) ? "" : OperandResolver.coerceValueToString(eval);
+ return PercentRank.instance.evaluate(args, ec.getRowIndex(), ec.getColumnIndex());
}
-
- //https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
- //in example 3, the delimiter is defined by a large area but only the last row of that area seems to be used
- //this is why lastRowOnly is supported
- private List<ValueEval> getValues(ValueEval eval, int srcRowIndex, int srcColumnIndex,
- boolean lastRowOnly) throws EvaluationException {
- if (eval instanceof AreaEval) {
- AreaEval ae = (AreaEval)eval;
- List<ValueEval> list = new ArrayList<>();
- int startRow = lastRowOnly ? ae.getLastRow() : ae.getFirstRow();
- for (int r = startRow; r <= ae.getLastRow(); r++) {
- for (int c = ae.getFirstColumn(); c <= ae.getLastColumn(); c++) {
- list.add(OperandResolver.getSingleValue(ae.getAbsoluteValue(r, c), r, c));
- }
- }
- return list;
- } else {
- return Collections.singletonList(OperandResolver.getSingleValue(eval, srcRowIndex, srcColumnIndex));
- }
- }
-
}
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankIncFunction.java (from r1892078, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankIncFunction.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankIncFunction.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java&r1=1892078&r2=1892081&rev=1892081&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestPercentRankIncFunction.java Sat Aug 7 14:42:47 2021
@@ -15,37 +15,39 @@
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
-package org.apache.poi.ss.formula.functions;
+package org.apache.poi.ss.formula.atp;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
-import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.usermodel.CellValue;
+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.junit.jupiter.api.Assertions.assertEquals;
/**
- * Testcase for function PERCENTRANK()
+ * Testcase for function PERCENTRANK.INC()
*/
-public class TestPercentRank {
+public class TestPercentRankIncFunction {
- //https://support.microsoft.com/en-us/office/percentrank-function-f1b5836c-9619-4847-9fc9-080ec9024442
+ //https://support.microsoft.com/en-us/office/percentrank-inc-function-149592c9-00c0-49ba-86c1-c1f45b80463a
@Test
void testMicrosoftExample1() throws IOException {
try (HSSFWorkbook wb = initWorkbook1()) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,2)", 0.333);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,4)", 0.555);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,8)", 0.666);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,8,2)", 0.66);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,8,4)", 0.6666);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,5)", 0.583);
+ assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,2)", 0.333);
+ assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,4)", 0.555);
+ assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,8)", 0.666);
+ assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,8,2)", 0.66);
+ assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,8,4)", 0.6666);
+ assertDouble(fe, cell, "PERCENTRANK.INC(A2:A11,5)", 0.583);
}
}
@@ -54,10 +56,10 @@ public class TestPercentRank {
try (HSSFWorkbook wb = initWorkbook1()) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
- confirmErrorResult(fe, cell, "PERCENTRANK(A2:A11,0)", FormulaError.NA);
- confirmErrorResult(fe, cell, "PERCENTRANK(A2:A11,100)", FormulaError.NA);
- confirmErrorResult(fe, cell, "PERCENTRANK(B2:B11,100)", FormulaError.NUM);
- confirmErrorResult(fe, cell, "PERCENTRANK(A2:A11,8,0)", FormulaError.NUM);
+ confirmErrorResult(fe, cell, "PERCENTRANK.INC(A2:A11,0)", FormulaError.NA);
+ confirmErrorResult(fe, cell, "PERCENTRANK.INC(A2:A11,100)", FormulaError.NA);
+ confirmErrorResult(fe, cell, "PERCENTRANK.INC(B2:B11,100)", FormulaError.NUM);
+ confirmErrorResult(fe, cell, "PERCENTRANK.INC(A2:A11,8,0)", FormulaError.NUM);
}
}
@@ -78,14 +80,6 @@ public class TestPercentRank {
return wb;
}
- private static void confirmNumericResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, double expectedResult) {
- cell.setCellFormula(formulaText);
- fe.notifyUpdateCell(cell);
- CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.NUMERIC);
- assertEquals(expectedResult, result.getNumberValue(), 0.0001);
- }
-
private static void confirmErrorResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, FormulaError expectedResult) {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java?rev=1892081&r1=1892080&r2=1892081&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestPercentRank.java Sat Aug 7 14:42:47 2021
@@ -27,6 +27,7 @@ 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.junit.jupiter.api.Assertions.assertEquals;
/**
@@ -40,12 +41,12 @@ public class TestPercentRank {
try (HSSFWorkbook wb = initWorkbook1()) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,2)", 0.333);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,4)", 0.555);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,8)", 0.666);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,8,2)", 0.66);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,8,4)", 0.6666);
- confirmNumericResult(fe, cell, "PERCENTRANK(A2:A11,5)", 0.583);
+ assertDouble(fe, cell, "PERCENTRANK(A2:A11,2)", 0.333);
+ assertDouble(fe, cell, "PERCENTRANK(A2:A11,4)", 0.555);
+ assertDouble(fe, cell, "PERCENTRANK(A2:A11,8)", 0.666);
+ assertDouble(fe, cell, "PERCENTRANK(A2:A11,8,2)", 0.66);
+ assertDouble(fe, cell, "PERCENTRANK(A2:A11,8,4)", 0.6666);
+ assertDouble(fe, cell, "PERCENTRANK(A2:A11,5)", 0.583);
}
}
@@ -78,14 +79,6 @@ public class TestPercentRank {
return wb;
}
- private static void confirmNumericResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, double expectedResult) {
- cell.setCellFormula(formulaText);
- fe.notifyUpdateCell(cell);
- CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.NUMERIC);
- assertEquals(expectedResult, result.getNumberValue(), 0.0001);
- }
-
private static void confirmErrorResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, FormulaError expectedResult) {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org