From 658c123cd28e42f6b41799cf786fedb7729f6d3e Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Wed, 21 Oct 2009 23:00:09 +0000 Subject: [PATCH] Bugzilla 48036 - added IntersectionEval to allow evaluation of the intersection formula operator. git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@828244 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 1 + .../record/formula/eval/IntersectionEval.java | 98 +++++++++++++++++++ .../ss/formula/OperationEvaluatorFactory.java | 3 + .../poi/ss/formula/WorkbookEvaluator.java | 7 +- .../eval/TestFormulasFromSpreadsheet.java | 56 +++++------ 5 files changed, 135 insertions(+), 30 deletions(-) create mode 100644 src/java/org/apache/poi/hssf/record/formula/eval/IntersectionEval.java diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index dac810ede6..6dac6eac44 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,6 +33,7 @@ + 48036 - added IntersectionEval to allow evaluation of the intersection formula operator 47999 - avoid un-needed call to the JVM Garbage Collector when working on OOXML OPC Packages 47922 - added example HSMF application that converts a .msg file to text and extracts attachments 47903 - added Ant target to compile scratchpad examples diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/IntersectionEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/IntersectionEval.java new file mode 100644 index 0000000000..290bc9f74e --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/eval/IntersectionEval.java @@ -0,0 +1,98 @@ +/* ==================================================================== + 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.hssf.record.formula.eval; + +import org.apache.poi.hssf.record.formula.functions.Function; + +/** + * @author Josh Micich + */ +public final class IntersectionEval implements Function { + + public static final Function instance = new IntersectionEval(); + + private IntersectionEval() { + // enforces singleton + } + + public ValueEval evaluate(ValueEval[] args, int srcRow, short srcCol) { + if(args.length != 2) { + return ErrorEval.VALUE_INVALID; + } + + try { + AreaEval reA = evaluateRef(args[0]); + AreaEval reB = evaluateRef(args[1]); + AreaEval result = resolveRange(reA, reB); + if (result == null) { + return ErrorEval.NULL_INTERSECTION; + } + return result; + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + /** + * @return simple rectangular {@link AreaEval} which represents the intersection of areas + * aeA and aeB. If the two areas do not intersect, the result is null. + */ + private static AreaEval resolveRange(AreaEval aeA, AreaEval aeB) { + + int aeAfr = aeA.getFirstRow(); + int aeAfc = aeA.getFirstColumn(); + int aeBlc = aeB.getLastColumn(); + if (aeAfc > aeBlc) { + return null; + } + int aeBfc = aeB.getFirstColumn(); + if (aeBfc > aeA.getLastColumn()) { + return null; + } + int aeBlr = aeB.getLastRow(); + if (aeAfr > aeBlr) { + return null; + } + int aeBfr = aeB.getFirstRow(); + int aeAlr = aeA.getLastRow(); + if (aeBfr > aeAlr) { + return null; + } + + + int top = Math.max(aeAfr, aeBfr); + int bottom = Math.min(aeAlr, aeBlr); + int left = Math.max(aeAfc, aeBfc); + int right = Math.min(aeA.getLastColumn(), aeBlc); + + return aeA.offset(top-aeAfr, bottom-aeAfr, left-aeAfc, right-aeAfc); + } + + private static AreaEval evaluateRef(ValueEval arg) throws EvaluationException { + if (arg instanceof AreaEval) { + return (AreaEval) arg; + } + if (arg instanceof RefEval) { + return ((RefEval) arg).offset(0, 0, 0, 0); + } + if (arg instanceof ErrorEval) { + throw new EvaluationException((ErrorEval)arg); + } + throw new IllegalArgumentException("Unexpected ref arg class (" + arg.getClass().getName() + ")"); + } +} diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java index 3af37bb20d..e416a7ee87 100644 --- a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java +++ b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java @@ -29,6 +29,7 @@ import org.apache.poi.hssf.record.formula.DividePtg; import org.apache.poi.hssf.record.formula.EqualPtg; import org.apache.poi.hssf.record.formula.GreaterEqualPtg; import org.apache.poi.hssf.record.formula.GreaterThanPtg; +import org.apache.poi.hssf.record.formula.IntersectionPtg; import org.apache.poi.hssf.record.formula.LessEqualPtg; import org.apache.poi.hssf.record.formula.LessThanPtg; import org.apache.poi.hssf.record.formula.MultiplyPtg; @@ -42,6 +43,7 @@ import org.apache.poi.hssf.record.formula.UnaryMinusPtg; import org.apache.poi.hssf.record.formula.UnaryPlusPtg; import org.apache.poi.hssf.record.formula.eval.ConcatEval; import org.apache.poi.hssf.record.formula.eval.FunctionEval; +import org.apache.poi.hssf.record.formula.eval.IntersectionEval; import org.apache.poi.hssf.record.formula.eval.OperationEval; import org.apache.poi.hssf.record.formula.eval.PercentEval; import org.apache.poi.hssf.record.formula.eval.RangeEval; @@ -86,6 +88,7 @@ final class OperationEvaluatorFactory { put(m, 1, UnaryMinusPtg.instance, UnaryMinusEval.instance); put(m, 1, UnaryPlusPtg.instance, UnaryPlusEval.instance); put(m, 2, RangePtg.instance, RangeEval.instance); + put(m, 2, IntersectionPtg.instance, IntersectionEval.instance); return m; } diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java index 66da4ca252..2a272fa3db 100644 --- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java @@ -33,6 +33,7 @@ import org.apache.poi.hssf.record.formula.ErrPtg; import org.apache.poi.hssf.record.formula.ExpPtg; import org.apache.poi.hssf.record.formula.FuncVarPtg; import org.apache.poi.hssf.record.formula.IntPtg; +import org.apache.poi.hssf.record.formula.MemAreaPtg; import org.apache.poi.hssf.record.formula.MemErrPtg; import org.apache.poi.hssf.record.formula.MemFuncPtg; import org.apache.poi.hssf.record.formula.MissingArgPtg; @@ -348,11 +349,13 @@ public final class WorkbookEvaluator { // skip Parentheses, Attr, etc continue; } - if (ptg instanceof MemFuncPtg) { + if (ptg instanceof MemFuncPtg || ptg instanceof MemAreaPtg) { // can ignore, rest of tokens for this expression are in OK RPN order continue; } - if (ptg instanceof MemErrPtg) { continue; } + if (ptg instanceof MemErrPtg) { + continue; + } ValueEval opResult; if (ptg instanceof OperationPtg) { diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java index aee26093e2..2c7c7c8df4 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java @@ -37,24 +37,24 @@ import org.apache.poi.ss.usermodel.Sheet; * This class does not test implementors of Function and OperationEval in * isolation. Much of the evaluation engine (i.e. HSSFFormulaEvaluator, ...) gets * exercised as well. Tests for bug fixes and specific/tricky behaviour can be found in the - * corresponding test class (TestXxxx) of the target (Xxxx) implementor, + * corresponding test class (TestXxxx) of the target (Xxxx) implementor, * where execution can be observed more easily. - * + * * @author Amol S. Deshmukh < amolweb at ya hoo dot com > */ public final class TestFormulasFromSpreadsheet 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) */ @@ -66,31 +66,31 @@ public final class TestFormulasFromSpreadsheet extends TestCase { /** * Row (zero-based) in the test spreadsheet where the function examples start. */ - public static final int START_FUNCTIONS_ROW_INDEX = 87; // Row '88' - /** + public static final int START_FUNCTIONS_ROW_INDEX = 95; // Row '96' + /** * Index of the column that contains the function names */ public static final int COLUMN_INDEX_FUNCTION_NAME = 1; // Column 'B' - + /** * 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 + * Each function takes 4 rows in the test spreadsheet */ public static final int NUMBER_OF_ROWS_PER_FUNCTION = 4; } private HSSFWorkbook workbook; private Sheet sheet; - // Note - multiple failures are aggregated before ending. + // Note - multiple failures are aggregated before ending. // If one or more functions fail, a single AssertionFailedError is thrown at the end private int _functionFailureCount; private int _functionSuccessCount; @@ -112,7 +112,7 @@ public final class TestFormulasFromSpreadsheet extends TestCase { if(actual == null) { throw new AssertionFailedError(msg + " - actual value was null"); } - + switch (expected.getCellType()) { case Cell.CELL_TYPE_BLANK: assertEquals(msg, Cell.CELL_TYPE_BLANK, actual.getCellType()); @@ -149,17 +149,17 @@ public final class TestFormulasFromSpreadsheet extends TestCase { _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 " + String successMsg = "There were " + _evaluationSuccessCount + " successful evaluation(s) and " + _functionSuccessCount + " function(s) without error"; if(_functionFailureCount > 0) { @@ -173,8 +173,8 @@ public final class TestFormulasFromSpreadsheet extends TestCase { } /** - * @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. + * @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) { @@ -185,7 +185,7 @@ public final class TestFormulasFromSpreadsheet extends TestCase { Row r = sheet.getRow(rowIndex); String targetFunctionName = getTargetFunctionName(r); if(targetFunctionName == null) { - throw new AssertionFailedError("Test spreadsheet cell empty on row (" + throw new AssertionFailedError("Test spreadsheet cell empty on row (" + (rowIndex+1) + "). Expected function name or '" + SS.FUNCTION_NAMES_END_SENTINEL + "'"); } @@ -194,13 +194,13 @@ public final class TestFormulasFromSpreadsheet extends TestCase { break; } if(testFocusFunctionName == null || targetFunctionName.equalsIgnoreCase(testFocusFunctionName)) { - + // expected results are on the row below Row 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 + ")"); + 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; @@ -215,13 +215,13 @@ public final class TestFormulasFromSpreadsheet extends TestCase { } /** - * + * * @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, + private int processFunctionRow(HSSFFormulaEvaluator evaluator, String targetFunctionName, Row formulasRow, Row expectedValuesRow) { - + int result = Result.NO_EVALUATIONS_FOUND; // so far short endcolnum = formulasRow.getLastCellNum(); @@ -256,7 +256,7 @@ public final class TestFormulasFromSpreadsheet extends TestCase { */ private static void printShortStackTrace(PrintStream ps, AssertionFailedError e) { StackTraceElement[] stes = e.getStackTrace(); - + int startIx = 0; // skip any top frames inside junit.framework.Assert while(startIx