--- C:\josh\client\poi\svn\trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (revision 637139) +++ C:\josh\client\poi\svn\trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (working copy) @@ -24,71 +24,40 @@ import org.apache.poi.hssf.model.FormulaParser; import org.apache.poi.hssf.model.Workbook; -import org.apache.poi.hssf.record.formula.AddPtg; import org.apache.poi.hssf.record.formula.Area3DPtg; import org.apache.poi.hssf.record.formula.AreaPtg; import org.apache.poi.hssf.record.formula.AttrPtg; import org.apache.poi.hssf.record.formula.BoolPtg; -import org.apache.poi.hssf.record.formula.ConcatPtg; import org.apache.poi.hssf.record.formula.ControlPtg; -import org.apache.poi.hssf.record.formula.DividePtg; -import org.apache.poi.hssf.record.formula.EqualPtg; -import org.apache.poi.hssf.record.formula.FuncPtg; -import org.apache.poi.hssf.record.formula.FuncVarPtg; -import org.apache.poi.hssf.record.formula.GreaterEqualPtg; -import org.apache.poi.hssf.record.formula.GreaterThanPtg; import org.apache.poi.hssf.record.formula.IntPtg; -import org.apache.poi.hssf.record.formula.LessEqualPtg; -import org.apache.poi.hssf.record.formula.LessThanPtg; import org.apache.poi.hssf.record.formula.MemErrPtg; import org.apache.poi.hssf.record.formula.MissingArgPtg; -import org.apache.poi.hssf.record.formula.MultiplyPtg; import org.apache.poi.hssf.record.formula.NamePtg; import org.apache.poi.hssf.record.formula.NameXPtg; -import org.apache.poi.hssf.record.formula.NotEqualPtg; import org.apache.poi.hssf.record.formula.NumberPtg; import org.apache.poi.hssf.record.formula.OperationPtg; import org.apache.poi.hssf.record.formula.ParenthesisPtg; -import org.apache.poi.hssf.record.formula.PowerPtg; import org.apache.poi.hssf.record.formula.Ptg; import org.apache.poi.hssf.record.formula.Ref3DPtg; import org.apache.poi.hssf.record.formula.ReferencePtg; import org.apache.poi.hssf.record.formula.StringPtg; -import org.apache.poi.hssf.record.formula.SubtractPtg; -import org.apache.poi.hssf.record.formula.UnaryMinusPtg; -import org.apache.poi.hssf.record.formula.UnaryPlusPtg; import org.apache.poi.hssf.record.formula.UnionPtg; import org.apache.poi.hssf.record.formula.UnknownPtg; -import org.apache.poi.hssf.record.formula.eval.AddEval; import org.apache.poi.hssf.record.formula.eval.Area2DEval; import org.apache.poi.hssf.record.formula.eval.Area3DEval; import org.apache.poi.hssf.record.formula.eval.AreaEval; import org.apache.poi.hssf.record.formula.eval.BlankEval; import org.apache.poi.hssf.record.formula.eval.BoolEval; -import org.apache.poi.hssf.record.formula.eval.ConcatEval; -import org.apache.poi.hssf.record.formula.eval.DivideEval; -import org.apache.poi.hssf.record.formula.eval.EqualEval; import org.apache.poi.hssf.record.formula.eval.ErrorEval; import org.apache.poi.hssf.record.formula.eval.Eval; -import org.apache.poi.hssf.record.formula.eval.FuncVarEval; import org.apache.poi.hssf.record.formula.eval.FunctionEval; -import org.apache.poi.hssf.record.formula.eval.GreaterEqualEval; -import org.apache.poi.hssf.record.formula.eval.GreaterThanEval; -import org.apache.poi.hssf.record.formula.eval.LessEqualEval; -import org.apache.poi.hssf.record.formula.eval.LessThanEval; -import org.apache.poi.hssf.record.formula.eval.MultiplyEval; import org.apache.poi.hssf.record.formula.eval.NameEval; -import org.apache.poi.hssf.record.formula.eval.NotEqualEval; import org.apache.poi.hssf.record.formula.eval.NumberEval; import org.apache.poi.hssf.record.formula.eval.OperationEval; -import org.apache.poi.hssf.record.formula.eval.PowerEval; import org.apache.poi.hssf.record.formula.eval.Ref2DEval; import org.apache.poi.hssf.record.formula.eval.Ref3DEval; import org.apache.poi.hssf.record.formula.eval.RefEval; import org.apache.poi.hssf.record.formula.eval.StringEval; -import org.apache.poi.hssf.record.formula.eval.SubtractEval; -import org.apache.poi.hssf.record.formula.eval.UnaryMinusEval; -import org.apache.poi.hssf.record.formula.eval.UnaryPlusEval; import org.apache.poi.hssf.record.formula.eval.ValueEval; /** @@ -98,8 +67,6 @@ public class HSSFFormulaEvaluator { // params to lookup the right constructor using reflection - private static final Class[] OPERATION_CONSTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class }; - private static final Class[] VALUE_CONTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class }; private static final Class[] AREA3D_CONSTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class, ValueEval[].class }; @@ -111,8 +78,6 @@ // Maps for mapping *Eval to *Ptg private static final Map VALUE_EVALS_MAP = new HashMap(); - private static final Map OPERATION_EVALS_MAP = new HashMap(); - /* * Following is the mapping between the Ptg tokens returned * by the FormulaParser and the *Eval classes that are used @@ -124,26 +89,6 @@ VALUE_EVALS_MAP.put(NumberPtg.class, NumberEval.class); VALUE_EVALS_MAP.put(StringPtg.class, StringEval.class); - OPERATION_EVALS_MAP.put(AddPtg.class, AddEval.class); - OPERATION_EVALS_MAP.put(ConcatPtg.class, ConcatEval.class); - OPERATION_EVALS_MAP.put(DividePtg.class, DivideEval.class); - OPERATION_EVALS_MAP.put(EqualPtg.class, EqualEval.class); - //OPERATION_EVALS_MAP.put(ExpPtg.class, ExpEval.class); // TODO: check - // this - OPERATION_EVALS_MAP.put(FuncPtg.class, FuncVarEval.class); // TODO: - // check this - OPERATION_EVALS_MAP.put(FuncVarPtg.class, FuncVarEval.class); - OPERATION_EVALS_MAP.put(GreaterEqualPtg.class, GreaterEqualEval.class); - OPERATION_EVALS_MAP.put(GreaterThanPtg.class, GreaterThanEval.class); - OPERATION_EVALS_MAP.put(LessEqualPtg.class, LessEqualEval.class); - OPERATION_EVALS_MAP.put(LessThanPtg.class, LessThanEval.class); - OPERATION_EVALS_MAP.put(MultiplyPtg.class, MultiplyEval.class); - OPERATION_EVALS_MAP.put(NotEqualPtg.class, NotEqualEval.class); - OPERATION_EVALS_MAP.put(PowerPtg.class, PowerEval.class); - OPERATION_EVALS_MAP.put(SubtractPtg.class, SubtractEval.class); - OPERATION_EVALS_MAP.put(UnaryMinusPtg.class, UnaryMinusEval.class); - OPERATION_EVALS_MAP.put(UnaryPlusPtg.class, UnaryPlusEval.class); - } @@ -402,7 +347,7 @@ if (optg instanceof AttrPtg) { continue; } if (optg instanceof UnionPtg) { continue; } - OperationEval operation = (OperationEval) getOperationEvalForPtg(optg); + OperationEval operation = OperationEvaluatorFactory.create(optg); int numops = operation.getNumberOfOperands(); Eval[] ops = new Eval[numops]; @@ -558,25 +503,6 @@ } /** - * returns the OperationEval concrete impl instance corresponding - * to the suplied operationPtg - * @param ptg - */ - protected static Eval getOperationEvalForPtg(OperationPtg ptg) { - Eval retval = null; - - Class clazz = (Class) OPERATION_EVALS_MAP.get(ptg.getClass()); - try { - Constructor constructor = clazz.getConstructor(OPERATION_CONSTRUCTOR_CLASS_ARRAY); - retval = (OperationEval) constructor.newInstance(new Ptg[] { ptg }); - } - catch (Exception e) { - throw new RuntimeException("Fatal Error: ", e); - } - return retval; - } - - /** * returns an appropriate Eval impl instance for the Ptg. The Ptg must be * one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg, * StringPtg, BoolPtg
special Note: OperationPtg subtypes cannot be --- C:\josh\client\poi\svn\trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java (revision 637139) +++ C:\josh\client\poi\svn\trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java (working copy) @@ -15,7 +15,6 @@ * limitations under the License. */ - package org.apache.poi.hssf.record.formula.eval; import java.io.FileInputStream; @@ -59,36 +58,36 @@ * Name of the test spreadsheet (found in the standard test data folder) */ public final static String FILENAME = "FormulaEvalTestData.xls"; - /** - * Row (zero-based) in the test spreadsheet where the operator examples start. - */ + /** + * Row (zero-based) in the test spreadsheet where the operator examples start. + */ public static final int START_OPERATORS_ROW_INDEX = 22; // Row '23' - /** - * Row (zero-based) in the test spreadsheet where the function examples start. - */ - public static final int START_FUNCTIONS_ROW_INDEX = 83; // Row '84' + /** + * Row (zero-based) in the test spreadsheet where the function examples start. + */ + public static final int START_FUNCTIONS_ROW_INDEX = 87; // Row '88' /** * Index of the column that contains the function names */ - public static final short COLUMN_INDEX_FUNCTION_NAME = 1; // Column 'B' + public static final short COLUMN_INDEX_FUNCTION_NAME = 1; // Column 'B' - /** - * Used to indicate when there are no more functions left - */ + /** + * Used to indicate when there are no more functions left + */ public static final String FUNCTION_NAMES_END_SENTINEL = ""; /** * Index of the column where the test values start (for each function) */ - public static final short COLUMN_INDEX_FIRST_TEST_VALUE = 3; // Column 'D' - - /** - * Each function takes 4 rows in the test spreadsheet - */ + public static final short COLUMN_INDEX_FIRST_TEST_VALUE = 3; // Column 'D' + + /** + * Each function takes 4 rows in the test spreadsheet + */ public static final int NUMBER_OF_ROWS_PER_FUNCTION = 4; } - private HSSFWorkbook workbook; + private HSSFWorkbook workbook; private HSSFSheet sheet; // Note - multiple failures are aggregated before ending. // If one or more functions fail, a single AssertionFailedError is thrown at the end @@ -97,138 +96,138 @@ private int _evaluationFailureCount; private int _evaluationSuccessCount; - private static final HSSFCell getExpectedValueCell(HSSFRow row, short columnIndex) { - if (row == null) { - return null; - } - return row.getCell(columnIndex); - } + private static final HSSFCell getExpectedValueCell(HSSFRow row, short columnIndex) { + if (row == null) { + return null; + } + return row.getCell(columnIndex); + } - private static void confirmExpectedResult(String msg, HSSFCell expected, HSSFFormulaEvaluator.CellValue actual) { - if (expected == null) { + private static void confirmExpectedResult(String msg, HSSFCell expected, HSSFFormulaEvaluator.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_STRING) { - String value = expected.getRichStringCellValue().getString(); - if (value.startsWith("#")) { - // TODO - this code never called - expected.setCellType(HSSFCell.CELL_TYPE_ERROR); - // expected.setCellErrorValue(...?); - } + String value = expected.getRichStringCellValue().getString(); + if (value.startsWith("#")) { + // TODO - this code never called + expected.setCellType(HSSFCell.CELL_TYPE_ERROR); + // expected.setCellErrorValue(...?); + } } switch (expected.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: - assertEquals(msg, HSSFCell.CELL_TYPE_BLANK, actual.getCellType()); - break; + assertEquals(msg, HSSFCell.CELL_TYPE_BLANK, actual.getCellType()); + break; case HSSFCell.CELL_TYPE_BOOLEAN: - assertEquals(msg, HSSFCell.CELL_TYPE_BOOLEAN, actual.getCellType()); - assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue()); - break; + assertEquals(msg, HSSFCell.CELL_TYPE_BOOLEAN, actual.getCellType()); + assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue()); + break; case HSSFCell.CELL_TYPE_ERROR: - assertEquals(msg, HSSFCell.CELL_TYPE_ERROR, actual.getCellType()); - if(false) { // TODO: fix ~45 functions which are currently returning incorrect error values - assertEquals(msg, expected.getErrorCellValue(), actual.getErrorValue()); - } - break; + assertEquals(msg, HSSFCell.CELL_TYPE_ERROR, actual.getCellType()); + if(false) { // TODO: fix ~45 functions which are currently returning incorrect error values + assertEquals(msg, expected.getErrorCellValue(), actual.getErrorValue()); + } + 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); + throw new AssertionFailedError("Cannot expect formula as result of formula evaluation: " + msg); case HSSFCell.CELL_TYPE_NUMERIC: - assertEquals(msg, HSSFCell.CELL_TYPE_NUMERIC, actual.getCellType()); - TestMathX.assertEquals(msg, expected.getNumericCellValue(), actual.getNumberValue(), TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR); -// double delta = Math.abs(expected.getNumericCellValue()-actual.getNumberValue()); -// double pctExpected = Math.abs(0.00001*expected.getNumericCellValue()); -// assertTrue(msg, delta <= pctExpected); - break; + assertEquals(msg, HSSFCell.CELL_TYPE_NUMERIC, actual.getCellType()); + TestMathX.assertEquals(msg, expected.getNumericCellValue(), actual.getNumberValue(), TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR); +// double delta = Math.abs(expected.getNumericCellValue()-actual.getNumberValue()); +// double pctExpected = Math.abs(0.00001*expected.getNumericCellValue()); +// assertTrue(msg, delta <= pctExpected); + break; case HSSFCell.CELL_TYPE_STRING: - assertEquals(msg, HSSFCell.CELL_TYPE_STRING, actual.getCellType()); - assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getRichTextStringValue().getString()); - break; + assertEquals(msg, HSSFCell.CELL_TYPE_STRING, actual.getCellType()); + assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getRichTextStringValue().getString()); + break; } - } + } protected void setUp() throws Exception { - if (workbook == null) { - String filePath = System.getProperty("HSSF.testdata.path")+ "/" + SS.FILENAME; - FileInputStream fin = new FileInputStream( filePath ); - workbook = new HSSFWorkbook( fin ); - sheet = workbook.getSheetAt( 0 ); - } - _functionFailureCount = 0; - _functionSuccessCount = 0; - _evaluationFailureCount = 0; - _evaluationSuccessCount = 0; - } - - public void testFunctionsFromTestSpreadsheet() { - - processFunctionGroup(SS.START_OPERATORS_ROW_INDEX, null); - processFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, null); - // example for debugging individual functions/operators: -// processFunctionGroup(SS.START_OPERATORS_ROW_INDEX, "ConcatEval"); -// processFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, "AVERAGE"); - - // confirm results - String successMsg = "There were " - + _evaluationSuccessCount + " successful evaluation(s) and " + if (workbook == null) { + String filePath = System.getProperty("HSSF.testdata.path")+ "/" + SS.FILENAME; + FileInputStream fin = new FileInputStream( filePath ); + workbook = new HSSFWorkbook( fin ); + sheet = workbook.getSheetAt( 0 ); + } + _functionFailureCount = 0; + _functionSuccessCount = 0; + _evaluationFailureCount = 0; + _evaluationSuccessCount = 0; + } + + public void testFunctionsFromTestSpreadsheet() { + + processFunctionGroup(SS.START_OPERATORS_ROW_INDEX, null); + processFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, null); + // example for debugging individual functions/operators: +// processFunctionGroup(SS.START_OPERATORS_ROW_INDEX, "ConcatEval"); +// processFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, "AVERAGE"); + + // confirm results + String successMsg = "There were " + + _evaluationSuccessCount + " successful evaluation(s) and " + _functionSuccessCount + " function(s) without error"; if(_functionFailureCount > 0) { String msg = _functionFailureCount + " function(s) failed in " + _evaluationFailureCount + " evaluation(s). " + successMsg; - throw new AssertionFailedError(msg); - } + throw new AssertionFailedError(msg); + } if(false) { // normally no output for successful tests System.out.println(getClass().getName() + ": " + successMsg); } } - /** - * @param startRowIndex row index in the spreadsheet where the first function/operator is found - * @param testFocusFunctionName name of a single function/operator to test alone. - * Typically pass null to test all functions - */ + /** + * @param startRowIndex row index in the spreadsheet where the first function/operator is found + * @param testFocusFunctionName name of a single function/operator to test alone. + * Typically pass null to test all functions + */ private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) { HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook); - int rowIndex = startRowIndex; - while (true) { - HSSFRow r = sheet.getRow(rowIndex); - String targetFunctionName = getTargetFunctionName(r); - if(targetFunctionName == null) { - throw new AssertionFailedError("Test spreadsheet cell empty on row (" - + (rowIndex+1) + "). Expected function name or '" - + SS.FUNCTION_NAMES_END_SENTINEL + "'"); - } - if(targetFunctionName.equals(SS.FUNCTION_NAMES_END_SENTINEL)) { - // found end of functions list - break; - } - if(testFocusFunctionName == null || targetFunctionName.equalsIgnoreCase(testFocusFunctionName)) { - - // expected results are on the row below - HSSFRow expectedValuesRow = sheet.getRow(rowIndex + 1); - if(expectedValuesRow == null) { - int missingRowNum = rowIndex + 2; //+1 for 1-based, +1 for next row - throw new AssertionFailedError("Missing expected values row for function '" - + targetFunctionName + " (row " + missingRowNum + ")"); - } - switch(processFunctionRow(evaluator, targetFunctionName, r, expectedValuesRow)) { - case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break; - case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break; - default: - throw new RuntimeException("unexpected result"); - case Result.NO_EVALUATIONS_FOUND: // do nothing - } - } - rowIndex += SS.NUMBER_OF_ROWS_PER_FUNCTION; - } + int rowIndex = startRowIndex; + while (true) { + HSSFRow r = sheet.getRow(rowIndex); + String targetFunctionName = getTargetFunctionName(r); + if(targetFunctionName == null) { + throw new AssertionFailedError("Test spreadsheet cell empty on row (" + + (rowIndex+1) + "). Expected function name or '" + + SS.FUNCTION_NAMES_END_SENTINEL + "'"); + } + if(targetFunctionName.equals(SS.FUNCTION_NAMES_END_SENTINEL)) { + // found end of functions list + break; + } + if(testFocusFunctionName == null || targetFunctionName.equalsIgnoreCase(testFocusFunctionName)) { + + // expected results are on the row below + HSSFRow expectedValuesRow = sheet.getRow(rowIndex + 1); + if(expectedValuesRow == null) { + int missingRowNum = rowIndex + 2; //+1 for 1-based, +1 for next row + throw new AssertionFailedError("Missing expected values row for function '" + + targetFunctionName + " (row " + missingRowNum + ")"); + } + switch(processFunctionRow(evaluator, targetFunctionName, r, expectedValuesRow)) { + case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break; + case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break; + default: + throw new RuntimeException("unexpected result"); + case Result.NO_EVALUATIONS_FOUND: // do nothing + } + } + rowIndex += SS.NUMBER_OF_ROWS_PER_FUNCTION; + } } /** @@ -236,16 +235,16 @@ * @return a constant from the local Result class denoting whether there were any evaluation * cases, and whether they all succeeded. */ - private int processFunctionRow(HSSFFormulaEvaluator evaluator, String targetFunctionName, - HSSFRow formulasRow, HSSFRow expectedValuesRow) { - - int result = Result.NO_EVALUATIONS_FOUND; // so far - short endcolnum = formulasRow.getLastCellNum(); - evaluator.setCurrentRow(formulasRow); + private int processFunctionRow(HSSFFormulaEvaluator evaluator, String targetFunctionName, + HSSFRow formulasRow, HSSFRow expectedValuesRow) { + + int result = Result.NO_EVALUATIONS_FOUND; // so far + short endcolnum = formulasRow.getLastCellNum(); + evaluator.setCurrentRow(formulasRow); - // iterate across the row for all the evaluation cases - for (short colnum=SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++) { - HSSFCell c = formulasRow.getCell(colnum); + // iterate across the row for all the evaluation cases + for (short colnum=SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++) { + HSSFCell c = formulasRow.getCell(colnum); if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) { continue; } @@ -265,13 +264,13 @@ printShortStackTrace(System.err, e); result = Result.SOME_EVALUATIONS_FAILED; } - } + } return result; } - /** - * Useful to keep output concise when expecting many failures to be reported by this test case - */ + /** + * Useful to keep output concise when expecting many failures to be reported by this test case + */ private static void printShortStackTrace(PrintStream ps, AssertionFailedError e) { StackTraceElement[] stes = e.getStackTrace(); @@ -304,8 +303,8 @@ } /** - * @return null if cell is missing, empty or blank - */ + * @return null if cell is missing, empty or blank + */ private static String getTargetFunctionName(HSSFRow r) { if(r == null) { System.err.println("Warning - given null row, can't figure out function name"); --- C:\josh\client\poi\svn\trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/AllFormulaEvalTests.java (revision 637139) +++ C:\josh\client\poi\svn\trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/AllFormulaEvalTests.java (working copy) @@ -32,6 +32,7 @@ result.addTestSuite(TestCircularReferences.class); result.addTestSuite(TestExternalFunction.class); result.addTestSuite(TestFormulasFromSpreadsheet.class); + result.addTestSuite(TestPercentEval.class); result.addTestSuite(TestUnaryPlusEval.class); return result; }