You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2009/10/22 01:00:10 UTC
svn commit: r828244 - in /poi/trunk/src: documentation/content/xdocs/
java/org/apache/poi/hssf/record/formula/eval/ java/org/apache/poi/ss/formula/
testcases/org/apache/poi/hssf/record/formula/eval/
Author: josh
Date: Wed Oct 21 23:00:09 2009
New Revision: 828244
URL: http://svn.apache.org/viewvc?rev=828244&view=rev
Log:
Bugzilla 48036 - added IntersectionEval to allow evaluation of the intersection formula operator.
Added:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/IntersectionEval.java
Modified:
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java
poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=828244&r1=828243&r2=828244&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Wed Oct 21 23:00:09 2009
@@ -33,6 +33,7 @@
<changes>
<release version="3.6-beta1" date="2009-??-??">
+ <action dev="POI-DEVELOPERS" type="fix">48036 - added IntersectionEval to allow evaluation of the intersection formula operator</action>
<action dev="POI-DEVELOPERS" type="fix">47999 - avoid un-needed call to the JVM Garbage Collector when working on OOXML OPC Packages</action>
<action dev="POI-DEVELOPERS" type="add">47922 - added example HSMF application that converts a .msg file to text and extracts attachments</action>
<action dev="POI-DEVELOPERS" type="add">47903 - added Ant target to compile scratchpad examples</action>
Added: poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/IntersectionEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/IntersectionEval.java?rev=828244&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/IntersectionEval.java (added)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/IntersectionEval.java Wed Oct 21 23:00:09 2009
@@ -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
+ * <tt>aeA</tt> and <tt>aeB</tt>. If the two areas do not intersect, the result is <code>null</code>.
+ */
+ 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() + ")");
+ }
+}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java?rev=828244&r1=828243&r2=828244&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java Wed Oct 21 23:00:09 2009
@@ -29,6 +29,7 @@
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.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 @@
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;
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java?rev=828244&r1=828243&r2=828244&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java Wed Oct 21 23:00:09 2009
@@ -33,6 +33,7 @@
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 @@
// 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) {
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java?rev=828244&r1=828243&r2=828244&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java Wed Oct 21 23:00:09 2009
@@ -37,24 +37,24 @@
* This class does not test implementors of <tt>Function</tt> and <tt>OperationEval</tt> in
* isolation. Much of the evaluation engine (i.e. <tt>HSSFFormulaEvaluator</tt>, ...) gets
* exercised as well. 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,
+ * corresponding test class (<tt>TestXxxx</tt>) of the target (<tt>Xxxx</tt>) 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 @@
/**
* 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 = "<END-OF-FUNCTIONS>";
-
+
/**
* 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 @@
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 @@
_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 @@
}
/**
- * @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 <code>null</code> to test all functions
*/
private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) {
@@ -185,7 +185,7 @@
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 @@
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 @@
}
/**
- *
+ *
* @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 @@
*/
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<stes.length) {
@@ -303,7 +303,7 @@
if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
return cell.getRichStringCellValue().getString();
}
-
+
throw new AssertionFailedError("Bad cell type for 'function name' column: ("
+ cell.getCellType() + ") row (" + (r.getRowNum() +1) + ")");
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org