Author: fanningpj
Date: Fri Feb 25 12:17:51 2022
New Revision: 1898413
URL: http://svn.apache.org/viewvc?rev=1898413&view=rev
Log:
add tests for 'OR' function
Added:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestOrFunction.java
- copied, changed from r1898397,
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDollarFr.java
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java
Copied:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestOrFunction.java
(from r1898397,
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDollarFr.java)
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestOrFunction.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestOrFunction.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDollarFr.java&r1=1898397&r2=1898413&rev=1898413&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDollarFr.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestOrFunction.java
Fri Feb 25 12:17:51 2022
@@ -23,84 +23,83 @@ import org.apache.poi.hssf.usermodel.HSS
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.OperationEvaluationContext;
-import org.apache.poi.ss.formula.eval.ErrorEval;
-import org.apache.poi.ss.formula.eval.NumberEval;
-import org.apache.poi.ss.formula.eval.StringEval;
-import org.apache.poi.ss.formula.eval.ValueEval;
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.assertBoolean;
import static org.apache.poi.ss.util.Utils.assertDouble;
-import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.apache.poi.ss.util.Utils.assertString;
/**
- * Tests for {@link DollarFr}
+ * Tests for {@link BooleanFunction#OR}
*/
-final class TestDollarFr {
+final class TestOrFunction {
private static final OperationEvaluationContext ec = new
OperationEvaluationContext(null, null, 0, 0, 0, null);
@Test
- void testInvalid() {
- confirmInvalidError("A1","B2");
- }
-
- @Test
- void testNumError() {
- confirmNumError("22.5","-40");
- }
-
- @Test
- void testDiv0() {
- confirmDiv0("22.5","0");
- confirmDiv0("22.5","0.9");
- confirmDiv0("22.5","-0.9");
- }
-
-
//https://support.microsoft.com/en-us/office/dollarfr-function-0835d163-3023-4a33-9824-3042c5d4f495
- @Test
- void testMicrosoftExample1() throws IOException {
+ void testMicrosoftExample0() throws IOException {
+
//https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0
try (HSSFWorkbook wb = new HSSFWorkbook()) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
- HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = row.createCell(0);
- double tolerance = 0.000000000000001;
- assertDouble(fe, cell, "DOLLARFR(1.125,16)", 1.02, tolerance);
- assertDouble(fe, cell, "DOLLARFR(-1.125,16)", -1.02, tolerance);
- assertDouble(fe, cell, "DOLLARFR(1.000125,16)", 1.00002,
tolerance);
- assertDouble(fe, cell, "DOLLARFR(1.125,32)", 1.04, tolerance);
+ assertBoolean(fe, cell, "OR(TRUE,TRUE)", true);
+ assertBoolean(fe, cell, "OR(TRUE,FALSE)", true);
+ assertBoolean(fe, cell, "OR(1=1,2=2,3=3)", true);
+ assertBoolean(fe, cell, "OR(1=2,2=3,3=4)", false);
}
}
- private static ValueEval invokeValue(String number1, String number2) {
- ValueEval[] args = new ValueEval[] { new StringEval(number1), new
StringEval(number2) };
- return DollarDe.instance.evaluate(args, ec);
- }
-
- 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.00000000000001);
- }
-
- private static void confirmInvalidError(String number1, String number2) {
- ValueEval result = invokeValue(number1, number2);
- assertEquals(ErrorEval.class, result.getClass());
- assertEquals(ErrorEval.VALUE_INVALID, result);
+ @Test
+ void testMicrosoftExample1() throws IOException {
+
//https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0
+ try (HSSFWorkbook wb = initWorkbook1()) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+ assertBoolean(fe, cell, "OR(A2>1,A2<100)", true);
+ assertDouble(fe, cell, "IF(OR(A2>1,A2<100),A3,\"The value is out
of range\")", 100);
+ assertString(fe, cell, "IF(OR(A2<0,A2>50),A2,\"The value is out of
range\")", "The value is out of range");
+ }
}
- private static void confirmNumError(String number1, String number2) {
- ValueEval result = invokeValue(number1, number2);
- assertEquals(ErrorEval.class, result.getClass());
- assertEquals(ErrorEval.NUM_ERROR, result);
+ @Test
+ void testMicrosoftExample2() throws IOException {
+
//https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0
+ try (HSSFWorkbook wb = initWorkbook2()) {
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+ HSSFCell cell = wb.getSheetAt(0).getRow(13).createCell(3);
+ assertDouble(fe, cell, "IF(OR(B14>=$B$4,C14>=$B$5),B14*$B$6,0)",
314);
+ }
}
- private static void confirmDiv0(String number1, String number2) {
- ValueEval result = invokeValue(number1, number2);
- assertEquals(ErrorEval.class, result.getClass());
- assertEquals(ErrorEval.DIV_ZERO, result);
+ private HSSFWorkbook initWorkbook1() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet();
+ addRow(sheet, 0, "Values");
+ addRow(sheet, 1, 50);
+ addRow(sheet, 2, 100);
+ return wb;
+ }
+
+ private HSSFWorkbook initWorkbook2() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet();
+ addRow(sheet, 0, "Goals");
+ addRow(sheet, 2, "Criteria", "Amount");
+ addRow(sheet, 3, "Sales Goal", 8500);
+ addRow(sheet, 4, "Account Goal", 5);
+ addRow(sheet, 5, "Commission Rate", 0.02);
+ addRow(sheet, 6, "Bonus Goal", 12500);
+ addRow(sheet, 7, "Bonus %", 0.015);
+ addRow(sheet, 9, "Commission Calculations");
+ addRow(sheet, 11, "Salesperson", "Total Sales", "Accounts",
"Commission", "Bonus");
+ addRow(sheet, 12, "Millicent Shelton", 10260, 9);
+ addRow(sheet, 13, "Miguel Ferrari", 15700, 7);
+ return wb;
}
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java?rev=1898413&r1=1898412&r2=1898413&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.java Fri Feb 25
12:17:51 2022
@@ -80,6 +80,14 @@ public class Utils {
assertEquals(expectedResult, result.getNumberValue(), tolerance);
}
+ public static void assertBoolean(FormulaEvaluator fe, Cell cell, String
formulaText, boolean expectedResult) {
+ cell.setCellFormula(formulaText);
+ fe.notifyUpdateCell(cell);
+ CellValue result = fe.evaluate(cell);
+ assertEquals(CellType.BOOLEAN, result.getCellType());
+ assertEquals(expectedResult, result.getBooleanValue());
+ }
+
public static void assertError(FormulaEvaluator fe, Cell cell, String
formulaText, FormulaError expectedError) {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
