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 23:23:39 UTC
svn commit: r1892096 - 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/
test/java/org/apache/poi/ss/util/
Author: fanningpj
Date: Sat Aug 7 23:23:39 2021
New Revision: 1892096
URL: http://svn.apache.org/viewvc?rev=1892096&view=rev
Log:
init support for XLOOKUP
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.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/TestXLookupFunction.java
- copied, changed from r1892070, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.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/atp/TestTextJoinFunction.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAddress.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAreas.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestClean.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestConcat.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFind.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/util/Utils.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=1892096&r1=1892095&r2=1892096&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 23:23:39 2021
@@ -175,6 +175,7 @@ public final class AnalysisToolPak imple
r(m, "WEEKNUM", WeekNum.instance);
r(m, "WORKDAY", WorkdayFunction.instance);
r(m, "XIRR", null);
+ r(m, "XLOOKUP", XLookupFunction.instance);
r(m, "XNPV", null);
r(m, "YEARFRAC", YearFrac.instance);
r(m, "YIELD", null);
Copied: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.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/XLookupFunction.java?p2=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/XLookupFunction.java&p1=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/TextJoinFunction.java&r1=1892070&r2=1892096&rev=1892096&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/XLookupFunction.java Sat Aug 7 23:23:39 2021
@@ -21,127 +21,65 @@ import org.apache.poi.ss.formula.Operati
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;
-
/**
- * Implementation of Excel function TEXTJOIN()
+ * Implementation of Excel function XLOOKUP()
*
* <b>Syntax</b><br>
- * <b>TEXTJOIN</b>(<b>delimiter</b>, <b>ignore_empty</b>, <b>text1</b>, <b>[text2]<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>XLOOKUP</b><p>
*
* @since POI 5.0.1
*/
-final class TextJoinFunction implements FreeRefFunction {
+final class XLookupFunction implements FreeRefFunction {
- public static final FreeRefFunction instance = new TextJoinFunction(ArgumentsEvaluator.instance);
+ public static final FreeRefFunction instance = new XLookupFunction(ArgumentsEvaluator.instance);
private ArgumentsEvaluator evaluator;
- private TextJoinFunction(ArgumentsEvaluator anEvaluator) {
+ private XLookupFunction(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();
+ if (args.length < 3) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2]);
+ }
+ private ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval,
+ ValueEval valueEval) {
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);
- }
+ ValueEval lookupValue = OperandResolver.getSingleValue(lookupEval, srcRowIndex, srcColumnIndex);
+ String lookup = OperandResolver.coerceValueToString(lookupValue);
+ int matchedRow = matchedIndex(indexEval, lookup);
+ if (matchedRow != -1) {
+ if (valueEval instanceof AreaEval) {
+ AreaEval area = (AreaEval)valueEval;
+ return area.getRelativeValue(matchedRow, 0);
}
}
-
- // 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 ErrorEval.NUM_ERROR;
+ } catch (EvaluationException e) {
return e.getErrorEval();
}
}
- private String coerceValueToString(ValueEval eval) {
- return (eval instanceof MissingArgEval) ? "" : OperandResolver.coerceValueToString(eval);
- }
-
- //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));
+ private int matchedIndex(ValueEval areaEval, String lookup) {
+ if (areaEval instanceof AreaEval) {
+ AreaEval area = (AreaEval)areaEval;
+ for (int r = 0; r <= area.getHeight(); r++) {
+ for (int c = 0; c <= area.getWidth(); c++) {
+ ValueEval cellEval = area.getRelativeValue(r, c);
+ String cellValue = OperandResolver.coerceValueToString(cellEval);
+ if (lookup.equals(cellValue)) {
+ return r;
+ }
}
}
- return list;
- } else {
- return Collections.singletonList(OperandResolver.getSingleValue(eval, srcRowIndex, srcColumnIndex));
}
+ return -1;
}
-
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java?rev=1892096&r1=1892095&r2=1892096&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java Sat Aug 7 23:23:39 2021
@@ -41,11 +41,9 @@ public class TestTextJoinFunction {
private FormulaEvaluator evaluator;
private Cell textCell1;
private Cell textCell2;
- private Cell textCell3;
private Cell numericCell1;
private Cell numericCell2;
private Cell blankCell;
- private Cell emptyCell;
private Cell formulaCell;
@BeforeEach
@@ -250,7 +248,7 @@ public class TestTextJoinFunction {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.STRING);
+ assertEquals(CellType.STRING, result.getCellType());
assertEquals(expectedResult, result.getStringValue());
}
}
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java (from r1892070, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java&r1=1892070&r2=1892096&rev=1892096&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestTextJoinFunction.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/atp/TestXLookupFunction.java Sat Aug 7 23:23:39 2021
@@ -21,9 +21,7 @@ import org.apache.poi.hssf.usermodel.HSS
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.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.*;
-import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import java.io.IOException;
@@ -32,217 +30,34 @@ import static org.apache.poi.ss.util.Uti
import static org.junit.jupiter.api.Assertions.assertEquals;
/**
- * Testcase for function TEXTJOIN()
+ * Testcase for function XLOOKUP()
*/
-public class TestTextJoinFunction {
-
- private Workbook wb;
- private Sheet sheet;
- private FormulaEvaluator evaluator;
- private Cell textCell1;
- private Cell textCell2;
- private Cell textCell3;
- private Cell numericCell1;
- private Cell numericCell2;
- private Cell blankCell;
- private Cell emptyCell;
- private Cell formulaCell;
-
- @BeforeEach
- public void setUp() throws Exception {
- wb = new HSSFWorkbook();
- evaluator = wb.getCreationHelper().createFormulaEvaluator();
-
- sheet = wb.createSheet("TextJoin");
- Row row = sheet.createRow(0);
-
- textCell1 = row.createCell(0);
- textCell1.setCellValue("One");
-
- textCell2 = row.createCell(1);
- textCell2.setCellValue("Two");
-
- blankCell = row.createCell(2);
- blankCell.setBlank();
-
- numericCell1 = row.createCell(3);
- numericCell1.setCellValue(1);
-
- numericCell2 = row.createCell(4);
- numericCell2.setCellValue(2);
-
- formulaCell = row.createCell(100, CellType.FORMULA);
- }
-
- @Test
- public void testJoinSingleLiteralText() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", true, \"Text\")");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals("Text", formulaCell.getStringCellValue());
- }
-
- @Test
- public void testJoinMultipleLiteralText() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", true, \"One\", \"Two\", \"Three\")");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals("One,Two,Three", formulaCell.getStringCellValue());
- }
-
- @Test
- public void testJoinLiteralTextAndNumber() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", true, \"Text\", 1)");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals("Text,1", formulaCell.getStringCellValue());
- }
-
- @Test
- public void testJoinEmptyStringIncludeEmpty() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", false, \"A\", \"\", \"B\")");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals("A,,B", formulaCell.getStringCellValue());
- }
-
- @Test
- public void testJoinEmptyStringIgnoreEmpty() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", true, \"A\", \"\", \"B\")");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals("A,B", formulaCell.getStringCellValue());
- }
-
- @Test
- public void testJoinEmptyStringsIncludeEmpty() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", false, \"\", \"\")");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals(",", formulaCell.getStringCellValue());
- }
-
- @Test
- public void testJoinEmptyStringsIgnoreEmpty() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", true, \"\", \"\")");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals("", formulaCell.getStringCellValue());
- }
-
- @Test
- public void testJoinTextCellValues() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", true, A1, B1)");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals("One,Two", formulaCell.getStringCellValue());
- }
-
- @Test
- public void testJoinNumericCellValues() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", true, D1, E1)");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals("1,2", formulaCell.getStringCellValue());
- }
+public class TestXLookupFunction {
- @Test
- public void testJoinBlankCellIncludeEmpty() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", false, A1, C1, B1)");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals("One,,Two", formulaCell.getStringCellValue());
- }
-
- @Test
- public void testJoinBlankCellIgnoreEmpty() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", true, A1, C1, B1)");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals("One,Two", formulaCell.getStringCellValue());
- }
-
- @Test
- public void testNoTextArgument() {
- evaluator.clearAllCachedResultValues();
- formulaCell.setCellFormula("TEXTJOIN(\",\", true)");
- evaluator.evaluateFormulaCell(formulaCell);
- assertEquals(CellType.ERROR, formulaCell.getCachedFormulaResultType());
- assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), formulaCell.getErrorCellValue());
- }
-
- //https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
+ //https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
@Test
void testMicrosoftExample1() throws IOException {
try (HSSFWorkbook wb = initWorkbook1()) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
- confirmResult(fe, cell, "TEXTJOIN(\", \", TRUE, A2:A8)",
- "US Dollar, Australian Dollar, Chinese Yuan, Hong Kong Dollar, Israeli Shekel, South Korean Won, Russian Ruble");
- }
- }
-
- @Test
- void testMicrosoftExample2() throws IOException {
- try (HSSFWorkbook wb = initWorkbook2()) {
- HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
- HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
- confirmResult(fe, cell, "TEXTJOIN(\", \", TRUE, A2:B8)",
- "a1, b1, a2, b2, a4, b4, a5, b5, a6, b6, a7, b7");
- confirmResult(fe, cell, "TEXTJOIN(\", \", FALSE, A2:B8)",
- "a1, b1, a2, b2, , , a4, b4, a5, b5, a6, b6, a7, b7");
- }
- }
-
- @Test
- void testMicrosoftExample3() throws IOException {
- try (HSSFWorkbook wb = initWorkbook3()) {
- HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
- HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
- confirmResult(fe, cell, "TEXTJOIN(A8:D8, TRUE, A2:D7)",
- "Tulsa,OK,74133,US;Seattle,WA,98109,US;Iselin,NJ,08830,US;Fort Lauderdale,FL,33309,US;Tempe,AZ,85285,US;end");
- confirmResult(fe, cell, "TEXTJOIN(, TRUE, A2:D7)",
- "TulsaOK74133USSeattleWA98109USIselinNJ08830USFort LauderdaleFL33309USTempeAZ85285USend");
+ confirmResult(fe, cell, "XLOOKUP(F2,B2:B11,D2:D11)", "+55");
}
}
private HSSFWorkbook initWorkbook1() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
- addRow(sheet, 0, "Currency");
- addRow(sheet, 1, "US Dollar");
- addRow(sheet, 2, "Australian Dollar");
- addRow(sheet, 3, "Chinese Yuan");
- addRow(sheet, 4, "Hong Kong Dollar");
- addRow(sheet, 5, "Israeli Shekel");
- addRow(sheet, 6, "South Korean Won");
- addRow(sheet, 7, "Russian Ruble");
- return wb;
- }
-
- private HSSFWorkbook initWorkbook2() {
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet();
- addRow(sheet, 0, "Aâs", "Bâs");
- for (int i = 1; i <= 7; i++) {
- if (i != 3) {
- addRow(sheet, i, "a" + i, "b" + i);
- }
- }
- return wb;
- }
-
- private HSSFWorkbook initWorkbook3() {
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet();
- addRow(sheet, 0, "City", "State", "Postcode", "Country");
- addRow(sheet, 1, "Tulsa", "OK", "74133", "US");
- addRow(sheet, 2, "Seattle", "WA", "98109", "US");
- addRow(sheet, 3, "Iselin", "NJ", "08830", "US");
- addRow(sheet, 4, "Fort Lauderdale", "FL", "33309", "US");
- addRow(sheet, 5, "Tempe", "AZ", "85285", "US");
- addRow(sheet, 6, "end");
- addRow(sheet, 7, ",", ",", ",", ";");
+ addRow(sheet, 0, null, "Country", "Abr", "Prefix");
+ addRow(sheet, 1, null, "China", "CN", "+86", null, "Brazil");
+ addRow(sheet, 2, null, "India", "IN", "+91");
+ addRow(sheet, 3, null, "United States", "US", "+1");
+ addRow(sheet, 4, null, "Indonesia", "ID", "+62");
+ addRow(sheet, 5, null, "Brazil", "BR", "+55");
+ addRow(sheet, 6, null, "Pakistan", "PK", "+92");
+ addRow(sheet, 7, null, "Nigeria", "NG", "+234");
+ addRow(sheet, 8, null, "Bangladesh", "BD", "+880");
+ addRow(sheet, 9, null, "Russia", "RU", "+7");
+ addRow(sheet, 10, null, "Mexico", "MX", "+52");
return wb;
}
@@ -250,7 +65,7 @@ public class TestTextJoinFunction {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.STRING);
+ assertEquals(CellType.STRING, result.getCellType());
assertEquals(expectedResult, result.getStringValue());
}
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAddress.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAddress.java?rev=1892096&r1=1892095&r2=1892096&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAddress.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAddress.java Sat Aug 7 23:23:39 2021
@@ -75,7 +75,7 @@ final class TestAddress {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.STRING);
+ assertEquals(CellType.STRING, result.getCellType());
assertEquals(expectedResult, result.getStringValue());
}
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAreas.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAreas.java?rev=1892096&r1=1892095&r2=1892096&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAreas.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestAreas.java Sat Aug 7 23:23:39 2021
@@ -54,7 +54,7 @@ final class TestAreas {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.NUMERIC);
+ assertEquals(CellType.NUMERIC, result.getCellType());
assertEquals(expectedResult, result.getNumberValue(), 0);
}
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestClean.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestClean.java?rev=1892096&r1=1892095&r2=1892096&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestClean.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestClean.java Sat Aug 7 23:23:39 2021
@@ -62,7 +62,7 @@ final class TestClean {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.STRING);
+ assertEquals(CellType.STRING, result.getCellType());
assertEquals(expectedResult, result.getStringValue());
}
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestConcat.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestConcat.java?rev=1892096&r1=1892095&r2=1892096&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestConcat.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestConcat.java Sat Aug 7 23:23:39 2021
@@ -100,7 +100,7 @@ final class TestConcat {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.STRING);
+ assertEquals(CellType.STRING, result.getCellType());
assertEquals(expectedResult, result.getStringValue());
}
}
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=1892096&r1=1892095&r2=1892096&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 Aug 7 23:23:39 2021
@@ -17,6 +17,7 @@
package org.apache.poi.ss.formula.functions;
+import static org.apache.poi.ss.util.Utils.assertError;
import static org.junit.jupiter.api.Assertions.assertEquals;
import java.io.IOException;
@@ -51,12 +52,12 @@ final class TestFind {
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);
+ assertError(fe, cell, "find(\"n\", \"haystack\")", FormulaError.VALUE);
+ assertError(fe, cell, "find(\"k\", \"haystack\",9)", FormulaError.VALUE);
+ assertError(fe, cell, "find(\"k\", \"haystack\",#REF!)", FormulaError.REF);
+ assertError(fe, cell, "find(\"k\", \"haystack\",0)", FormulaError.VALUE);
+ assertError(fe, cell, "find(#DIV/0!, #N/A, #REF!)", FormulaError.DIV0);
+ assertError(fe, cell, "find(2, #N/A, #REF!)", FormulaError.NA);
wb.close();
}
@@ -66,16 +67,7 @@ final class TestFind {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.NUMERIC);
+ assertEquals(CellType.NUMERIC, result.getCellType());
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/TestSum.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java?rev=1892096&r1=1892095&r2=1892096&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSum.java Sat Aug 7 23:23:39 2021
@@ -29,6 +29,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.assertError;
import static org.junit.jupiter.api.Assertions.*;
/**
@@ -59,7 +60,7 @@ final class TestSum {
try (HSSFWorkbook wb = initWorkbookWithNA()) {
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
- confirmError(fe, cell, "SUM(B2:B6)", FormulaError.NA);
+ assertError(fe, cell, "SUM(B2:B6)", FormulaError.NA);
}
}
@@ -92,15 +93,7 @@ final class TestSum {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.NUMERIC);
+ assertEquals(CellType.NUMERIC, result.getCellType());
assertEquals(expectedResult, result.getNumberValue());
}
-
- private static void confirmError(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, FormulaError expectedError) {
- cell.setCellFormula(formulaText);
- fe.notifyUpdateCell(cell);
- CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.ERROR);
- assertEquals(expectedError.getCode(), result.getErrorValue());
- }
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java?rev=1892096&r1=1892095&r2=1892096&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java Sat Aug 7 23:23:39 2021
@@ -19,8 +19,6 @@ package org.apache.poi.ss.formula.functi
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.formula.eval.*;
-import org.apache.poi.ss.usermodel.CellType;
-import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.util.LocaleUtil;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
@@ -29,6 +27,7 @@ import org.junit.jupiter.api.Test;
import java.io.IOException;
import java.util.Locale;
+import static org.apache.poi.ss.util.Utils.assertDouble;
import static org.junit.jupiter.api.Assertions.assertEquals;
/**
@@ -89,7 +88,7 @@ final class TestTimeValue {
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue("8/22/2011 12:00");
HSSFCell cell = row.createCell(1);
- confirmNumericResult(fe, cell, "TIMEVALUE(A1)", 0.5);
+ assertDouble(fe, cell, "TIMEVALUE(A1)", 0.5);
}
}
@@ -113,12 +112,4 @@ final class TestTimeValue {
assertEquals(ErrorEval.class, result.getClass());
assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), ((ErrorEval) result).getErrorCode());
}
-
- 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);
- }
}
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=1892096&r1=1892095&r2=1892096&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 Sat Aug 7 23:23:39 2021
@@ -59,7 +59,7 @@ public class Utils {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.NUMERIC);
+ assertEquals(CellType.NUMERIC, result.getCellType());
assertEquals(expectedResult, result.getNumberValue());
}
@@ -67,7 +67,7 @@ public class Utils {
cell.setCellFormula(formulaText);
fe.notifyUpdateCell(cell);
CellValue result = fe.evaluate(cell);
- assertEquals(result.getCellType(), CellType.ERROR);
+ assertEquals(CellType.ERROR, result.getCellType());
assertEquals(expectedError.getCode(), result.getErrorValue());
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org