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/21 16:08:45 UTC
svn commit: r629821 - in /poi/trunk/src: documentation/content/xdocs/
java/org/apache/poi/hssf/record/formula/
scratchpad/src/org/apache/poi/hssf/record/formula/eval/
scratchpad/src/org/apache/poi/hssf/record/formula/functions/
scratchpad/src/org/apach...
Author: nick
Date: Thu Feb 21 07:08:44 2008
New Revision: 629821
URL: http://svn.apache.org/viewvc?rev=629821&view=rev
Log:
Patch from Josh from bug #44371 - support for OFFSET function, and various tweaks to the formula evaluator to support this
Added:
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java (with props)
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestOffset.java (with props)
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/record/formula/Area3DPtg.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/Area3DEval.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/FunctionEval.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/Ref3DEval.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Indirect.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Offset.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rows.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
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=629821&r1=629820&r2=629821&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Thu Feb 21 07:08:44 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">44371 - Support for the Offset function</action>
<action dev="POI-DEVELOPERS" type="fix">38921 - Have HSSFPalette.findSimilar() work properly</action>
<action dev="POI-DEVELOPERS" type="fix">44456 - Fix the contrib SViewer / SViewerPanel to not fail on sheets with missing rows</action>
<action dev="POI-DEVELOPERS" type="fix">44403 - Further support for unusual, but valid, arguments to the Mid function</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=629821&r1=629820&r2=629821&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Thu Feb 21 07:08:44 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">44371 - Support for the Offset function</action>
<action dev="POI-DEVELOPERS" type="fix">38921 - Have HSSFPalette.findSimilar() work properly</action>
<action dev="POI-DEVELOPERS" type="fix">44456 - Fix the contrib SViewer / SViewerPanel to not fail on sheets with missing rows</action>
<action dev="POI-DEVELOPERS" type="fix">44403 - Further support for unusual, but valid, arguments to the Mid function</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java?rev=629821&r1=629820&r2=629821&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java Thu Feb 21 07:08:44 2008
@@ -70,7 +70,21 @@
field_5_last_column = in.readShort();
}
- public String toString()
+ public Area3DPtg(short firstRow, short lastRow, short firstColumn, short lastColumn,
+ boolean firstRowRelative, boolean lastRowRelative, boolean firstColRelative, boolean lastColRelative,
+ short externalSheetIndex) {
+ setFirstRow(firstRow);
+ setLastRow(lastRow);
+ setFirstColumn(firstColumn);
+ setLastColumn(lastColumn);
+ setFirstRowRelative(firstRowRelative);
+ setLastRowRelative(lastRowRelative);
+ setFirstColRelative(firstColRelative);
+ setLastColRelative(lastColRelative);
+ setExternSheetIndex(externalSheetIndex);
+ }
+
+ public String toString()
{
StringBuffer buffer = new StringBuffer();
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/Area3DEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/Area3DEval.java?rev=629821&r1=629820&r2=629821&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/Area3DEval.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/Area3DEval.java Thu Feb 21 07:08:44 2008
@@ -27,7 +27,7 @@
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
*
*/
-public class Area3DEval implements AreaEval {
+public final class Area3DEval implements AreaEval {
private Area3DPtg delegate;
@@ -88,6 +88,10 @@
public boolean isRow() {
return delegate.getFirstRow() == delegate.getLastRow();
+ }
+
+ public int getExternSheetIndex() {
+ return delegate.getExternSheetIndex();
}
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/FunctionEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/FunctionEval.java?rev=629821&r1=629820&r2=629821&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/FunctionEval.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/FunctionEval.java Thu Feb 21 07:08:44 2008
@@ -20,6 +20,9 @@
*/
package org.apache.poi.hssf.record.formula.eval;
+import java.util.HashMap;
+import java.util.Map;
+
import org.apache.poi.hssf.record.formula.functions.*;
/**
@@ -27,12 +30,47 @@
*
*/
public abstract class FunctionEval implements OperationEval {
+ /**
+ * Some function IDs that require special treatment
+ */
+ private static final class FunctionID {
+ /** 78 */
+ public static final int OFFSET = 78;
+ /** 148 */
+ public static final int INDIRECT = 148;
+
+ }
+ // convenient access to namespace
+ private static final FunctionID ID = null;
+
protected static Function[] functions = produceFunctions();
+ private static Map freeRefFunctionsByIdMap;
+
+ static {
+ Map m = new HashMap();
+ addMapping(m, ID.OFFSET, new Offset());
+ addMapping(m, ID.INDIRECT, new Indirect());
+ freeRefFunctionsByIdMap = m;
+ }
+ private static void addMapping(Map m, int offset, FreeRefFunction frf) {
+ m.put(createFRFKey(offset), frf);
+ }
+ private static Integer createFRFKey(int functionIndex) {
+ return new Integer(functionIndex);
+ }
+
+
public Function getFunction() {
short fidx = getFunctionIndex();
return functions[fidx];
}
+ public boolean isFreeRefFunction() {
+ return freeRefFunctionsByIdMap.containsKey(createFRFKey(getFunctionIndex()));
+ }
+ public FreeRefFunction getFreeRefFunction() {
+ return (FreeRefFunction) freeRefFunctionsByIdMap.get(createFRFKey(getFunctionIndex()));
+ }
public abstract short getFunctionIndex();
@@ -115,7 +153,7 @@
retval[75] = new Areas(); // AREAS
retval[76] = new Rows(); // ROWS
retval[77] = new Columns(); // COLUMNS
- retval[78] = new Offset(); // OFFSET
+ retval[ID.OFFSET] = null; // Offset.evaluate has a different signature
retval[79] = new Absref(); // ABSREF
retval[80] = new Relref(); // RELREF
retval[81] = new Argument(); // ARGUMENT
@@ -185,7 +223,7 @@
retval[145] = new NotImplementedFunction(); // GETDEF
retval[146] = new Reftext(); // REFTEXT
retval[147] = new Textref(); // TEXTREF
- retval[148] = new Indirect(); // INDIRECT
+ retval[ID.INDIRECT] = null; // Indirect.evaluate has different signature
retval[149] = new NotImplementedFunction(); // REGISTER
retval[150] = new Call(); // CALL
retval[151] = new NotImplementedFunction(); // ADDBAR
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/Ref3DEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/Ref3DEval.java?rev=629821&r1=629820&r2=629821&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/Ref3DEval.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/Ref3DEval.java Thu Feb 21 07:08:44 2008
@@ -27,7 +27,7 @@
* @author Amol S. Deshmukh
*
*/
-public class Ref3DEval implements RefEval {
+public final class Ref3DEval implements RefEval {
private ValueEval value;
@@ -55,6 +55,10 @@
public boolean isEvaluated() {
return evaluated;
+ }
+
+ public int getExternSheetIndex() {
+ return delegate.getExternSheetIndex();
}
}
Added: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java?rev=629821&view=auto
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java (added)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java Thu Feb 21 07:08:44 2008
@@ -0,0 +1,57 @@
+/* ====================================================================
+ 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;
+
+import org.apache.poi.hssf.record.formula.eval.Eval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+
+
+/**
+ * For most Excel functions, involving references ((cell, area), (2d, 3d)), the references are
+ * passed in as arguments, and the exact location remains fixed. However, a select few Excel
+ * functions have the ability to access cells that were not part of any reference passed as an
+ * argument.<br/>
+ * Two important functions with this feature are <b>INDIRECT</b> and <b>OFFSET</b><p/>
+ *
+ * In POI, the <tt>HSSFFormulaEvaluator</tt> evaluates every cell in each reference argument before
+ * calling the function. This means that functions using fixed references do not need access to
+ * the rest of the workbook to execute. Hence the <tt>evaluate()</tt> method on the common
+ * interface <tt>Function</tt> does not take a workbook parameter.<p>
+ *
+ * This interface recognises the requirement of some functions to freely create and evaluate
+ * references beyond those passed in as arguments.
+ *
+ * @author Josh Micich
+ */
+public interface FreeRefFunction {
+ /**
+ *
+ * @param args the pre-evaluated arguments for this function. args is never <code>null</code>,
+ * nor are any of its elements.
+ * @param srcCellRow zero based row index of the cell containing the currently evaluating formula
+ * @param srcCellCol zero based column index of the cell containing the currently evaluating formula
+ * @param workbook is the workbook containing the formula/cell being evaluated
+ * @param sheet is the sheet containing the formula/cell being evaluated
+ * @return never <code>null</code>. Possibly an instance of <tt>ErrorEval</tt> in the case of
+ * a specified Excel error (Exceptions are never thrown to represent Excel errors).
+ *
+ */
+ ValueEval evaluate(Eval[] args, int srcCellRow, short srcCellCol, HSSFWorkbook workbook, HSSFSheet sheet);
+}
Propchange: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java
------------------------------------------------------------------------------
svn:executable = *
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Indirect.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Indirect.java?rev=629821&r1=629820&r2=629821&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Indirect.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Indirect.java Thu Feb 21 07:08:44 2008
@@ -14,12 +14,36 @@
* 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 Indirect extends NotImplementedFunction {
+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.ValueEval;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+
+/**
+ * Implementation for Excel function INDIRECT<p/>
+ *
+ * INDIRECT() returns the cell or area reference denoted by the text argument.<p/>
+ *
+ * <b>Syntax</b>:</br>
+ * <b>INDIRECT</b>(<b>ref_text</b>,isA1Style)<p/>
+ *
+ * <b>ref_text</b> a string representation of the desired reference as it would normally be written
+ * in a cell formula.<br/>
+ * <b>isA1Style</b> (default TRUE) specifies whether the ref_text should be interpreted as A1-style
+ * or R1C1-style.
+ *
+ *
+ * @author Josh Micich
+ */
+public final class Indirect implements FreeRefFunction {
+
+ public ValueEval evaluate(Eval[] args, int srcCellRow, short srcCellCol, HSSFWorkbook workbook, HSSFSheet sheet) {
+ // TODO - implement INDIRECT()
+ return ErrorEval.FUNCTION_NOT_IMPLEMENTED;
+ }
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Offset.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Offset.java?rev=629821&r1=629820&r2=629821&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Offset.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Offset.java Thu Feb 21 07:08:44 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/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rows.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rows.java?rev=629821&r1=629820&r2=629821&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rows.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Rows.java Thu Feb 21 07:08:44 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/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=629821&r1=629820&r2=629821&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 21 07:08:44 2008
@@ -74,6 +74,7 @@
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;
@@ -91,7 +92,6 @@
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 >
@@ -379,7 +379,7 @@
Stack stack = new Stack();
for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
- // since we dont know how to handle these yet :(
+ // since we don't know how to handle these yet :(
if (ptgs[i] instanceof ControlPtg) { continue; }
if (ptgs[i] instanceof MemErrPtg) { continue; }
if (ptgs[i] instanceof MissingArgPtg) { continue; }
@@ -405,7 +405,7 @@
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) {
@@ -428,56 +428,12 @@
}
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();
-
- // 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 = 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);
+ 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()));
-
- // 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 = 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);
+ AreaEval ae = evaluateArea3dPtg(workbook, a3dp);
stack.push(ae);
}
else {
@@ -485,22 +441,94 @@
stack.push(ptgEval);
}
}
+
ValueEval value = ((ValueEval) stack.pop());
if (value instanceof RefEval) {
RefEval rv = (RefEval) value;
- value = rv.getInnerValueEval();
+ return rv.getInnerValueEval();
}
- else if (value instanceof AreaEval) {
+ 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 (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 value;
}
+
+ 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) {
+ short row0 = ap.getFirstRow();
+ short col0 = ap.getFirstColumn();
+ short row1 = ap.getLastRow();
+ short 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 = 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);
+ return ae;
+ }
+
+ public static AreaEval evaluateArea3dPtg(HSSFWorkbook workbook, Area3DPtg a3dp) {
+ 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()));
+
+ // 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 = 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);
+ return ae;
+ }
+
/**
* returns the OperationEval concrete impl instance corresponding
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=629821&r1=629820&r2=629821&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 21 07:08:44 2008
@@ -38,6 +38,7 @@
result.addTestSuite(TestMid.class);
result.addTestSuite(TestMathX.class);
result.addTestSuite(TestMatch.class);
+ result.addTestSuite(TestOffset.class);
result.addTestSuite(TestRowCol.class);
result.addTestSuite(TestSumproduct.class);
result.addTestSuite(TestStatsLib.class);
Added: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestOffset.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestOffset.java?rev=629821&view=auto
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestOffset.java (added)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestOffset.java Thu Feb 21 07:08:44 2008
@@ -0,0 +1,92 @@
+/* ====================================================================
+ 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;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.record.formula.functions.Offset.LinearOffsetRange;
+
+/**
+ * Tests for OFFSET function implementation
+ *
+ * @author Josh Micich
+ */
+public final class TestOffset extends TestCase {
+
+
+ private static void confirmDoubleConvert(double doubleVal, int expected) {
+ assertEquals(expected, Offset.convertDoubleToInt(doubleVal));
+ }
+ /**
+ * Excel's double to int conversion (for function 'OFFSET()') behaves more like Math.floor().
+ * Note - negative values are not symmetrical
+ */
+ public void testDoubleConversion() {
+
+ confirmDoubleConvert(100.09, 100);
+ confirmDoubleConvert(100.01, 100);
+ confirmDoubleConvert(100.00, 100);
+ confirmDoubleConvert(99.99, 99);
+
+ confirmDoubleConvert(+2.01, +2);
+ confirmDoubleConvert(+2.00, +2);
+ confirmDoubleConvert(+1.99, +1);
+ confirmDoubleConvert(+1.01, +1);
+ confirmDoubleConvert(+1.00, +1);
+ confirmDoubleConvert(+0.99, 0);
+ confirmDoubleConvert(+0.01, 0);
+ confirmDoubleConvert( 0.00, 0);
+ confirmDoubleConvert(-0.01, -1);
+ confirmDoubleConvert(-0.99, -1);
+ confirmDoubleConvert(-1.00, -1);
+ confirmDoubleConvert(-1.01, -2);
+ confirmDoubleConvert(-1.99, -2);
+ confirmDoubleConvert(-2.00, -2);
+ confirmDoubleConvert(-2.01, -3);
+ }
+
+ public void testLinearOffsetRange() {
+ LinearOffsetRange lor;
+
+ lor = new LinearOffsetRange(3, 2);
+ assertEquals(3, lor.getFirstIndex());
+ assertEquals(4, lor.getLastIndex());
+ lor = lor.normaliseAndTranslate(0); // expected no change
+ assertEquals(3, lor.getFirstIndex());
+ assertEquals(4, lor.getLastIndex());
+
+ lor = lor.normaliseAndTranslate(5);
+ assertEquals(8, lor.getFirstIndex());
+ assertEquals(9, lor.getLastIndex());
+
+ // negative length
+
+ lor = new LinearOffsetRange(6, -4).normaliseAndTranslate(0);
+ assertEquals(3, lor.getFirstIndex());
+ assertEquals(6, lor.getLastIndex());
+
+
+ // bounds checking
+ lor = new LinearOffsetRange(0, 100);
+ assertFalse(lor.isOutOfBounds(0, 16383));
+ lor = lor.normaliseAndTranslate(16300);
+ assertTrue(lor.isOutOfBounds(0, 16383));
+ assertFalse(lor.isOutOfBounds(0, 65535));
+ }
+
+}
Propchange: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestOffset.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestOffset.java
------------------------------------------------------------------------------
svn:executable = *
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org