You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2013/03/16 13:33:08 UTC
svn commit: r1457243 - in /poi/trunk:
src/java/org/apache/poi/ss/formula/functions/
src/testcases/org/apache/poi/ss/formula/functions/ test-data/spreadsheet/
Author: yegor
Date: Sat Mar 16 12:33:08 2013
New Revision: 1457243
URL: http://svn.apache.org/r1457243
Log:
Bug 54673 - [PATCH] Simple wildcard support in HLOOKUP, VOOLKUP, MATCH, COUNTIF
Added:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java
poi/trunk/test-data/spreadsheet/MatchFunctionTestCaseData.xls (with props)
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Match.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Vlookup.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIndexFunctionFromSpreadsheet.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestLookupFunctionsFromSpreadsheet.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMatch.java
poi/trunk/test-data/spreadsheet/IndexFunctionTestCaseData.xls
poi/trunk/test-data/spreadsheet/LookupFunctionsTestCaseData.xls
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java?rev=1457243&r1=1457242&r2=1457243&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java Sat Mar 16 12:33:08 2013
@@ -43,6 +43,7 @@ import org.apache.poi.ss.usermodel.Error
* </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 Fixed
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 Fixed
* 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(".*");
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java?rev=1457243&r1=1457242&r2=1457243&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/LookupUtils.java Sat Mar 16 12:33:08 2013
@@ -29,10 +29,14 @@ import org.apache.poi.ss.formula.eval.St
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);
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Match.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Match.java?rev=1457243&r1=1457242&r2=1457243&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Match.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Match.java Sat Mar 16 12:33:08 2013
@@ -62,6 +62,7 @@ import org.apache.poi.ss.formula.TwoDEva
*
*
* @author Josh Micich
+ * @author Cedric Walter at innoveo.com
*/
public final class Match extends Var2or3ArgFunction {
@@ -232,14 +233,7 @@ public final class Match extends Var2or3
}
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) {
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Vlookup.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Vlookup.java?rev=1457243&r1=1457242&r2=1457243&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Vlookup.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Vlookup.java Sat Mar 16 12:33:08 2013
@@ -38,6 +38,7 @@ import org.apache.poi.ss.formula.TwoDEva
* 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 Var3o
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) {
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java?rev=1457243&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java Sat Mar 16 12:33:08 2013
@@ -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) + ")");
+ }
+
+}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java?rev=1457243&r1=1457242&r2=1457243&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java Sat Mar 16 12:33:08 2013
@@ -43,6 +43,7 @@ import org.apache.poi.ss.util.CellRefere
* Test cases for COUNT(), COUNTA() COUNTIF(), COUNTBLANK()
*
* @author Josh Micich
+ * @author Cedric Walter at innoveo.com
*/
public final class TestCountFuncs extends TestCase {
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIndexFunctionFromSpreadsheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIndexFunctionFromSpreadsheet.java?rev=1457243&r1=1457242&r2=1457243&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIndexFunctionFromSpreadsheet.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIndexFunctionFromSpreadsheet.java Sat Mar 16 12:33:08 2013
@@ -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
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestLookupFunctionsFromSpreadsheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestLookupFunctionsFromSpreadsheet.java?rev=1457243&r1=1457242&r2=1457243&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestLookupFunctionsFromSpreadsheet.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestLookupFunctionsFromSpreadsheet.java Sat Mar 16 12:33:08 2013
@@ -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.CellV
* 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";
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMatch.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMatch.java?rev=1457243&r1=1457242&r2=1457243&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMatch.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMatch.java Sat Mar 16 12:33:08 2013
@@ -19,6 +19,8 @@ package org.apache.poi.ss.formula.functi
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.Nu
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 Tes
}
public void testSimpleString() {
-
+ // Arrange
ValueEval[] values = {
new StringEval("Albert"),
new StringEval("Charles"),
@@ -109,10 +113,52 @@ public final class TestMatch extends Tes
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 Tes
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));
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java?rev=1457243&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMatchFunctionsFromSpreadsheet.java Sat Mar 16 12:33:08 2013
@@ -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";
+ }
+}
Modified: poi/trunk/test-data/spreadsheet/IndexFunctionTestCaseData.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/IndexFunctionTestCaseData.xls?rev=1457243&r1=1457242&r2=1457243&view=diff
==============================================================================
Binary files - no diff available.
Modified: poi/trunk/test-data/spreadsheet/LookupFunctionsTestCaseData.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/LookupFunctionsTestCaseData.xls?rev=1457243&r1=1457242&r2=1457243&view=diff
==============================================================================
Binary files - no diff available.
Added: poi/trunk/test-data/spreadsheet/MatchFunctionTestCaseData.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/MatchFunctionTestCaseData.xls?rev=1457243&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/MatchFunctionTestCaseData.xls
------------------------------------------------------------------------------
svn:mime-type = application/vnd.ms-excel
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org