You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2008/02/14 17:01:16 UTC
svn commit: r627788 [1/2] - in /poi/trunk/src: documentation/content/xdocs/
java/org/apache/poi/hssf/model/ java/org/apache/poi/hssf/record/formula/
scratchpad/src/org/apache/poi/hssf/record/formula/eval/
scratchpad/src/org/apache/poi/hssf/record/formu...
Author: nick
Date: Thu Feb 14 08:01:10 2008
New Revision: 627788
URL: http://svn.apache.org/viewvc?rev=627788&view=rev
Log:
Big formula update from Josh from bug #44364 - support for Match, NA and SumProduct functions, and initial error support in functions
Added:
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java (with props)
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestUnaryPlusEval.java (with props)
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMatch.java (with props)
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestRoundFuncs.java (with props)
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestSumproduct.java (with props)
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestTFunc.java (with props)
Removed:
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/data/42464-ExpPtg-bad.xls
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/data/42464-ExpPtg-ok.xls
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/GenericFormulaTestCase.java
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestEverything.java
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestEverything.java
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/model/FormulaParser.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/FuncPtg.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/BoolEval.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/ErrorEval.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/UnaryPlusEval.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Match.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Na.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rounddown.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Roundup.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumproduct.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/T.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/NumericFunctionInvoker.java
poi/trunk/src/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls
poi/trunk/src/testcases/org/apache/poi/hssf/data/TestDataValidation.xls
poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Thu Feb 14 08:01:10 2008
@@ -36,6 +36,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.1-beta1" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">44364 - Support for Match, NA and SumProduct functions, as well as initial function error support</action>
<action dev="POI-DEVELOPERS" type="fix">44375 - Cope with a broken dictionary in Document Summary Information stream. RuntimeExceptions that occured when trying to read bogus data are now caught. Dictionary entries up to but not including the bogus one are preserved, the rest is ignored.</action>
<action dev="POI-DEVELOPERS" type="fix">38641 - Handle timezones better with cell.setCellValue(Calendar), so now 20:00-03:00, 20:00+00:00 and 20:00+03:00 will all be recorded as 20:00, and not 17:00 / 20:00 / 23:00 (pass a Date not a Calendar for old behaviour)</action>
<action dev="POI-DEVELOPERS" type="fix">44373 - Have HSSFDateUtil.isADateFormat recognize more formats as being dates</action>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Thu Feb 14 08:01:10 2008
@@ -33,6 +33,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.1-beta1" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">44364 - Support for Match, NA and SumProduct functions, as well as initial function error support</action>
<action dev="POI-DEVELOPERS" type="fix">44375 - Cope with a broken dictionary in Document Summary Information stream. RuntimeExceptions that occured when trying to read bogus data are now caught. Dictionary entries up to but not including the bogus one are preserved, the rest is ignored.</action>
<action dev="POI-DEVELOPERS" type="fix">38641 - Handle timezones better with cell.setCellValue(Calendar), so now 20:00-03:00, 20:00+00:00 and 20:00+03:00 will all be recorded as 20:00, and not 17:00 / 20:00 / 23:00 (pass a Date not a Calendar for old behaviour)</action>
<action dev="POI-DEVELOPERS" type="fix">44373 - Have HSSFDateUtil.isADateFormat recognize more formats as being dates</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/model/FormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/model/FormulaParser.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/model/FormulaParser.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/model/FormulaParser.java Thu Feb 14 08:01:10 2008
@@ -947,20 +947,28 @@
// Excel allows to have AttrPtg at position 0 (such as Blanks) which
// do not have any operands. Skip them.
- stack.push(ptgs[0].toFormulaString(book));
+ int i;
+ if(ptgs[0] instanceof AttrPtg) {
+ // TODO -this requirement is unclear and is not addressed by any junits
+ stack.push(ptgs[0].toFormulaString(book));
+ i=1;
+ } else {
+ i=0;
+ }
- for (int i = 1; i < ptgs.length; i++) {
- if (! (ptgs[i] instanceof OperationPtg)) {
- stack.push(ptgs[i].toFormulaString(book));
+ for ( ; i < ptgs.length; i++) {
+ Ptg ptg = ptgs[i];
+ if (! (ptg instanceof OperationPtg)) {
+ stack.push(ptg.toFormulaString(book));
continue;
}
- if (ptgs[i] instanceof AttrPtg && ((AttrPtg) ptgs[i]).isOptimizedIf()) {
- ifptg = (AttrPtg) ptgs[i];
+ if (ptg instanceof AttrPtg && ((AttrPtg) ptg).isOptimizedIf()) {
+ ifptg = (AttrPtg) ptg;
continue;
}
- final OperationPtg o = (OperationPtg) ptgs[i];
+ final OperationPtg o = (OperationPtg) ptg;
final String[] operands = new String[o.getNumberOfOperands()];
for (int j = operands.length; j > 0; j--) {
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/FuncPtg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/FuncPtg.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/FuncPtg.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/FuncPtg.java Thu Feb 14 08:01:10 2008
@@ -63,6 +63,10 @@
}
}
+ public FuncPtg(int functionIndex, int numberOfParameters) {
+ field_2_fnc_index = (short) functionIndex;
+ numParams = numberOfParameters;
+ }
public void writeBytes(byte[] array, int offset) {
array[offset+0]= (byte) (sid + ptgClass);
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/BoolEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/BoolEval.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/BoolEval.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/BoolEval.java Thu Feb 14 08:01:10 2008
@@ -34,6 +34,16 @@
public static final BoolEval FALSE = new BoolEval(false);
public static final BoolEval TRUE = new BoolEval(true);
+
+ /**
+ * Convenience method for the following:<br/>
+ * <code>(b ? BoolEval.TRUE : BoolEval.FALSE)</code>
+ * @return a <tt>BoolEval</tt> instance representing <tt>b</tt>.
+ */
+ public static final BoolEval valueOf(boolean b) {
+ // TODO - find / replace all occurrences
+ return b ? TRUE : FALSE;
+ }
public BoolEval(Ptg ptg) {
this.value = ((BoolPtg) ptg).getValue();
@@ -48,10 +58,17 @@
}
public double getNumberValue() {
- return value ? (short) 1 : (short) 0;
+ return value ? 1 : 0;
}
public String getStringValue() {
return value ? "TRUE" : "FALSE";
+ }
+ public String toString() {
+ StringBuffer sb = new StringBuffer(64);
+ sb.append(getClass().getName()).append(" [");
+ sb.append(getStringValue());
+ sb.append("]");
+ return sb.toString();
}
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/ErrorEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/ErrorEval.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/ErrorEval.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/ErrorEval.java Thu Feb 14 08:01:10 2008
@@ -24,31 +24,91 @@
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
*
*/
-public class ErrorEval implements ValueEval {
-
- private int errorCode;
-
-
- public static final ErrorEval NAME_INVALID = new ErrorEval(525);
+public final class ErrorEval implements ValueEval {
+ /**
+ * Contains raw Excel error codes (as defined in OOO's excelfileformat.pdf (2.5.6)
+ */
+ private static final class ErrorCode {
+ /** <b>#NULL!</b> - Intersection of two cell ranges is empty */
+ public static final int NULL = 0x00;
+ /** <b>#DIV/0!</b> - Division by zero */
+ public static final int DIV_0 = 0x07;
+ /** <b>#VALUE!</b> - Wrong type of operand */
+ public static final int VALUE = 0x0F;
+ /** <b>#REF!</b> - Illegal or deleted cell reference */
+ public static final int REF = 0x17;
+ /** <b>#NAME?</b> - Wrong function or range name */
+ public static final int NAME = 0x1D;
+ /** <b>#NUM!</b> - Value range overflow */
+ public static final int NUM = 0x24;
+ /** <b>#N/A</b> - Argument or function not available */
+ public static final int N_A = 0x2A;
+
+ public static final String getText(int errorCode) {
+ switch(errorCode) {
+ case NULL: return "#NULL!";
+ case DIV_0: return "#DIV/0!";
+ case VALUE: return "#VALUE!";
+ case REF: return "#REF!";
+ case NAME: return "#NAME?";
+ case NUM: return "#NUM!";
+ case N_A: return "#N/A";
+ }
+ return "???";
+ }
+ }
- public static final ErrorEval VALUE_INVALID = new ErrorEval(519);
+ /** <b>#NULL!</b> - Intersection of two cell ranges is empty */
+ public static final ErrorEval NULL_INTERSECTION = new ErrorEval(ErrorCode.NULL);
+ /** <b>#DIV/0!</b> - Division by zero */
+ public static final ErrorEval DIV_ZERO = new ErrorEval(ErrorCode.DIV_0);
+ /** <b>#VALUE!</b> - Wrong type of operand */
+ public static final ErrorEval VALUE_INVALID = new ErrorEval(ErrorCode.VALUE);
+ /** <b>#REF!</b> - Illegal or deleted cell reference */
+ public static final ErrorEval REF_INVALID = new ErrorEval(ErrorCode.REF);
+ /** <b>#NAME?</b> - Wrong function or range name */
+ public static final ErrorEval NAME_INVALID = new ErrorEval(ErrorCode.NAME);
+ /** <b>#NUM!</b> - Value range overflow */
+ public static final ErrorEval NUM_ERROR = new ErrorEval(ErrorCode.NUM);
+ /** <b>#N/A</b> - Argument or function not available */
+ public static final ErrorEval NA = new ErrorEval(ErrorCode.N_A);
- // Non std error codes
+ /**
+ * Translates an Excel internal error code into the corresponding POI ErrorEval instance
+ * @param errorCode
+ */
+ public static ErrorEval valueOf(int errorCode) {
+ switch(errorCode) {
+ case ErrorCode.NULL: return NULL_INTERSECTION;
+ case ErrorCode.DIV_0: return DIV_ZERO;
+ case ErrorCode.VALUE: return VALUE_INVALID;
+// case ErrorCode.REF: return REF_INVALID;
+ case ErrorCode.REF: return UNKNOWN_ERROR;
+ case ErrorCode.NAME: return NAME_INVALID;
+ case ErrorCode.NUM: return NUM_ERROR;
+ case ErrorCode.N_A: return NA;
+
+ // these cases probably shouldn't be coming through here
+ // but (as of Jan-2008) a lot of code depends on it.
+// case -20: return UNKNOWN_ERROR;
+// case -30: return FUNCTION_NOT_IMPLEMENTED;
+// case -60: return CIRCULAR_REF_ERROR;
+ }
+ throw new RuntimeException("Unexpected error code (" + errorCode + ")");
+ }
+
+ // POI internal error codes
public static final ErrorEval UNKNOWN_ERROR = new ErrorEval(-20);
-
public static final ErrorEval FUNCTION_NOT_IMPLEMENTED = new ErrorEval(-30);
+ // Note - Excel does not seem to represent this condition with an error code
+ public static final ErrorEval CIRCULAR_REF_ERROR = new ErrorEval(-60);
- public static final ErrorEval REF_INVALID = new ErrorEval(-40);
-
- public static final ErrorEval NA = new ErrorEval(-50);
-
- public static final ErrorEval CIRCULAR_REF_ERROR = new ErrorEval(-60);
-
- public static final ErrorEval DIV_ZERO = new ErrorEval(-70);
-
- public static final ErrorEval NUM_ERROR = new ErrorEval(-80);
+ private int errorCode;
+ /**
+ * @param errorCode an 8-bit value
+ */
private ErrorEval(int errorCode) {
this.errorCode = errorCode;
}
@@ -56,9 +116,11 @@
public int getErrorCode() {
return errorCode;
}
-
- public String getStringValue() {
- return "Err:" + Integer.toString(errorCode);
+ public String toString() {
+ StringBuffer sb = new StringBuffer(64);
+ sb.append(getClass().getName()).append(" [");
+ sb.append(ErrorCode.getText(errorCode));
+ sb.append("]");
+ return sb.toString();
}
-
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/UnaryPlusEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/UnaryPlusEval.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/UnaryPlusEval.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/UnaryPlusEval.java Thu Feb 14 08:01:10 2008
@@ -58,6 +58,9 @@
// ));
+ /**
+ * called by reflection
+ */
public UnaryPlusEval(Ptg ptg) {
this.delegate = (UnaryPlusPtg) ptg;
}
@@ -108,7 +111,7 @@
}
else if (ae.isColumn()) {
if (ae.containsRow(srcRow)) {
- ValueEval ve = ae.getValueAt(ae.getFirstRow(), srcCol);
+ ValueEval ve = ae.getValueAt(srcRow, ae.getFirstColumn());
if (ve instanceof RefEval) {
ve = ((RefEval) ve).getInnerValueEval();
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Match.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Match.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Match.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Match.java Thu Feb 14 08:01:10 2008
@@ -14,12 +14,300 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-/*
- * Created on May 15, 2005
- *
- */
+
package org.apache.poi.hssf.record.formula.functions;
-public class Match extends NotImplementedFunction {
+import org.apache.poi.hssf.record.formula.eval.AreaEval;
+import org.apache.poi.hssf.record.formula.eval.BoolEval;
+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.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
+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.ValueEval;
+import org.apache.poi.hssf.util.AreaReference;
+
+/**
+ * Implementation for the MATCH() Excel function.<p/>
+ *
+ * <b>Syntax:</b><br/>
+ * <b>MATCH</b>(<b>lookup_value</b>, <b>lookup_array</b>, match_type)<p/>
+ *
+ * Returns a 1-based index specifying at what position in the <b>lookup_array</b> the specified
+ * <b>lookup_value</b> is found.<p/>
+ *
+ * Specific matching behaviour can be modified with the optional <b>match_type</b> parameter.
+ *
+ * <table border="0" cellpadding="1" cellspacing="0" summary="match_type parameter description">
+ * <tr><th>Value</th><th>Matching Behaviour</th></tr>
+ * <tr><td>1</td><td>(default) find the largest value that is less than or equal to lookup_value.
+ * The lookup_array must be in ascending <i>order</i>*.</td></tr>
+ * <tr><td>0</td><td>find the first value that is exactly equal to lookup_value.
+ * The lookup_array can be in any order.</td></tr>
+ * <tr><td>-1</td><td>find the smallest value that is greater than or equal to lookup_value.
+ * The lookup_array must be in descending <i>order</i>*.</td></tr>
+ * </table>
+ *
+ * * Note regarding <i>order</i> - For the <b>match_type</b> cases that require the lookup_array to
+ * be ordered, MATCH() can produce incorrect results if this requirement is not met. Observed
+ * behaviour in Excel is to return the lowest index value for which every item after that index
+ * breaks the match rule.<br>
+ * The (ascending) sort order expected by MATCH() is:<br/>
+ * numbers (low to high), strings (A to Z), boolean (FALSE to TRUE)<br/>
+ * MATCH() ignores all elements in the lookup_array with a different type to the lookup_value.
+ * Type conversion of the lookup_array elements is never performed.
+ *
+ *
+ * @author Josh Micich
+ */
+public final class Match implements Function {
+
+ private static final class EvalEx extends Exception {
+ private final ErrorEval _error;
+
+ public EvalEx(ErrorEval error) {
+ _error = error;
+ }
+ public ErrorEval getError() {
+ return _error;
+ }
+ }
+
+
+ public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+
+ double match_type = 1; // default
+
+ switch(args.length) {
+ case 3:
+ try {
+ match_type = evaluateMatchTypeArg(args[2], srcCellRow, srcCellCol);
+ } catch (EvalEx e) {
+ // Excel/MATCH() seems to have slightly abnormal handling of errors with
+ // the last parameter. Errors do not propagate up. Every error gets
+ // translated into #REF!
+ return ErrorEval.REF_INVALID;
+ }
+ case 2:
+ break;
+ default:
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ boolean matchExact = match_type == 0;
+ // Note - Excel does not strictly require -1 and +1
+ boolean findLargestLessThanOrEqual = match_type > 0;
+
+
+ try {
+ ValueEval lookupValue = evaluateLookupValue(args[0], srcCellRow, srcCellCol);
+ ValueEval[] lookupRange = evaluateLookupRange(args[1]);
+ int index = findIndexOfValue(lookupValue, lookupRange, matchExact, findLargestLessThanOrEqual);
+ return new NumberEval(index + 1); // +1 to convert to 1-based
+ } catch (EvalEx e) {
+ return e.getError();
+ }
+ }
+
+ private static ValueEval chooseSingleElementFromArea(AreaEval ae,
+ int srcCellRow, short srcCellCol) throws EvalEx {
+ if (ae.isColumn()) {
+ if(ae.isRow()) {
+ return ae.getValues()[0];
+ }
+ if(!ae.containsRow(srcCellRow)) {
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+ return ae.getValueAt(srcCellRow, ae.getFirstColumn());
+ }
+ if(!ae.isRow()) {
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+ if(!ae.containsColumn(srcCellCol)) {
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+ return ae.getValueAt(ae.getFirstRow(), srcCellCol);
+
+ }
+
+ private static ValueEval evaluateLookupValue(Eval eval, int srcCellRow, short srcCellCol)
+ throws EvalEx {
+ if (eval instanceof RefEval) {
+ RefEval re = (RefEval) eval;
+ return re.getInnerValueEval();
+ }
+ if (eval instanceof AreaEval) {
+ return chooseSingleElementFromArea((AreaEval) eval, srcCellRow, srcCellCol);
+ }
+ if (eval instanceof ValueEval) {
+ return (ValueEval) eval;
+ }
+ throw new RuntimeException("Unexpected eval type (" + eval.getClass().getName() + ")");
+ }
+
+
+ private static ValueEval[] evaluateLookupRange(Eval eval) throws EvalEx {
+ if (eval instanceof RefEval) {
+ RefEval re = (RefEval) eval;
+ return new ValueEval[] { re.getInnerValueEval(), };
+ }
+ if (eval instanceof AreaEval) {
+ AreaEval ae = (AreaEval) eval;
+ if(!ae.isColumn() && !ae.isRow()) {
+ throw new EvalEx(ErrorEval.NA);
+ }
+ return ae.getValues();
+ }
+
+ // Error handling for lookup_range arg is also unusual
+ if(eval instanceof NumericValueEval) {
+ throw new EvalEx(ErrorEval.NA);
+ }
+ if (eval instanceof StringEval) {
+ StringEval se = (StringEval) eval;
+ Double d = parseDouble(se.getStringValue());
+ if(d == null) {
+ // plain string
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+ // else looks like a number
+ throw new EvalEx(ErrorEval.NA);
+ }
+ throw new RuntimeException("Unexpected eval type (" + eval.getClass().getName() + ")");
+ }
+
+
+ private static Double parseDouble(String stringValue) {
+ // TODO find better home for parseDouble
+ return Countif.parseDouble(stringValue);
+ }
+
+
+
+ private static double evaluateMatchTypeArg(Eval arg, int srcCellRow, short srcCellCol)
+ throws EvalEx {
+ Eval match_type = arg;
+ if(arg instanceof AreaReference) {
+ AreaEval ae = (AreaEval) arg;
+ // an area ref can work as a scalar value if it is 1x1
+ if(ae.isColumn() && ae.isRow()) {
+ match_type = ae.getValues()[0];
+ } else {
+ match_type = chooseSingleElementFromArea(ae, srcCellRow, srcCellCol);
+ }
+ }
+
+ if(match_type instanceof RefEval) {
+ RefEval re = (RefEval) match_type;
+ match_type = re.getInnerValueEval();
+ }
+ if(match_type instanceof ErrorEval) {
+ throw new EvalEx((ErrorEval)match_type);
+ }
+ if(match_type instanceof NumericValueEval) {
+ NumericValueEval ne = (NumericValueEval) match_type;
+ return ne.getNumberValue();
+ }
+ if (match_type instanceof StringEval) {
+ StringEval se = (StringEval) match_type;
+ Double d = parseDouble(se.getStringValue());
+ if(d == null) {
+ // plain string
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+ // if the string parses as a number, it is ok
+ return d.doubleValue();
+ }
+ throw new RuntimeException("Unexpected match_type type (" + match_type.getClass().getName() + ")");
+ }
+
+ /**
+ * @return zero based index
+ */
+ private static int findIndexOfValue(ValueEval lookupValue, ValueEval[] lookupRange,
+ boolean matchExact, boolean findLargestLessThanOrEqual) throws EvalEx {
+ // TODO - wildcard matching when matchExact and lookupValue is text containing * or ?
+ if(matchExact) {
+ for (int i = 0; i < lookupRange.length; i++) {
+ ValueEval lri = lookupRange[i];
+ if(lri.getClass() != lookupValue.getClass()) {
+ continue;
+ }
+ if(compareValues(lookupValue, lri) == 0) {
+ return i;
+ }
+ }
+ } else {
+ // Note - backward iteration
+ if(findLargestLessThanOrEqual) {
+ for (int i = lookupRange.length - 1; i>=0; i--) {
+ ValueEval lri = lookupRange[i];
+ if(lri.getClass() != lookupValue.getClass()) {
+ continue;
+ }
+ int cmp = compareValues(lookupValue, lri);
+ if(cmp == 0) {
+ return i;
+ }
+ if(cmp > 0) {
+ return i;
+ }
+ }
+ } else {
+ // find smallest greater than or equal to
+ for (int i = 0; i<lookupRange.length; i++) {
+ ValueEval lri = lookupRange[i];
+ if(lri.getClass() != lookupValue.getClass()) {
+ continue;
+ }
+ int cmp = compareValues(lookupValue, lri);
+ if(cmp == 0) {
+ return i;
+ }
+ if(cmp > 0) {
+ if(i<1) {
+ throw new EvalEx(ErrorEval.NA);
+ }
+ return i-1;
+ }
+ }
+
+ }
+ }
+
+ throw new EvalEx(ErrorEval.NA);
+ }
+
+ /**
+ * This method can only compare a pair of <tt>NumericValueEval</tt>s, <tt>StringEval</tt>s
+ * or <tt>BoolEval</tt>s
+ * @return negative for a<b, positive for a>b and 0 for a = b
+ */
+ private static int compareValues(ValueEval a, ValueEval b) {
+ if (a instanceof StringEval) {
+ StringEval sa = (StringEval) a;
+ StringEval sb = (StringEval) b;
+ return sa.getStringValue().compareToIgnoreCase(sb.getStringValue());
+ }
+ if (a instanceof NumericValueEval) {
+ NumericValueEval na = (NumericValueEval) a;
+ NumericValueEval nb = (NumericValueEval) b;
+ return Double.compare(na.getNumberValue(), nb.getNumberValue());
+ }
+ if (a instanceof BoolEval) {
+ boolean ba = ((BoolEval) a).getBooleanValue();
+ boolean bb = ((BoolEval) b).getBooleanValue();
+ if(ba == bb) {
+ return 0;
+ }
+ // TRUE > FALSE
+ if(ba) {
+ return +1;
+ }
+ return -1;
+ }
+ throw new RuntimeException("bad eval type (" + a.getClass().getName() + ")");
+ }
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java Thu Feb 14 08:01:10 2008
@@ -14,10 +14,7 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-/*
- * Created on May 22, 2005
- *
- */
+
package org.apache.poi.hssf.record.formula.functions;
import org.apache.poi.hssf.record.formula.eval.AreaEval;
@@ -36,6 +33,49 @@
* where the order of operands does not matter
*/
public abstract class MultiOperandNumericFunction extends NumericFunction {
+ static final double[] EMPTY_DOUBLE_ARRAY = { };
+
+ private static class DoubleList {
+ private double[] _array;
+ private int _count;
+
+ public DoubleList() {
+ _array = new double[8];
+ _count = 0;
+ }
+
+ public double[] toArray() {
+ if(_count < 1) {
+ return EMPTY_DOUBLE_ARRAY;
+ }
+ double[] result = new double[_count];
+ System.arraycopy(_array, 0, result, 0, _count);
+ return result;
+ }
+
+ public void add(double[] values) {
+ int addLen = values.length;
+ ensureCapacity(_count + addLen);
+ System.arraycopy(values, 0, _array, _count, addLen);
+ _count += addLen;
+ }
+
+ private void ensureCapacity(int reqSize) {
+ if(reqSize > _array.length) {
+ int newSize = reqSize * 3 / 2; // grow with 50% extra
+ double[] newArr = new double[newSize];
+ System.arraycopy(_array, 0, newArr, 0, _count);
+ _array = newArr;
+ }
+ }
+
+ public void add(double value) {
+ ensureCapacity(_count + 1);
+ _array[_count] = value;
+ _count++;
+ }
+ }
+
private static final ValueEvalToNumericXlator DEFAULT_NUM_XLATOR =
new ValueEvalToNumericXlator((short) (
@@ -76,40 +116,26 @@
* from among the list of operands. Blanks and Blank equivalent cells
* are ignored. Error operands or cells containing operands of type
* that are considered invalid and would result in #VALUE! error in
- * excel cause this function to return null.
+ * excel cause this function to return <code>null</code>.
*
* @param operands
* @param srcRow
* @param srcCol
*/
protected double[] getNumberArray(Eval[] operands, int srcRow, short srcCol) {
- double[] retval = new double[30];
- int count = 0;
+ if (operands.length > getMaxNumOperands()) {
+ return null;
+ }
+ DoubleList retval = new DoubleList();
- outer: do { // goto simulator loop
- if (operands.length > getMaxNumOperands()) {
- break outer;
- }
- else {
- for (int i=0, iSize=operands.length; i<iSize; i++) {
- double[] temp = getNumberArray(operands[i], srcRow, srcCol);
- if (temp == null) {
- retval = null; // error occurred.
- break;
- }
- retval = putInArray(retval, count, temp);
- count += temp.length;
- }
+ for (int i=0, iSize=operands.length; i<iSize; i++) {
+ double[] temp = getNumberArray(operands[i], srcRow, srcCol);
+ if (temp == null) {
+ return null; // error occurred.
}
- } while (false); // end goto simulator loop
-
- if (retval != null) {
- double[] temp = retval;
- retval = new double[count];
- System.arraycopy(temp, 0, retval, 0, count);
+ retval.add(temp);
}
-
- return retval;
+ return retval.toArray();
}
/**
@@ -120,13 +146,11 @@
* @param srcCol
*/
protected double[] getNumberArray(Eval operand, int srcRow, short srcCol) {
- double[] retval;
- int count = 0;
if (operand instanceof AreaEval) {
AreaEval ae = (AreaEval) operand;
ValueEval[] values = ae.getValues();
- retval = new double[values.length];
+ DoubleList retval = new DoubleList();
for (int j=0, jSize=values.length; j<jSize; j++) {
/*
* TODO: For an AreaEval, we are constructing a RefEval
@@ -143,91 +167,61 @@
if (ve instanceof NumericValueEval) {
NumericValueEval nve = (NumericValueEval) ve;
- retval = putInArray(retval, count++, nve.getNumberValue());
+ retval.add(nve.getNumberValue());
}
- else if (ve instanceof BlankEval) {} // ignore operand
+ else if (ve instanceof BlankEval) {
+ // note - blanks are ignored, so returned array will be smaller.
+ }
else {
- retval = null; // null => indicate to calling subclass that error occurred
- break;
+ return null; // indicate to calling subclass that error occurred
}
}
+ return retval.toArray();
}
- else { // for ValueEvals other than AreaEval
- retval = new double[1];
- ValueEval ve = singleOperandEvaluate(operand, srcRow, srcCol);
-
- if (ve instanceof NumericValueEval) {
- NumericValueEval nve = (NumericValueEval) ve;
- retval = putInArray(retval, count++, nve.getNumberValue());
- }
- else if (ve instanceof BlankEval) {} // ignore operand
- else {
- retval = null; // null => indicate to calling subclass that error occurred
- }
- }
- if (retval != null && retval.length >= 1) {
- double[] temp = retval;
- retval = new double[count];
- System.arraycopy(temp, 0, retval, 0, count);
+ // for ValueEvals other than AreaEval
+ ValueEval ve = singleOperandEvaluate(operand, srcRow, srcCol);
+
+ if (ve instanceof NumericValueEval) {
+ NumericValueEval nve = (NumericValueEval) ve;
+ return new double[] { nve.getNumberValue(), };
}
- return retval;
+ if (ve instanceof BlankEval) {
+ // ignore blanks
+ return EMPTY_DOUBLE_ARRAY;
+ }
+ return null;
}
/**
- * puts d at position pos in array arr. If pos is greater than arr, the
- * array is dynamically resized (using a simple doubling rule).
- * @param arr
- * @param pos
- * @param d
+ * Ensures that a two dimensional array has all sub-arrays present and the same length
+ * @return <code>false</code> if any sub-array is missing, or is of different length
*/
- private static double[] putInArray(double[] arr, int pos, double d) {
- double[] tarr = arr;
- while (pos >= arr.length) {
- arr = new double[arr.length << 1];
- }
- if (tarr.length != arr.length) {
- System.arraycopy(tarr, 0, arr, 0, tarr.length);
- }
- arr[pos] = d;
- return arr;
- }
-
- private static double[] putInArray(double[] arr, int pos, double[] d) {
- double[] tarr = arr;
- while (pos+d.length >= arr.length) {
- arr = new double[arr.length << 1];
- }
- if (tarr.length != arr.length) {
- System.arraycopy(tarr, 0, arr, 0, tarr.length);
- }
- for (int i=0, iSize=d.length; i<iSize; i++) {
- arr[pos+i] = d[i];
- }
- return arr;
- }
-
- protected static boolean areSubArraysConsistent(double[][] values) {
- boolean retval = false;
-
- outer: do {
- if (values != null && values.length > 0) {
- if (values[0] == null)
- break outer;
- int len = values[0].length;
- for (int i=1, iSize=values.length; i<iSize; i++) {
- if (values[i] == null)
- break outer;
- int tlen = values[i].length;
- if (len != tlen) {
- break outer;
- }
- }
+ protected static final boolean areSubArraysConsistent(double[][] values) {
+
+ if (values == null || values.length < 1) {
+ // TODO this doesn't seem right. Fix or add comment.
+ return true;
+ }
+
+ if (values[0] == null) {
+ return false;
+ }
+ int outerMax = values.length;
+ int innerMax = values[0].length;
+ for (int i=1; i<outerMax; i++) { // note - 'i=1' start at second sub-array
+ double[] subArr = values[i];
+ if (subArr == null) {
+ return false;
+ }
+ if (innerMax != subArr.length) {
+ return false;
}
- retval = true;
- } while (false);
- return retval;
+ }
+ return true;
}
+
+
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Na.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Na.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Na.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Na.java Thu Feb 14 08:01:10 2008
@@ -14,12 +14,22 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-/*
- * Created on May 15, 2005
- *
- */
+
+
package org.apache.poi.hssf.record.formula.functions;
-public class Na extends NotImplementedFunction {
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.Eval;
+
+/**
+ * Implementation of Excel function NA()
+ *
+ * @author Josh Micich
+ */
+public final class Na implements Function {
+
+ public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+ return ErrorEval.NA;
+ }
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rounddown.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rounddown.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rounddown.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rounddown.java Thu Feb 14 08:01:10 2008
@@ -40,6 +40,9 @@
break;
case 2:
ValueEval ve = singleOperandEvaluate(operands[0], srcRow, srcCol);
+ if(ve instanceof ErrorEval) {
+ return ve;
+ }
if (ve instanceof NumericValueEval) {
NumericValueEval ne = (NumericValueEval) ve;
d0 = ne.getNumberValue();
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Roundup.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Roundup.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Roundup.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Roundup.java Thu Feb 14 08:01:10 2008
@@ -40,6 +40,9 @@
break;
case 2:
ValueEval ve = singleOperandEvaluate(operands[0], srcRow, srcCol);
+ if(ve instanceof ErrorEval) {
+ return ve;
+ }
if (ve instanceof NumericValueEval) {
NumericValueEval ne = (NumericValueEval) ve;
d0 = ne.getNumberValue();
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumproduct.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumproduct.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumproduct.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumproduct.java Thu Feb 14 08:01:10 2008
@@ -14,16 +14,228 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-/*
- * Created on May 15, 2005
- *
- */
+
+
package org.apache.poi.hssf.record.formula.functions;
+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.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.Eval;
+import org.apache.poi.hssf.record.formula.eval.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
+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.ValueEval;
+
/**
- * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
- *
+ * Implementation for the Excel function SUMPRODUCT<p/>
+ *
+ * Syntax : <br/>
+ * SUMPRODUCT ( array1[, array2[, array3[, ...]]])
+ * <table border="0" cellpadding="1" cellspacing="0" summary="Parameter descriptions">
+ * <tr><th>array1, ... arrayN </th><td>typically area references,
+ * possibly cell references or scalar values</td></tr>
+ * </table><br/>
+ *
+ * Let A<b>n</b><sub>(<b>i</b>,<b>j</b>)</sub> represent the element in the <b>i</b>th row <b>j</b>th column
+ * of the <b>n</b>th array<br/>
+ * Assuming each array has the same dimensions (W, H), the result is defined as:<br/>
+ * SUMPRODUCT = Σ<sub><b>i</b>: 1..H</sub>
+ * ( Σ<sub><b>j</b>: 1..W</sub>
+ * ( Π<sub><b>n</b>: 1..N</sub>
+ * A<b>n</b><sub>(<b>i</b>,<b>j</b>)</sub>
+ * )
+ * )
+ *
+ * @author Josh Micich
*/
-public class Sumproduct extends NotImplementedFunction {
+public final class Sumproduct implements Function {
+
+ private static final class EvalEx extends Exception {
+ private final ErrorEval _error;
+
+ public EvalEx(ErrorEval error) {
+ _error = error;
+ }
+ public ErrorEval getError() {
+ return _error;
+ }
+ }
+
+ public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+
+ int maxN = args.length;
+
+ if(maxN < 1) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ Eval firstArg = args[0];
+ try {
+ if(firstArg instanceof NumericValueEval) {
+ return evaluateSingleProduct(args);
+ }
+ if(firstArg instanceof RefEval) {
+ return evaluateSingleProduct(args);
+ }
+ if(firstArg instanceof AreaEval) {
+ AreaEval ae = (AreaEval) firstArg;
+ if(ae.isRow() && ae.isColumn()) {
+ return evaluateSingleProduct(args);
+ }
+ return evaluateAreaSumProduct(args);
+ }
+ } catch (EvalEx e) {
+ return e.getError();
+ }
+ throw new RuntimeException("Invalid arg type for SUMPRODUCT: ("
+ + firstArg.getClass().getName() + ")");
+ }
+
+ private Eval evaluateSingleProduct(Eval[] evalArgs) throws EvalEx {
+ int maxN = evalArgs.length;
+
+ double term = 1D;
+ for(int n=0; n<maxN; n++) {
+ double val = getScalarValue(evalArgs[n]);
+ term *= val;
+ }
+ return new NumberEval(term);
+ }
+
+ private double getScalarValue(Eval arg) throws EvalEx {
+
+ Eval eval;
+ if (arg instanceof RefEval) {
+ RefEval re = (RefEval) arg;
+ eval = re.getInnerValueEval();
+ } else {
+ eval = arg;
+ }
+
+ if (eval == null) {
+ throw new RuntimeException("parameter may not be null");
+ }
+ if (eval instanceof AreaEval) {
+ AreaEval ae = (AreaEval) eval;
+ // an area ref can work as a scalar value if it is 1x1
+ if(!ae.isColumn() || !ae.isRow()) {
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+ eval = ae.getValues()[0];
+ }
+
+ if (!(eval instanceof ValueEval)) {
+ throw new RuntimeException("Unexpected value eval class ("
+ + eval.getClass().getName() + ")");
+ }
+
+ return getProductTerm((ValueEval) eval, true);
+ }
+
+ private Eval evaluateAreaSumProduct(Eval[] evalArgs) throws EvalEx {
+ int maxN = evalArgs.length;
+ AreaEval[] args = new AreaEval[maxN];
+ try {
+ System.arraycopy(evalArgs, 0, args, 0, maxN);
+ } catch (ArrayStoreException e) {
+ // one of the other args was not an AreaRef
+ return ErrorEval.VALUE_INVALID;
+ }
+
+
+ AreaEval firstArg = args[0];
+
+ int height = firstArg.getLastRow() - firstArg.getFirstRow() + 1;
+ int width = firstArg.getLastColumn() - firstArg.getFirstColumn() + 1; // TODO - junit
+
+
+
+ double[][][] elements = new double[maxN][][];
+
+ for (int n = 0; n < maxN; n++) {
+ elements[n] = evaluateArea(args[n], height, width);
+ }
+ double acc = 0;
+
+ for(int r=0; r<height; r++) {
+ for(int c=0; c<width; c++) {
+ double term = 1D;
+ for(int n=0; n<maxN; n++) {
+ term *= elements[n][r][c];
+ }
+ acc += term;
+ }
+ }
+
+ return new NumberEval(acc);
+ }
+
+ /**
+ * @return a 2-D array of the specified height and width corresponding to the evaluated cell
+ * values of the specified areaEval
+ * @throws EvalEx if any ErrorEval value was encountered while evaluating the area
+ */
+ private static double[][] evaluateArea(AreaEval areaEval, int height, int width) throws EvalEx {
+ int fr =areaEval.getFirstRow();
+ int fc =areaEval.getFirstColumn();
+
+ // check that height and width match
+ if(areaEval.getLastRow() - fr + 1 != height) {
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+ if(areaEval.getLastColumn() - fc + 1 != width) {
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+ ValueEval[] values = areaEval.getValues();
+ double[][] result = new double[height][width];
+ for(int r=0; r<height; r++) {
+ for(int c=0; c<width; c++) {
+ ValueEval ve = values[r*width + c];
+ result[r][c] = getProductTerm(ve, false);
+ }
+ }
+ return result;
+ }
+
+ /**
+ * Determines a <code>double</code> value for the specified <code>ValueEval</code>.
+ * @param isScalarProduct <code>false</code> for SUMPRODUCTs over area refs.
+ * @throws EvalEx if <code>ve</code> represents an error value.
+ * <p/>
+ * Note - string values and empty cells are interpreted differently depending on
+ * <code>isScalarProduct</code>. For scalar products, if any term is blank or a string, the
+ * error (#VALUE!) is raised. For area (sum)products, if any term is blank or a string, the
+ * result is zero.
+ */
+ private static double getProductTerm(ValueEval ve, boolean isScalarProduct) throws EvalEx {
+
+ if(ve instanceof BlankEval || ve == null) {
+ // TODO - shouldn't BlankEval.INSTANCE be used always instead of null?
+ // null seems to occur when the blank cell is part of an area ref (but not reliably)
+ if(isScalarProduct) {
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+ return 0;
+ }
+
+ if(ve instanceof ErrorEval) {
+ throw new EvalEx((ErrorEval)ve);
+ }
+ if(ve instanceof StringEval) {
+ if(isScalarProduct) {
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+ // Note for area SUMPRODUCTs, string values are interpreted as zero
+ // even if they would parse as valid numeric values
+ return 0;
+ }
+ if(ve instanceof NumericValueEval) {
+ NumericValueEval nve = (NumericValueEval) ve;
+ return nve.getNumberValue();
+ }
+ throw new RuntimeException("Unexpected value eval class ("
+ + ve.getClass().getName() + ")");
+ }
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/T.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/T.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/T.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/T.java Thu Feb 14 08:01:10 2008
@@ -22,28 +22,34 @@
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.RefEval;
import org.apache.poi.hssf.record.formula.eval.StringEval;
-import org.apache.poi.hssf.record.formula.eval.ValueEval;
-public class T implements Function {
-
-
+public final class T implements Function {
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- ValueEval retval = null;
- switch (operands.length) {
- default:
- retval = ErrorEval.VALUE_INVALID;
- break;
- case 1:
- if (operands[0] instanceof StringEval
- || operands[0] instanceof ErrorEval) {
- retval = (ValueEval) operands[0];
- }
- else if (operands[0] instanceof ErrorEval) {
- retval = StringEval.EMPTY_INSTANCE;
- }
+ public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+ switch (args.length) {
+ default:
+ return ErrorEval.VALUE_INVALID;
+ case 1:
+ break;
}
- return retval;
+ Eval arg = args[0];
+ if (arg instanceof RefEval) {
+ RefEval re = (RefEval) arg;
+ arg = re.getInnerValueEval();
+ }
+
+ if (arg instanceof StringEval) {
+ // Text values are returned unmodified
+ return arg;
+ }
+
+ if (arg instanceof ErrorEval) {
+ // Error values also returned unmodified
+ return arg;
+ }
+ // for all other argument types the result is empty string
+ return StringEval.EMPTY_INSTANCE;
}
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java Thu Feb 14 08:01:10 2008
@@ -173,7 +173,7 @@
* formula evaluated.
*/
public static FormulaParser getUnderlyingParser(HSSFWorkbook workbook, String formula) {
- return new FormulaParser(formula, workbook.getWorkbook());
+ return new FormulaParser(formula, workbook.getWorkbook());
}
/**
@@ -286,19 +286,19 @@
CellValue cv = getCellValueForEval(internalEvaluate(cell, row, sheet, workbook));
switch (cv.getCellType()) {
case HSSFCell.CELL_TYPE_BOOLEAN:
- cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
+ cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
cell.setCellValue(cv.getBooleanValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
- cell.setCellType(HSSFCell.CELL_TYPE_ERROR);
+ cell.setCellType(HSSFCell.CELL_TYPE_ERROR);
cell.setCellValue(cv.getErrorValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
- cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
+ cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(cv.getNumberValue());
break;
case HSSFCell.CELL_TYPE_STRING:
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(cv.getRichTextStringValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
@@ -337,6 +337,11 @@
else if (eval instanceof BlankEval) {
retval = new CellValue(HSSFCell.CELL_TYPE_BLANK);
}
+ else if (eval instanceof ErrorEval) {
+ retval = new CellValue(HSSFCell.CELL_TYPE_ERROR);
+ retval.setErrorValue((byte)((ErrorEval)eval).getErrorCode());
+// retval.setRichTextStringValue(new HSSFRichTextString("#An error occurred. check cell.getErrorCode()"));
+ }
else {
retval = new CellValue(HSSFCell.CELL_TYPE_ERROR);
}
@@ -401,7 +406,7 @@
short rownum = ptg.getRow();
HSSFRow row = sheet.getRow(rownum);
HSSFCell cell = (row != null) ? row.getCell(colnum) : null;
- pushRef2DEval(ptg, stack, cell, row, sheet, workbook);
+ stack.push(createRef2DEval(ptg, cell, row, sheet, workbook));
}
else if (ptgs[i] instanceof Ref3DPtg) {
Ref3DPtg ptg = (Ref3DPtg) ptgs[i];
@@ -411,7 +416,7 @@
HSSFSheet xsheet = workbook.getSheetAt(wb.getSheetIndexFromExternSheetIndex(ptg.getExternSheetIndex()));
HSSFRow row = xsheet.getRow(rownum);
HSSFCell cell = (row != null) ? row.getCell(colnum) : null;
- pushRef3DEval(ptg, stack, cell, row, xsheet, workbook);
+ stack.push(createRef3DEval(ptg, cell, row, xsheet, workbook));
}
else if (ptgs[i] instanceof AreaPtg) {
AreaPtg ap = (AreaPtg) ptgs[i];
@@ -544,104 +549,77 @@
* @param workbook
*/
protected static ValueEval getEvalForCell(HSSFCell cell, HSSFRow row, HSSFSheet sheet, HSSFWorkbook workbook) {
- ValueEval retval = BlankEval.INSTANCE;
- if (cell != null) {
- switch (cell.getCellType()) {
+
+ if (cell == null) {
+ return BlankEval.INSTANCE;
+ }
+ switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
- retval = new NumberEval(cell.getNumericCellValue());
- break;
+ return new NumberEval(cell.getNumericCellValue());
case HSSFCell.CELL_TYPE_STRING:
- retval = new StringEval(cell.getRichStringCellValue().getString());
- break;
+ return new StringEval(cell.getRichStringCellValue().getString());
case HSSFCell.CELL_TYPE_FORMULA:
- retval = internalEvaluate(cell, row, sheet, workbook);
- break;
+ return internalEvaluate(cell, row, sheet, workbook);
case HSSFCell.CELL_TYPE_BOOLEAN:
- retval = cell.getBooleanCellValue() ? BoolEval.TRUE : BoolEval.FALSE;
- break;
+ return BoolEval.valueOf(cell.getBooleanCellValue());
case HSSFCell.CELL_TYPE_BLANK:
- retval = BlankEval.INSTANCE;
- break;
+ return BlankEval.INSTANCE;
case HSSFCell.CELL_TYPE_ERROR:
- retval = ErrorEval.UNKNOWN_ERROR; // TODO: think about this...
- break;
- }
+ return ErrorEval.valueOf(cell.getErrorCellValue());
}
- return retval;
+ throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
}
/**
- * create a Ref2DEval for ReferencePtg and push it on the stack.
+ * Creates a Ref2DEval for ReferencePtg.
* Non existent cells are treated as RefEvals containing BlankEval.
- * @param ptg
- * @param stack
- * @param cell
- * @param sheet
- * @param workbook
*/
- protected static void pushRef2DEval(ReferencePtg ptg, Stack stack,
- HSSFCell cell, HSSFRow row, HSSFSheet sheet, HSSFWorkbook workbook) {
- if (cell != null)
- switch (cell.getCellType()) {
+ private static Ref2DEval createRef2DEval(ReferencePtg ptg, HSSFCell cell,
+ HSSFRow row, HSSFSheet sheet, HSSFWorkbook workbook) {
+ if (cell == null) {
+ return new Ref2DEval(ptg, BlankEval.INSTANCE, false);
+ }
+
+ switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
- stack.push(new Ref2DEval(ptg, new NumberEval(cell.getNumericCellValue()), false));
- break;
+ return new Ref2DEval(ptg, new NumberEval(cell.getNumericCellValue()), false);
case HSSFCell.CELL_TYPE_STRING:
- stack.push(new Ref2DEval(ptg, new StringEval(cell.getRichStringCellValue().getString()), false));
- break;
+ return new Ref2DEval(ptg, new StringEval(cell.getRichStringCellValue().getString()), false);
case HSSFCell.CELL_TYPE_FORMULA:
- stack.push(new Ref2DEval(ptg, internalEvaluate(cell, row, sheet, workbook), true));
- break;
+ return new Ref2DEval(ptg, internalEvaluate(cell, row, sheet, workbook), true);
case HSSFCell.CELL_TYPE_BOOLEAN:
- stack.push(new Ref2DEval(ptg, cell.getBooleanCellValue() ? BoolEval.TRUE : BoolEval.FALSE, false));
- break;
+ return new Ref2DEval(ptg, BoolEval.valueOf(cell.getBooleanCellValue()), false);
case HSSFCell.CELL_TYPE_BLANK:
- stack.push(new Ref2DEval(ptg, BlankEval.INSTANCE, false));
- break;
+ return new Ref2DEval(ptg, BlankEval.INSTANCE, false);
case HSSFCell.CELL_TYPE_ERROR:
- stack.push(new Ref2DEval(ptg, ErrorEval.UNKNOWN_ERROR, false)); // TODO: think abt this
- break;
- }
- else {
- stack.push(new Ref2DEval(ptg, BlankEval.INSTANCE, false));
+ return new Ref2DEval(ptg, ErrorEval.valueOf(cell.getErrorCellValue()), false);
}
+ throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
}
/**
- * create a Ref3DEval for Ref3DPtg and push it on the stack.
- *
- * @param ptg
- * @param stack
- * @param cell
- * @param sheet
- * @param workbook
+ * create a Ref3DEval for Ref3DPtg.
*/
- protected static void pushRef3DEval(Ref3DPtg ptg, Stack stack, HSSFCell cell,
+ private static Ref3DEval createRef3DEval(Ref3DPtg ptg, HSSFCell cell,
HSSFRow row, HSSFSheet sheet, HSSFWorkbook workbook) {
- if (cell != null)
- switch (cell.getCellType()) {
+ if (cell == null) {
+ return new Ref3DEval(ptg, BlankEval.INSTANCE, false);
+ }
+ switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
- stack.push(new Ref3DEval(ptg, new NumberEval(cell.getNumericCellValue()), false));
- break;
+ return new Ref3DEval(ptg, new NumberEval(cell.getNumericCellValue()), false);
case HSSFCell.CELL_TYPE_STRING:
- stack.push(new Ref3DEval(ptg, new StringEval(cell.getRichStringCellValue().getString()), false));
- break;
+ return new Ref3DEval(ptg, new StringEval(cell.getRichStringCellValue().getString()), false);
case HSSFCell.CELL_TYPE_FORMULA:
- stack.push(new Ref3DEval(ptg, internalEvaluate(cell, row, sheet, workbook), true));
- break;
+ return new Ref3DEval(ptg, internalEvaluate(cell, row, sheet, workbook), true);
case HSSFCell.CELL_TYPE_BOOLEAN:
- stack.push(new Ref3DEval(ptg, cell.getBooleanCellValue() ? BoolEval.TRUE : BoolEval.FALSE, false));
- break;
+ return new Ref3DEval(ptg, BoolEval.valueOf(cell.getBooleanCellValue()), false);
case HSSFCell.CELL_TYPE_BLANK:
- stack.push(new Ref3DEval(ptg, BlankEval.INSTANCE, false));
- break;
+ return new Ref3DEval(ptg, BlankEval.INSTANCE, false);
case HSSFCell.CELL_TYPE_ERROR:
- stack.push(new Ref3DEval(ptg, ErrorEval.UNKNOWN_ERROR, false)); // TODO: think abt this
- break;
- }
- else {
- stack.push(new Ref3DEval(ptg, BlankEval.INSTANCE, false));
+ return new Ref3DEval(ptg, ErrorEval.valueOf(cell.getErrorCellValue()), false);
}
+ throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
}
/**
@@ -726,15 +704,15 @@
/**
* @return Returns the richTextStringValue.
*/
- public HSSFRichTextString getRichTextStringValue() {
- return richTextStringValue;
- }
+ public HSSFRichTextString getRichTextStringValue() {
+ return richTextStringValue;
+ }
/**
* @param richTextStringValue The richTextStringValue to set.
*/
- public void setRichTextStringValue(HSSFRichTextString richTextStringValue) {
- this.richTextStringValue = richTextStringValue;
- }
+ public void setRichTextStringValue(HSSFRichTextString richTextStringValue) {
+ this.richTextStringValue = richTextStringValue;
+ }
}
/**
Added: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java?rev=627788&view=auto
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java (added)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java Thu Feb 14 08:01:10 2008
@@ -0,0 +1,329 @@
+/*
+* 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 java.io.FileInputStream;
+import java.io.PrintStream;
+
+import junit.framework.Assert;
+import junit.framework.AssertionFailedError;
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.record.formula.functions.TestMathX;
+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;
+
+/**
+ * Tests formulas and operators as loaded from a test data spreadsheet.<p/>
+ * 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,
+ * 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)
+ */
+ public final static String FILENAME = "FormulaEvalTestData.xls";
+ /**
+ * 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'
+ /**
+ * Index of the column that contains the function names
+ */
+ public static final short 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
+ */
+ public static final int NUMBER_OF_ROWS_PER_FUNCTION = 4;
+ }
+
+ 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
+ private int _functionFailureCount;
+ private int _functionSuccessCount;
+ 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 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(...?);
+ }
+ }
+
+ switch (expected.getCellType()) {
+ case HSSFCell.CELL_TYPE_BLANK:
+ 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;
+ 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;
+ 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, 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;
+ }
+ }
+
+
+ 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 "
+ + _functionSuccessCount + " function(s) without error";
+ if(_functionFailureCount > 0) {
+ String msg = _functionFailureCount + " function(s) failed in "
+ + _evaluationFailureCount + " evaluation(s). " + successMsg;
+ 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 <code>null</code> 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;
+ }
+ }
+
+ /**
+ *
+ * @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);
+
+ // 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;
+ }
+
+ HSSFFormulaEvaluator.CellValue actualValue = evaluator.evaluate(c);
+
+ HSSFCell expectedValueCell = getExpectedValueCell(expectedValuesRow, colnum);
+ try {
+ confirmExpectedResult("Function '" + targetFunctionName + "': Formula: " + c.getCellFormula() + " @ " + formulasRow.getRowNum() + ":" + colnum,
+ expectedValueCell, actualValue);
+ _evaluationSuccessCount ++;
+ if(result != Result.SOME_EVALUATIONS_FAILED) {
+ result = Result.ALL_EVALUATIONS_SUCCEEDED;
+ }
+ } catch (AssertionFailedError e) {
+ _evaluationFailureCount ++;
+ 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
+ */
+ 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) {
+ 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());
+ }
+
+ }
+
+ /**
+ * @return <code>null</code> 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");
+ return null;
+ }
+ HSSFCell cell = r.getCell(SS.COLUMN_INDEX_FUNCTION_NAME);
+ if(cell == null) {
+ System.err.println("Warning - Row " + r.getRowNum() + " has no cell " + SS.COLUMN_INDEX_FUNCTION_NAME + ", can't figure out function name");
+ return null;
+ }
+ if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
+ return null;
+ }
+ if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
+ return cell.getRichStringCellValue().getString();
+ }
+
+ throw new AssertionFailedError("Bad cell type for 'function name' column: ("
+ + cell.getCellType() + ") row (" + (r.getRowNum() +1) + ")");
+ }
+}
Propchange: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestUnaryPlusEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestUnaryPlusEval.java?rev=627788&view=auto
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestUnaryPlusEval.java (added)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestUnaryPlusEval.java Thu Feb 14 08:01:10 2008
@@ -0,0 +1,61 @@
+/*
+* 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.AreaPtg;
+import org.apache.poi.hssf.record.formula.UnaryPlusPtg;
+import org.apache.poi.hssf.record.formula.functions.NumericFunctionInvoker;
+
+import junit.framework.TestCase;
+
+/**
+ * Test for unary plus operator evaluator.
+ *
+ * @author Josh Micich
+ */
+public final class TestUnaryPlusEval extends TestCase {
+
+ /**
+ * Test for bug observable at svn revision 618865 (5-Feb-2008)<br/>
+ * The code for handling column operands had been copy-pasted from the row handling code.
+ */
+ public void testColumnOperand() {
+
+ short firstRow = (short)8;
+ short lastRow = (short)12;
+ short colNum = (short)5;
+ AreaPtg areaPtg = new AreaPtg(firstRow, lastRow, colNum, colNum, false, false, false, false);
+ ValueEval[] values = {
+ new NumberEval(27),
+ new NumberEval(29),
+ new NumberEval(35), // value in row 10
+ new NumberEval(37),
+ new NumberEval(38),
+ };
+ Eval areaEval = new Area2DEval(areaPtg, values);
+ Eval[] args = {
+ areaEval,
+ };
+
+ double result = NumericFunctionInvoker.invoke(new UnaryPlusEval(new UnaryPlusPtg()), args, 10, (short)20);
+
+ assertEquals(35, result, 0);
+ }
+
+}
Propchange: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestUnaryPlusEval.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestUnaryPlusEval.java
------------------------------------------------------------------------------
svn:executable = *
Modified: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java (original)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java Thu Feb 14 08:01:10 2008
@@ -36,8 +36,11 @@
result.addTestSuite(TestFinanceLib.class);
result.addTestSuite(TestIndex.class);
result.addTestSuite(TestMathX.class);
+ result.addTestSuite(TestMatch.class);
result.addTestSuite(TestRowCol.class);
+ result.addTestSuite(TestSumproduct.class);
result.addTestSuite(TestStatsLib.class);
+ result.addTestSuite(TestTFunc.class);
return result;
}
Modified: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/NumericFunctionInvoker.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/NumericFunctionInvoker.java?rev=627788&r1=627787&r2=627788&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/NumericFunctionInvoker.java (original)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/NumericFunctionInvoker.java Thu Feb 14 08:01:10 2008
@@ -23,13 +23,14 @@
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.NumericValueEval;
+import org.apache.poi.hssf.record.formula.eval.OperationEval;
/**
* Test helper class for invoking functions with numeric results.
*
* @author Josh Micich
*/
-final class NumericFunctionInvoker {
+public final class NumericFunctionInvoker {
private NumericFunctionInvoker() {
// no instances of this class
@@ -61,11 +62,35 @@
}
/**
+ * Invokes the specified operator with the arguments.
+ * <p/>
+ * This method cannot be used for confirming error return codes. Any non-numeric evaluation
+ * result causes the current junit test to fail.
+ */
+ public static double invoke(OperationEval f, Eval[] args, int srcCellRow, int srcCellCol) {
+ try {
+ return invokeInternal(f, args, srcCellRow, srcCellCol);
+ } catch (NumericEvalEx e) {
+ throw new AssertionFailedError("Evaluation of function (" + f.getClass().getName()
+ + ") failed: " + e.getMessage());
+ }
+
+ }
+ /**
* Formats nicer error messages for the junit output
*/
- private static double invokeInternal(Function f, Eval[] args, int srcCellRow, int srcCellCol)
+ private static double invokeInternal(Object target, Eval[] args, int srcCellRow, int srcCellCol)
throws NumericEvalEx {
- Eval evalResult = f.evaluate(args, srcCellRow, (short)srcCellCol);
+ Eval evalResult;
+ // TODO - make OperationEval extend Function
+ if (target instanceof Function) {
+ Function ff = (Function) target;
+ evalResult = ff.evaluate(args, srcCellRow, (short)srcCellCol);
+ } else {
+ OperationEval ff = (OperationEval) target;
+ evalResult = ff.evaluate(args, srcCellRow, (short)srcCellCol);
+ }
+
if(evalResult == null) {
throw new NumericEvalEx("Result object was null");
}
@@ -88,6 +113,9 @@
}
if(errorCodesAreEqual(ee, ErrorEval.UNKNOWN_ERROR)) {
return "Unknown error";
+ }
+ if(errorCodesAreEqual(ee, ErrorEval.VALUE_INVALID)) {
+ return "Error code: #VALUE! (invalid value)";
}
return "Error code=" + ee.getErrorCode();
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org