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/05/22 20:56:49 UTC
svn commit: r1890120 [40/43] - in /poi/trunk/poi/src:
main/java/org/apache/poi/ main/java/org/apache/poi/ddf/
main/java/org/apache/poi/extractor/ main/java/org/apache/poi/hpsf/
main/java/org/apache/poi/hssf/ main/java/org/apache/poi/hssf/dev/
main/java...
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCountFuncs.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCountFuncs.java?rev=1890120&r1=1890119&r2=1890120&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCountFuncs.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCountFuncs.java Sat May 22 20:56:44 2021
@@ -48,159 +48,159 @@ import org.junit.jupiter.api.Test;
*/
final class TestCountFuncs {
- private static final String NULL = null;
+ private static final String NULL = null;
- @Test
- void testCountBlank() {
- AreaEval range;
- ValueEval[] values;
-
- values = new ValueEval[] {
- new NumberEval(0),
- new StringEval(""), // note - does match blank
- BoolEval.TRUE,
- BoolEval.FALSE,
- ErrorEval.DIV_ZERO,
- BlankEval.instance,
- };
- range = EvalFactory.createAreaEval("A1:B3", values);
- confirmCountBlank(2, range);
-
- values = new ValueEval[] {
- new NumberEval(0),
- new StringEval(""), // does match blank
- BlankEval.instance,
- BoolEval.FALSE,
- BoolEval.TRUE,
- BlankEval.instance,
- };
- range = EvalFactory.createAreaEval("A1:B3", values);
- confirmCountBlank(3, range);
- }
-
- @Test
- void testCountA() {
- ValueEval[] args;
-
- args = new ValueEval[] {
- new NumberEval(0),
- };
- confirmCountA(1, args);
-
- args = new ValueEval[] {
- new NumberEval(0),
- new NumberEval(0),
- new StringEval(""),
- };
- confirmCountA(3, args);
-
- args = new ValueEval[] {
- EvalFactory.createAreaEval("D2:F5", new ValueEval[12]),
- };
- confirmCountA(12, args);
-
- args = new ValueEval[] {
- EvalFactory.createAreaEval("D1:F5", new ValueEval[15]),
- EvalFactory.createRefEval("A1"),
- EvalFactory.createAreaEval("A1:G6", new ValueEval[42]),
- new NumberEval(0),
- };
- confirmCountA(59, args);
- }
-
- @Test
- void testCountIf() {
- AreaEval range;
- ValueEval[] values;
-
- // when criteria is a boolean value
- values = new ValueEval[] {
- new NumberEval(0),
- new StringEval("TRUE"), // note - does not match boolean TRUE
- BoolEval.TRUE,
- BoolEval.FALSE,
- BoolEval.TRUE,
- BlankEval.instance,
- };
- range = EvalFactory.createAreaEval("A1:B3", values);
- confirmCountIf(2, range, BoolEval.TRUE);
-
- // when criteria is numeric
- values = new ValueEval[] {
- new NumberEval(0),
- new StringEval("2"),
- new StringEval("2.001"),
- new NumberEval(2),
- new NumberEval(2),
- BoolEval.TRUE,
- };
- range = EvalFactory.createAreaEval("A1:B3", values);
- confirmCountIf(3, range, new NumberEval(2));
- // note - same results when criteria is a string that parses as the number with the same value
- confirmCountIf(3, range, new StringEval("2.00"));
-
- // when criteria is an expression (starting with a comparison operator)
- confirmCountIf(2, range, new StringEval(">1"));
- // when criteria is an expression (starting with a comparison operator)
- confirmCountIf(2, range, new StringEval(">0.5"));
- }
-
- @Test
- void testCriteriaPredicateNe_Bug46647() {
- I_MatchPredicate mp = Countif.createCriteriaPredicate(new StringEval("<>aa"), 0, 0);
- assertNotNull(mp);
- StringEval seA = new StringEval("aa"); // this should not match the criteria '<>aa'
- StringEval seB = new StringEval("bb"); // this should match
- assertFalse(mp.matches(seA) && !mp.matches(seB), "Identified bug 46647");
- assertFalse(mp.matches(seA));
- assertTrue(mp.matches(seB));
-
- // general tests for not-equal (<>) operator
- AreaEval range;
- ValueEval[] values;
-
- values = new ValueEval[] {
- new StringEval("aa"),
- new StringEval("def"),
- new StringEval("aa"),
- new StringEval("ghi"),
- new StringEval("aa"),
- new StringEval("aa"),
- };
-
- range = EvalFactory.createAreaEval("A1:A6", values);
- confirmCountIf(2, range, new StringEval("<>aa"));
-
- values = new ValueEval[] {
- new StringEval("ab"),
- new StringEval("aabb"),
- new StringEval("aa"), // match
- new StringEval("abb"),
- new StringEval("aab"),
- new StringEval("ba"), // match
- };
-
- range = EvalFactory.createAreaEval("A1:A6", values);
- confirmCountIf(2, range, new StringEval("<>a*b"));
-
-
- values = new ValueEval[] {
- new NumberEval(222),
- new NumberEval(222),
- new NumberEval(111),
- new StringEval("aa"),
- new StringEval("111"),
- };
-
- range = EvalFactory.createAreaEval("A1:A5", values);
- confirmCountIf(4, range, new StringEval("<>111"));
- }
+ @Test
+ void testCountBlank() {
+ AreaEval range;
+ ValueEval[] values;
+
+ values = new ValueEval[] {
+ new NumberEval(0),
+ new StringEval(""), // note - does match blank
+ BoolEval.TRUE,
+ BoolEval.FALSE,
+ ErrorEval.DIV_ZERO,
+ BlankEval.instance,
+ };
+ range = EvalFactory.createAreaEval("A1:B3", values);
+ confirmCountBlank(2, range);
+
+ values = new ValueEval[] {
+ new NumberEval(0),
+ new StringEval(""), // does match blank
+ BlankEval.instance,
+ BoolEval.FALSE,
+ BoolEval.TRUE,
+ BlankEval.instance,
+ };
+ range = EvalFactory.createAreaEval("A1:B3", values);
+ confirmCountBlank(3, range);
+ }
+
+ @Test
+ void testCountA() {
+ ValueEval[] args;
+
+ args = new ValueEval[] {
+ new NumberEval(0),
+ };
+ confirmCountA(1, args);
+
+ args = new ValueEval[] {
+ new NumberEval(0),
+ new NumberEval(0),
+ new StringEval(""),
+ };
+ confirmCountA(3, args);
+
+ args = new ValueEval[] {
+ EvalFactory.createAreaEval("D2:F5", new ValueEval[12]),
+ };
+ confirmCountA(12, args);
+
+ args = new ValueEval[] {
+ EvalFactory.createAreaEval("D1:F5", new ValueEval[15]),
+ EvalFactory.createRefEval("A1"),
+ EvalFactory.createAreaEval("A1:G6", new ValueEval[42]),
+ new NumberEval(0),
+ };
+ confirmCountA(59, args);
+ }
+
+ @Test
+ void testCountIf() {
+ AreaEval range;
+ ValueEval[] values;
+
+ // when criteria is a boolean value
+ values = new ValueEval[] {
+ new NumberEval(0),
+ new StringEval("TRUE"), // note - does not match boolean TRUE
+ BoolEval.TRUE,
+ BoolEval.FALSE,
+ BoolEval.TRUE,
+ BlankEval.instance,
+ };
+ range = EvalFactory.createAreaEval("A1:B3", values);
+ confirmCountIf(2, range, BoolEval.TRUE);
+
+ // when criteria is numeric
+ values = new ValueEval[] {
+ new NumberEval(0),
+ new StringEval("2"),
+ new StringEval("2.001"),
+ new NumberEval(2),
+ new NumberEval(2),
+ BoolEval.TRUE,
+ };
+ range = EvalFactory.createAreaEval("A1:B3", values);
+ confirmCountIf(3, range, new NumberEval(2));
+ // note - same results when criteria is a string that parses as the number with the same value
+ confirmCountIf(3, range, new StringEval("2.00"));
+
+ // when criteria is an expression (starting with a comparison operator)
+ confirmCountIf(2, range, new StringEval(">1"));
+ // when criteria is an expression (starting with a comparison operator)
+ confirmCountIf(2, range, new StringEval(">0.5"));
+ }
+
+ @Test
+ void testCriteriaPredicateNe_Bug46647() {
+ I_MatchPredicate mp = Countif.createCriteriaPredicate(new StringEval("<>aa"), 0, 0);
+ assertNotNull(mp);
+ StringEval seA = new StringEval("aa"); // this should not match the criteria '<>aa'
+ StringEval seB = new StringEval("bb"); // this should match
+ assertFalse(mp.matches(seA) && !mp.matches(seB), "Identified bug 46647");
+ assertFalse(mp.matches(seA));
+ assertTrue(mp.matches(seB));
+
+ // general tests for not-equal (<>) operator
+ AreaEval range;
+ ValueEval[] values;
+
+ values = new ValueEval[] {
+ new StringEval("aa"),
+ new StringEval("def"),
+ new StringEval("aa"),
+ new StringEval("ghi"),
+ new StringEval("aa"),
+ new StringEval("aa"),
+ };
+
+ range = EvalFactory.createAreaEval("A1:A6", values);
+ confirmCountIf(2, range, new StringEval("<>aa"));
+
+ values = new ValueEval[] {
+ new StringEval("ab"),
+ new StringEval("aabb"),
+ new StringEval("aa"), // match
+ new StringEval("abb"),
+ new StringEval("aab"),
+ new StringEval("ba"), // match
+ };
+
+ range = EvalFactory.createAreaEval("A1:A6", values);
+ confirmCountIf(2, range, new StringEval("<>a*b"));
+
+
+ values = new ValueEval[] {
+ new NumberEval(222),
+ new NumberEval(222),
+ new NumberEval(111),
+ new StringEval("aa"),
+ new StringEval("111"),
+ };
+
+ range = EvalFactory.createAreaEval("A1:A5", values);
+ confirmCountIf(4, range, new StringEval("<>111"));
+ }
/**
* String criteria in COUNTIF are case insensitive;
* for example, the string "apples" and the string "APPLES" will match the same cells.
*/
- @Test
+ @Test
void testCaseInsensitiveStringComparison() {
AreaEval range;
ValueEval[] values;
@@ -218,199 +218,199 @@ final class TestCountFuncs {
confirmCountIf(3, range, new StringEval("No"));
}
- /**
- * special case where the criteria argument is a cell reference
- */
- @Test
- void testCountIfWithCriteriaReference() {
-
- ValueEval[] values = {
- new NumberEval(22),
- new NumberEval(25),
- new NumberEval(21),
- new NumberEval(25),
- new NumberEval(25),
- new NumberEval(25),
- };
- AreaEval arg0 = EvalFactory.createAreaEval("C1:C6", values);
-
- ValueEval criteriaArg = EvalFactory.createRefEval("A1", new NumberEval(25));
- ValueEval[] args= { arg0, criteriaArg, };
-
- double actual = NumericFunctionInvoker.invoke(new Countif(), args);
- assertEquals(4, actual, 0D);
- }
-
- private static void confirmCountA(int expected, ValueEval[] args) {
- double result = NumericFunctionInvoker.invoke(new Counta(), args);
- assertEquals(expected, result, 0);
- }
- private static void confirmCountIf(int expected, AreaEval range, ValueEval criteria) {
-
- ValueEval[] args = { range, criteria, };
- double result = NumericFunctionInvoker.invoke(new Countif(), args);
- assertEquals(expected, result, 0);
- }
- private static void confirmCountBlank(int expected, AreaEval range) {
-
- ValueEval[] args = { range };
- double result = NumericFunctionInvoker.invoke(new Countblank(), args);
- assertEquals(expected, result, 0);
- }
-
- private static I_MatchPredicate createCriteriaPredicate(ValueEval ev) {
- return Countif.createCriteriaPredicate(ev, 0, 0);
- }
-
- /**
- * the criteria arg is mostly handled by {@link OperandResolver#getSingleValue(org.apache.poi.ss.formula.eval.ValueEval, int, int)}}
- */
- @Test
- void testCountifAreaCriteria() {
- int srcColIx = 2; // anything but column A
-
- ValueEval v0 = new NumberEval(2.0);
- ValueEval v1 = new StringEval("abc");
- ValueEval v2 = ErrorEval.DIV_ZERO;
-
- AreaEval ev = EvalFactory.createAreaEval("A10:A12", new ValueEval[] { v0, v1, v2, });
-
- I_MatchPredicate mp;
- mp = Countif.createCriteriaPredicate(ev, 9, srcColIx);
- assertNotNull(mp);
- confirmPredicate(true, mp, srcColIx);
- confirmPredicate(false, mp, "abc");
- confirmPredicate(false, mp, ErrorEval.DIV_ZERO);
-
- mp = Countif.createCriteriaPredicate(ev, 10, srcColIx);
- assertNotNull(mp);
- confirmPredicate(false, mp, srcColIx);
- confirmPredicate(true, mp, "abc");
- confirmPredicate(false, mp, ErrorEval.DIV_ZERO);
-
- mp = Countif.createCriteriaPredicate(ev, 11, srcColIx);
- assertNotNull(mp);
- confirmPredicate(false, mp, srcColIx);
- confirmPredicate(false, mp, "abc");
- confirmPredicate(true, mp, ErrorEval.DIV_ZERO);
- confirmPredicate(false, mp, ErrorEval.VALUE_INVALID);
-
- // tricky: indexing outside of A10:A12
- // even this #VALUE! error gets used by COUNTIF as valid criteria
- mp = Countif.createCriteriaPredicate(ev, 12, srcColIx);
- assertNotNull(mp);
- confirmPredicate(false, mp, srcColIx);
- confirmPredicate(false, mp, "abc");
- confirmPredicate(false, mp, ErrorEval.DIV_ZERO);
- confirmPredicate(true, mp, ErrorEval.VALUE_INVALID);
- }
-
- @Test
- void testCountifEmptyStringCriteria() {
- I_MatchPredicate mp;
-
- // pred '=' matches blank cell but not empty string
- mp = createCriteriaPredicate(new StringEval("="));
- confirmPredicate(false, mp, "");
- confirmPredicate(true, mp, NULL);
-
- // pred '' matches both blank cell but not empty string
- mp = createCriteriaPredicate(new StringEval(""));
- confirmPredicate(true, mp, "");
- confirmPredicate(true, mp, NULL);
-
- // pred '<>' matches empty string but not blank cell
- mp = createCriteriaPredicate(new StringEval("<>"));
- confirmPredicate(false, mp, NULL);
- confirmPredicate(true, mp, "");
- }
-
- @Test
- void testCountifComparisons() {
- I_MatchPredicate mp;
-
- mp = createCriteriaPredicate(new StringEval(">5"));
- confirmPredicate(false, mp, 4);
- confirmPredicate(false, mp, 5);
- confirmPredicate(true, mp, 6);
-
- mp = createCriteriaPredicate(new StringEval("<=5"));
- confirmPredicate(true, mp, 4);
- confirmPredicate(true, mp, 5);
- confirmPredicate(false, mp, 6);
- confirmPredicate(false, mp, "4.9");
- confirmPredicate(false, mp, "4.9t");
- confirmPredicate(false, mp, "5.1");
- confirmPredicate(false, mp, NULL);
-
- mp = createCriteriaPredicate(new StringEval("=abc"));
- confirmPredicate(true, mp, "abc");
-
- mp = createCriteriaPredicate(new StringEval("=42"));
- confirmPredicate(false, mp, 41);
- confirmPredicate(true, mp, 42);
- confirmPredicate(true, mp, "42");
-
- mp = createCriteriaPredicate(new StringEval(">abc"));
- confirmPredicate(false, mp, 4);
- confirmPredicate(false, mp, "abc");
- confirmPredicate(true, mp, "abd");
-
- mp = createCriteriaPredicate(new StringEval(">4t3"));
- confirmPredicate(false, mp, 4);
- confirmPredicate(false, mp, 500);
- confirmPredicate(true, mp, "500");
- confirmPredicate(true, mp, "4t4");
- }
-
- /**
- * the criteria arg value can be an error code (the error does not
- * propagate to the COUNTIF result).
- */
- @Test
- void testCountifErrorCriteria() {
- I_MatchPredicate mp;
-
- mp = createCriteriaPredicate(new StringEval("#REF!"));
- confirmPredicate(false, mp, 4);
- confirmPredicate(false, mp, "#REF!");
- confirmPredicate(true, mp, ErrorEval.REF_INVALID);
-
- mp = createCriteriaPredicate(new StringEval("<#VALUE!"));
- confirmPredicate(false, mp, 4);
- confirmPredicate(false, mp, "#DIV/0!");
- confirmPredicate(false, mp, "#REF!");
- confirmPredicate(true, mp, ErrorEval.DIV_ZERO);
- confirmPredicate(false, mp, ErrorEval.REF_INVALID);
-
- // not quite an error literal, should be treated as plain text
- mp = createCriteriaPredicate(new StringEval("<=#REF!a"));
- confirmPredicate(false, mp, 4);
- confirmPredicate(true, mp, "#DIV/0!");
- confirmPredicate(true, mp, "#REF!");
- confirmPredicate(false, mp, ErrorEval.DIV_ZERO);
- confirmPredicate(false, mp, ErrorEval.REF_INVALID);
- }
+ /**
+ * special case where the criteria argument is a cell reference
+ */
+ @Test
+ void testCountIfWithCriteriaReference() {
+
+ ValueEval[] values = {
+ new NumberEval(22),
+ new NumberEval(25),
+ new NumberEval(21),
+ new NumberEval(25),
+ new NumberEval(25),
+ new NumberEval(25),
+ };
+ AreaEval arg0 = EvalFactory.createAreaEval("C1:C6", values);
+
+ ValueEval criteriaArg = EvalFactory.createRefEval("A1", new NumberEval(25));
+ ValueEval[] args= { arg0, criteriaArg, };
+
+ double actual = NumericFunctionInvoker.invoke(new Countif(), args);
+ assertEquals(4, actual, 0D);
+ }
+
+ private static void confirmCountA(int expected, ValueEval[] args) {
+ double result = NumericFunctionInvoker.invoke(new Counta(), args);
+ assertEquals(expected, result, 0);
+ }
+ private static void confirmCountIf(int expected, AreaEval range, ValueEval criteria) {
+
+ ValueEval[] args = { range, criteria, };
+ double result = NumericFunctionInvoker.invoke(new Countif(), args);
+ assertEquals(expected, result, 0);
+ }
+ private static void confirmCountBlank(int expected, AreaEval range) {
+
+ ValueEval[] args = { range };
+ double result = NumericFunctionInvoker.invoke(new Countblank(), args);
+ assertEquals(expected, result, 0);
+ }
+
+ private static I_MatchPredicate createCriteriaPredicate(ValueEval ev) {
+ return Countif.createCriteriaPredicate(ev, 0, 0);
+ }
+
+ /**
+ * the criteria arg is mostly handled by {@link OperandResolver#getSingleValue(org.apache.poi.ss.formula.eval.ValueEval, int, int)}}
+ */
+ @Test
+ void testCountifAreaCriteria() {
+ int srcColIx = 2; // anything but column A
+
+ ValueEval v0 = new NumberEval(2.0);
+ ValueEval v1 = new StringEval("abc");
+ ValueEval v2 = ErrorEval.DIV_ZERO;
+
+ AreaEval ev = EvalFactory.createAreaEval("A10:A12", new ValueEval[] { v0, v1, v2, });
+
+ I_MatchPredicate mp;
+ mp = Countif.createCriteriaPredicate(ev, 9, srcColIx);
+ assertNotNull(mp);
+ confirmPredicate(true, mp, srcColIx);
+ confirmPredicate(false, mp, "abc");
+ confirmPredicate(false, mp, ErrorEval.DIV_ZERO);
+
+ mp = Countif.createCriteriaPredicate(ev, 10, srcColIx);
+ assertNotNull(mp);
+ confirmPredicate(false, mp, srcColIx);
+ confirmPredicate(true, mp, "abc");
+ confirmPredicate(false, mp, ErrorEval.DIV_ZERO);
+
+ mp = Countif.createCriteriaPredicate(ev, 11, srcColIx);
+ assertNotNull(mp);
+ confirmPredicate(false, mp, srcColIx);
+ confirmPredicate(false, mp, "abc");
+ confirmPredicate(true, mp, ErrorEval.DIV_ZERO);
+ confirmPredicate(false, mp, ErrorEval.VALUE_INVALID);
+
+ // tricky: indexing outside of A10:A12
+ // even this #VALUE! error gets used by COUNTIF as valid criteria
+ mp = Countif.createCriteriaPredicate(ev, 12, srcColIx);
+ assertNotNull(mp);
+ confirmPredicate(false, mp, srcColIx);
+ confirmPredicate(false, mp, "abc");
+ confirmPredicate(false, mp, ErrorEval.DIV_ZERO);
+ confirmPredicate(true, mp, ErrorEval.VALUE_INVALID);
+ }
+
+ @Test
+ void testCountifEmptyStringCriteria() {
+ I_MatchPredicate mp;
+
+ // pred '=' matches blank cell but not empty string
+ mp = createCriteriaPredicate(new StringEval("="));
+ confirmPredicate(false, mp, "");
+ confirmPredicate(true, mp, NULL);
+
+ // pred '' matches both blank cell but not empty string
+ mp = createCriteriaPredicate(new StringEval(""));
+ confirmPredicate(true, mp, "");
+ confirmPredicate(true, mp, NULL);
+
+ // pred '<>' matches empty string but not blank cell
+ mp = createCriteriaPredicate(new StringEval("<>"));
+ confirmPredicate(false, mp, NULL);
+ confirmPredicate(true, mp, "");
+ }
+
+ @Test
+ void testCountifComparisons() {
+ I_MatchPredicate mp;
+
+ mp = createCriteriaPredicate(new StringEval(">5"));
+ confirmPredicate(false, mp, 4);
+ confirmPredicate(false, mp, 5);
+ confirmPredicate(true, mp, 6);
+
+ mp = createCriteriaPredicate(new StringEval("<=5"));
+ confirmPredicate(true, mp, 4);
+ confirmPredicate(true, mp, 5);
+ confirmPredicate(false, mp, 6);
+ confirmPredicate(false, mp, "4.9");
+ confirmPredicate(false, mp, "4.9t");
+ confirmPredicate(false, mp, "5.1");
+ confirmPredicate(false, mp, NULL);
+
+ mp = createCriteriaPredicate(new StringEval("=abc"));
+ confirmPredicate(true, mp, "abc");
+
+ mp = createCriteriaPredicate(new StringEval("=42"));
+ confirmPredicate(false, mp, 41);
+ confirmPredicate(true, mp, 42);
+ confirmPredicate(true, mp, "42");
+
+ mp = createCriteriaPredicate(new StringEval(">abc"));
+ confirmPredicate(false, mp, 4);
+ confirmPredicate(false, mp, "abc");
+ confirmPredicate(true, mp, "abd");
+
+ mp = createCriteriaPredicate(new StringEval(">4t3"));
+ confirmPredicate(false, mp, 4);
+ confirmPredicate(false, mp, 500);
+ confirmPredicate(true, mp, "500");
+ confirmPredicate(true, mp, "4t4");
+ }
+
+ /**
+ * the criteria arg value can be an error code (the error does not
+ * propagate to the COUNTIF result).
+ */
+ @Test
+ void testCountifErrorCriteria() {
+ I_MatchPredicate mp;
+
+ mp = createCriteriaPredicate(new StringEval("#REF!"));
+ confirmPredicate(false, mp, 4);
+ confirmPredicate(false, mp, "#REF!");
+ confirmPredicate(true, mp, ErrorEval.REF_INVALID);
+
+ mp = createCriteriaPredicate(new StringEval("<#VALUE!"));
+ confirmPredicate(false, mp, 4);
+ confirmPredicate(false, mp, "#DIV/0!");
+ confirmPredicate(false, mp, "#REF!");
+ confirmPredicate(true, mp, ErrorEval.DIV_ZERO);
+ confirmPredicate(false, mp, ErrorEval.REF_INVALID);
+
+ // not quite an error literal, should be treated as plain text
+ mp = createCriteriaPredicate(new StringEval("<=#REF!a"));
+ confirmPredicate(false, mp, 4);
+ confirmPredicate(true, mp, "#DIV/0!");
+ confirmPredicate(true, mp, "#REF!");
+ confirmPredicate(false, mp, ErrorEval.DIV_ZERO);
+ confirmPredicate(false, mp, ErrorEval.REF_INVALID);
+ }
/**
* Bug #51498 - Check that CountIf behaves correctly for GTE, LTE
* and NEQ cases
*/
@Test
- void testCountifBug51498() {
- final int REF_COL = 4;
- final int EVAL_COL = 3;
+ void testCountifBug51498() {
+ final int REF_COL = 4;
+ final int EVAL_COL = 3;
HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("51498.xls");
- FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
- HSSFSheet sheet = workbook.getSheetAt(0);
+ FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
+ HSSFSheet sheet = workbook.getSheetAt(0);
- // numeric criteria
+ // numeric criteria
for (int i = 0; i < 8; i++) {
- CellValue expected = evaluator.evaluate(sheet.getRow(i).getCell(REF_COL));
- CellValue actual = evaluator.evaluate(sheet.getRow(i).getCell(EVAL_COL));
- assertEquals(expected.formatAsString(), actual.formatAsString());
- }
+ CellValue expected = evaluator.evaluate(sheet.getRow(i).getCell(REF_COL));
+ CellValue actual = evaluator.evaluate(sheet.getRow(i).getCell(EVAL_COL));
+ assertEquals(expected.formatAsString(), actual.formatAsString());
+ }
// boolean criteria
for (int i = 0; i < 8; i++) {
@@ -421,7 +421,7 @@ final class TestCountFuncs {
double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
assertEquals(expectedValue, actualValue, 0.0001,
- "Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula()+"] ");
+ "Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula()+"] ");
}
// string criteria
@@ -433,87 +433,87 @@ final class TestCountFuncs {
double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
assertEquals(expectedValue, actualValue, 0.0001,
- "Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula()+"] ");
+ "Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula()+"] ");
}
- }
+ }
+
+ @Test
+ void testWildCards() {
+ I_MatchPredicate mp;
+
+ mp = createCriteriaPredicate(new StringEval("a*b"));
+ confirmPredicate(false, mp, "abc");
+ confirmPredicate(true, mp, "ab");
+ confirmPredicate(true, mp, "axxb");
+ confirmPredicate(false, mp, "xab");
+
+ mp = createCriteriaPredicate(new StringEval("a?b"));
+ confirmPredicate(false, mp, "abc");
+ confirmPredicate(false, mp, "ab");
+ confirmPredicate(false, mp, "axxb");
+ confirmPredicate(false, mp, "xab");
+ confirmPredicate(true, mp, "axb");
+
+ mp = createCriteriaPredicate(new StringEval("a~?"));
+ confirmPredicate(false, mp, "a~a");
+ confirmPredicate(false, mp, "a~?");
+ confirmPredicate(true, mp, "a?");
+
+ mp = createCriteriaPredicate(new StringEval("~*a"));
+ confirmPredicate(false, mp, "~aa");
+ confirmPredicate(false, mp, "~*a");
+ confirmPredicate(true, mp, "*a");
+
+ mp = createCriteriaPredicate(new StringEval("12?12"));
+ confirmPredicate(false, mp, 12812);
+ confirmPredicate(true, mp, "12812");
+ confirmPredicate(false, mp, "128812");
+ }
- @Test
- void testWildCards() {
- I_MatchPredicate mp;
-
- mp = createCriteriaPredicate(new StringEval("a*b"));
- confirmPredicate(false, mp, "abc");
- confirmPredicate(true, mp, "ab");
- confirmPredicate(true, mp, "axxb");
- confirmPredicate(false, mp, "xab");
-
- mp = createCriteriaPredicate(new StringEval("a?b"));
- confirmPredicate(false, mp, "abc");
- confirmPredicate(false, mp, "ab");
- confirmPredicate(false, mp, "axxb");
- confirmPredicate(false, mp, "xab");
- confirmPredicate(true, mp, "axb");
-
- mp = createCriteriaPredicate(new StringEval("a~?"));
- confirmPredicate(false, mp, "a~a");
- confirmPredicate(false, mp, "a~?");
- confirmPredicate(true, mp, "a?");
-
- mp = createCriteriaPredicate(new StringEval("~*a"));
- confirmPredicate(false, mp, "~aa");
- confirmPredicate(false, mp, "~*a");
- confirmPredicate(true, mp, "*a");
-
- mp = createCriteriaPredicate(new StringEval("12?12"));
- confirmPredicate(false, mp, 12812);
- confirmPredicate(true, mp, "12812");
- confirmPredicate(false, mp, "128812");
- }
-
- @Test
- void testNotQuiteWildCards() {
- I_MatchPredicate mp;
-
- // make sure special reg-ex chars are treated like normal chars
- mp = createCriteriaPredicate(new StringEval("a.b"));
- confirmPredicate(false, mp, "aab");
- confirmPredicate(true, mp, "a.b");
-
-
- mp = createCriteriaPredicate(new StringEval("a~b"));
- confirmPredicate(false, mp, "ab");
- confirmPredicate(false, mp, "axb");
- confirmPredicate(false, mp, "a~~b");
- confirmPredicate(true, mp, "a~b");
-
- mp = createCriteriaPredicate(new StringEval(">a*b"));
- confirmPredicate(false, mp, "a(b");
- confirmPredicate(true, mp, "aab");
- confirmPredicate(false, mp, "a*a");
- confirmPredicate(true, mp, "a*c");
- }
-
- private static void confirmPredicate(boolean expectedResult, I_MatchPredicate matchPredicate, int value) {
- assertEquals(expectedResult, matchPredicate.matches(new NumberEval(value)));
- }
- private static void confirmPredicate(boolean expectedResult, I_MatchPredicate matchPredicate, String value) {
- ValueEval ev = value == null ? BlankEval.instance : new StringEval(value);
- assertEquals(expectedResult, matchPredicate.matches(ev));
- }
- private static void confirmPredicate(boolean expectedResult, I_MatchPredicate matchPredicate, ErrorEval value) {
- assertEquals(expectedResult, matchPredicate.matches(value));
- }
-
- @Test
- void testCountifFromSpreadsheet() {
- testCountFunctionFromSpreadsheet("countifExamples.xls", 1, 2, 3, "countif");
- }
+ @Test
+ void testNotQuiteWildCards() {
+ I_MatchPredicate mp;
+
+ // make sure special reg-ex chars are treated like normal chars
+ mp = createCriteriaPredicate(new StringEval("a.b"));
+ confirmPredicate(false, mp, "aab");
+ confirmPredicate(true, mp, "a.b");
+
+
+ mp = createCriteriaPredicate(new StringEval("a~b"));
+ confirmPredicate(false, mp, "ab");
+ confirmPredicate(false, mp, "axb");
+ confirmPredicate(false, mp, "a~~b");
+ confirmPredicate(true, mp, "a~b");
+
+ mp = createCriteriaPredicate(new StringEval(">a*b"));
+ confirmPredicate(false, mp, "a(b");
+ confirmPredicate(true, mp, "aab");
+ confirmPredicate(false, mp, "a*a");
+ confirmPredicate(true, mp, "a*c");
+ }
+
+ private static void confirmPredicate(boolean expectedResult, I_MatchPredicate matchPredicate, int value) {
+ assertEquals(expectedResult, matchPredicate.matches(new NumberEval(value)));
+ }
+ private static void confirmPredicate(boolean expectedResult, I_MatchPredicate matchPredicate, String value) {
+ ValueEval ev = value == null ? BlankEval.instance : new StringEval(value);
+ assertEquals(expectedResult, matchPredicate.matches(ev));
+ }
+ private static void confirmPredicate(boolean expectedResult, I_MatchPredicate matchPredicate, ErrorEval value) {
+ assertEquals(expectedResult, matchPredicate.matches(value));
+ }
+
+ @Test
+ void testCountifFromSpreadsheet() {
+ testCountFunctionFromSpreadsheet("countifExamples.xls", 1, 2, 3, "countif");
+ }
/**
* Two COUNTIF examples taken from
* http://office.microsoft.com/en-us/excel-help/countif-function-HP010069840.aspx?CTT=5&origin=HA010277524
*/
- @Test
+ @Test
void testCountifExamples() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("countifExamples.xls");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
@@ -530,7 +530,7 @@ final class TestCountFuncs {
double actualValue = cv.getNumberValue();
double expectedValue = cellC.getNumericCellValue();
assertEquals(expectedValue, actualValue, 0.0001,
- "Problem with a formula at " + new CellReference(cellA).formatAsString() + ": " + cellA.getCellFormula() + " :Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
+ "Problem with a formula at " + new CellReference(cellA).formatAsString() + ": " + cellA.getCellFormula() + " :Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
}
HSSFSheet sheet2 = wb.getSheet("MSDN Example 2");
@@ -546,39 +546,39 @@ final class TestCountFuncs {
double expectedValue = cellC.getNumericCellValue();
assertEquals(expectedValue, actualValue, 0.0001,
- "Problem with a formula at " + new CellReference(cellA).formatAsString() + "[" +
- cellA.getCellFormula()+"]: Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
+ "Problem with a formula at " + new CellReference(cellA).formatAsString() + "[" +
+ cellA.getCellFormula()+"]: Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
}
}
- @Test
- void testCountBlankFromSpreadsheet() {
- testCountFunctionFromSpreadsheet("countblankExamples.xls", 1, 3, 4, "countblank");
- }
-
- private static void testCountFunctionFromSpreadsheet(String FILE_NAME, int START_ROW_IX, int COL_IX_ACTUAL, int COL_IX_EXPECTED, String functionName) {
-
- int failureCount = 0;
- HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(FILE_NAME);
- HSSFSheet sheet = wb.getSheetAt(0);
- HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
- int maxRow = sheet.getLastRowNum();
- for (int rowIx=START_ROW_IX; rowIx<maxRow; rowIx++) {
- HSSFRow row = sheet.getRow(rowIx);
- if(row == null) {
- continue;
- }
- HSSFCell cell = row.getCell(COL_IX_ACTUAL);
- CellValue cv = fe.evaluate(cell);
- double actualValue = cv.getNumberValue();
- double expectedValue = row.getCell(COL_IX_EXPECTED).getNumericCellValue();
- if (actualValue != expectedValue) {
- System.err.println("Problem with test case on row " + (rowIx+1) + " "
- + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
- failureCount++;
- }
- }
+ @Test
+ void testCountBlankFromSpreadsheet() {
+ testCountFunctionFromSpreadsheet("countblankExamples.xls", 1, 3, 4, "countblank");
+ }
+
+ private static void testCountFunctionFromSpreadsheet(String FILE_NAME, int START_ROW_IX, int COL_IX_ACTUAL, int COL_IX_EXPECTED, String functionName) {
- assertEquals(0, failureCount, failureCount + " " + functionName + " evaluations failed.");
- }
+ int failureCount = 0;
+ HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(FILE_NAME);
+ HSSFSheet sheet = wb.getSheetAt(0);
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ int maxRow = sheet.getLastRowNum();
+ for (int rowIx=START_ROW_IX; rowIx<maxRow; rowIx++) {
+ HSSFRow row = sheet.getRow(rowIx);
+ if(row == null) {
+ continue;
+ }
+ HSSFCell cell = row.getCell(COL_IX_ACTUAL);
+ CellValue cv = fe.evaluate(cell);
+ double actualValue = cv.getNumberValue();
+ double expectedValue = row.getCell(COL_IX_EXPECTED).getNumericCellValue();
+ if (actualValue != expectedValue) {
+ System.err.println("Problem with test case on row " + (rowIx+1) + " "
+ + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
+ failureCount++;
+ }
+ }
+
+ assertEquals(0, failureCount, failureCount + " " + functionName + " evaluations failed.");
+ }
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDec2Bin.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDec2Bin.java?rev=1890120&r1=1890119&r2=1890120&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDec2Bin.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDec2Bin.java Sat May 22 20:56:44 2021
@@ -38,9 +38,9 @@ import org.junit.jupiter.api.Test;
final class TestDec2Bin {
private static ValueEval invokeValue(String number1) {
- ValueEval[] args = new ValueEval[] { new StringEval(number1) };
- return new Dec2Bin().evaluate(args, -1, -1);
- }
+ ValueEval[] args = new ValueEval[] { new StringEval(number1) };
+ return new Dec2Bin().evaluate(args, -1, -1);
+ }
private static ValueEval invokeBack(String number1) {
ValueEval[] args = new ValueEval[] { new StringEval(number1) };
@@ -48,10 +48,10 @@ final class TestDec2Bin {
}
private static void confirmValue(String msg, String number1, String expected) {
- ValueEval result = invokeValue(number1);
- assertEquals(StringEval.class, result.getClass(), "Had: " + result);
- assertEquals(expected, ((StringEval) result).getStringValue(), msg);
- }
+ ValueEval result = invokeValue(number1);
+ assertEquals(StringEval.class, result.getClass(), "Had: " + result);
+ assertEquals(expected, ((StringEval) result).getStringValue(), msg);
+ }
private static void confirmValueError(String msg, String number1, ErrorEval numError) {
ValueEval result = invokeValue(number1);
@@ -60,13 +60,13 @@ final class TestDec2Bin {
}
@Test
- void testBasic() {
- confirmValue("Converts binary '00101' from binary (5)", "5", "101");
- confirmValue("Converts binary '1111111111' from binary (-1)", "-1", "1111111111");
- confirmValue("Converts binary '1111111110' from binary (-2)", "-2", "1111111110");
+ void testBasic() {
+ confirmValue("Converts binary '00101' from binary (5)", "5", "101");
+ confirmValue("Converts binary '1111111111' from binary (-1)", "-1", "1111111111");
+ confirmValue("Converts binary '1111111110' from binary (-2)", "-2", "1111111110");
confirmValue("Converts binary '0111111111' from binary (511)", "511", "111111111");
confirmValue("Converts binary '1000000000' from binary (511)", "-512", "1000000000");
- }
+ }
@Test
void testErrors() {
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDec2Hex.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDec2Hex.java?rev=1890120&r1=1890119&r2=1890120&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDec2Hex.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDec2Hex.java Sat May 22 20:56:44 2021
@@ -37,10 +37,10 @@ import org.junit.jupiter.api.Test;
*/
final class TestDec2Hex {
- private static ValueEval invokeValue(String number1, String number2) {
- ValueEval[] args = new ValueEval[] { new StringEval(number1), new StringEval(number2), };
- return new Dec2Hex().evaluate(args, -1, -1);
- }
+ private static ValueEval invokeValue(String number1, String number2) {
+ ValueEval[] args = new ValueEval[] { new StringEval(number1), new StringEval(number2), };
+ return new Dec2Hex().evaluate(args, -1, -1);
+ }
private static ValueEval invokeBack(String number1) {
ValueEval[] args = new ValueEval[] { new StringEval(number1) };
@@ -48,21 +48,21 @@ final class TestDec2Hex {
}
private static ValueEval invokeValue(String number1) {
- ValueEval[] args = new ValueEval[] { new StringEval(number1), };
- return new Dec2Hex().evaluate(args, -1, -1);
- }
-
- private static void confirmValue(String msg, String number1, String number2, String expected) {
- ValueEval result = invokeValue(number1, number2);
- assertEquals(StringEval.class, result.getClass());
- assertEquals(expected, ((StringEval) result).getStringValue(), msg);
- }
+ ValueEval[] args = new ValueEval[] { new StringEval(number1), };
+ return new Dec2Hex().evaluate(args, -1, -1);
+ }
+
+ private static void confirmValue(String msg, String number1, String number2, String expected) {
+ ValueEval result = invokeValue(number1, number2);
+ assertEquals(StringEval.class, result.getClass());
+ assertEquals(expected, ((StringEval) result).getStringValue(), msg);
+ }
private static void confirmValue(String msg, String number1, String expected) {
- ValueEval result = invokeValue(number1);
- assertEquals(StringEval.class, result.getClass());
- assertEquals(expected, ((StringEval) result).getStringValue(), msg);
- }
+ ValueEval result = invokeValue(number1);
+ assertEquals(StringEval.class, result.getClass());
+ assertEquals(expected, ((StringEval) result).getStringValue(), msg);
+ }
private static void confirmValueError(String msg, String number1, String number2, ErrorEval numError) {
ValueEval result = invokeValue(number1, number2);
@@ -71,19 +71,19 @@ final class TestDec2Hex {
}
@Test
- void testBasic() {
- confirmValue("Converts decimal 100 to hexadecimal with 0 characters (64)", "100","0", "64");
- confirmValue("Converts decimal 100 to hexadecimal with 4 characters (0064)", "100","4", "0064");
- confirmValue("Converts decimal 100 to hexadecimal with 5 characters (0064)", "100","5", "00064");
- confirmValue("Converts decimal 100 to hexadecimal with 10 (default) characters", "100","10", "0000000064");
- confirmValue("If argument places contains a decimal value, dec2hex ignores the numbers to the right side of the decimal point.", "100","10.0", "0000000064");
+ void testBasic() {
+ confirmValue("Converts decimal 100 to hexadecimal with 0 characters (64)", "100","0", "64");
+ confirmValue("Converts decimal 100 to hexadecimal with 4 characters (0064)", "100","4", "0064");
+ confirmValue("Converts decimal 100 to hexadecimal with 5 characters (0064)", "100","5", "00064");
+ confirmValue("Converts decimal 100 to hexadecimal with 10 (default) characters", "100","10", "0000000064");
+ confirmValue("If argument places contains a decimal value, dec2hex ignores the numbers to the right side of the decimal point.", "100","10.0", "0000000064");
- confirmValue("Converts decimal -54 to hexadecimal, 2 is ignored","-54", "2", "FFFFFFFFCA");
- confirmValue("places is optionnal","-54", "FFFFFFFFCA");
+ confirmValue("Converts decimal -54 to hexadecimal, 2 is ignored","-54", "2", "FFFFFFFFCA");
+ confirmValue("places is optionnal","-54", "FFFFFFFFCA");
- confirmValue("Converts normal decimal number to hexadecimal", "100", "64");
+ confirmValue("Converts normal decimal number to hexadecimal", "100", "64");
- String maxInt = Integer.toString(Integer.MAX_VALUE);
+ String maxInt = Integer.toString(Integer.MAX_VALUE);
assertEquals("2147483647", maxInt);
confirmValue("Converts INT_MAX to hexadecimal", maxInt, "7FFFFFFF");
@@ -102,7 +102,7 @@ final class TestDec2Hex {
String minLong = Long.toString(-549755813888L);
assertEquals("-549755813888", minLong);
confirmValue("Converts the min supported value to hexadecimal", minLong, "FF80000000");
- }
+ }
@Test
void testErrors() {
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDelta.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDelta.java?rev=1890120&r1=1890119&r2=1890120&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDelta.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDelta.java Sat May 22 20:56:44 2021
@@ -30,16 +30,16 @@ import org.junit.jupiter.api.Test;
*/
final class TestDelta {
- private static ValueEval invokeValue(String number1, String number2) {
- ValueEval[] args = new ValueEval[] { new StringEval(number1), new StringEval(number2), };
- return new Delta().evaluate(args, -1, -1);
- }
-
- private static void confirmValue(String number1, String number2, double expected) {
- ValueEval result = invokeValue(number1, number2);
- assertEquals(NumberEval.class, result.getClass());
- assertEquals(expected, ((NumberEval) result).getNumberValue(), 0.0);
- }
+ private static ValueEval invokeValue(String number1, String number2) {
+ ValueEval[] args = new ValueEval[] { new StringEval(number1), new StringEval(number2), };
+ return new Delta().evaluate(args, -1, -1);
+ }
+
+ private static void confirmValue(String number1, String number2, double expected) {
+ ValueEval result = invokeValue(number1, number2);
+ assertEquals(NumberEval.class, result.getClass());
+ assertEquals(expected, ((NumberEval) result).getNumberValue(), 0.0);
+ }
private static void confirmValueError(String number1, String number2) {
ValueEval result = invokeValue(number1, number2);
@@ -47,17 +47,17 @@ final class TestDelta {
assertEquals(ErrorEval.VALUE_INVALID, result);
}
- @Test
- void testBasic() {
- confirmValue("5","4", 0); // Checks whether 5 equals 4 (0)
- confirmValue("5","5", 1); // Checks whether 5 equals 5 (1)
+ @Test
+ void testBasic() {
+ confirmValue("5","4", 0); // Checks whether 5 equals 4 (0)
+ confirmValue("5","5", 1); // Checks whether 5 equals 5 (1)
confirmValue("0.5","0", 0); // Checks whether 0.5 equals 0 (0)
confirmValue("0.50","0.5", 1);
confirmValue("0.5000000000","0.5", 1);
- }
+ }
- @Test
+ @Test
void testErrors() {
confirmValueError("A1","B2");
confirmValueError("AAAA","BBBB");
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFind.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFind.java?rev=1890120&r1=1890119&r2=1890120&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFind.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFind.java Sat May 22 20:56:44 2021
@@ -35,47 +35,47 @@ import org.junit.jupiter.api.Test;
final class TestFind {
@Test
- void testFind() throws IOException {
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
-
- HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
-
- confirmResult(fe, cell, "find(\"h\", \"haystack\")", 1);
- confirmResult(fe, cell, "find(\"a\", \"haystack\",2)", 2);
- confirmResult(fe, cell, "find(\"a\", \"haystack\",3)", 6);
-
- // number args converted to text
- confirmResult(fe, cell, "find(7, 32768)", 3);
- confirmResult(fe, cell, "find(\"34\", 1341235233412, 3)", 10);
- confirmResult(fe, cell, "find(5, 87654)", 4);
-
- // Errors
- confirmError(fe, cell, "find(\"n\", \"haystack\")", FormulaError.VALUE);
- confirmError(fe, cell, "find(\"k\", \"haystack\",9)", FormulaError.VALUE);
- confirmError(fe, cell, "find(\"k\", \"haystack\",#REF!)", FormulaError.REF);
- confirmError(fe, cell, "find(\"k\", \"haystack\",0)", FormulaError.VALUE);
- confirmError(fe, cell, "find(#DIV/0!, #N/A, #REF!)", FormulaError.DIV0);
- confirmError(fe, cell, "find(2, #N/A, #REF!)", FormulaError.NA);
-
- wb.close();
- }
-
- private static void confirmResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText,
- int expectedResult) {
- cell.setCellFormula(formulaText);
- fe.notifyUpdateCell(cell);
- CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.NUMERIC);
- assertEquals(expectedResult, result.getNumberValue(), 0.0);
- }
-
- private static void confirmError(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText,
- FormulaError expectedErrorCode) {
- cell.setCellFormula(formulaText);
- fe.notifyUpdateCell(cell);
- CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.ERROR);
- assertEquals(expectedErrorCode.getCode(), result.getErrorValue());
- }
+ void testFind() throws IOException {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
+
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+
+ confirmResult(fe, cell, "find(\"h\", \"haystack\")", 1);
+ confirmResult(fe, cell, "find(\"a\", \"haystack\",2)", 2);
+ confirmResult(fe, cell, "find(\"a\", \"haystack\",3)", 6);
+
+ // number args converted to text
+ confirmResult(fe, cell, "find(7, 32768)", 3);
+ confirmResult(fe, cell, "find(\"34\", 1341235233412, 3)", 10);
+ confirmResult(fe, cell, "find(5, 87654)", 4);
+
+ // Errors
+ confirmError(fe, cell, "find(\"n\", \"haystack\")", FormulaError.VALUE);
+ confirmError(fe, cell, "find(\"k\", \"haystack\",9)", FormulaError.VALUE);
+ confirmError(fe, cell, "find(\"k\", \"haystack\",#REF!)", FormulaError.REF);
+ confirmError(fe, cell, "find(\"k\", \"haystack\",0)", FormulaError.VALUE);
+ confirmError(fe, cell, "find(#DIV/0!, #N/A, #REF!)", FormulaError.DIV0);
+ confirmError(fe, cell, "find(2, #N/A, #REF!)", FormulaError.NA);
+
+ wb.close();
+ }
+
+ private static void confirmResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText,
+ int expectedResult) {
+ cell.setCellFormula(formulaText);
+ fe.notifyUpdateCell(cell);
+ CellValue result = fe.evaluate(cell);
+ assertEquals(result.getCellType(), CellType.NUMERIC);
+ assertEquals(expectedResult, result.getNumberValue(), 0.0);
+ }
+
+ private static void confirmError(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText,
+ FormulaError expectedErrorCode) {
+ cell.setCellFormula(formulaText);
+ fe.notifyUpdateCell(cell);
+ CellValue result = fe.evaluate(cell);
+ assertEquals(result.getCellType(), CellType.ERROR);
+ assertEquals(expectedErrorCode.getCode(), result.getErrorValue());
+ }
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestHex2Dec.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestHex2Dec.java?rev=1890120&r1=1890119&r2=1890120&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestHex2Dec.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestHex2Dec.java Sat May 22 20:56:44 2021
@@ -35,15 +35,15 @@ import org.junit.jupiter.api.Test;
final class TestHex2Dec {
private static ValueEval invokeValue(String number1) {
- ValueEval[] args = new ValueEval[] { new StringEval(number1) };
- return new Hex2Dec().evaluate(args, -1, -1);
- }
+ ValueEval[] args = new ValueEval[] { new StringEval(number1) };
+ return new Hex2Dec().evaluate(args, -1, -1);
+ }
private static void confirmValue(String msg, String number1, String expected) {
- ValueEval result = invokeValue(number1);
- assertEquals(NumberEval.class, result.getClass());
- assertEquals(expected, ((NumberEval) result).getStringValue(), msg);
- }
+ ValueEval result = invokeValue(number1);
+ assertEquals(NumberEval.class, result.getClass());
+ assertEquals(expected, ((NumberEval) result).getStringValue(), msg);
+ }
private static void confirmValueError(String msg, String number1, ErrorEval numError) {
ValueEval result = invokeValue(number1);
@@ -52,11 +52,11 @@ final class TestHex2Dec {
}
@Test
- void testBasic() {
- confirmValue("Converts hex 'A5' to decimal (165)", "A5", "165");
- confirmValue("Converts hex FFFFFFFF5B to decimal (-165)", "FFFFFFFF5B", "-165");
- confirmValue("Converts hex 3DA408B9 to decimal (-165)", "3DA408B9", "1034160313");
- }
+ void testBasic() {
+ confirmValue("Converts hex 'A5' to decimal (165)", "A5", "165");
+ confirmValue("Converts hex FFFFFFFF5B to decimal (-165)", "FFFFFFFF5B", "-165");
+ confirmValue("Converts hex 3DA408B9 to decimal (-165)", "3DA408B9", "1034160313");
+ }
@Test
void testErrors() {
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndex.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndex.java?rev=1890120&r1=1890119&r2=1890120&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndex.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIndex.java Sat May 22 20:56:44 2021
@@ -47,274 +47,274 @@ import org.junit.jupiter.api.Test;
*/
final class TestIndex {
- private static final Index FUNC_INST = new Index();
- private static final double[] TEST_VALUES0 = {
- 1, 2,
- 3, 4,
- 5, 6,
- 7, 8,
- 9, 10,
- 11, 12,
- };
-
- /**
- * For the case when the first argument to INDEX() is an area reference
- */
- @Test
- void testEvaluateAreaReference() {
-
- double[] values = TEST_VALUES0;
- confirmAreaEval("C1:D6", values, 4, 1, 7);
- confirmAreaEval("C1:D6", values, 6, 2, 12);
- confirmAreaEval("C1:D6", values, 3, 1, 5);
-
- // now treat same data as 3 columns, 4 rows
- confirmAreaEval("C10:E13", values, 2, 2, 5);
- confirmAreaEval("C10:E13", values, 4, 1, 10);
- }
-
- /**
- * @param areaRefString in Excel notation e.g. 'D2:E97'
- * @param dValues array of evaluated values for the area reference
- * @param rowNum 1-based
- * @param colNum 1-based, pass -1 to signify argument not present
- */
- private static void confirmAreaEval(String areaRefString, double[] dValues,
- int rowNum, int colNum, double expectedResult) {
- ValueEval[] values = new ValueEval[dValues.length];
- for (int i = 0; i < values.length; i++) {
- values[i] = new NumberEval(dValues[i]);
- }
- AreaEval arg0 = EvalFactory.createAreaEval(areaRefString, values);
-
- ValueEval[] args;
- if (colNum > 0) {
- args = new ValueEval[] { arg0, new NumberEval(rowNum), new NumberEval(colNum), };
- } else {
- args = new ValueEval[] { arg0, new NumberEval(rowNum), };
- }
-
- double actual = invokeAndDereference(args);
- assertEquals(expectedResult, actual, 0D);
- }
-
- private static double invokeAndDereference(ValueEval[] args) {
- ValueEval ve = FUNC_INST.evaluate(args, -1, -1);
- ve = WorkbookEvaluator.dereferenceResult(ve, -1, -1);
- assertEquals(NumberEval.class, ve.getClass());
- return ((NumberEval)ve).getNumberValue();
- }
-
- /**
- * Tests expressions like "INDEX(A1:C1,,2)".<br>
- * This problem was found while fixing bug 47048 and is observable up to svn r773441.
- */
- @Test
- void testMissingArg() {
- ValueEval[] values = {
- new NumberEval(25.0),
- new NumberEval(26.0),
- new NumberEval(28.0),
- };
- AreaEval arg0 = EvalFactory.createAreaEval("A10:C10", values);
- ValueEval[] args = new ValueEval[] { arg0, MissingArgEval.instance, new NumberEval(2), };
- // Identified bug 47048b - INDEX() should support missing-arg
- ValueEval actualResult = FUNC_INST.evaluate(args, -1, -1);
- // result should be an area eval "B10:B10"
- AreaEval ae = confirmAreaEval("B10:B10", actualResult);
- actualResult = ae.getValue(0, 0);
- assertEquals(NumberEval.class, actualResult.getClass());
- assertEquals(26.0, ((NumberEval)actualResult).getNumberValue(), 0.0);
- }
-
- /**
- * When the argument to INDEX is a reference, the result should be a reference
- * A formula like "OFFSET(INDEX(A1:B2,2,1),1,1,1,1)" should return the value of cell B3.
- * This works because the INDEX() function returns a reference to A2 (not the value of A2)
- */
- @Test
- void testReferenceResult() {
- ValueEval[] values = new ValueEval[4];
- Arrays.fill(values, NumberEval.ZERO);
- AreaEval arg0 = EvalFactory.createAreaEval("A1:B2", values);
- ValueEval[] args = new ValueEval[] { arg0, new NumberEval(2), new NumberEval(1), };
- ValueEval ve = FUNC_INST.evaluate(args, -1, -1);
- confirmAreaEval("A2:A2", ve);
- }
-
- /**
- * Confirms that the result is an area ref with the specified coordinates
- * @return {@code ve} cast to {@link AreaEval} if it is valid
- */
- private static AreaEval confirmAreaEval(String refText, ValueEval ve) {
- CellRangeAddress cra = CellRangeAddress.valueOf(refText);
- assertTrue(ve instanceof AreaEval);
- AreaEval ae = (AreaEval) ve;
- assertEquals(cra.getFirstRow(), ae.getFirstRow());
- assertEquals(cra.getFirstColumn(), ae.getFirstColumn());
- assertEquals(cra.getLastRow(), ae.getLastRow());
- assertEquals(cra.getLastColumn(), ae.getLastColumn());
- return ae;
- }
-
- @Test
- void test61859(){
- Workbook wb = HSSFTestDataSamples.openSampleWorkbook("maxindextest.xls");
- FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
-
- Sheet example1 = wb.getSheetAt(0);
- Cell ex1cell1 = example1.getRow(1).getCell(6);
- assertEquals("MAX(INDEX(($B$2:$B$11=F2)*$A$2:$A$11,0))", ex1cell1.getCellFormula());
- fe.evaluate(ex1cell1);
- assertEquals(4.0, ex1cell1.getNumericCellValue(), 0);
-
- Cell ex1cell2 = example1.getRow(2).getCell(6);
- assertEquals("MAX(INDEX(($B$2:$B$11=F3)*$A$2:$A$11,0))", ex1cell2.getCellFormula());
- fe.evaluate(ex1cell2);
- assertEquals(10.0, ex1cell2.getNumericCellValue(), 0);
-
- Cell ex1cell3 = example1.getRow(3).getCell(6);
- assertEquals("MAX(INDEX(($B$2:$B$11=F4)*$A$2:$A$11,0))", ex1cell3.getCellFormula());
- fe.evaluate(ex1cell3);
- assertEquals(20.0, ex1cell3.getNumericCellValue(), 0);
- }
-
- @Test
- void test61116(){
- Workbook workbook = HSSFTestDataSamples.openSampleWorkbook("61116.xls");
- FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
- Sheet sheet = workbook.getSheet("sample2");
-
- Row row = sheet.getRow(1);
- assertEquals(3.0, evaluator.evaluate(row.getCell(1)).getNumberValue(), 0);
- assertEquals(3.0, evaluator.evaluate(row.getCell(2)).getNumberValue(), 0);
-
- row = sheet.getRow(2);
- assertEquals(5.0, evaluator.evaluate(row.getCell(1)).getNumberValue(), 0);
- assertEquals(5.0, evaluator.evaluate(row.getCell(2)).getNumberValue(), 0);
- }
-
- /**
- * If both the Row_num and Column_num arguments are used,
- * INDEX returns the value in the cell at the intersection of Row_num and Column_num
- */
- @Test
- void testReference2DArea(){
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet();
- /*
- * 1 2 3
- * 4 5 6
- * 7 8 9
- */
- int val = 0;
- for(int i = 0; i < 3; i++){
- Row row = sheet.createRow(i);
- for(int j = 0; j < 3; j++){
- row.createCell(j).setCellValue(++val);
- }
- }
- FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
-
- Cell c1 = sheet.getRow(0).createCell(5);
- c1.setCellFormula("INDEX(A1:C3,2,2)");
- Cell c2 = sheet.getRow(0).createCell(6);
- c2.setCellFormula("INDEX(A1:C3,3,2)");
-
- assertEquals(5.0, fe.evaluate(c1).getNumberValue(), 0);
- assertEquals(8.0, fe.evaluate(c2).getNumberValue(), 0);
- }
-
- /**
- * If Column_num is 0 (zero), INDEX returns the array of values for the entire row.
- */
- @Test
- void testArrayArgument_RowLookup(){
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet();
- /*
- * 1 2 3
- * 4 5 6
- * 7 8 9
- */
- int val = 0;
- for(int i = 0; i < 3; i++){
- Row row = sheet.createRow(i);
- for(int j = 0; j < 3; j++){
- row.createCell(j).setCellValue(++val);
- }
- }
- Cell c1 = sheet.getRow(0).createCell(5);
- c1.setCellFormula("SUM(INDEX(A1:C3,1,0))"); // sum of all values in the 1st row: 1 + 2 + 3 = 6
-
- Cell c2 = sheet.getRow(0).createCell(6);
- c2.setCellFormula("SUM(INDEX(A1:C3,2,0))"); // sum of all values in the 2nd row: 4 + 5 + 6 = 15
-
- FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
-
- assertEquals(6.0, fe.evaluate(c1).getNumberValue(), 0);
- assertEquals(15.0, fe.evaluate(c2).getNumberValue(), 0);
-
- }
-
- /**
- * If Row_num is 0 (zero), INDEX returns the array of values for the entire column.
- */
- @Test
- void testArrayArgument_ColumnLookup(){
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet();
- /*
- * 1 2 3
- * 4 5 6
- * 7 8 9
- */
- int val = 0;
- for(int i = 0; i < 3; i++){
- Row row = sheet.createRow(i);
- for(int j = 0; j < 3; j++){
- row.createCell(j).setCellValue(++val);
- }
- }
- Cell c1 = sheet.getRow(0).createCell(5);
- c1.setCellFormula("SUM(INDEX(A1:C3,0,1))"); // sum of all values in the 1st column: 1 + 4 + 7 = 12
-
- Cell c2 = sheet.getRow(0).createCell(6);
- c2.setCellFormula("SUM(INDEX(A1:C3,0,3))"); // sum of all values in the 3rd column: 3 + 6 + 9 = 18
-
- FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
-
- assertEquals(12.0, fe.evaluate(c1).getNumberValue(), 0);
- assertEquals(18.0, fe.evaluate(c2).getNumberValue(), 0);
- }
-
- /**
- * =SUM(B1:INDEX(B1:B3,2))
- *
- * The sum of the range starting at B1, and ending at the intersection of the 2nd row of the range B1:B3,
- * which is the sum of B1:B2.
- */
- @Test
- void testDynamicReference(){
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet();
- /*
- * 1 2 3
- * 4 5 6
- * 7 8 9
- */
- int val = 0;
- for(int i = 0; i < 3; i++){
- Row row = sheet.createRow(i);
- for(int j = 0; j < 3; j++){
- row.createCell(j).setCellValue(++val);
- }
- }
- Cell c1 = sheet.getRow(0).createCell(5);
- c1.setCellFormula("SUM(B1:INDEX(B1:B3,2))"); // B1:INDEX(B1:B3,2) evaluates to B1:B2
+ private static final Index FUNC_INST = new Index();
+ private static final double[] TEST_VALUES0 = {
+ 1, 2,
+ 3, 4,
+ 5, 6,
+ 7, 8,
+ 9, 10,
+ 11, 12,
+ };
+
+ /**
+ * For the case when the first argument to INDEX() is an area reference
+ */
+ @Test
+ void testEvaluateAreaReference() {
+
+ double[] values = TEST_VALUES0;
+ confirmAreaEval("C1:D6", values, 4, 1, 7);
+ confirmAreaEval("C1:D6", values, 6, 2, 12);
+ confirmAreaEval("C1:D6", values, 3, 1, 5);
+
+ // now treat same data as 3 columns, 4 rows
+ confirmAreaEval("C10:E13", values, 2, 2, 5);
+ confirmAreaEval("C10:E13", values, 4, 1, 10);
+ }
+
+ /**
+ * @param areaRefString in Excel notation e.g. 'D2:E97'
+ * @param dValues array of evaluated values for the area reference
+ * @param rowNum 1-based
+ * @param colNum 1-based, pass -1 to signify argument not present
+ */
+ private static void confirmAreaEval(String areaRefString, double[] dValues,
+ int rowNum, int colNum, double expectedResult) {
+ ValueEval[] values = new ValueEval[dValues.length];
+ for (int i = 0; i < values.length; i++) {
+ values[i] = new NumberEval(dValues[i]);
+ }
+ AreaEval arg0 = EvalFactory.createAreaEval(areaRefString, values);
+
+ ValueEval[] args;
+ if (colNum > 0) {
+ args = new ValueEval[] { arg0, new NumberEval(rowNum), new NumberEval(colNum), };
+ } else {
+ args = new ValueEval[] { arg0, new NumberEval(rowNum), };
+ }
+
+ double actual = invokeAndDereference(args);
+ assertEquals(expectedResult, actual, 0D);
+ }
+
+ private static double invokeAndDereference(ValueEval[] args) {
+ ValueEval ve = FUNC_INST.evaluate(args, -1, -1);
+ ve = WorkbookEvaluator.dereferenceResult(ve, -1, -1);
+ assertEquals(NumberEval.class, ve.getClass());
+ return ((NumberEval)ve).getNumberValue();
+ }
+
+ /**
+ * Tests expressions like "INDEX(A1:C1,,2)".<br>
+ * This problem was found while fixing bug 47048 and is observable up to svn r773441.
+ */
+ @Test
+ void testMissingArg() {
+ ValueEval[] values = {
+ new NumberEval(25.0),
+ new NumberEval(26.0),
+ new NumberEval(28.0),
+ };
+ AreaEval arg0 = EvalFactory.createAreaEval("A10:C10", values);
+ ValueEval[] args = new ValueEval[] { arg0, MissingArgEval.instance, new NumberEval(2), };
+ // Identified bug 47048b - INDEX() should support missing-arg
+ ValueEval actualResult = FUNC_INST.evaluate(args, -1, -1);
+ // result should be an area eval "B10:B10"
+ AreaEval ae = confirmAreaEval("B10:B10", actualResult);
+ actualResult = ae.getValue(0, 0);
+ assertEquals(NumberEval.class, actualResult.getClass());
+ assertEquals(26.0, ((NumberEval)actualResult).getNumberValue(), 0.0);
+ }
+
+ /**
+ * When the argument to INDEX is a reference, the result should be a reference
+ * A formula like "OFFSET(INDEX(A1:B2,2,1),1,1,1,1)" should return the value of cell B3.
+ * This works because the INDEX() function returns a reference to A2 (not the value of A2)
+ */
+ @Test
+ void testReferenceResult() {
+ ValueEval[] values = new ValueEval[4];
+ Arrays.fill(values, NumberEval.ZERO);
+ AreaEval arg0 = EvalFactory.createAreaEval("A1:B2", values);
+ ValueEval[] args = new ValueEval[] { arg0, new NumberEval(2), new NumberEval(1), };
+ ValueEval ve = FUNC_INST.evaluate(args, -1, -1);
+ confirmAreaEval("A2:A2", ve);
+ }
+
+ /**
+ * Confirms that the result is an area ref with the specified coordinates
+ * @return {@code ve} cast to {@link AreaEval} if it is valid
+ */
+ private static AreaEval confirmAreaEval(String refText, ValueEval ve) {
+ CellRangeAddress cra = CellRangeAddress.valueOf(refText);
+ assertTrue(ve instanceof AreaEval);
+ AreaEval ae = (AreaEval) ve;
+ assertEquals(cra.getFirstRow(), ae.getFirstRow());
+ assertEquals(cra.getFirstColumn(), ae.getFirstColumn());
+ assertEquals(cra.getLastRow(), ae.getLastRow());
+ assertEquals(cra.getLastColumn(), ae.getLastColumn());
+ return ae;
+ }
+
+ @Test
+ void test61859(){
+ Workbook wb = HSSFTestDataSamples.openSampleWorkbook("maxindextest.xls");
+ FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+
+ Sheet example1 = wb.getSheetAt(0);
+ Cell ex1cell1 = example1.getRow(1).getCell(6);
+ assertEquals("MAX(INDEX(($B$2:$B$11=F2)*$A$2:$A$11,0))", ex1cell1.getCellFormula());
+ fe.evaluate(ex1cell1);
+ assertEquals(4.0, ex1cell1.getNumericCellValue(), 0);
+
+ Cell ex1cell2 = example1.getRow(2).getCell(6);
+ assertEquals("MAX(INDEX(($B$2:$B$11=F3)*$A$2:$A$11,0))", ex1cell2.getCellFormula());
+ fe.evaluate(ex1cell2);
+ assertEquals(10.0, ex1cell2.getNumericCellValue(), 0);
+
+ Cell ex1cell3 = example1.getRow(3).getCell(6);
+ assertEquals("MAX(INDEX(($B$2:$B$11=F4)*$A$2:$A$11,0))", ex1cell3.getCellFormula());
+ fe.evaluate(ex1cell3);
+ assertEquals(20.0, ex1cell3.getNumericCellValue(), 0);
+ }
+
+ @Test
+ void test61116(){
+ Workbook workbook = HSSFTestDataSamples.openSampleWorkbook("61116.xls");
+ FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
+ Sheet sheet = workbook.getSheet("sample2");
+
+ Row row = sheet.getRow(1);
+ assertEquals(3.0, evaluator.evaluate(row.getCell(1)).getNumberValue(), 0);
+ assertEquals(3.0, evaluator.evaluate(row.getCell(2)).getNumberValue(), 0);
+
+ row = sheet.getRow(2);
+ assertEquals(5.0, evaluator.evaluate(row.getCell(1)).getNumberValue(), 0);
+ assertEquals(5.0, evaluator.evaluate(row.getCell(2)).getNumberValue(), 0);
+ }
+
+ /**
+ * If both the Row_num and Column_num arguments are used,
+ * INDEX returns the value in the cell at the intersection of Row_num and Column_num
+ */
+ @Test
+ void testReference2DArea(){
+ Workbook wb = new HSSFWorkbook();
+ Sheet sheet = wb.createSheet();
+ /*
+ * 1 2 3
+ * 4 5 6
+ * 7 8 9
+ */
+ int val = 0;
+ for(int i = 0; i < 3; i++){
+ Row row = sheet.createRow(i);
+ for(int j = 0; j < 3; j++){
+ row.createCell(j).setCellValue(++val);
+ }
+ }
+ FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+
+ Cell c1 = sheet.getRow(0).createCell(5);
+ c1.setCellFormula("INDEX(A1:C3,2,2)");
+ Cell c2 = sheet.getRow(0).createCell(6);
+ c2.setCellFormula("INDEX(A1:C3,3,2)");
+
+ assertEquals(5.0, fe.evaluate(c1).getNumberValue(), 0);
+ assertEquals(8.0, fe.evaluate(c2).getNumberValue(), 0);
+ }
+
+ /**
+ * If Column_num is 0 (zero), INDEX returns the array of values for the entire row.
+ */
+ @Test
+ void testArrayArgument_RowLookup(){
+ Workbook wb = new HSSFWorkbook();
+ Sheet sheet = wb.createSheet();
+ /*
+ * 1 2 3
+ * 4 5 6
+ * 7 8 9
+ */
+ int val = 0;
+ for(int i = 0; i < 3; i++){
+ Row row = sheet.createRow(i);
+ for(int j = 0; j < 3; j++){
+ row.createCell(j).setCellValue(++val);
+ }
+ }
+ Cell c1 = sheet.getRow(0).createCell(5);
+ c1.setCellFormula("SUM(INDEX(A1:C3,1,0))"); // sum of all values in the 1st row: 1 + 2 + 3 = 6
+
+ Cell c2 = sheet.getRow(0).createCell(6);
+ c2.setCellFormula("SUM(INDEX(A1:C3,2,0))"); // sum of all values in the 2nd row: 4 + 5 + 6 = 15
+
+ FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+
+ assertEquals(6.0, fe.evaluate(c1).getNumberValue(), 0);
+ assertEquals(15.0, fe.evaluate(c2).getNumberValue(), 0);
+
+ }
+
+ /**
+ * If Row_num is 0 (zero), INDEX returns the array of values for the entire column.
+ */
+ @Test
+ void testArrayArgument_ColumnLookup(){
+ Workbook wb = new HSSFWorkbook();
+ Sheet sheet = wb.createSheet();
+ /*
+ * 1 2 3
+ * 4 5 6
+ * 7 8 9
+ */
+ int val = 0;
+ for(int i = 0; i < 3; i++){
+ Row row = sheet.createRow(i);
+ for(int j = 0; j < 3; j++){
+ row.createCell(j).setCellValue(++val);
+ }
+ }
+ Cell c1 = sheet.getRow(0).createCell(5);
+ c1.setCellFormula("SUM(INDEX(A1:C3,0,1))"); // sum of all values in the 1st column: 1 + 4 + 7 = 12
+
+ Cell c2 = sheet.getRow(0).createCell(6);
+ c2.setCellFormula("SUM(INDEX(A1:C3,0,3))"); // sum of all values in the 3rd column: 3 + 6 + 9 = 18
+
+ FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+
+ assertEquals(12.0, fe.evaluate(c1).getNumberValue(), 0);
+ assertEquals(18.0, fe.evaluate(c2).getNumberValue(), 0);
+ }
+
+ /**
+ * =SUM(B1:INDEX(B1:B3,2))
+ *
+ * The sum of the range starting at B1, and ending at the intersection of the 2nd row of the range B1:B3,
+ * which is the sum of B1:B2.
+ */
+ @Test
+ void testDynamicReference(){
+ Workbook wb = new HSSFWorkbook();
+ Sheet sheet = wb.createSheet();
+ /*
+ * 1 2 3
+ * 4 5 6
+ * 7 8 9
+ */
+ int val = 0;
+ for(int i = 0; i < 3; i++){
+ Row row = sheet.createRow(i);
+ for(int j = 0; j < 3; j++){
+ row.createCell(j).setCellValue(++val);
+ }
+ }
+ Cell c1 = sheet.getRow(0).createCell(5);
+ c1.setCellFormula("SUM(B1:INDEX(B1:B3,2))"); // B1:INDEX(B1:B3,2) evaluates to B1:B2
- FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
+ FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
- assertEquals(7.0, fe.evaluate(c1).getNumberValue(), 0);
- }
+ assertEquals(7.0, fe.evaluate(c1).getNumberValue(), 0);
+ }
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIntercept.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIntercept.java?rev=1890120&r1=1890119&r2=1890120&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIntercept.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestIntercept.java Sat May 22 20:56:44 2021
@@ -35,119 +35,119 @@ import org.junit.jupiter.api.Test;
* Test for Excel function INTERCEPT()
*/
final class TestIntercept {
- private static final Function INTERCEPT = new Intercept();
+ private static final Function INTERCEPT = new Intercept();
- private static ValueEval invoke(Function function, ValueEval xArray, ValueEval yArray) {
- ValueEval[] args = new ValueEval[] { xArray, yArray, };
- return function.evaluate(args, -1, (short)-1);
- }
-
- private void confirm(Function function, ValueEval xArray, ValueEval yArray, double expected) {
- ValueEval result = invoke(function, xArray, yArray);
- assertEquals(NumberEval.class, result.getClass());
- assertEquals(expected, ((NumberEval)result).getNumberValue(), 0);
- }
- private void confirmError(Function function, ValueEval xArray, ValueEval yArray, ErrorEval expectedError) {
- ValueEval result = invoke(function, xArray, yArray);
- assertEquals(ErrorEval.class, result.getClass());
- assertEquals(expectedError.getErrorCode(), ((ErrorEval)result).getErrorCode());
- }
-
- private void confirmError(ValueEval xArray, ValueEval yArray, ErrorEval expectedError) {
- confirmError(INTERCEPT, xArray, yArray, expectedError);
- }
-
- @Test
- void testBasic() {
- Double exp = Math.pow(10, 7.5);
- ValueEval[] yValues = {
- new NumberEval(3+exp),
- new NumberEval(4+exp),
- new NumberEval(2+exp),
- new NumberEval(5+exp),
- new NumberEval(4+exp),
- new NumberEval(7+exp),
- };
- ValueEval areaEvalY = createAreaEval(yValues);
-
- ValueEval[] xValues = {
- new NumberEval(1),
- new NumberEval(2),
- new NumberEval(3),
- new NumberEval(4),
- new NumberEval(5),
- new NumberEval(6),
- };
- ValueEval areaEvalX = createAreaEval(xValues);
- confirm(INTERCEPT, areaEvalX, areaEvalY, -24516534.39905822);
- // Excel 2010 gives -24516534.3990583
- }
-
- /**
- * number of items in array is not limited to 30
- */
- @Test
- void testLargeArrays() {
- ValueEval[] yValues = createMockNumberArray(100, 3); // [1,2,0,1,2,0,...,0,1]
- yValues[0] = new NumberEval(2.0); // Changes first element to 2
- ValueEval[] xValues = createMockNumberArray(100, 101); // [1,2,3,4,...,99,100]
-
- confirm(INTERCEPT, createAreaEval(xValues), createAreaEval(yValues), 51.74384236453202);
- // Excel 2010 gives 51.74384236453200
- }
-
- private ValueEval[] createMockNumberArray(int size, double value) {
- ValueEval[] result = new ValueEval[size];
- for (int i = 0; i < result.length; i++) {
- result[i] = new NumberEval((i+1)%value);
- }
- return result;
- }
-
- private static ValueEval createAreaEval(ValueEval[] values) {
- String refStr = "A1:A" + values.length;
- return EvalFactory.createAreaEval(refStr, values);
- }
-
- @Test
- void testErrors() {
- ValueEval[] xValues = {
- ErrorEval.REF_INVALID,
- new NumberEval(2),
- };
- ValueEval areaEvalX = createAreaEval(xValues);
- ValueEval[] yValues = {
- new NumberEval(2),
- ErrorEval.NULL_INTERSECTION,
- };
- ValueEval areaEvalY = createAreaEval(yValues);
- ValueEval[] zValues = { // wrong size
- new NumberEval(2),
- };
- ValueEval areaEvalZ = createAreaEval(zValues);
-
- // if either arg is an error, that error propagates
- confirmError(ErrorEval.REF_INVALID, ErrorEval.NAME_INVALID, ErrorEval.REF_INVALID);
- confirmError(areaEvalX, ErrorEval.NAME_INVALID, ErrorEval.NAME_INVALID);
- confirmError(ErrorEval.NAME_INVALID, areaEvalX, ErrorEval.NAME_INVALID);
-
- // array sizes must match
- confirmError(areaEvalX, areaEvalZ, ErrorEval.NA);
- confirmError(areaEvalZ, areaEvalY, ErrorEval.NA);
-
- // any error in an array item propagates up
- confirmError(areaEvalX, areaEvalX, ErrorEval.REF_INVALID);
-
- // search for errors array by array, not pair by pair
- confirmError(areaEvalX, areaEvalY, ErrorEval.NULL_INTERSECTION);
- confirmError(areaEvalY, areaEvalX, ErrorEval.REF_INVALID);
- }
+ private static ValueEval invoke(Function function, ValueEval xArray, ValueEval yArray) {
+ ValueEval[] args = new ValueEval[] { xArray, yArray, };
+ return function.evaluate(args, -1, (short)-1);
+ }
+
+ private void confirm(Function function, ValueEval xArray, ValueEval yArray, double expected) {
+ ValueEval result = invoke(function, xArray, yArray);
+ assertEquals(NumberEval.class, result.getClass());
+ assertEquals(expected, ((NumberEval)result).getNumberValue(), 0);
+ }
+ private void confirmError(Function function, ValueEval xArray, ValueEval yArray, ErrorEval expectedError) {
+ ValueEval result = invoke(function, xArray, yArray);
+ assertEquals(ErrorEval.class, result.getClass());
+ assertEquals(expectedError.getErrorCode(), ((ErrorEval)result).getErrorCode());
+ }
+
+ private void confirmError(ValueEval xArray, ValueEval yArray, ErrorEval expectedError) {
+ confirmError(INTERCEPT, xArray, yArray, expectedError);
+ }
+
+ @Test
+ void testBasic() {
+ Double exp = Math.pow(10, 7.5);
+ ValueEval[] yValues = {
+ new NumberEval(3+exp),
+ new NumberEval(4+exp),
+ new NumberEval(2+exp),
+ new NumberEval(5+exp),
+ new NumberEval(4+exp),
+ new NumberEval(7+exp),
+ };
+ ValueEval areaEvalY = createAreaEval(yValues);
+
+ ValueEval[] xValues = {
+ new NumberEval(1),
+ new NumberEval(2),
+ new NumberEval(3),
+ new NumberEval(4),
+ new NumberEval(5),
+ new NumberEval(6),
+ };
+ ValueEval areaEvalX = createAreaEval(xValues);
+ confirm(INTERCEPT, areaEvalX, areaEvalY, -24516534.39905822);
+ // Excel 2010 gives -24516534.3990583
+ }
+
+ /**
+ * number of items in array is not limited to 30
+ */
+ @Test
+ void testLargeArrays() {
+ ValueEval[] yValues = createMockNumberArray(100, 3); // [1,2,0,1,2,0,...,0,1]
+ yValues[0] = new NumberEval(2.0); // Changes first element to 2
+ ValueEval[] xValues = createMockNumberArray(100, 101); // [1,2,3,4,...,99,100]
+
+ confirm(INTERCEPT, createAreaEval(xValues), createAreaEval(yValues), 51.74384236453202);
+ // Excel 2010 gives 51.74384236453200
+ }
+
+ private ValueEval[] createMockNumberArray(int size, double value) {
+ ValueEval[] result = new ValueEval[size];
+ for (int i = 0; i < result.length; i++) {
+ result[i] = new NumberEval((i+1)%value);
+ }
+ return result;
+ }
+
+ private static ValueEval createAreaEval(ValueEval[] values) {
+ String refStr = "A1:A" + values.length;
+ return EvalFactory.createAreaEval(refStr, values);
+ }
+
+ @Test
+ void testErrors() {
+ ValueEval[] xValues = {
+ ErrorEval.REF_INVALID,
+ new NumberEval(2),
+ };
+ ValueEval areaEvalX = createAreaEval(xValues);
+ ValueEval[] yValues = {
+ new NumberEval(2),
+ ErrorEval.NULL_INTERSECTION,
+ };
+ ValueEval areaEvalY = createAreaEval(yValues);
+ ValueEval[] zValues = { // wrong size
+ new NumberEval(2),
+ };
+ ValueEval areaEvalZ = createAreaEval(zValues);
+
+ // if either arg is an error, that error propagates
+ confirmError(ErrorEval.REF_INVALID, ErrorEval.NAME_INVALID, ErrorEval.REF_INVALID);
+ confirmError(areaEvalX, ErrorEval.NAME_INVALID, ErrorEval.NAME_INVALID);
+ confirmError(ErrorEval.NAME_INVALID, areaEvalX, ErrorEval.NAME_INVALID);
+
+ // array sizes must match
+ confirmError(areaEvalX, areaEvalZ, ErrorEval.NA);
+ confirmError(areaEvalZ, areaEvalY, ErrorEval.NA);
+
+ // any error in an array item propagates up
+ confirmError(areaEvalX, areaEvalX, ErrorEval.REF_INVALID);
+
+ // search for errors array by array, not pair by pair
+ confirmError(areaEvalX, areaEvalY, ErrorEval.NULL_INTERSECTION);
+ confirmError(areaEvalY, areaEvalX, ErrorEval.REF_INVALID);
+ }
/**
* Example from
* http://office.microsoft.com/en-us/excel-help/intercept-function-HP010062512.aspx?CTT=5&origin=HA010277524
*/
- @Test
+ @Test
void testFromFile() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("intercept.xls");
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestLeftRight.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestLeftRight.java?rev=1890120&r1=1890119&r2=1890120&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestLeftRight.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestLeftRight.java Sat May 22 20:56:44 2021
@@ -32,29 +32,29 @@ import org.junit.jupiter.api.Test;
*/
class TestLeftRight {
- private static final NumberEval NEGATIVE_OPERAND = new NumberEval(-1.0);
- private static final StringEval ANY_STRING_VALUE = new StringEval("ANYSTRINGVALUE");
+ private static final NumberEval NEGATIVE_OPERAND = new NumberEval(-1.0);
+ private static final StringEval ANY_STRING_VALUE = new StringEval("ANYSTRINGVALUE");
- private static ValueEval invokeLeft(ValueEval text, ValueEval operand) {
- ValueEval[] args = new ValueEval[] { text, operand };
- return TextFunction.LEFT.evaluate(args, -1, (short)-1);
- }
-
- private static ValueEval invokeRight(ValueEval text, ValueEval operand) {
- ValueEval[] args = new ValueEval[] { text, operand };
- return TextFunction.RIGHT.evaluate(args, -1, (short)-1);
- }
-
- @Test
- void testLeftRight_bug49841() {
- assertEquals(ErrorEval.VALUE_INVALID, invokeLeft(ANY_STRING_VALUE, NEGATIVE_OPERAND));
- assertEquals(ErrorEval.VALUE_INVALID, invokeRight(ANY_STRING_VALUE, NEGATIVE_OPERAND));
- }
-
- @Test
- void testLeftRightNegativeOperand() {
- assertEquals(ErrorEval.VALUE_INVALID, invokeRight(ANY_STRING_VALUE, NEGATIVE_OPERAND));
- assertEquals(ErrorEval.VALUE_INVALID, invokeLeft(ANY_STRING_VALUE, NEGATIVE_OPERAND));
- }
+ private static ValueEval invokeLeft(ValueEval text, ValueEval operand) {
+ ValueEval[] args = new ValueEval[] { text, operand };
+ return TextFunction.LEFT.evaluate(args, -1, (short)-1);
+ }
+
+ private static ValueEval invokeRight(ValueEval text, ValueEval operand) {
+ ValueEval[] args = new ValueEval[] { text, operand };
+ return TextFunction.RIGHT.evaluate(args, -1, (short)-1);
+ }
+
+ @Test
+ void testLeftRight_bug49841() {
+ assertEquals(ErrorEval.VALUE_INVALID, invokeLeft(ANY_STRING_VALUE, NEGATIVE_OPERAND));
+ assertEquals(ErrorEval.VALUE_INVALID, invokeRight(ANY_STRING_VALUE, NEGATIVE_OPERAND));
+ }
+
+ @Test
+ void testLeftRightNegativeOperand() {
+ assertEquals(ErrorEval.VALUE_INVALID, invokeRight(ANY_STRING_VALUE, NEGATIVE_OPERAND));
+ assertEquals(ErrorEval.VALUE_INVALID, invokeLeft(ANY_STRING_VALUE, NEGATIVE_OPERAND));
+ }
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestLen.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestLen.java?rev=1890120&r1=1890119&r2=1890120&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestLen.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestLen.java Sat May 22 20:56:44 2021
@@ -32,43 +32,43 @@ import org.junit.jupiter.api.Test;
*/
final class TestLen {
- private static ValueEval invokeLen(ValueEval text) {
- ValueEval[] args = new ValueEval[] { text, };
- return TextFunction.LEN.evaluate(args, -1, (short)-1);
- }
-
- private void confirmLen(ValueEval text, int expected) {
- ValueEval result = invokeLen(text);
- assertEquals(NumberEval.class, result.getClass());
- assertEquals(expected, ((NumberEval)result).getNumberValue(), 0);
- }
-
- private void confirmLen(ValueEval text, ErrorEval expectedError) {
- ValueEval result = invokeLen(text);
- assertEquals(ErrorEval.class, result.getClass());
- assertEquals(expectedError.getErrorCode(), ((ErrorEval)result).getErrorCode());
- }
-
- @Test
- void testBasic() {
- confirmLen(new StringEval("galactic"), 8);
- }
-
- /**
- * Valid cases where text arg is not exactly a string
- */
- @Test
- void testUnusualArgs() {
-
- // text (first) arg type is number, other args are strings with fractional digits
- confirmLen(new NumberEval(123456), 6);
- confirmLen(BoolEval.FALSE, 5);
- confirmLen(BoolEval.TRUE, 4);
- confirmLen(BlankEval.instance, 0);
- }
-
- @Test
- void testErrors() {
- confirmLen(ErrorEval.NAME_INVALID, ErrorEval.NAME_INVALID);
- }
+ private static ValueEval invokeLen(ValueEval text) {
+ ValueEval[] args = new ValueEval[] { text, };
+ return TextFunction.LEN.evaluate(args, -1, (short)-1);
+ }
+
+ private void confirmLen(ValueEval text, int expected) {
+ ValueEval result = invokeLen(text);
+ assertEquals(NumberEval.class, result.getClass());
+ assertEquals(expected, ((NumberEval)result).getNumberValue(), 0);
+ }
+
+ private void confirmLen(ValueEval text, ErrorEval expectedError) {
+ ValueEval result = invokeLen(text);
+ assertEquals(ErrorEval.class, result.getClass());
+ assertEquals(expectedError.getErrorCode(), ((ErrorEval)result).getErrorCode());
+ }
+
+ @Test
+ void testBasic() {
+ confirmLen(new StringEval("galactic"), 8);
+ }
+
+ /**
+ * Valid cases where text arg is not exactly a string
+ */
+ @Test
+ void testUnusualArgs() {
+
+ // text (first) arg type is number, other args are strings with fractional digits
+ confirmLen(new NumberEval(123456), 6);
+ confirmLen(BoolEval.FALSE, 5);
+ confirmLen(BoolEval.TRUE, 4);
+ confirmLen(BlankEval.instance, 0);
+ }
+
+ @Test
+ void testErrors() {
+ confirmLen(ErrorEval.NAME_INVALID, ErrorEval.NAME_INVALID);
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org