diff options
| -rw-r--r-- | src/java/org/apache/poi/ss/formula/eval/ErrorEval.java | 6 | ||||
| -rw-r--r-- | src/java/org/apache/poi/ss/formula/eval/NumericValueEval.java | 4 | ||||
| -rw-r--r-- | src/java/org/apache/poi/ss/formula/functions/Match.java | 9 | ||||
| -rw-r--r-- | src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java | 25 | ||||
| -rw-r--r-- | test-data/spreadsheet/MatchFunctionTestCaseData.xls | bin | 40448 -> 14848 bytes |
5 files changed, 37 insertions, 7 deletions
diff --git a/src/java/org/apache/poi/ss/formula/eval/ErrorEval.java b/src/java/org/apache/poi/ss/formula/eval/ErrorEval.java index faa8bed265..31d08d05e5 100644 --- a/src/java/org/apache/poi/ss/formula/eval/ErrorEval.java +++ b/src/java/org/apache/poi/ss/formula/eval/ErrorEval.java @@ -27,7 +27,7 @@ import org.apache.poi.ss.usermodel.FormulaError; */ public final class ErrorEval implements ValueEval { private static final Map<FormulaError,ErrorEval> evals = new HashMap<>(); - + /** <b>#NULL!</b> - Intersection of two cell ranges is empty */ public static final ErrorEval NULL_INTERSECTION = new ErrorEval(FormulaError.NULL); /** <b>#DIV/0!</b> - Division by zero */ @@ -65,7 +65,7 @@ public final class ErrorEval implements ValueEval { } /** - * Converts error codes to text. Handles non-standard error codes OK. + * Converts error codes to text. Handles non-standard error codes OK. * For debug/test purposes (and for formatting error messages). * @return the String representation of the specified Excel error code. */ @@ -77,7 +77,7 @@ public final class ErrorEval implements ValueEval { return "~non~std~err(" + errorCode + ")~"; } - private FormulaError _error; + private final FormulaError _error; private ErrorEval(FormulaError error) { _error = error; evals.put(error, this); diff --git a/src/java/org/apache/poi/ss/formula/eval/NumericValueEval.java b/src/java/org/apache/poi/ss/formula/eval/NumericValueEval.java index 056f21cdf7..c6dcf1dc27 100644 --- a/src/java/org/apache/poi/ss/formula/eval/NumericValueEval.java +++ b/src/java/org/apache/poi/ss/formula/eval/NumericValueEval.java @@ -22,9 +22,9 @@ package org.apache.poi.ss.formula.eval; /** * @author Amol S. Deshmukh < amolweb at ya hoo dot com > - * + * */ public interface NumericValueEval extends ValueEval { - public abstract double getNumberValue(); + double getNumberValue(); } diff --git a/src/java/org/apache/poi/ss/formula/functions/Match.java b/src/java/org/apache/poi/ss/formula/functions/Match.java index 4b16b0c87f..4a61476657 100644 --- a/src/java/org/apache/poi/ss/formula/functions/Match.java +++ b/src/java/org/apache/poi/ss/formula/functions/Match.java @@ -18,8 +18,10 @@ package org.apache.poi.ss.formula.functions; import org.apache.poi.ss.formula.TwoDEval; +import org.apache.poi.ss.formula.eval.BlankEval; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.MissingArgEval; import org.apache.poi.ss.formula.eval.NumberEval; import org.apache.poi.ss.formula.eval.NumericValueEval; import org.apache.poi.ss.formula.eval.OperandResolver; @@ -177,7 +179,12 @@ public final class Match extends Var2or3ArgFunction { throw new EvaluationException(ErrorEval.VALUE_INVALID); } // if the string parses as a number, it is OK - return d.doubleValue(); + return d; + } + if (match_type instanceof MissingArgEval || match_type instanceof BlankEval) { + // Excel-Online ignores a missing match-type and + // uses the default-value instead + return 1; } throw new RuntimeException("Unexpected match_type type (" + match_type.getClass().getName() + ")"); } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java index f19190e852..5476e28688 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java @@ -119,7 +119,6 @@ import org.junit.jupiter.api.Disabled; import org.junit.jupiter.api.Test; import org.junit.jupiter.params.ParameterizedTest; import org.junit.jupiter.params.provider.EnumSource; -import org.junit.jupiter.params.provider.ValueSource; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCalcCell; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; @@ -3638,4 +3637,28 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues { } } } + + @Test + public void test64986() throws IOException { + XSSFWorkbook w = new XSSFWorkbook(); + XSSFSheet s = w.createSheet(); + XSSFRow r = s.createRow(0); + XSSFCell c = r.createCell(0); + c.setCellFormula("MATCH(\"VAL\",B1:B11,)"); + + FormulaEvaluator evaluator = w.getCreationHelper().createFormulaEvaluator(); + CellValue value = evaluator.evaluate(c); + assertEquals(CellType.ERROR, value.getCellType()); + assertEquals(ErrorEval.NA.getErrorCode(), value.getErrorValue()); + + // put a value in place so the match should find something + Cell val = r.createCell(1); + val.setCellValue("VAL"); + + // clear and check that now we find a match + evaluator.clearAllCachedResultValues(); + value = evaluator.evaluate(c); + assertEquals(CellType.NUMERIC, value.getCellType()); + assertEquals(1, value.getNumberValue(), 0.01); + } } diff --git a/test-data/spreadsheet/MatchFunctionTestCaseData.xls b/test-data/spreadsheet/MatchFunctionTestCaseData.xls Binary files differindex 0b98a17713..a2a99b4c39 100644 --- a/test-data/spreadsheet/MatchFunctionTestCaseData.xls +++ b/test-data/spreadsheet/MatchFunctionTestCaseData.xls |