You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ug...@apache.org on 2008/03/08 12:49:17 UTC
svn commit: r634936 [6/8] - in /poi/branches/ooxml: ./ legal/
src/contrib/src/org/apache/poi/hssf/contrib/view/
src/documentation/content/xdocs/ src/documentation/content/xdocs/getinvolved/
src/documentation/content/xdocs/hslf/ src/java/org/apache/poi/...
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Offset.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Offset.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Offset.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Offset.java Sat Mar 8 03:49:00 2008
@@ -14,12 +14,349 @@
* 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 Offset extends NotImplementedFunction {
+import org.apache.poi.hssf.record.formula.Area3DPtg;
+import org.apache.poi.hssf.record.formula.AreaPtg;
+import org.apache.poi.hssf.record.formula.eval.Area3DEval;
+import org.apache.poi.hssf.record.formula.eval.AreaEval;
+import org.apache.poi.hssf.record.formula.eval.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.NumericValueEval;
+import org.apache.poi.hssf.record.formula.eval.Ref3DEval;
+import org.apache.poi.hssf.record.formula.eval.RefEval;
+import org.apache.poi.hssf.record.formula.eval.StringEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+/**
+ * Implementation for Excel function OFFSET()<p/>
+ *
+ * OFFSET returns an area reference that is a specified number of rows and columns from a
+ * reference cell or area.<p/>
+ *
+ * <b>Syntax</b>:<br/>
+ * <b>OFFSET</b>(<b>reference</b>, <b>rows</b>, <b>cols</b>, height, width)<p/>
+ * <b>reference</b> is the base reference.<br/>
+ * <b>rows</b> is the number of rows up or down from the base reference.<br/>
+ * <b>cols</b> is the number of columns left or right from the base reference.<br/>
+ * <b>height</b> (default same height as base reference) is the row count for the returned area reference.<br/>
+ * <b>width</b> (default same width as base reference) is the column count for the returned area reference.<br/>
+ *
+ * @author Josh Micich
+ */
+public final class Offset implements FreeRefFunction {
+ // These values are specific to BIFF8
+ private static final int LAST_VALID_ROW_INDEX = 0xFFFF;
+ private static final int LAST_VALID_COLUMN_INDEX = 0xFF;
+
+
+ /**
+ * Exceptions are used within this class to help simplify flow control when error conditions
+ * are encountered
+ */
+ private static final class EvalEx extends Exception {
+ private final ErrorEval _error;
+
+ public EvalEx(ErrorEval error) {
+ _error = error;
+ }
+ public ErrorEval getError() {
+ return _error;
+ }
+ }
+
+ /**
+ * A one dimensional base + offset. Represents either a row range or a column range.
+ * Two instances of this class together specify an area range.
+ */
+ /* package */ static final class LinearOffsetRange {
+
+ private final int _offset;
+ private final int _length;
+
+ public LinearOffsetRange(int offset, int length) {
+ if(length == 0) {
+ // handled that condition much earlier
+ throw new RuntimeException("length may not be zero");
+ }
+ _offset = offset;
+ _length = length;
+ }
+
+ public short getFirstIndex() {
+ return (short) _offset;
+ }
+ public short getLastIndex() {
+ return (short) (_offset + _length - 1);
+ }
+ /**
+ * Moves the range by the specified translation amount.<p/>
+ *
+ * This method also 'normalises' the range: Excel specifies that the width and height
+ * parameters (length field here) cannot be negative. However, OFFSET() does produce
+ * sensible results in these cases. That behavior is replicated here. <p/>
+ *
+ * @param translationAmount may be zero negative or positive
+ *
+ * @return the equivalent <tt>LinearOffsetRange</tt> with a positive length, moved by the
+ * specified translationAmount.
+ */
+ public LinearOffsetRange normaliseAndTranslate(int translationAmount) {
+ if (_length > 0) {
+ if(translationAmount == 0) {
+ return this;
+ }
+ return new LinearOffsetRange(translationAmount + _offset, _length);
+ }
+ return new LinearOffsetRange(translationAmount + _offset + _length + 1, -_length);
+ }
+
+ public boolean isOutOfBounds(int lowValidIx, int highValidIx) {
+ if(_offset < lowValidIx) {
+ return true;
+ }
+ if(getLastIndex() > highValidIx) {
+ return true;
+ }
+ return false;
+ }
+ public String toString() {
+ StringBuffer sb = new StringBuffer(64);
+ sb.append(getClass().getName()).append(" [");
+ sb.append(_offset).append("...").append(getLastIndex());
+ sb.append("]");
+ return sb.toString();
+ }
+ }
+
+
+ /**
+ * Encapsulates either an area or cell reference which may be 2d or 3d.
+ */
+ private static final class BaseRef {
+ private static final int INVALID_SHEET_INDEX = -1;
+ private final int _firstRowIndex;
+ private final int _firstColumnIndex;
+ private final int _width;
+ private final int _height;
+ private final int _externalSheetIndex;
+
+ public BaseRef(RefEval re) {
+ _firstRowIndex = re.getRow();
+ _firstColumnIndex = re.getColumn();
+ _height = 1;
+ _width = 1;
+ if (re instanceof Ref3DEval) {
+ Ref3DEval r3e = (Ref3DEval) re;
+ _externalSheetIndex = r3e.getExternSheetIndex();
+ } else {
+ _externalSheetIndex = INVALID_SHEET_INDEX;
+ }
+ }
+
+ public BaseRef(AreaEval ae) {
+ _firstRowIndex = ae.getFirstRow();
+ _firstColumnIndex = ae.getFirstColumn();
+ _height = ae.getLastRow() - ae.getFirstRow() + 1;
+ _width = ae.getLastColumn() - ae.getFirstColumn() + 1;
+ if (ae instanceof Area3DEval) {
+ Area3DEval a3e = (Area3DEval) ae;
+ _externalSheetIndex = a3e.getExternSheetIndex();
+ } else {
+ _externalSheetIndex = INVALID_SHEET_INDEX;
+ }
+ }
+
+ public int getWidth() {
+ return _width;
+ }
+
+ public int getHeight() {
+ return _height;
+ }
+
+ public int getFirstRowIndex() {
+ return _firstRowIndex;
+ }
+
+ public int getFirstColumnIndex() {
+ return _firstColumnIndex;
+ }
+
+ public boolean isIs3d() {
+ return _externalSheetIndex > 0;
+ }
+
+ public short getExternalSheetIndex() {
+ if(_externalSheetIndex < 0) {
+ throw new IllegalStateException("external sheet index only available for 3d refs");
+ }
+ return (short) _externalSheetIndex;
+ }
+
+ }
+
+ public ValueEval evaluate(Eval[] args, int srcCellRow, short srcCellCol, HSSFWorkbook workbook, HSSFSheet sheet) {
+
+ if(args.length < 3 || args.length > 5) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+
+ try {
+ BaseRef baseRef = evaluateBaseRef(args[0]);
+ int rowOffset = evaluateIntArg(args[1], srcCellRow, srcCellCol);
+ int columnOffset = evaluateIntArg(args[2], srcCellRow, srcCellCol);
+ int height = baseRef.getHeight();
+ int width = baseRef.getWidth();
+ switch(args.length) {
+ case 5:
+ width = evaluateIntArg(args[4], srcCellRow, srcCellCol);
+ case 4:
+ height = evaluateIntArg(args[3], srcCellRow, srcCellCol);
+ }
+ // Zero height or width raises #REF! error
+ if(height == 0 || width == 0) {
+ return ErrorEval.REF_INVALID;
+ }
+ LinearOffsetRange rowOffsetRange = new LinearOffsetRange(rowOffset, height);
+ LinearOffsetRange colOffsetRange = new LinearOffsetRange(columnOffset, width);
+ return createOffset(baseRef, rowOffsetRange, colOffsetRange, workbook, sheet);
+ } catch (EvalEx e) {
+ return e.getError();
+ }
+ }
+
+
+ private static AreaEval createOffset(BaseRef baseRef,
+ LinearOffsetRange rowOffsetRange, LinearOffsetRange colOffsetRange,
+ HSSFWorkbook workbook, HSSFSheet sheet) throws EvalEx {
+
+ LinearOffsetRange rows = rowOffsetRange.normaliseAndTranslate(baseRef.getFirstRowIndex());
+ LinearOffsetRange cols = colOffsetRange.normaliseAndTranslate(baseRef.getFirstColumnIndex());
+
+ if(rows.isOutOfBounds(0, LAST_VALID_ROW_INDEX)) {
+ throw new EvalEx(ErrorEval.REF_INVALID);
+ }
+ if(cols.isOutOfBounds(0, LAST_VALID_COLUMN_INDEX)) {
+ throw new EvalEx(ErrorEval.REF_INVALID);
+ }
+ if(baseRef.isIs3d()) {
+ Area3DPtg a3dp = new Area3DPtg(rows.getFirstIndex(), rows.getLastIndex(),
+ cols.getFirstIndex(), cols.getLastIndex(),
+ false, false, false, false,
+ baseRef.getExternalSheetIndex());
+ return HSSFFormulaEvaluator.evaluateArea3dPtg(workbook, a3dp);
+ }
+
+ AreaPtg ap = new AreaPtg(rows.getFirstIndex(), rows.getLastIndex(),
+ cols.getFirstIndex(), cols.getLastIndex(),
+ false, false, false, false);
+ return HSSFFormulaEvaluator.evaluateAreaPtg(sheet, workbook, ap);
+ }
+
+
+ private static BaseRef evaluateBaseRef(Eval eval) throws EvalEx {
+
+ if(eval instanceof RefEval) {
+ return new BaseRef((RefEval)eval);
+ }
+ if(eval instanceof AreaEval) {
+ return new BaseRef((AreaEval)eval);
+ }
+ if (eval instanceof ErrorEval) {
+ throw new EvalEx((ErrorEval) eval);
+ }
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+
+
+ /**
+ * OFFSET's numeric arguments (2..5) have similar processing rules
+ */
+ private static int evaluateIntArg(Eval eval, int srcCellRow, short srcCellCol) throws EvalEx {
+
+ double d = evaluateDoubleArg(eval, srcCellRow, srcCellCol);
+ return convertDoubleToInt(d);
+ }
+
+ /**
+ * Fractional values are silently truncated by Excel.
+ * Truncation is toward negative infinity.
+ */
+ /* package */ static int convertDoubleToInt(double d) {
+ // Note - the standard java type conversion from double to int truncates toward zero.
+ // but Math.floor() truncates toward negative infinity
+ return (int)Math.floor(d);
+ }
+
+
+ private static double evaluateDoubleArg(Eval eval, int srcCellRow, short srcCellCol) throws EvalEx {
+ ValueEval ve = evaluateSingleValue(eval, srcCellRow, srcCellCol);
+
+ if (ve instanceof NumericValueEval) {
+ return ((NumericValueEval) ve).getNumberValue();
+ }
+ if (ve instanceof StringEval) {
+ StringEval se = (StringEval) ve;
+ Double d = parseDouble(se.getStringValue());
+ if(d == null) {
+ throw new EvalEx(ErrorEval.VALUE_INVALID);
+ }
+ return d.doubleValue();
+ }
+ if (ve instanceof BoolEval) {
+ // in the context of OFFSET, booleans resolve to 0 and 1.
+ if(((BoolEval) ve).getBooleanValue()) {
+ return 1;
+ }
+ return 0;
+ }
+ throw new RuntimeException("Unexpected eval type (" + ve.getClass().getName() + ")");
+ }
+
+ private static Double parseDouble(String s) {
+ // TODO - find a home for this method
+ // TODO - support various number formats: sign char, dollars, commas
+ // OFFSET and COUNTIF seem to handle these
+ return Countif.parseDouble(s);
+ }
+
+ private static ValueEval evaluateSingleValue(Eval eval, int srcCellRow, short srcCellCol) throws EvalEx {
+ if(eval instanceof RefEval) {
+ return ((RefEval)eval).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() + ")");
+ }
+ // TODO - this code seems to get repeated a bit
+ 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);
+ }
}
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rounddown.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rounddown.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rounddown.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rounddown.java Sat Mar 8 03:49:00 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/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Roundup.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Roundup.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Roundup.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Roundup.java Sat Mar 8 03:49:00 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/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rows.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rows.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rows.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rows.java Sat Mar 8 03:49:00 2008
@@ -25,7 +25,7 @@
import org.apache.poi.hssf.record.formula.eval.RefEval;
/**
- * Implementation for Excel COLUMNS function.
+ * Implementation for Excel ROWS function.
*
* @author Josh Micich
*/
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Stdev.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Stdev.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Stdev.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Stdev.java Sat Mar 8 03:49:00 2008
@@ -33,8 +33,8 @@
public class Stdev extends MultiOperandNumericFunction {
private static final ValueEvalToNumericXlator DEFAULT_NUM_XLATOR =
- new ValueEvalToNumericXlator((short) (0
- // ValueEvalToNumericXlator.BOOL_IS_PARSED
+ new ValueEvalToNumericXlator((short) (
+ ValueEvalToNumericXlator.BOOL_IS_PARSED
//| ValueEvalToNumericXlator.REF_BOOL_IS_PARSED
//| ValueEvalToNumericXlator.EVALUATED_REF_BOOL_IS_PARSED
| ValueEvalToNumericXlator.STRING_IS_PARSED
@@ -44,7 +44,6 @@
//| ValueEvalToNumericXlator.REF_STRING_TO_BOOL_IS_PARSED
//| ValueEvalToNumericXlator.STRING_IS_INVALID_VALUE
//| ValueEvalToNumericXlator.REF_STRING_IS_INVALID_VALUE
- | ValueEvalToNumericXlator.EVALUATED_REF_BLANK_IS_PARSED
));
/**
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumproduct.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumproduct.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumproduct.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumproduct.java Sat Mar 8 03:49:00 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/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumsq.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumsq.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumsq.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumsq.java Sat Mar 8 03:49:00 2008
@@ -33,18 +33,18 @@
public class Sumsq extends MultiOperandNumericFunction {
private static final ValueEvalToNumericXlator DEFAULT_NUM_XLATOR =
new ValueEvalToNumericXlator((short) (
- // ValueEvalToNumericXlator.BOOL_IS_PARSED
+ ValueEvalToNumericXlator.BOOL_IS_PARSED
//| ValueEvalToNumericXlator.REF_BOOL_IS_PARSED
//| ValueEvalToNumericXlator.EVALUATED_REF_BOOL_IS_PARSED
- //| ValueEvalToNumericXlator.STRING_IS_PARSED
+ | ValueEvalToNumericXlator.STRING_IS_PARSED
//| ValueEvalToNumericXlator.REF_STRING_IS_PARSED
//| ValueEvalToNumericXlator.EVALUATED_REF_STRING_IS_PARSED
//| ValueEvalToNumericXlator.STRING_TO_BOOL_IS_PARSED
//| ValueEvalToNumericXlator.REF_STRING_TO_BOOL_IS_PARSED
//| ValueEvalToNumericXlator.STRING_IS_INVALID_VALUE
//| ValueEvalToNumericXlator.REF_STRING_IS_INVALID_VALUE
- ValueEvalToNumericXlator.REF_BLANK_IS_PARSED
- | ValueEvalToNumericXlator.BLANK_IS_PARSED
+ //| ValueEvalToNumericXlator.REF_BLANK_IS_PARSED
+ //| ValueEvalToNumericXlator.BLANK_IS_PARSED
));
protected ValueEvalToNumericXlator getXlator() {
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumx2my2.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumx2my2.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumx2my2.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumx2my2.java Sat Mar 8 03:49:00 2008
@@ -14,50 +14,23 @@
* 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.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.ValueEval;
+package org.apache.poi.hssf.record.formula.functions;
/**
+ * Implementation of Excel function SUMX2MY2()<p/>
+ *
+ * Calculates the sum of differences of squares in two arrays of the same size.<br/>
+ * <b>Syntax</b>:<br/>
+ * <b>SUMX2MY2</b>(<b>arrayX</b>, <b>arrayY</b>)<p/>
+ *
+ * result = Σ<sub>i: 0..n</sub>(x<sub>i</sub><sup>2</sup>-y<sub>i</sub><sup>2</sup>)
+ *
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
- *
*/
-public class Sumx2my2 extends XYNumericFunction {
+public final class Sumx2my2 extends XYNumericFunction {
-
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- ValueEval retval = null;
- double[][] values = null;
-
- int checkLen = 0; // check to see that all array lengths are equal
- switch (operands.length) {
- default:
- retval = ErrorEval.VALUE_INVALID;
- break;
- case 2:
- values = getValues(operands, srcCellRow, srcCellCol);
- if (values==null
- || values[X] == null || values[Y] == null
- || values[X].length == 0 || values[Y].length == 0
- || values[X].length != values[Y].length) {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
-
- if (retval == null) {
- double d = MathX.sumx2my2(values[X], values[Y]);
- retval = (Double.isNaN(d) || Double.isInfinite(d))
- ? (ValueEval) ErrorEval.NUM_ERROR
- : new NumberEval(d);
- }
-
- return retval;
+ protected double evaluate(double[] xArray, double[] yArray) {
+ return MathX.sumx2my2(xArray, yArray);
}
}
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumx2py2.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumx2py2.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumx2py2.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumx2py2.java Sat Mar 8 03:49:00 2008
@@ -14,50 +14,23 @@
* 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.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.ValueEval;
+package org.apache.poi.hssf.record.formula.functions;
/**
+ * Implementation of Excel function SUMX2PY2()<p/>
+ *
+ * Calculates the sum of squares in two arrays of the same size.<br/>
+ * <b>Syntax</b>:<br/>
+ * <b>SUMX2PY2</b>(<b>arrayX</b>, <b>arrayY</b>)<p/>
+ *
+ * result = Σ<sub>i: 0..n</sub>(x<sub>i</sub><sup>2</sup>+y<sub>i</sub><sup>2</sup>)
+ *
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
- *
*/
-public class Sumx2py2 extends XYNumericFunction {
+public final class Sumx2py2 extends XYNumericFunction {
-
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- ValueEval retval = null;
- double[][] values = null;
-
- int checkLen = 0; // check to see that all array lengths are equal
- switch (operands.length) {
- default:
- retval = ErrorEval.VALUE_INVALID;
- break;
- case 2:
- values = getValues(operands, srcCellRow, srcCellCol);
- if (values==null
- || values[X] == null || values[Y] == null
- || values[X].length == 0 || values[Y].length == 0
- || values[X].length != values[Y].length) {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
-
- if (retval == null) {
- double d = MathX.sumx2py2(values[X], values[Y]);
- retval = (Double.isNaN(d) || Double.isInfinite(d))
- ? (ValueEval) ErrorEval.NUM_ERROR
- : new NumberEval(d);
- }
-
- return retval;
+ protected double evaluate(double[] xArray, double[] yArray) {
+ return MathX.sumx2py2(xArray, yArray);
}
}
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumxmy2.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumxmy2.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumxmy2.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Sumxmy2.java Sat Mar 8 03:49:00 2008
@@ -14,50 +14,23 @@
* 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.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.ValueEval;
+package org.apache.poi.hssf.record.formula.functions;
/**
+ * Implementation of Excel function SUMXMY2()<p/>
+ *
+ * Calculates the sum of squares of differences between two arrays of the same size.<br/>
+ * <b>Syntax</b>:<br/>
+ * <b>SUMXMY2</b>(<b>arrayX</b>, <b>arrayY</b>)<p/>
+ *
+ * result = Σ<sub>i: 0..n</sub>(x<sub>i</sub>-y<sub>i</sub>)<sup>2</sup>
+ *
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
- *
*/
-public class Sumxmy2 extends XYNumericFunction {
+public final class Sumxmy2 extends XYNumericFunction {
-
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- ValueEval retval = null;
- double[][] values = null;
-
- int checkLen = 0; // check to see that all array lengths are equal
- switch (operands.length) {
- default:
- retval = ErrorEval.VALUE_INVALID;
- break;
- case 2:
- values = getValues(operands, srcCellRow, srcCellCol);
- if (values==null
- || values[X] == null || values[Y] == null
- || values[X].length == 0 || values[Y].length == 0
- || values[X].length != values[Y].length) {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
-
- if (retval == null) {
- double d = MathX.sumxmy2(values[X], values[Y]);
- retval = (Double.isNaN(d) || Double.isInfinite(d))
- ? (ValueEval) ErrorEval.NUM_ERROR
- : new NumberEval(d);
- }
-
- return retval;
+ protected double evaluate(double[] xArray, double[] yArray) {
+ return MathX.sumxmy2(xArray, yArray);
}
}
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/T.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/T.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/T.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/T.java Sat Mar 8 03:49:00 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/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Trim.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Trim.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Trim.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Trim.java Sat Mar 8 03:49:00 2008
@@ -16,12 +16,11 @@
*/
package org.apache.poi.hssf.record.formula.functions;
-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.EvaluationException;
+import org.apache.poi.hssf.record.formula.eval.OperandResolver;
import org.apache.poi.hssf.record.formula.eval.StringEval;
-import org.apache.poi.hssf.record.formula.eval.StringValueEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
@@ -30,46 +29,25 @@
* value is string.
* @author Manda Wilson < wilson at c bio dot msk cc dot org >
*/
-public class Trim extends TextFunction {
+public final class Trim extends TextFunction {
- /**
- * Removes leading and trailing spaces from value if evaluated
- * operand value is string.
- * Returns StringEval only if evaluated operand is of type string
- * (and is not blank or null) or number. If evaluated operand is
- * of type string and is blank or null, or if evaluated operand is
- * of type blank, returns BlankEval. Otherwise returns ErrorEval.
- *
- * @see org.apache.poi.hssf.record.formula.eval.Eval
- */
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- Eval retval = ErrorEval.VALUE_INVALID;
- String str = null;
-
- switch (operands.length) {
- default:
- break;
- case 1:
- ValueEval veval = singleOperandEvaluate(operands[0], srcCellRow, srcCellCol);
- if (veval instanceof StringValueEval) {
- StringValueEval sve = (StringValueEval) veval;
- str = sve.getStringValue();
- if (str == null || str.trim().equals("")) {
- return BlankEval.INSTANCE;
- }
- }
- else if (veval instanceof NumberEval) {
- NumberEval neval = (NumberEval) veval;
- str = neval.getStringValue();
- }
- else if (veval instanceof BlankEval) {
- return BlankEval.INSTANCE;
- }
- }
-
- if (str != null) {
- retval = new StringEval(str.trim());
- }
- return retval;
- }
+ public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+
+ if(args.length != 1) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ try {
+ ValueEval veval = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
+
+ String str = OperandResolver.coerceValueToString(veval);
+ str = str.trim();
+ if(str.length() < 1) {
+ return StringEval.EMPTY_INSTANCE;
+ }
+ return new StringEval(str);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ }
}
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Vlookup.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Vlookup.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Vlookup.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Vlookup.java Sat Mar 8 03:49:00 2008
@@ -1,25 +1,123 @@
-/*
-* 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.
-*/
-/*
- * Created on May 15, 2005
- *
- */
+/* ====================================================================
+ 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.functions;
-public class Vlookup extends NotImplementedFunction {
+import org.apache.poi.hssf.record.formula.eval.AreaEval;
+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.EvaluationException;
+import org.apache.poi.hssf.record.formula.eval.OperandResolver;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector;
+/**
+ * Implementation of the VLOOKUP() function.<p/>
+ *
+ * VLOOKUP finds a row in a lookup table by the first column value and returns the value from another column.
+ *
+ * <b>Syntax</b>:<br/>
+ * <b>VLOOKUP</b>(<b>lookup_value</b>, <b>table_array</b>, <b>col_index_num</b>, range_lookup)<p/>
+ *
+ * <b>lookup_value</b> The value to be found in the first column of the table array.<br/>
+ * <b>table_array</> An area reference for the lookup data. <br/>
+ * <b>col_index_num</b> a 1 based index specifying which column value of the lookup data will be returned.<br/>
+ * <b>range_lookup</b> If TRUE (default), VLOOKUP finds the largest value less than or equal to
+ * the lookup_value. If FALSE, only exact matches will be considered<br/>
+ *
+ * @author Josh Micich
+ */
+public final class Vlookup implements Function {
+
+ private static final class ColumnVector implements ValueVector {
+
+ private final AreaEval _tableArray;
+ private final int _size;
+ private final int _columnAbsoluteIndex;
+ private final int _firstRowAbsoluteIndex;
+
+ public ColumnVector(AreaEval tableArray, int columnIndex) {
+ _columnAbsoluteIndex = tableArray.getFirstColumn() + columnIndex;
+ if(!tableArray.containsColumn((short)_columnAbsoluteIndex)) {
+ int lastColIx = tableArray.getLastColumn() - tableArray.getFirstColumn();
+ throw new IllegalArgumentException("Specified column index (" + columnIndex
+ + ") is outside the allowed range (0.." + lastColIx + ")");
+ }
+ _tableArray = tableArray;
+ _size = tableArray.getLastRow() - tableArray.getFirstRow() + 1;
+ if(_size < 1) {
+ throw new RuntimeException("bad table array size zero");
+ }
+ _firstRowAbsoluteIndex = tableArray.getFirstRow();
+ }
+
+ public ValueEval getItem(int index) {
+ if(index>_size) {
+ throw new ArrayIndexOutOfBoundsException("Specified index (" + index
+ + ") is outside the allowed range (0.." + (_size-1) + ")");
+ }
+ return _tableArray.getValueAt(_firstRowAbsoluteIndex + index, (short)_columnAbsoluteIndex);
+ }
+ public int getSize() {
+ return _size;
+ }
+ }
+
+ public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+ Eval arg3 = null;
+ switch(args.length) {
+ case 4:
+ arg3 = args[3]; // important: assumed array element is never null
+ case 3:
+ break;
+ default:
+ // wrong number of arguments
+ return ErrorEval.VALUE_INVALID;
+ }
+ try {
+ // Evaluation order:
+ // arg0 lookup_value, arg1 table_array, arg3 range_lookup, find lookup value, arg2 col_index, fetch result
+ ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
+ AreaEval tableArray = LookupUtils.resolveTableArrayArg(args[1]);
+ boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(arg3, srcCellRow, srcCellCol);
+ int rowIndex = LookupUtils.lookupIndexOfValue(lookupValue, new ColumnVector(tableArray, 0), isRangeLookup);
+ ValueEval veColIndex = OperandResolver.getSingleValue(args[2], srcCellRow, srcCellCol);
+ int colIndex = LookupUtils.resolveRowOrColIndexArg(veColIndex);
+ ValueVector resultCol = createResultColumnVector(tableArray, colIndex);
+ return resultCol.getItem(rowIndex);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ }
+
+ /**
+ * Returns one column from an <tt>AreaEval</tt>
+ *
+ * @throws EvaluationException (#VALUE!) if colIndex is negative, (#REF!) if colIndex is too high
+ */
+ private ValueVector createResultColumnVector(AreaEval tableArray, int colIndex) throws EvaluationException {
+ if(colIndex < 0) {
+ throw EvaluationException.invalidValue();
+ }
+ int nCols = tableArray.getLastColumn() - tableArray.getFirstColumn() + 1;
+
+ if(colIndex >= nCols) {
+ throw EvaluationException.invalidRef();
+ }
+ return new ColumnVector(tableArray, colIndex);
+ }
}
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/XYNumericFunction.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/XYNumericFunction.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/XYNumericFunction.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/XYNumericFunction.java Sat Mar 8 03:49:00 2008
@@ -14,154 +14,151 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-/*
- * Created on May 29, 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.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.Eval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
import org.apache.poi.hssf.record.formula.eval.NumberEval;
import org.apache.poi.hssf.record.formula.eval.RefEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
-import org.apache.poi.hssf.record.formula.eval.ValueEvalToNumericXlator;
/**
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
*
*/
-public abstract class XYNumericFunction extends NumericFunction {
+public abstract class XYNumericFunction implements Function {
protected static final int X = 0;
protected static final int Y = 1;
+
+ protected static final class DoubleArrayPair {
- private static final ValueEvalToNumericXlator DEFAULT_NUM_XLATOR =
- new ValueEvalToNumericXlator((short) (
- ValueEvalToNumericXlator.BOOL_IS_PARSED
- | ValueEvalToNumericXlator.REF_BOOL_IS_PARSED
- | ValueEvalToNumericXlator.EVALUATED_REF_BOOL_IS_PARSED
- //| ValueEvalToNumericXlator.STRING_IS_PARSED
- | ValueEvalToNumericXlator.REF_STRING_IS_PARSED
- | ValueEvalToNumericXlator.EVALUATED_REF_STRING_IS_PARSED
- //| ValueEvalToNumericXlator.STRING_TO_BOOL_IS_PARSED
- //| ValueEvalToNumericXlator.REF_STRING_TO_BOOL_IS_PARSED
- //| ValueEvalToNumericXlator.STRING_IS_INVALID_VALUE
- //| ValueEvalToNumericXlator.REF_STRING_IS_INVALID_VALUE
- ));
+ private final double[] _xArray;
+ private final double[] _yArray;
- /**
- * this is the default impl for the factory method getXlator
- * of the super class NumericFunction. Subclasses can override this method
- * if they desire to return a different ValueEvalToNumericXlator instance
- * than the default.
- */
- protected ValueEvalToNumericXlator getXlator() {
- return DEFAULT_NUM_XLATOR;
- }
-
- protected int getMaxNumOperands() {
- return 30;
+ public DoubleArrayPair(double[] xArray, double[] yArray) {
+ _xArray = xArray;
+ _yArray = yArray;
+ }
+ public double[] getXArray() {
+ return _xArray;
+ }
+ public double[] getYArray() {
+ return _yArray;
+ }
}
+
+ public final Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+ if(args.length != 2) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ double[][] values;
+ try {
+ values = getValues(args[0], args[1]);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ if (values==null
+ || values[X] == null || values[Y] == null
+ || values[X].length == 0 || values[Y].length == 0
+ || values[X].length != values[Y].length) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ double d = evaluate(values[X], values[Y]);
+ if (Double.isNaN(d) || Double.isInfinite(d)) {
+ return ErrorEval.NUM_ERROR;
+ }
+ return new NumberEval(d);
+ }
+ protected abstract double evaluate(double[] xArray, double[] yArray);
+
/**
* Returns a double array that contains values for the numeric cells
* 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.
- *
- * @param xops
- * @param yops
- * @param srcRow
- * @param srcCol
*/
- protected double[][] getNumberArray(Eval[] xops, Eval[] yops, int srcRow, short srcCol) {
- double[][] retval = new double[2][30];
+ private static double[][] getNumberArray(Eval[] xops, Eval[] yops) throws EvaluationException {
+
+ // check for errors first: size mismatch, value errors in x, value errors in y
+
+ int nArrayItems = xops.length;
+ if(nArrayItems != yops.length) {
+ throw new EvaluationException(ErrorEval.NA);
+ }
+ for (int i = 0; i < xops.length; i++) {
+ Eval eval = xops[i];
+ if (eval instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval) eval);
+ }
+ }
+ for (int i = 0; i < yops.length; i++) {
+ Eval eval = yops[i];
+ if (eval instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval) eval);
+ }
+ }
+
+ double[] xResult = new double[nArrayItems];
+ double[] yResult = new double[nArrayItems];
+
int count = 0;
- if (xops.length > getMaxNumOperands()
- || yops.length > getMaxNumOperands()
- || xops.length != yops.length) {
- retval = null;
- }
- else {
-
- for (int i=0, iSize=xops.length; i<iSize; i++) {
- Eval xEval = xops[i];
- Eval yEval = yops[i];
-
- if (isNumberEval(xEval) && isNumberEval(yEval)) {
- retval[X] = ensureCapacity(retval[X], count);
- retval[Y] = ensureCapacity(retval[Y], count);
- retval[X][count] = getDoubleValue(xEval);
- retval[Y][count] = getDoubleValue(yEval);
- if (Double.isNaN(retval[X][count]) || Double.isNaN(retval[Y][count])) {
- retval = null;
- break;
- }
- count++;
- }
- }
- }
-
- if (retval != null) {
- double[][] temp = retval;
- retval[X] = trimToSize(retval[X], count);
- retval[Y] = trimToSize(retval[Y], count);
- }
-
- return retval;
- }
-
- protected double[][] getValues(Eval[] operands, int srcCellRow, short srcCellCol) {
- double[][] retval = null;
-
- outer: do {
- if (operands.length == 2) {
- Eval[] xEvals = new Eval[1];
- Eval[] yEvals = new Eval[1];
- if (operands[X] instanceof AreaEval) {
- AreaEval ae = (AreaEval) operands[0];
- xEvals = ae.getValues();
- }
- else if (operands[X] instanceof ErrorEval) {
- break outer;
- }
- else {
- xEvals[0] = operands[X];
- }
-
- if (operands[Y] instanceof AreaEval) {
- AreaEval ae = (AreaEval) operands[Y];
- yEvals = ae.getValues();
- }
- else if (operands[Y] instanceof ErrorEval) {
- break outer;
- }
- else {
- yEvals[0] = operands[Y];
- }
-
- retval = getNumberArray(xEvals, yEvals, srcCellRow, srcCellCol);
- }
- } while (false);
-
- return retval;
- }
-
-
- protected static double[] ensureCapacity(double[] arr, int pos) {
- double[] temp = arr;
- while (pos >= arr.length) {
- arr = new double[arr.length << 2];
- }
- if (temp.length != arr.length)
- System.arraycopy(temp, 0, arr, 0, temp.length);
- return arr;
+ for (int i=0, iSize=nArrayItems; i<iSize; i++) {
+ Eval xEval = xops[i];
+ Eval yEval = yops[i];
+
+ if (isNumberEval(xEval) && isNumberEval(yEval)) {
+ xResult[count] = getDoubleValue(xEval);
+ yResult[count] = getDoubleValue(yEval);
+ if (Double.isNaN(xResult[count]) || Double.isNaN(xResult[count])) {
+ throw new EvaluationException(ErrorEval.NUM_ERROR);
+ }
+ count++;
+ }
+ }
+
+ return new double[][] {
+ trimToSize(xResult, count),
+ trimToSize(yResult, count),
+ };
+ }
+
+ private static double[][] getValues(Eval argX, Eval argY) throws EvaluationException {
+
+ if (argX instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval) argX);
+ }
+ if (argY instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval) argY);
+ }
+
+ Eval[] xEvals;
+ Eval[] yEvals;
+ if (argX instanceof AreaEval) {
+ AreaEval ae = (AreaEval) argX;
+ xEvals = ae.getValues();
+ } else {
+ xEvals = new Eval[] { argX, };
+ }
+
+ if (argY instanceof AreaEval) {
+ AreaEval ae = (AreaEval) argY;
+ yEvals = ae.getValues();
+ } else {
+ yEvals = new Eval[] { argY, };
+ }
+
+ return getNumberArray(xEvals, yEvals);
}
- protected static double[] trimToSize(double[] arr, int len) {
+ private static double[] trimToSize(double[] arr, int len) {
double[] tarr = arr;
if (arr.length > len) {
tarr = new double[len];
@@ -170,7 +167,7 @@
return tarr;
}
- protected static boolean isNumberEval(Eval eval) {
+ private static boolean isNumberEval(Eval eval) {
boolean retval = false;
if (eval instanceof NumberEval) {
@@ -185,7 +182,7 @@
return retval;
}
- protected static double getDoubleValue(Eval eval) {
+ private static double getDoubleValue(Eval eval) {
double retval = 0;
if (eval instanceof NumberEval) {
NumberEval ne = (NumberEval) eval;
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java Sat Mar 8 03:49:00 2008
@@ -14,10 +14,7 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-/*
- * Created on May 5, 2005
- *
- */
+
package org.apache.poi.hssf.usermodel;
import java.lang.reflect.Constructor;
@@ -74,11 +71,13 @@
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.Eval;
import org.apache.poi.hssf.record.formula.eval.FuncVarEval;
+import org.apache.poi.hssf.record.formula.eval.FunctionEval;
import org.apache.poi.hssf.record.formula.eval.GreaterEqualEval;
import org.apache.poi.hssf.record.formula.eval.GreaterThanEval;
import org.apache.poi.hssf.record.formula.eval.LessEqualEval;
import org.apache.poi.hssf.record.formula.eval.LessThanEval;
import org.apache.poi.hssf.record.formula.eval.MultiplyEval;
+import org.apache.poi.hssf.record.formula.eval.NameEval;
import org.apache.poi.hssf.record.formula.eval.NotEqualEval;
import org.apache.poi.hssf.record.formula.eval.NumberEval;
import org.apache.poi.hssf.record.formula.eval.OperationEval;
@@ -91,13 +90,10 @@
import org.apache.poi.hssf.record.formula.eval.UnaryMinusEval;
import org.apache.poi.hssf.record.formula.eval.UnaryPlusEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
-import org.apache.poi.hssf.usermodel.HSSFSheet;
/**
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
*
- * Limitations: Unfortunately, cyclic references will cause stackoverflow
- * exception
*/
public class HSSFFormulaEvaluator {
@@ -173,7 +169,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 +282,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 +333,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);
}
@@ -348,16 +349,26 @@
* Dev. Note: Internal evaluate must be passed only a formula cell
* else a runtime exception will be thrown somewhere inside the method.
* (Hence this is a private method.)
- *
- * @param srcCell
- * @param srcRow
- * @param sheet
- * @param workbook
*/
- protected static ValueEval internalEvaluate(HSSFCell srcCell, HSSFRow srcRow, HSSFSheet sheet, HSSFWorkbook workbook) {
+ private static ValueEval internalEvaluate(HSSFCell srcCell, HSSFRow srcRow, HSSFSheet sheet, HSSFWorkbook workbook) {
int srcRowNum = srcRow.getRowNum();
short srcColNum = srcCell.getCellNum();
- FormulaParser parser = new FormulaParser(srcCell.getCellFormula(), workbook.getWorkbook());
+
+
+ EvaluationCycleDetector tracker = EvaluationCycleDetectorManager.getTracker();
+
+ if(!tracker.startEvaluate(workbook, sheet, srcRowNum, srcColNum)) {
+ return ErrorEval.CIRCULAR_REF_ERROR;
+ }
+ try {
+ return evaluateCell(workbook, sheet, srcRowNum, srcColNum, srcCell.getCellFormula());
+ } finally {
+ tracker.endEvaluate(workbook, sheet, srcRowNum, srcColNum);
+ }
+ }
+ private static ValueEval evaluateCell(HSSFWorkbook workbook, HSSFSheet sheet,
+ int srcRowNum, short srcColNum, String cellFormulaText) {
+ FormulaParser parser = new FormulaParser(cellFormulaText, workbook.getWorkbook());
parser.parse();
Ptg[] ptgs = parser.getRPNPtg();
// -- parsing over --
@@ -366,16 +377,25 @@
Stack stack = new Stack();
for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
- // since we dont know how to handle these yet :(
- if (ptgs[i] instanceof ControlPtg) { continue; }
- if (ptgs[i] instanceof MemErrPtg) { continue; }
- if (ptgs[i] instanceof MissingArgPtg) { continue; }
- if (ptgs[i] instanceof NamePtg) { continue; }
- if (ptgs[i] instanceof NameXPtg) { continue; }
- if (ptgs[i] instanceof UnknownPtg) { continue; }
+ // since we don't know how to handle these yet :(
+ Ptg ptg = ptgs[i];
+ if (ptg instanceof ControlPtg) { continue; }
+ if (ptg instanceof MemErrPtg) { continue; }
+ if (ptg instanceof MissingArgPtg) { continue; }
+ if (ptg instanceof NamePtg) {
+ // named ranges, macro functions
+ NamePtg namePtg = (NamePtg) ptg;
+ stack.push(new NameEval(namePtg.getIndex()));
+ continue;
+ }
+ if (ptg instanceof NameXPtg) {
+ // TODO - external functions
+ continue;
+ }
+ if (ptg instanceof UnknownPtg) { continue; }
- if (ptgs[i] instanceof OperationPtg) {
- OperationPtg optg = (OperationPtg) ptgs[i];
+ if (ptg instanceof OperationPtg) {
+ OperationPtg optg = (OperationPtg) ptg;
// parens can be ignored since we have RPN tokens
if (optg instanceof ParenthesisPtg) { continue; }
@@ -392,86 +412,152 @@
Eval p = (Eval) stack.pop();
ops[j] = p;
}
- Eval opresult = operation.evaluate(ops, srcRowNum, srcColNum);
+ Eval opresult = invokeOperation(operation, ops, srcRowNum, srcColNum, workbook, sheet);
stack.push(opresult);
}
- else if (ptgs[i] instanceof ReferencePtg) {
- ReferencePtg ptg = (ReferencePtg) ptgs[i];
- short colnum = ptg.getColumn();
- short rownum = ptg.getRow();
- HSSFRow row = sheet.getRow(rownum);
- HSSFCell cell = (row != null) ? row.getCell(colnum) : null;
- pushRef2DEval(ptg, stack, cell, row, sheet, workbook);
- }
- else if (ptgs[i] instanceof Ref3DPtg) {
- Ref3DPtg ptg = (Ref3DPtg) ptgs[i];
- short colnum = ptg.getColumn();
- short rownum = ptg.getRow();
+ else if (ptg instanceof ReferencePtg) {
+ ReferencePtg refPtg = (ReferencePtg) ptg;
+ int colIx = refPtg.getColumn();
+ int rowIx = refPtg.getRow();
+ HSSFRow row = sheet.getRow(rowIx);
+ HSSFCell cell = (row != null) ? row.getCell(colIx) : null;
+ stack.push(createRef2DEval(refPtg, cell, row, sheet, workbook));
+ }
+ else if (ptg instanceof Ref3DPtg) {
+ Ref3DPtg refPtg = (Ref3DPtg) ptg;
+ int colIx = refPtg.getColumn();
+ int rowIx = refPtg.getRow();
Workbook wb = workbook.getWorkbook();
- 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);
- }
- else if (ptgs[i] instanceof AreaPtg) {
- AreaPtg ap = (AreaPtg) ptgs[i];
- short row0 = ap.getFirstRow();
- short col0 = ap.getFirstColumn();
- short row1 = ap.getLastRow();
- short col1 = ap.getLastColumn();
- ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)];
- for (short x = row0; sheet != null && x < row1 + 1; x++) {
- HSSFRow row = sheet.getRow(x);
- for (short y = col0; row != null && y < col1 + 1; y++) {
- values[(x - row0) * (col1 - col0 + 1) + (y - col0)] =
- getEvalForCell(row.getCell(y), row, sheet, workbook);
- }
- }
- AreaEval ae = new Area2DEval(ap, values);
+ HSSFSheet xsheet = workbook.getSheetAt(wb.getSheetIndexFromExternSheetIndex(refPtg.getExternSheetIndex()));
+ HSSFRow row = xsheet.getRow(rowIx);
+ HSSFCell cell = (row != null) ? row.getCell(colIx) : null;
+ stack.push(createRef3DEval(refPtg, cell, row, xsheet, workbook));
+ }
+ else if (ptg instanceof AreaPtg) {
+ AreaPtg ap = (AreaPtg) ptg;
+ AreaEval ae = evaluateAreaPtg(sheet, workbook, ap);
stack.push(ae);
}
- else if (ptgs[i] instanceof Area3DPtg) {
- Area3DPtg a3dp = (Area3DPtg) ptgs[i];
- short row0 = a3dp.getFirstRow();
- short col0 = a3dp.getFirstColumn();
- short row1 = a3dp.getLastRow();
- short col1 = a3dp.getLastColumn();
- Workbook wb = workbook.getWorkbook();
- HSSFSheet xsheet = workbook.getSheetAt(wb.getSheetIndexFromExternSheetIndex(a3dp.getExternSheetIndex()));
- ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)];
- for (short x = row0; xsheet != null && x < row1 + 1; x++) {
- HSSFRow row = xsheet.getRow(x);
- for (short y = col0; row != null && y < col1 + 1; y++) {
- values[(x - row0) * (col1 - col0 + 1) + (y - col0)] =
- getEvalForCell(row.getCell(y), row, xsheet, workbook);
- }
- }
- AreaEval ae = new Area3DEval(a3dp, values);
+ else if (ptg instanceof Area3DPtg) {
+ Area3DPtg a3dp = (Area3DPtg) ptg;
+ AreaEval ae = evaluateArea3dPtg(workbook, a3dp);
stack.push(ae);
}
else {
- Eval ptgEval = getEvalForPtg(ptgs[i]);
+ Eval ptgEval = getEvalForPtg(ptg);
stack.push(ptgEval);
}
}
+
ValueEval value = ((ValueEval) stack.pop());
- if (value instanceof RefEval) {
- RefEval rv = (RefEval) value;
- value = rv.getInnerValueEval();
- }
- else if (value instanceof AreaEval) {
- AreaEval ae = (AreaEval) value;
- if (ae.isRow())
- value = ae.getValueAt(ae.getFirstRow(), srcColNum);
- else if (ae.isColumn())
- value = ae.getValueAt(srcRowNum, ae.getFirstColumn());
- else
- value = ErrorEval.VALUE_INVALID;
+ if (!stack.isEmpty()) {
+ throw new IllegalStateException("evaluation stack not empty");
+ }
+ value = dereferenceValue(value, srcRowNum, srcColNum);
+ if (value instanceof BlankEval) {
+ // Note Excel behaviour here. A blank final final value is converted to zero.
+ return NumberEval.ZERO;
+ // Formulas _never_ evaluate to blank. If a formula appears to have evaluated to
+ // blank, the actual value is empty string. This can be verified with ISBLANK().
}
return value;
}
/**
+ * Dereferences a single value from any AreaEval or RefEval evaluation result.
+ * If the supplied evaluationResult is just a plain value, it is returned as-is.
+ * @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>,
+ * <tt>BlankEval</tt> or <tt>ErrorEval</tt>. Never <code>null</code>.
+ */
+ private static ValueEval dereferenceValue(ValueEval evaluationResult, int srcRowNum, short srcColNum) {
+ if (evaluationResult instanceof RefEval) {
+ RefEval rv = (RefEval) evaluationResult;
+ return rv.getInnerValueEval();
+ }
+ if (evaluationResult instanceof AreaEval) {
+ AreaEval ae = (AreaEval) evaluationResult;
+ if (ae.isRow()) {
+ if(ae.isColumn()) {
+ return ae.getValues()[0];
+ }
+ return ae.getValueAt(ae.getFirstRow(), srcColNum);
+ }
+ if (ae.isColumn()) {
+ return ae.getValueAt(srcRowNum, ae.getFirstColumn());
+ }
+ return ErrorEval.VALUE_INVALID;
+ }
+ return evaluationResult;
+ }
+
+ private static Eval invokeOperation(OperationEval operation, Eval[] ops, int srcRowNum, short srcColNum,
+ HSSFWorkbook workbook, HSSFSheet sheet) {
+
+ if(operation instanceof FunctionEval) {
+ FunctionEval fe = (FunctionEval) operation;
+ if(fe.isFreeRefFunction()) {
+ return fe.getFreeRefFunction().evaluate(ops, srcRowNum, srcColNum, workbook, sheet);
+ }
+ }
+ return operation.evaluate(ops, srcRowNum, srcColNum);
+ }
+
+ public static AreaEval evaluateAreaPtg(HSSFSheet sheet, HSSFWorkbook workbook, AreaPtg ap) {
+ int row0 = ap.getFirstRow();
+ int col0 = ap.getFirstColumn();
+ int row1 = ap.getLastRow();
+ int col1 = ap.getLastColumn();
+
+ // If the last row is -1, then the
+ // reference is for the rest of the column
+ // (eg C:C)
+ // TODO: Handle whole column ranges properly
+ if(row1 == -1 && row0 >= 0) {
+ row1 = (short)sheet.getLastRowNum();
+ }
+ ValueEval[] values = evalArea(workbook, sheet, row0, col0, row1, col1);
+ return new Area2DEval(ap, values);
+ }
+
+ public static AreaEval evaluateArea3dPtg(HSSFWorkbook workbook, Area3DPtg a3dp) {
+ int row0 = a3dp.getFirstRow();
+ int col0 = a3dp.getFirstColumn();
+ int row1 = a3dp.getLastRow();
+ int col1 = a3dp.getLastColumn();
+ Workbook wb = workbook.getWorkbook();
+ HSSFSheet xsheet = workbook.getSheetAt(wb.getSheetIndexFromExternSheetIndex(a3dp.getExternSheetIndex()));
+
+ // If the last row is -1, then the
+ // reference is for the rest of the column
+ // (eg C:C)
+ // TODO: Handle whole column ranges properly
+ if(row1 == -1 && row0 >= 0) {
+ row1 = (short)xsheet.getLastRowNum();
+ }
+
+ ValueEval[] values = evalArea(workbook, xsheet, row0, col0, row1, col1);
+ return new Area3DEval(a3dp, values);
+ }
+
+ private static ValueEval[] evalArea(HSSFWorkbook workbook, HSSFSheet sheet,
+ int row0, int col0, int row1, int col1) {
+ ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)];
+ for (int x = row0; sheet != null && x < row1 + 1; x++) {
+ HSSFRow row = sheet.getRow(x);
+ for (int y = col0; y < col1 + 1; y++) {
+ ValueEval cellEval;
+ if(row == null) {
+ cellEval = BlankEval.INSTANCE;
+ } else {
+ cellEval = getEvalForCell(row.getCell(y), row, sheet, workbook);
+ }
+ values[(x - row0) * (col1 - col0 + 1) + (y - col0)] = cellEval;
+ }
+ }
+ return values;
+ }
+
+ /**
* returns the OperationEval concrete impl instance corresponding
* to the suplied operationPtg
* @param ptg
@@ -544,104 +630,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);
+ }
+
+ 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()));
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()));
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));
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()));
case HSSFCell.CELL_TYPE_BLANK:
- stack.push(new Ref2DEval(ptg, BlankEval.INSTANCE, false));
- break;
+ return new Ref2DEval(ptg, BlankEval.INSTANCE);
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()));
}
+ 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);
+ }
+ 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()));
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()));
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));
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()));
case HSSFCell.CELL_TYPE_BLANK:
- stack.push(new Ref3DEval(ptg, BlankEval.INSTANCE, false));
- break;
+ return new Ref3DEval(ptg, BlankEval.INSTANCE);
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()));
}
+ throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
}
/**
@@ -726,15 +785,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;
+ }
}
/**
Modified: poi/branches/ooxml/src/scratchpad/testcases/org/apache/poi/hssf/eventusermodel/TestMissingRecordAwareHSSFListener.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/testcases/org/apache/poi/hssf/eventusermodel/TestMissingRecordAwareHSSFListener.java?rev=634936&r1=634935&r2=634936&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/testcases/org/apache/poi/hssf/eventusermodel/TestMissingRecordAwareHSSFListener.java (original)
+++ poi/branches/ooxml/src/scratchpad/testcases/org/apache/poi/hssf/eventusermodel/TestMissingRecordAwareHSSFListener.java Sat Mar 8 03:49:00 2008
@@ -16,14 +16,14 @@
==================================================================== */
package org.apache.poi.hssf.eventusermodel;
-import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
-import org.apache.poi.hssf.eventusermodel.HSSFListener;
-
import java.io.File;
import java.io.FileInputStream;
+import java.io.IOException;
import java.util.ArrayList;
+import java.util.List;
+
+import junit.framework.TestCase;
-import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingRowDummyRecord;
@@ -31,31 +31,33 @@
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.RowRecord;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
-
-import junit.framework.TestCase;
-
-public class TestMissingRecordAwareHSSFListener extends TestCase {
- private String dirname;
-
- public TestMissingRecordAwareHSSFListener() {
- dirname = System.getProperty("HSSF.testdata.path");
- }
+/**
+ * Tests for MissingRecordAwareHSSFListener
+ */
+public final class TestMissingRecordAwareHSSFListener extends TestCase {
- public void testMissingRowRecords() throws Exception {
+ private Record[] r;
+
+ public void setUp() {
+ String dirname = System.getProperty("HSSF.testdata.path");
File f = new File(dirname + "/MissingBits.xls");
-
+
HSSFRequest req = new HSSFRequest();
MockHSSFListener mockListen = new MockHSSFListener();
MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(mockListen);
req.addListenerForAllRecords(listener);
- POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f));
HSSFEventFactory factory = new HSSFEventFactory();
- factory.processWorkbookEvents(req, fs);
-
- // Check we got the dummy records
- Record[] r = (Record[])
- mockListen.records.toArray(new Record[mockListen.records.size()]);
+ try {
+ POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f));
+ factory.processWorkbookEvents(req, fs);
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ r = mockListen.getRecords();
+ }
+
+ public void testMissingRowRecords() throws Exception {
// We have rows 0, 1, 2, 20 and 21
int row0 = -1;
@@ -105,20 +107,6 @@
}
public void testEndOfRowRecords() throws Exception {
- File f = new File(dirname + "/MissingBits.xls");
-
- HSSFRequest req = new HSSFRequest();
- MockHSSFListener mockListen = new MockHSSFListener();
- MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(mockListen);
- req.addListenerForAllRecords(listener);
-
- POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f));
- HSSFEventFactory factory = new HSSFEventFactory();
- factory.processWorkbookEvents(req, fs);
-
- // Check we got the dummy records
- Record[] r = (Record[])
- mockListen.records.toArray(new Record[mockListen.records.size()]);
// Find the cell at 0,0
int cell00 = -1;
@@ -240,20 +228,6 @@
public void testMissingCellRecords() throws Exception {
- File f = new File(dirname + "/MissingBits.xls");
-
- HSSFRequest req = new HSSFRequest();
- MockHSSFListener mockListen = new MockHSSFListener();
- MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(mockListen);
- req.addListenerForAllRecords(listener);
-
- POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f));
- HSSFEventFactory factory = new HSSFEventFactory();
- factory.processWorkbookEvents(req, fs);
-
- // Check we got the dummy records
- Record[] r = (Record[])
- mockListen.records.toArray(new Record[mockListen.records.size()]);
// Find the cell at 0,0
int cell00 = -1;
@@ -352,25 +326,35 @@
assertEquals(10, mc.getColumn());
}
- private static class MockHSSFListener implements HSSFListener {
- private MockHSSFListener() {}
- private ArrayList records = new ArrayList();
+ private static final class MockHSSFListener implements HSSFListener {
+ public MockHSSFListener() {}
+ private final List _records = new ArrayList();
public void processRecord(Record record) {
- records.add(record);
+ _records.add(record);
if(record instanceof MissingRowDummyRecord) {
MissingRowDummyRecord mr = (MissingRowDummyRecord)record;
- System.out.println("Got dummy row " + mr.getRowNumber());
+ log("Got dummy row " + mr.getRowNumber());
}
if(record instanceof MissingCellDummyRecord) {
MissingCellDummyRecord mc = (MissingCellDummyRecord)record;
- System.out.println("Got dummy cell " + mc.getRow() + " " + mc.getColumn());
+ log("Got dummy cell " + mc.getRow() + " " + mc.getColumn());
}
if(record instanceof LastCellOfRowDummyRecord) {
LastCellOfRowDummyRecord lc = (LastCellOfRowDummyRecord)record;
- System.out.println("Got end-of row, row was " + lc.getRow() + ", last column was " + lc.getLastColumnNumber());
+ log("Got end-of row, row was " + lc.getRow() + ", last column was " + lc.getLastColumnNumber());
+ }
+ }
+ private static void log(String msg) {
+ if(false) { // successful tests should be quiet
+ System.out.println(msg);
}
+ }
+ public Record[] getRecords() {
+ Record[] result = new Record[_records.size()];
+ _records.toArray(result);
+ return result;
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org