diff options
13 files changed, 526 insertions, 586 deletions
diff --git a/src/java/org/apache/poi/ss/formula/functions/Countif.java b/src/java/org/apache/poi/ss/formula/functions/Countif.java index ef524acde3..9a8a034104 100644 --- a/src/java/org/apache/poi/ss/formula/functions/Countif.java +++ b/src/java/org/apache/poi/ss/formula/functions/Countif.java @@ -43,6 +43,7 @@ import org.apache.poi.ss.usermodel.ErrorConstants; * </p> * * @author Josh Micich + * @author Cedric Walter at innoveo.com */ public final class Countif extends Fixed2ArgFunction { @@ -309,7 +310,7 @@ public final class Countif extends Fixed2ArgFunction { return false; } } - private static final class StringMatcher extends MatcherBase { + public static final class StringMatcher extends MatcherBase { private final String _value; private final Pattern _pattern; @@ -378,19 +379,19 @@ public final class Countif extends Fixed2ArgFunction { * Translates Excel countif wildcard strings into java regex strings * @return <code>null</code> if the specified value contains no special wildcard characters. */ - private static Pattern getWildCardPattern(String value) { + public static Pattern getWildCardPattern(String value) { int len = value.length(); StringBuffer sb = new StringBuffer(len); boolean hasWildCard = false; for(int i=0; i<len; i++) { char ch = value.charAt(i); switch(ch) { - case '?': + case '?': //Any single character hasWildCard = true; // match exactly one character sb.append('.'); continue; - case '*': + case '*': //Zero or more characters hasWildCard = true; // match one or more occurrences of any character sb.append(".*"); diff --git a/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java b/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java index 62bc71b120..f2cc607d04 100644 --- a/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java +++ b/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java @@ -29,10 +29,14 @@ import org.apache.poi.ss.formula.eval.StringEval; import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.formula.TwoDEval; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + /** * Common functionality used by VLOOKUP, HLOOKUP, LOOKUP and MATCH * * @author Josh Micich + * @author Cedric Walter at innoveo.com */ final class LookupUtils { @@ -167,6 +171,14 @@ final class LookupUtils { return EQUAL; } + public static final CompareResult valueOf(boolean matches) { + if(matches) { + return EQUAL ; + } + return LESS_THAN; + } + + public boolean isTypeMismatch() { return _isTypeMismatch; } @@ -243,16 +255,37 @@ final class LookupUtils { protected abstract String getValueAsString(); } - private static final class StringLookupComparer extends LookupValueComparerBase { - private String _value; - protected StringLookupComparer(StringEval se) { + private static final class StringLookupComparer extends LookupValueComparerBase { + + private String _value; + private final Pattern _wildCardPattern; + private boolean _matchExact; + private boolean _isMatchFunction; + + protected StringLookupComparer(StringEval se, boolean matchExact, boolean isMatchFunction) { super(se); _value = se.getStringValue(); + _wildCardPattern = Countif.StringMatcher.getWildCardPattern(_value); + _matchExact = matchExact; + _isMatchFunction = isMatchFunction; } + protected CompareResult compareSameType(ValueEval other) { - StringEval se = (StringEval) other; - return CompareResult.valueOf(_value.compareToIgnoreCase(se.getStringValue())); + StringEval se = (StringEval) other; + + String stringValue = se.getStringValue(); + if (_wildCardPattern != null) { + Matcher matcher = _wildCardPattern.matcher(stringValue); + boolean matches = matcher.matches(); + + if (_isMatchFunction || + !_matchExact) { + return CompareResult.valueOf(matches); + } + } + + return CompareResult.valueOf(_value.compareToIgnoreCase(stringValue)); } protected String getValueAsString() { return _value; @@ -423,7 +456,7 @@ final class LookupUtils { } public static int lookupIndexOfValue(ValueEval lookupValue, ValueVector vector, boolean isRangeLookup) throws EvaluationException { - LookupValueComparer lookupComparer = createLookupComparer(lookupValue); + LookupValueComparer lookupComparer = createLookupComparer(lookupValue, isRangeLookup, false); int result; if(isRangeLookup) { result = performBinarySearch(vector, lookupComparer); @@ -439,7 +472,7 @@ final class LookupUtils { /** * Finds first (lowest index) exact occurrence of specified value. - * @param lookupValue the value to be found in column or row vector + * @param lookupComparer the value to be found in column or row vector * @param vector the values to be searched. For VLOOKUP this is the first column of the * tableArray. For HLOOKUP this is the first row of the tableArray. * @return zero based index into the vector, -1 if value cannot be found @@ -581,7 +614,7 @@ final class LookupUtils { return maxIx - 1; } - public static LookupValueComparer createLookupComparer(ValueEval lookupValue) { + public static LookupValueComparer createLookupComparer(ValueEval lookupValue, boolean matchExact, boolean isMatchFunction) { if (lookupValue == BlankEval.instance) { // blank eval translates to zero @@ -590,7 +623,8 @@ final class LookupUtils { return new NumberLookupComparer(NumberEval.ZERO); } if (lookupValue instanceof StringEval) { - return new StringLookupComparer((StringEval) lookupValue); + //TODO eventually here return a WildcardStringLookupComparer + return new StringLookupComparer((StringEval) lookupValue, matchExact, isMatchFunction); } if (lookupValue instanceof NumberEval) { return new NumberLookupComparer((NumberEval) lookupValue); 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 8c89ab0404..bbcf0e2e6b 100644 --- a/src/java/org/apache/poi/ss/formula/functions/Match.java +++ b/src/java/org/apache/poi/ss/formula/functions/Match.java @@ -62,6 +62,7 @@ import org.apache.poi.ss.formula.TwoDEval; * * * @author Josh Micich + * @author Cedric Walter at innoveo.com */ public final class Match extends Var2or3ArgFunction { @@ -232,14 +233,7 @@ public final class Match extends Var2or3ArgFunction { } private static LookupValueComparer createLookupComparer(ValueEval lookupValue, boolean matchExact) { - if (matchExact && lookupValue instanceof StringEval) { - String stringValue = ((StringEval) lookupValue).getStringValue(); - if(isLookupValueWild(stringValue)) { - throw new RuntimeException("Wildcard lookup values '" + stringValue + "' not supported yet"); - } - - } - return LookupUtils.createLookupComparer(lookupValue); + return LookupUtils.createLookupComparer(lookupValue, matchExact, true); } private static boolean isLookupValueWild(String stringValue) { diff --git a/src/java/org/apache/poi/ss/formula/functions/Vlookup.java b/src/java/org/apache/poi/ss/formula/functions/Vlookup.java index 539be08163..3ece2f7f6d 100644 --- a/src/java/org/apache/poi/ss/formula/functions/Vlookup.java +++ b/src/java/org/apache/poi/ss/formula/functions/Vlookup.java @@ -38,6 +38,7 @@ import org.apache.poi.ss.formula.TwoDEval; * the lookup_value. If FALSE, only exact matches will be considered<br/> * * @author Josh Micich + * @author Cedric Walter at innoveo.com */ public final class Vlookup extends Var3or4ArgFunction { private static final ValueEval DEFAULT_ARG3 = BoolEval.TRUE; @@ -47,16 +48,16 @@ public final class Vlookup extends Var3or4ArgFunction { return evaluate(srcRowIndex, srcColumnIndex, arg0, arg1, arg2, DEFAULT_ARG3); } - public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, - ValueEval arg2, ValueEval arg3) { + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookup_value, ValueEval table_array, + ValueEval col_index, ValueEval range_lookup) { try { // Evaluation order: - // arg0 lookup_value, arg1 table_array, arg3 range_lookup, find lookup value, arg2 col_index, fetch result - ValueEval lookupValue = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex); - TwoDEval tableArray = LookupUtils.resolveTableArrayArg(arg1); - boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(arg3, srcRowIndex, srcColumnIndex); + // lookup_value , table_array, range_lookup, find lookup value, col_index, fetch result + ValueEval lookupValue = OperandResolver.getSingleValue(lookup_value, srcRowIndex, srcColumnIndex); + TwoDEval tableArray = LookupUtils.resolveTableArrayArg(table_array); + boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(range_lookup, srcRowIndex, srcColumnIndex); int rowIndex = LookupUtils.lookupIndexOfValue(lookupValue, LookupUtils.createColumnVector(tableArray, 0), isRangeLookup); - int colIndex = LookupUtils.resolveRowOrColIndexArg(arg2, srcRowIndex, srcColumnIndex); + int colIndex = LookupUtils.resolveRowOrColIndexArg(col_index, srcRowIndex, srcColumnIndex); ValueVector resultCol = createResultColumnVector(tableArray, colIndex); return resultCol.getItem(rowIndex); } catch (EvaluationException e) { diff --git a/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java b/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java new file mode 100644 index 0000000000..920593c83f --- /dev/null +++ b/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java @@ -0,0 +1,360 @@ +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+import java.io.PrintStream;
+
+import junit.framework.Assert;
+import junit.framework.AssertionFailedError;
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFRow;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.hssf.util.CellReference;
+import org.apache.poi.ss.usermodel.CellValue;
+
+/**
+ * @author Josh Micich
+ * @author Cedric Walter at innoveo.com
+ */
+public abstract class BaseTestFunctionsFromSpreadsheet extends TestCase {
+
+ private static final class Result {
+ public static final int SOME_EVALUATIONS_FAILED = -1;
+ public static final int ALL_EVALUATIONS_SUCCEEDED = +1;
+ public static final int NO_EVALUATIONS_FOUND = 0;
+ }
+
+ /**
+ * This class defines constants for navigating around the test data spreadsheet used for these tests.
+ */
+ private static final class SS {
+
+ /** Name of the test spreadsheet (found in the standard test data folder) */
+
+
+ /** Name of the first sheet in the spreadsheet (contains comments) */
+ public final static String README_SHEET_NAME = "Read Me";
+
+ /** Row (zero-based) in each sheet where the evaluation cases start. */
+ public static final int START_TEST_CASES_ROW_INDEX = 4; // Row '5'
+ /** Index of the column that contains the function names */
+ public static final int COLUMN_INDEX_MARKER = 0; // Column 'A'
+ public static final int COLUMN_INDEX_EVALUATION = 1; // Column 'B'
+ public static final int COLUMN_INDEX_EXPECTED_RESULT = 2; // Column 'C'
+ public static final int COLUMN_ROW_COMMENT = 3; // Column 'D'
+
+ /** Used to indicate when there are no more test cases on the current sheet */
+ public static final String TEST_CASES_END_MARKER = "<end>";
+ /** Used to indicate that the test on the current row should be ignored */
+ public static final String SKIP_CURRENT_TEST_CASE_MARKER = "<skip>";
+
+ }
+
+ // Note - multiple failures are aggregated before ending.
+ // If one or more functions fail, a single AssertionFailedError is thrown at the end
+ private int _sheetFailureCount;
+ private int _sheetSuccessCount;
+ private int _evaluationFailureCount;
+ private int _evaluationSuccessCount;
+
+
+
+ private static void confirmExpectedResult(String msg, HSSFCell expected, CellValue actual) {
+ if (expected == null) {
+ throw new AssertionFailedError(msg + " - Bad setup data expected value is null");
+ }
+ if(actual == null) {
+ throw new AssertionFailedError(msg + " - actual value was null");
+ }
+ if(expected.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
+ confirmErrorResult(msg, expected.getErrorCellValue(), actual);
+ return;
+ }
+ if(actual.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
+ throw unexpectedError(msg, expected, actual.getErrorValue());
+ }
+ if(actual.getCellType() != expected.getCellType()) {
+ throw wrongTypeError(msg, expected, actual);
+ }
+
+
+ switch (expected.getCellType()) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue());
+ break;
+ case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since we will call method after formula evaluation
+ throw new IllegalStateException("Cannot expect formula as result of formula evaluation: " + msg);
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ assertEquals(expected.getNumericCellValue(), actual.getNumberValue(), 0.0);
+ break;
+ case HSSFCell.CELL_TYPE_STRING:
+ assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getStringValue());
+ break;
+ }
+ }
+
+
+ private static AssertionFailedError wrongTypeError(String msgPrefix, HSSFCell expectedCell, CellValue actualValue) {
+ return new AssertionFailedError(msgPrefix + " Result type mismatch. Evaluated result was "
+ + actualValue.formatAsString()
+ + " but the expected result was "
+ + formatValue(expectedCell)
+ );
+ }
+ private static AssertionFailedError unexpectedError(String msgPrefix, HSSFCell expected, int actualErrorCode) {
+ return new AssertionFailedError(msgPrefix + " Error code ("
+ + ErrorEval.getText(actualErrorCode)
+ + ") was evaluated, but the expected result was "
+ + formatValue(expected)
+ );
+ }
+
+
+ private static void confirmErrorResult(String msgPrefix, int expectedErrorCode, CellValue actual) {
+ if(actual.getCellType() != HSSFCell.CELL_TYPE_ERROR) {
+ throw new AssertionFailedError(msgPrefix + " Expected cell error ("
+ + ErrorEval.getText(expectedErrorCode) + ") but actual value was "
+ + actual.formatAsString());
+ }
+ if(expectedErrorCode != actual.getErrorValue()) {
+ throw new AssertionFailedError(msgPrefix + " Expected cell error code ("
+ + ErrorEval.getText(expectedErrorCode)
+ + ") but actual error code was ("
+ + ErrorEval.getText(actual.getErrorValue())
+ + ")");
+ }
+ }
+
+
+ private static String formatValue(HSSFCell expecedCell) {
+ switch (expecedCell.getCellType()) {
+ case HSSFCell.CELL_TYPE_BLANK: return "<blank>";
+ case HSSFCell.CELL_TYPE_BOOLEAN: return String.valueOf(expecedCell.getBooleanCellValue());
+ case HSSFCell.CELL_TYPE_NUMERIC: return String.valueOf(expecedCell.getNumericCellValue());
+ case HSSFCell.CELL_TYPE_STRING: return expecedCell.getRichStringCellValue().getString();
+ }
+ throw new RuntimeException("Unexpected cell type of expected value (" + expecedCell.getCellType() + ")");
+ }
+
+
+ protected void setUp() {
+ _sheetFailureCount = 0;
+ _sheetSuccessCount = 0;
+ _evaluationFailureCount = 0;
+ _evaluationSuccessCount = 0;
+ }
+
+ public void testFunctionsFromTestSpreadsheet() {
+ HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook(this.getFilename());
+
+ confirmReadMeSheet(workbook);
+ int nSheets = workbook.getNumberOfSheets();
+ for(int i=1; i< nSheets; i++) {
+ int sheetResult = processTestSheet(workbook, i, workbook.getSheetName(i));
+ switch(sheetResult) {
+ case Result.ALL_EVALUATIONS_SUCCEEDED: _sheetSuccessCount ++; break;
+ case Result.SOME_EVALUATIONS_FAILED: _sheetFailureCount ++; break;
+ }
+ }
+
+ // confirm results
+ String successMsg = "There were "
+ + _sheetSuccessCount + " successful sheets(s) and "
+ + _evaluationSuccessCount + " function(s) without error";
+ if(_sheetFailureCount > 0) {
+ String msg = _sheetFailureCount + " sheets(s) failed with "
+ + _evaluationFailureCount + " evaluation(s). " + successMsg;
+ throw new AssertionFailedError(msg);
+ }
+ if(false) { // normally no output for successful tests
+ System.out.println(getClass().getName() + ": " + successMsg);
+ }
+ }
+
+ protected abstract String getFilename();
+
+ private int processTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName) {
+ HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
+ HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
+ int maxRows = sheet.getLastRowNum()+1;
+ int result = Result.NO_EVALUATIONS_FOUND; // so far
+
+ String currentGroupComment = null;
+ for(int rowIndex=SS.START_TEST_CASES_ROW_INDEX; rowIndex<maxRows; rowIndex++) {
+ HSSFRow r = sheet.getRow(rowIndex);
+ String newMarkerValue = getMarkerColumnValue(r);
+ if(r == null) {
+ continue;
+ }
+ if(SS.TEST_CASES_END_MARKER.equalsIgnoreCase(newMarkerValue)) {
+ // normal exit point
+ return result;
+ }
+ if(SS.SKIP_CURRENT_TEST_CASE_MARKER.equalsIgnoreCase(newMarkerValue)) {
+ // currently disabled test case row
+ continue;
+ }
+ if(newMarkerValue != null) {
+ currentGroupComment = newMarkerValue;
+ }
+ HSSFCell c = r.getCell(SS.COLUMN_INDEX_EVALUATION);
+ if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
+ continue;
+ }
+ CellValue actualValue = evaluator.evaluate(c);
+ HSSFCell expectedValueCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
+ String rowComment = getRowCommentColumnValue(r);
+
+ String msgPrefix = formatTestCaseDetails(sheetName, r.getRowNum(), c, currentGroupComment, rowComment);
+ try {
+ confirmExpectedResult(msgPrefix, expectedValueCell, actualValue);
+ _evaluationSuccessCount ++;
+ if(result != Result.SOME_EVALUATIONS_FAILED) {
+ result = Result.ALL_EVALUATIONS_SUCCEEDED;
+ }
+ } catch (RuntimeException e) {
+ _evaluationFailureCount ++;
+ printShortStackTrace(System.err, e);
+ result = Result.SOME_EVALUATIONS_FAILED;
+ } catch (AssertionFailedError e) {
+ _evaluationFailureCount ++;
+ printShortStackTrace(System.err, e);
+ result = Result.SOME_EVALUATIONS_FAILED;
+ }
+
+ }
+ throw new RuntimeException("Missing end marker '" + SS.TEST_CASES_END_MARKER
+ + "' on sheet '" + sheetName + "'");
+
+ }
+
+
+ private static String formatTestCaseDetails(String sheetName, int rowIndex, HSSFCell c, String currentGroupComment,
+ String rowComment) {
+
+ StringBuffer sb = new StringBuffer();
+ CellReference cr = new CellReference(sheetName, rowIndex, c.getColumnIndex(), false, false);
+ sb.append(cr.formatAsString());
+ sb.append(" {=").append(c.getCellFormula()).append("}");
+
+ if(currentGroupComment != null) {
+ sb.append(" '");
+ sb.append(currentGroupComment);
+ if(rowComment != null) {
+ sb.append(" - ");
+ sb.append(rowComment);
+ }
+ sb.append("' ");
+ } else {
+ if(rowComment != null) {
+ sb.append(" '");
+ sb.append(rowComment);
+ sb.append("' ");
+ }
+ }
+
+ return sb.toString();
+ }
+
+ /**
+ * Asserts that the 'read me' comment page exists, and has this class' name in one of the
+ * cells. This back-link is to make it easy to find this class if a reader encounters the
+ * spreadsheet first.
+ */
+ private void confirmReadMeSheet(HSSFWorkbook workbook) {
+ String firstSheetName = workbook.getSheetName(0);
+ if(!firstSheetName.equalsIgnoreCase(SS.README_SHEET_NAME)) {
+ throw new RuntimeException("First sheet's name was '" + firstSheetName + "' but expected '" + SS.README_SHEET_NAME + "'");
+ }
+ HSSFSheet sheet = workbook.getSheetAt(0);
+ String specifiedClassName = sheet.getRow(2).getCell(0).getRichStringCellValue().getString();
+ assertEquals("Test class name in spreadsheet comment", getClass().getName(), specifiedClassName);
+ }
+
+
+ /**
+ * Useful to keep output concise when expecting many failures to be reported by this test case
+ */
+ private static void printShortStackTrace(PrintStream ps, Throwable e) {
+ StackTraceElement[] stes = e.getStackTrace();
+
+ int startIx = 0;
+ // skip any top frames inside junit.framework.Assert
+ while(startIx<stes.length) {
+ if(!stes[startIx].getClassName().equals(Assert.class.getName())) {
+ break;
+ }
+ startIx++;
+ }
+ // skip bottom frames (part of junit framework)
+ int endIx = startIx+1;
+ while(endIx < stes.length) {
+ if(stes[endIx].getClassName().equals(TestCase.class.getName())) {
+ break;
+ }
+ endIx++;
+ }
+ if(startIx >= endIx) {
+ // something went wrong. just print the whole stack trace
+ e.printStackTrace(ps);
+ }
+ endIx -= 4; // skip 4 frames of reflection invocation
+ ps.println(e.toString());
+ for(int i=startIx; i<endIx; i++) {
+ ps.println("\tat " + stes[i].toString());
+ }
+
+ }
+
+ private static String getRowCommentColumnValue(HSSFRow r) {
+ return getCellTextValue(r, SS.COLUMN_ROW_COMMENT, "row comment");
+ }
+
+ private static String getMarkerColumnValue(HSSFRow r) {
+ return getCellTextValue(r, SS.COLUMN_INDEX_MARKER, "marker");
+ }
+
+ /**
+ * @return <code>null</code> if cell is missing, empty or blank
+ */
+ private static String getCellTextValue(HSSFRow r, int colIndex, String columnName) {
+ if(r == null) {
+ return null;
+ }
+ HSSFCell cell = r.getCell(colIndex);
+ if(cell == null) {
+ return null;
+ }
+ if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
+ return null;
+ }
+ if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
+ return cell.getRichStringCellValue().getString();
+ }
+
+ throw new RuntimeException("Bad cell type for '" + columnName + "' column: ("
+ + cell.getCellType() + ") row (" + (r.getRowNum() +1) + ")");
+ }
+
+}
diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java b/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java index 2d340ad8f1..02992fc5db 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java @@ -43,6 +43,7 @@ import org.apache.poi.ss.util.CellReference; * Test cases for COUNT(), COUNTA() COUNTIF(), COUNTBLANK() * * @author Josh Micich + * @author Cedric Walter at innoveo.com */ public final class TestCountFuncs extends TestCase { diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestIndexFunctionFromSpreadsheet.java b/src/testcases/org/apache/poi/ss/formula/functions/TestIndexFunctionFromSpreadsheet.java index c2978435a1..6d900e1ef4 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestIndexFunctionFromSpreadsheet.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestIndexFunctionFromSpreadsheet.java @@ -17,234 +17,16 @@ package org.apache.poi.ss.formula.functions; -import java.io.PrintStream; - -import junit.framework.Assert; -import junit.framework.AssertionFailedError; -import junit.framework.TestCase; - -import org.apache.poi.hssf.HSSFTestDataSamples; -import org.apache.poi.ss.formula.eval.ErrorEval; -import org.apache.poi.hssf.usermodel.HSSFCell; -import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; -import org.apache.poi.hssf.usermodel.HSSFRow; -import org.apache.poi.hssf.usermodel.HSSFSheet; -import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.hssf.util.CellReference; -import org.apache.poi.ss.usermodel.CellValue; - /** * Tests INDEX() as loaded from a test data spreadsheet.<p/> * * @author Josh Micich + * @author Cedric Walter at innoveo.com */ -public final class TestIndexFunctionFromSpreadsheet extends TestCase { - - private static final class Result { - public static final int SOME_EVALUATIONS_FAILED = -1; - public static final int ALL_EVALUATIONS_SUCCEEDED = +1; - public static final int NO_EVALUATIONS_FOUND = 0; - } - - /** - * This class defines constants for navigating around the test data spreadsheet used for these tests. - */ - private static final class SS { - - /** Name of the test spreadsheet (found in the standard test data folder) */ - public final static String FILENAME = "IndexFunctionTestCaseData.xls"; - - public static final int COLUMN_INDEX_EVALUATION = 2; // Column 'C' - public static final int COLUMN_INDEX_EXPECTED_RESULT = 3; // Column 'D' - - } - - // Note - multiple failures are aggregated before ending. - // If one or more functions fail, a single AssertionFailedError is thrown at the end - private int _evaluationFailureCount; - private int _evaluationSuccessCount; - - - - private static void confirmExpectedResult(String msg, HSSFCell expected, CellValue actual) { - if (expected == null) { - throw new AssertionFailedError(msg + " - Bad setup data expected value is null"); - } - if(actual == null) { - throw new AssertionFailedError(msg + " - actual value was null"); - } - if(expected.getCellType() == HSSFCell.CELL_TYPE_ERROR) { - confirmErrorResult(msg, expected.getErrorCellValue(), actual); - return; - } - if(actual.getCellType() == HSSFCell.CELL_TYPE_ERROR) { - throw unexpectedError(msg, expected, actual.getErrorValue()); - } - if(actual.getCellType() != expected.getCellType()) { - throw wrongTypeError(msg, expected, actual); - } - - - switch (expected.getCellType()) { - case HSSFCell.CELL_TYPE_BOOLEAN: - assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue()); - break; - case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since we will call method after formula evaluation - throw new AssertionFailedError("Cannot expect formula as result of formula evaluation: " + msg); - case HSSFCell.CELL_TYPE_NUMERIC: - assertEquals(msg, expected.getNumericCellValue(), actual.getNumberValue(), 0.0); - break; - case HSSFCell.CELL_TYPE_STRING: - assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getStringValue()); - break; - } - } - - - private static AssertionFailedError wrongTypeError(String msgPrefix, HSSFCell expectedCell, CellValue actualValue) { - return new AssertionFailedError(msgPrefix + " Result type mismatch. Evaluated result was " - + actualValue.formatAsString() - + " but the expected result was " - + formatValue(expectedCell) - ); - } - private static AssertionFailedError unexpectedError(String msgPrefix, HSSFCell expected, int actualErrorCode) { - return new AssertionFailedError(msgPrefix + " Error code (" - + ErrorEval.getText(actualErrorCode) - + ") was evaluated, but the expected result was " - + formatValue(expected) - ); - } - - - private static void confirmErrorResult(String msgPrefix, int expectedErrorCode, CellValue actual) { - if(actual.getCellType() != HSSFCell.CELL_TYPE_ERROR) { - throw new AssertionFailedError(msgPrefix + " Expected cell error (" - + ErrorEval.getText(expectedErrorCode) + ") but actual value was " - + actual.formatAsString()); - } - if(expectedErrorCode != actual.getErrorValue()) { - throw new AssertionFailedError(msgPrefix + " Expected cell error code (" - + ErrorEval.getText(expectedErrorCode) - + ") but actual error code was (" - + ErrorEval.getText(actual.getErrorValue()) - + ")"); - } - } - - - private static String formatValue(HSSFCell expecedCell) { - switch (expecedCell.getCellType()) { - case HSSFCell.CELL_TYPE_BLANK: return "<blank>"; - case HSSFCell.CELL_TYPE_BOOLEAN: return String.valueOf(expecedCell.getBooleanCellValue()); - case HSSFCell.CELL_TYPE_NUMERIC: return String.valueOf(expecedCell.getNumericCellValue()); - case HSSFCell.CELL_TYPE_STRING: return expecedCell.getRichStringCellValue().getString(); - } - throw new RuntimeException("Unexpected cell type of expected value (" + expecedCell.getCellType() + ")"); - } - - - protected void setUp() { - _evaluationFailureCount = 0; - _evaluationSuccessCount = 0; - } - - public void testFunctionsFromTestSpreadsheet() { - HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook(SS.FILENAME); - - processTestSheet(workbook, workbook.getSheetName(0)); - - // confirm results - String successMsg = "There were " - + _evaluationSuccessCount + " function(s) without error"; - if(_evaluationFailureCount > 0) { - String msg = _evaluationFailureCount + " evaluation(s) failed. " + successMsg; - throw new AssertionFailedError(msg); - } - if(false) { // normally no output for successful tests - System.out.println(getClass().getName() + ": " + successMsg); - } - } - - private void processTestSheet(HSSFWorkbook workbook, String sheetName) { - HSSFSheet sheet = workbook.getSheetAt(0); - HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook); - int maxRows = sheet.getLastRowNum()+1; - int result = Result.NO_EVALUATIONS_FOUND; // so far - - for(int rowIndex=0; rowIndex<maxRows; rowIndex++) { - HSSFRow r = sheet.getRow(rowIndex); - if(r == null) { - continue; - } - HSSFCell c = r.getCell(SS.COLUMN_INDEX_EVALUATION); - if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) { - continue; - } - HSSFCell expectedValueCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT); - - String msgPrefix = formatTestCaseDetails(sheetName, r.getRowNum(), c); - try { - CellValue actualValue = evaluator.evaluate(c); - confirmExpectedResult(msgPrefix, expectedValueCell, actualValue); - _evaluationSuccessCount ++; - if(result != Result.SOME_EVALUATIONS_FAILED) { - result = Result.ALL_EVALUATIONS_SUCCEEDED; - } - } catch (RuntimeException e) { - _evaluationFailureCount ++; - printShortStackTrace(System.err, e, msgPrefix); - result = Result.SOME_EVALUATIONS_FAILED; - } catch (AssertionFailedError e) { - _evaluationFailureCount ++; - printShortStackTrace(System.err, e, msgPrefix); - result = Result.SOME_EVALUATIONS_FAILED; - } - } - } - - - private static String formatTestCaseDetails(String sheetName, int rowIndex, HSSFCell c) { - - StringBuffer sb = new StringBuffer(); - CellReference cr = new CellReference(sheetName, rowIndex, c.getColumnIndex(), false, false); - sb.append(cr.formatAsString()); - sb.append(" [formula: ").append(c.getCellFormula()).append(" ]"); - return sb.toString(); - } - - /** - * Useful to keep output concise when expecting many failures to be reported by this test case - */ - private static void printShortStackTrace(PrintStream ps, Throwable e, String msgPrefix) { - System.err.println("Problem with " + msgPrefix); - StackTraceElement[] stes = e.getStackTrace(); - - int startIx = 0; - // skip any top frames inside junit.framework.Assert - while(startIx<stes.length) { - if(!stes[startIx].getClassName().equals(Assert.class.getName())) { - break; - } - startIx++; - } - // skip bottom frames (part of junit framework) - int endIx = startIx+1; - while(endIx < stes.length) { - if(stes[endIx].getClassName().equals(TestCase.class.getName())) { - break; - } - endIx++; - } - if(startIx >= endIx) { - // something went wrong. just print the whole stack trace - e.printStackTrace(ps); - } - endIx -= 4; // skip 4 frames of reflection invocation - ps.println(e.toString()); - for(int i=startIx; i<endIx; i++) { - ps.println("\tat " + stes[i].toString()); - } - } -} +public final class TestIndexFunctionFromSpreadsheet extends BaseTestFunctionsFromSpreadsheet { + @Override + protected String getFilename() { + return "IndexFunctionTestCaseData.xls"; + } +}
\ No newline at end of file diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestLookupFunctionsFromSpreadsheet.java b/src/testcases/org/apache/poi/ss/formula/functions/TestLookupFunctionsFromSpreadsheet.java index 6054d08208..09810a8dc7 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestLookupFunctionsFromSpreadsheet.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestLookupFunctionsFromSpreadsheet.java @@ -17,21 +17,7 @@ package org.apache.poi.ss.formula.functions; -import java.io.PrintStream; -import junit.framework.Assert; -import junit.framework.AssertionFailedError; -import junit.framework.TestCase; - -import org.apache.poi.hssf.HSSFTestDataSamples; -import org.apache.poi.ss.formula.eval.ErrorEval; -import org.apache.poi.hssf.usermodel.HSSFCell; -import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; -import org.apache.poi.hssf.usermodel.HSSFRow; -import org.apache.poi.hssf.usermodel.HSSFSheet; -import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.hssf.util.CellReference; -import org.apache.poi.ss.usermodel.CellValue; /** * Tests lookup functions (VLOOKUP, HLOOKUP, LOOKUP, MATCH) as loaded from a test data spreadsheet.<p/> @@ -43,323 +29,12 @@ import org.apache.poi.ss.usermodel.CellValue; * more easily. * * @author Josh Micich + * @author Cedric Walter at innoveo.com */ -public final class TestLookupFunctionsFromSpreadsheet extends TestCase { - - private static final class Result { - public static final int SOME_EVALUATIONS_FAILED = -1; - public static final int ALL_EVALUATIONS_SUCCEEDED = +1; - public static final int NO_EVALUATIONS_FOUND = 0; - } - - /** - * This class defines constants for navigating around the test data spreadsheet used for these tests. - */ - private static final class SS { - - /** Name of the test spreadsheet (found in the standard test data folder) */ - public final static String FILENAME = "LookupFunctionsTestCaseData.xls"; - - /** Name of the first sheet in the spreadsheet (contains comments) */ - public final static String README_SHEET_NAME = "Read Me"; - - - /** Row (zero-based) in each sheet where the evaluation cases start. */ - public static final int START_TEST_CASES_ROW_INDEX = 4; // Row '5' - /** Index of the column that contains the function names */ - public static final int COLUMN_INDEX_MARKER = 0; // Column 'A' - public static final int COLUMN_INDEX_EVALUATION = 1; // Column 'B' - public static final int COLUMN_INDEX_EXPECTED_RESULT = 2; // Column 'C' - public static final int COLUMN_ROW_COMMENT = 3; // Column 'D' - - /** Used to indicate when there are no more test cases on the current sheet */ - public static final String TEST_CASES_END_MARKER = "<end>"; - /** Used to indicate that the test on the current row should be ignored */ - public static final String SKIP_CURRENT_TEST_CASE_MARKER = "<skip>"; - - } - - // Note - multiple failures are aggregated before ending. - // If one or more functions fail, a single AssertionFailedError is thrown at the end - private int _sheetFailureCount; - private int _sheetSuccessCount; - private int _evaluationFailureCount; - private int _evaluationSuccessCount; - - - - private static void confirmExpectedResult(String msg, HSSFCell expected, CellValue actual) { - if (expected == null) { - throw new AssertionFailedError(msg + " - Bad setup data expected value is null"); - } - if(actual == null) { - throw new AssertionFailedError(msg + " - actual value was null"); - } - if(expected.getCellType() == HSSFCell.CELL_TYPE_ERROR) { - confirmErrorResult(msg, expected.getErrorCellValue(), actual); - return; - } - if(actual.getCellType() == HSSFCell.CELL_TYPE_ERROR) { - throw unexpectedError(msg, expected, actual.getErrorValue()); - } - if(actual.getCellType() != expected.getCellType()) { - throw wrongTypeError(msg, expected, actual); - } - - - switch (expected.getCellType()) { - case HSSFCell.CELL_TYPE_BOOLEAN: - assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue()); - break; - case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since we will call method after formula evaluation - throw new IllegalStateException("Cannot expect formula as result of formula evaluation: " + msg); - case HSSFCell.CELL_TYPE_NUMERIC: - assertEquals(expected.getNumericCellValue(), actual.getNumberValue(), 0.0); - break; - case HSSFCell.CELL_TYPE_STRING: - assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getStringValue()); - break; - } - } - - - private static AssertionFailedError wrongTypeError(String msgPrefix, HSSFCell expectedCell, CellValue actualValue) { - return new AssertionFailedError(msgPrefix + " Result type mismatch. Evaluated result was " - + actualValue.formatAsString() - + " but the expected result was " - + formatValue(expectedCell) - ); - } - private static AssertionFailedError unexpectedError(String msgPrefix, HSSFCell expected, int actualErrorCode) { - return new AssertionFailedError(msgPrefix + " Error code (" - + ErrorEval.getText(actualErrorCode) - + ") was evaluated, but the expected result was " - + formatValue(expected) - ); - } - - - private static void confirmErrorResult(String msgPrefix, int expectedErrorCode, CellValue actual) { - if(actual.getCellType() != HSSFCell.CELL_TYPE_ERROR) { - throw new AssertionFailedError(msgPrefix + " Expected cell error (" - + ErrorEval.getText(expectedErrorCode) + ") but actual value was " - + actual.formatAsString()); - } - if(expectedErrorCode != actual.getErrorValue()) { - throw new AssertionFailedError(msgPrefix + " Expected cell error code (" - + ErrorEval.getText(expectedErrorCode) - + ") but actual error code was (" - + ErrorEval.getText(actual.getErrorValue()) - + ")"); - } - } - - - private static String formatValue(HSSFCell expecedCell) { - switch (expecedCell.getCellType()) { - case HSSFCell.CELL_TYPE_BLANK: return "<blank>"; - case HSSFCell.CELL_TYPE_BOOLEAN: return String.valueOf(expecedCell.getBooleanCellValue()); - case HSSFCell.CELL_TYPE_NUMERIC: return String.valueOf(expecedCell.getNumericCellValue()); - case HSSFCell.CELL_TYPE_STRING: return expecedCell.getRichStringCellValue().getString(); - } - throw new RuntimeException("Unexpected cell type of expected value (" + expecedCell.getCellType() + ")"); - } - - - protected void setUp() { - _sheetFailureCount = 0; - _sheetSuccessCount = 0; - _evaluationFailureCount = 0; - _evaluationSuccessCount = 0; - } - - public void testFunctionsFromTestSpreadsheet() { - HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook(SS.FILENAME); - - confirmReadMeSheet(workbook); - int nSheets = workbook.getNumberOfSheets(); - for(int i=1; i< nSheets; i++) { - int sheetResult = processTestSheet(workbook, i, workbook.getSheetName(i)); - switch(sheetResult) { - case Result.ALL_EVALUATIONS_SUCCEEDED: _sheetSuccessCount ++; break; - case Result.SOME_EVALUATIONS_FAILED: _sheetFailureCount ++; break; - } - } - - // confirm results - String successMsg = "There were " - + _sheetSuccessCount + " successful sheets(s) and " - + _evaluationSuccessCount + " function(s) without error"; - if(_sheetFailureCount > 0) { - String msg = _sheetFailureCount + " sheets(s) failed with " - + _evaluationFailureCount + " evaluation(s). " + successMsg; - throw new AssertionFailedError(msg); - } - if(false) { // normally no output for successful tests - System.out.println(getClass().getName() + ": " + successMsg); - } - } - - private int processTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName) { - HSSFSheet sheet = workbook.getSheetAt(sheetIndex); - HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook); - int maxRows = sheet.getLastRowNum()+1; - int result = Result.NO_EVALUATIONS_FOUND; // so far - - String currentGroupComment = null; - for(int rowIndex=SS.START_TEST_CASES_ROW_INDEX; rowIndex<maxRows; rowIndex++) { - HSSFRow r = sheet.getRow(rowIndex); - String newMarkerValue = getMarkerColumnValue(r); - if(r == null) { - continue; - } - if(SS.TEST_CASES_END_MARKER.equalsIgnoreCase(newMarkerValue)) { - // normal exit point - return result; - } - if(SS.SKIP_CURRENT_TEST_CASE_MARKER.equalsIgnoreCase(newMarkerValue)) { - // currently disabled test case row - continue; - } - if(newMarkerValue != null) { - currentGroupComment = newMarkerValue; - } - HSSFCell c = r.getCell(SS.COLUMN_INDEX_EVALUATION); - if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) { - continue; - } - CellValue actualValue = evaluator.evaluate(c); - HSSFCell expectedValueCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT); - String rowComment = getRowCommentColumnValue(r); - - String msgPrefix = formatTestCaseDetails(sheetName, r.getRowNum(), c, currentGroupComment, rowComment); - try { - confirmExpectedResult(msgPrefix, expectedValueCell, actualValue); - _evaluationSuccessCount ++; - if(result != Result.SOME_EVALUATIONS_FAILED) { - result = Result.ALL_EVALUATIONS_SUCCEEDED; - } - } catch (RuntimeException e) { - _evaluationFailureCount ++; - printShortStackTrace(System.err, e); - result = Result.SOME_EVALUATIONS_FAILED; - } catch (AssertionFailedError e) { - _evaluationFailureCount ++; - printShortStackTrace(System.err, e); - result = Result.SOME_EVALUATIONS_FAILED; - } - - } - throw new RuntimeException("Missing end marker '" + SS.TEST_CASES_END_MARKER - + "' on sheet '" + sheetName + "'"); - - } - - - private static String formatTestCaseDetails(String sheetName, int rowIndex, HSSFCell c, String currentGroupComment, - String rowComment) { - - StringBuffer sb = new StringBuffer(); - CellReference cr = new CellReference(sheetName, rowIndex, c.getColumnIndex(), false, false); - sb.append(cr.formatAsString()); - sb.append(" {=").append(c.getCellFormula()).append("}"); - - if(currentGroupComment != null) { - sb.append(" '"); - sb.append(currentGroupComment); - if(rowComment != null) { - sb.append(" - "); - sb.append(rowComment); - } - sb.append("' "); - } else { - if(rowComment != null) { - sb.append(" '"); - sb.append(rowComment); - sb.append("' "); - } - } - - return sb.toString(); - } - - /** - * Asserts that the 'read me' comment page exists, and has this class' name in one of the - * cells. This back-link is to make it easy to find this class if a reader encounters the - * spreadsheet first. - */ - private void confirmReadMeSheet(HSSFWorkbook workbook) { - String firstSheetName = workbook.getSheetName(0); - if(!firstSheetName.equalsIgnoreCase(SS.README_SHEET_NAME)) { - throw new RuntimeException("First sheet's name was '" + firstSheetName + "' but expected '" + SS.README_SHEET_NAME + "'"); - } - HSSFSheet sheet = workbook.getSheetAt(0); - String specifiedClassName = sheet.getRow(2).getCell(0).getRichStringCellValue().getString(); - assertEquals("Test class name in spreadsheet comment", getClass().getName(), specifiedClassName); - } - - - /** - * Useful to keep output concise when expecting many failures to be reported by this test case - */ - private static void printShortStackTrace(PrintStream ps, Throwable e) { - StackTraceElement[] stes = e.getStackTrace(); - - int startIx = 0; - // skip any top frames inside junit.framework.Assert - while(startIx<stes.length) { - if(!stes[startIx].getClassName().equals(Assert.class.getName())) { - break; - } - startIx++; - } - // skip bottom frames (part of junit framework) - int endIx = startIx+1; - while(endIx < stes.length) { - if(stes[endIx].getClassName().equals(TestCase.class.getName())) { - break; - } - endIx++; - } - if(startIx >= endIx) { - // something went wrong. just print the whole stack trace - e.printStackTrace(ps); - } - endIx -= 4; // skip 4 frames of reflection invocation - ps.println(e.toString()); - for(int i=startIx; i<endIx; i++) { - ps.println("\tat " + stes[i].toString()); - } - - } - - private static String getRowCommentColumnValue(HSSFRow r) { - return getCellTextValue(r, SS.COLUMN_ROW_COMMENT, "row comment"); - } - - private static String getMarkerColumnValue(HSSFRow r) { - return getCellTextValue(r, SS.COLUMN_INDEX_MARKER, "marker"); - } - - /** - * @return <code>null</code> if cell is missing, empty or blank - */ - private static String getCellTextValue(HSSFRow r, int colIndex, String columnName) { - if(r == null) { - return null; - } - HSSFCell cell = r.getCell(colIndex); - if(cell == null) { - return null; - } - if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { - return null; - } - if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { - return cell.getRichStringCellValue().getString(); - } +public final class TestLookupFunctionsFromSpreadsheet extends BaseTestFunctionsFromSpreadsheet { - throw new RuntimeException("Bad cell type for '" + columnName + "' column: (" - + cell.getCellType() + ") row (" + (r.getRowNum() +1) + ")"); - } + @Override + protected String getFilename() { + return "LookupFunctionsTestCaseData.xls"; + } } diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestMatch.java b/src/testcases/org/apache/poi/ss/formula/functions/TestMatch.java index f9fdab6a79..ee6e45c1fa 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestMatch.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestMatch.java @@ -19,6 +19,8 @@ package org.apache.poi.ss.formula.functions; import junit.framework.TestCase; +import org.apache.poi.hssf.HSSFTestDataSamples; +import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.formula.eval.AreaEval; import org.apache.poi.ss.formula.eval.BoolEval; import org.apache.poi.ss.formula.eval.ErrorEval; @@ -26,11 +28,13 @@ import org.apache.poi.ss.formula.eval.NumberEval; import org.apache.poi.ss.formula.eval.NumericValueEval; import org.apache.poi.ss.formula.eval.StringEval; import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.usermodel.CellValue; /** * Test cases for MATCH() * * @author Josh Micich + * @author Cedric Walter at innoveo.com */ public final class TestMatch extends TestCase { /** less than or equal to */ @@ -93,7 +97,7 @@ public final class TestMatch extends TestCase { } public void testSimpleString() { - + // Arrange ValueEval[] values = { new StringEval("Albert"), new StringEval("Charles"), @@ -109,10 +113,52 @@ public final class TestMatch extends TestCase { confirmInt(3, invokeMatch(new StringEval("eD"), ae, MATCH_LARGEST_LTE)); confirmInt(3, invokeMatch(new StringEval("Ed"), ae, MATCH_EXACT)); confirmInt(3, invokeMatch(new StringEval("ed"), ae, MATCH_EXACT)); - confirmInt(4, invokeMatch(new StringEval("Hugh"), ae, MATCH_LARGEST_LTE)); + assertEquals(ErrorEval.NA, invokeMatch(new StringEval("Hugh"), ae, MATCH_EXACT)); } + public void testSimpleWildcardValuesString() { + // Arrange + ValueEval[] values = { + new StringEval("Albert"), + new StringEval("Charles"), + new StringEval("Ed"), + new StringEval("Greg"), + new StringEval("Ian"), + }; + + AreaEval ae = EvalFactory.createAreaEval("A1:A5", values); + + // Note String comparisons are case insensitive + confirmInt(3, invokeMatch(new StringEval("e*"), ae, MATCH_EXACT)); + confirmInt(3, invokeMatch(new StringEval("*d"), ae, MATCH_EXACT)); + + confirmInt(1, invokeMatch(new StringEval("Al*"), ae, MATCH_EXACT)); + confirmInt(2, invokeMatch(new StringEval("Char*"), ae, MATCH_EXACT)); + + confirmInt(4, invokeMatch(new StringEval("*eg"), ae, MATCH_EXACT)); + confirmInt(4, invokeMatch(new StringEval("G?eg"), ae, MATCH_EXACT)); + confirmInt(4, invokeMatch(new StringEval("??eg"), ae, MATCH_EXACT)); + confirmInt(4, invokeMatch(new StringEval("G*?eg"), ae, MATCH_EXACT)); + confirmInt(4, invokeMatch(new StringEval("Hugh"), ae, MATCH_LARGEST_LTE)); + + confirmInt(5, invokeMatch(new StringEval("*Ian*"), ae, MATCH_EXACT)); + confirmInt(5, invokeMatch(new StringEval("*Ian*"), ae, MATCH_LARGEST_LTE)); + } + + public void testTildeString() { + + ValueEval[] values = { + new StringEval("what?"), + new StringEval("all*") + }; + + AreaEval ae = EvalFactory.createAreaEval("A1:A2", values); + + confirmInt(1, invokeMatch(new StringEval("what~?"), ae, MATCH_EXACT)); + confirmInt(2, invokeMatch(new StringEval("all~*"), ae, MATCH_EXACT)); + } + public void testSimpleBoolean() { ValueEval[] values = { @@ -159,11 +205,17 @@ public final class TestMatch extends TestCase { confirmInt(3, invokeMatch(new NumberEval(5), ae, MATCH_EXACT)); confirmInt(8, invokeMatch(new StringEval("CHARLES"), ae, MATCH_EXACT)); + //wildcard values + confirmInt(8, invokeMatch(new StringEval("CHAR*"), ae, MATCH_EXACT)); + confirmInt(8, invokeMatch(new StringEval("*CHARLES"), ae, MATCH_EXACT)); confirmInt(4, invokeMatch(new StringEval("Ben"), ae, MATCH_LARGEST_LTE)); confirmInt(13, invokeMatch(new StringEval("ED"), ae, MATCH_LARGEST_LTE)); + confirmInt(13, invokeMatch(new StringEval("ED*"), ae, MATCH_LARGEST_LTE)); + confirmInt(13, invokeMatch(new StringEval("*ED"), ae, MATCH_LARGEST_LTE)); confirmInt(9, invokeMatch(new StringEval("ED"), ae, MATCH_EXACT)); + confirmInt(9, invokeMatch(new StringEval("ED*"), ae, MATCH_EXACT)); confirmInt(13, invokeMatch(new StringEval("Hugh"), ae, MATCH_LARGEST_LTE)); assertEquals(ErrorEval.NA, invokeMatch(new StringEval("Hugh"), ae, MATCH_EXACT)); diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java b/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java new file mode 100644 index 0000000000..7fcb1fa13b --- /dev/null +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java @@ -0,0 +1,40 @@ +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+
+
+/**
+ * Tests lookup functions (VLOOKUP, HLOOKUP, LOOKUP, MATCH) as loaded from a test data spreadsheet.<p/>
+ * These tests have been separated from the common function and operator tests because the lookup
+ * functions have more complex test cases and test data setup.
+ *
+ * Tests for bug fixes and specific/tricky behaviour can be found in the corresponding test class
+ * (<tt>TestXxxx</tt>) of the target (<tt>Xxxx</tt>) implementor, where execution can be observed
+ * more easily.
+ *
+ * @author Josh Micich
+ * @author Cedric Walter at innoveo.com
+ */
+public final class TestMatchFunctionsFromSpreadsheet extends BaseTestFunctionsFromSpreadsheet {
+
+ @Override
+ protected String getFilename() {
+ return "MatchFunctionTestCaseData.xls";
+ }
+}
diff --git a/test-data/spreadsheet/IndexFunctionTestCaseData.xls b/test-data/spreadsheet/IndexFunctionTestCaseData.xls Binary files differindex d1de304576..64df3ecd86 100644 --- a/test-data/spreadsheet/IndexFunctionTestCaseData.xls +++ b/test-data/spreadsheet/IndexFunctionTestCaseData.xls diff --git a/test-data/spreadsheet/LookupFunctionsTestCaseData.xls b/test-data/spreadsheet/LookupFunctionsTestCaseData.xls Binary files differindex ea7fad9f3d..56b6f12a00 100644 --- a/test-data/spreadsheet/LookupFunctionsTestCaseData.xls +++ b/test-data/spreadsheet/LookupFunctionsTestCaseData.xls diff --git a/test-data/spreadsheet/MatchFunctionTestCaseData.xls b/test-data/spreadsheet/MatchFunctionTestCaseData.xls Binary files differnew file mode 100644 index 0000000000..0b98a17713 --- /dev/null +++ b/test-data/spreadsheet/MatchFunctionTestCaseData.xls |