Top Description Inners Constructors Methods
org.apache.poi.ss.formula.functions

public final Class Offset

extends Object
implements Function
Class Inheritance
All Implemented Interfaces
org.apache.poi.ss.formula.functions.Function
Imports
org.apache.poi.ss.SpreadsheetVersion, org.apache.poi.ss.formula.eval.AreaEval, .ErrorEval, .EvaluationException, .MissingArgEval, .OperandResolver, .RefEval, .ValueEval
Implementation for Excel function OFFSET()

OFFSET returns an area reference that is a specified number of rows and columns from a reference cell or area.

Syntax:
OFFSET(reference, rows, cols, height, width)

reference is the base reference.
rows is the number of rows up or down from the base reference.
cols is the number of columns left or right from the base reference.
height (default same height as base reference) is the row count for the returned area reference.
width (default same width as base reference) is the column count for the returned area reference.

Nested and Inner Type Summary

Modifier and TypeClass and Description
private static class
Offset.BaseRef

Encapsulates either an area or cell reference which may be 2d or 3d.

pack-priv static class
Offset.LinearOffsetRange

A one dimensional base + offset.

Constructor Summary

AccessConstructor and Description
public
Offset()

Method Summary

Modifier and TypeMethod and Description
private static AreaEval
createOffset(Offset.BaseRef baseRef, Offset.LinearOffsetRange orRow, Offset.LinearOffsetRange orCol)

public ValueEval
evaluate(ValueEval[]
the evaluated function arguments. Empty values are represented with BlankEval or MissingArgEval, never null.
args, int
row index of the cell containing the formula under evaluation
srcCellRow, int
column index of the cell containing the formula under evaluation
srcCellCol)

Implements org.apache.poi.ss.formula.functions.Function.evaluate.

private static Offset.BaseRef
evaluateBaseRef(ValueEval eval)

pack-priv static int
evaluateIntArg(ValueEval eval, int srcCellRow, int srcCellCol)

OFFSET's numeric arguments (2..5) have similar processing rules

Inherited from java.lang.Object:
cloneequalsfinalizegetClasshashCodenotifynotifyAlltoStringwaitwaitwait

Constructor Detail

Offsetback to summary
public Offset()

Method Detail

createOffsetback to summary
private static AreaEval createOffset(Offset.BaseRef baseRef, Offset.LinearOffsetRange orRow, Offset.LinearOffsetRange orCol) throws EvaluationException
evaluateback to summary
public ValueEval evaluate(ValueEval[] args, int srcCellRow, int srcCellCol)

Implements org.apache.poi.ss.formula.functions.Function.evaluate.

Parameters
args:ValueEval[]

Doc from org.apache.poi.ss.formula.functions.Function.evaluate.

the evaluated function arguments. Empty values are represented with BlankEval or MissingArgEval, never null.

srcCellRow:int

Doc from org.apache.poi.ss.formula.functions.Function.evaluate.

row index of the cell containing the formula under evaluation

srcCellCol:int

Doc from org.apache.poi.ss.formula.functions.Function.evaluate.

column index of the cell containing the formula under evaluation

Returns:ValueEval

Doc from org.apache.poi.ss.formula.functions.Function.evaluate.

The evaluated result, possibly an ErrorEval, never null

Note

- Excel uses the error code #NUM! instead of IEEE NaN, so when numeric functions evaluate to Double#NaN be sure to translate the result to ErrorEval#NUM_ERROR.

Annotations
@Override
@SuppressWarnings:fallthrough
evaluateBaseRefback to summary
private static Offset.BaseRef evaluateBaseRef(ValueEval eval) throws EvaluationException
evaluateIntArgback to summary
pack-priv static int evaluateIntArg(ValueEval eval, int srcCellRow, int srcCellCol) throws EvaluationException

OFFSET's numeric arguments (2..5) have similar processing rules

org.apache.poi.ss.formula.functions back to summary

private final Class Offset.BaseRef

extends Object
Class Inheritance
Encapsulates either an area or cell reference which may be 2d or 3d.

Field Summary

Modifier and TypeField and Description
private final AreaEval
_areaEval

private final int
_firstColumnIndex

private final int
_firstRowIndex

private final int
_height

private final RefEval
_refEval

private final int
_width

Constructor Summary

AccessConstructor and Description
public
BaseRef(RefEval re)

public
BaseRef(AreaEval ae)

Method Summary

Modifier and TypeMethod and Description
public int
getFirstColumnIndex()

public int
getFirstRowIndex()

public int
getHeight()

public int
getWidth()

public AreaEval
offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx)

Inherited from java.lang.Object:
cloneequalsfinalizegetClasshashCodenotifynotifyAlltoStringwaitwaitwait

Field Detail

_areaEvalback to summary
private final AreaEval _areaEval
_firstColumnIndexback to summary
private final int _firstColumnIndex
_firstRowIndexback to summary
private final int _firstRowIndex
_heightback to summary
private final int _height
_refEvalback to summary
private final RefEval _refEval
_widthback to summary
private final int _width

Constructor Detail

BaseRefback to summary
public BaseRef(RefEval re)
BaseRefback to summary
public BaseRef(AreaEval ae)

Method Detail

getFirstColumnIndexback to summary
public int getFirstColumnIndex()
getFirstRowIndexback to summary
public int getFirstRowIndex()
getHeightback to summary
public int getHeight()
getWidthback to summary
public int getWidth()
offsetback to summary
public AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx)
org.apache.poi.ss.formula.functions back to summary

pack-priv final Class Offset.LinearOffsetRange

extends Object
Class Inheritance
A one dimensional base + offset. Represents either a row range or a column range. Two instances of this class together specify an area range.

Field Summary

Modifier and TypeField and Description
private final int
_length

private final int
_offset

Constructor Summary

AccessConstructor and Description
public
LinearOffsetRange(int offset, int length)

Method Summary

Modifier and TypeMethod and Description
public short
getFirstIndex()

public short
getLastIndex()

public boolean
isOutOfBounds(int lowValidIx, int highValidIx)

public Offset.LinearOffsetRange

Returns:

the equivalent LinearOffsetRange with a positive length, moved by the specified translationAmount.
normaliseAndTranslate(int
may be zero negative or positive
translationAmount)

Moves the range by the specified translation amount.

public String
toString()

Overrides java.lang.Object.toString.

Returns a string representation of the object.

Inherited from java.lang.Object:
cloneequalsfinalizegetClasshashCodenotifynotifyAllwaitwaitwait

Field Detail

_lengthback to summary
private final int _length
_offsetback to summary
private final int _offset

Constructor Detail

LinearOffsetRangeback to summary
public LinearOffsetRange(int offset, int length)

Method Detail

getFirstIndexback to summary
public short getFirstIndex()
getLastIndexback to summary
public short getLastIndex()
isOutOfBoundsback to summary
public boolean isOutOfBounds(int lowValidIx, int highValidIx)
normaliseAndTranslateback to summary
public Offset.LinearOffsetRange normaliseAndTranslate(int translationAmount)

Moves the range by the specified translation amount.

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.

Parameters
translationAmount:int

may be zero negative or positive

Returns:Offset.LinearOffsetRange

the equivalent LinearOffsetRange with a positive length, moved by the specified translationAmount.

toStringback to summary
public String toString()

Overrides java.lang.Object.toString.

Doc from java.lang.Object.toString.

Returns a string representation of the object. Satisfying this method's contract implies a non-null result must be returned.

Returns:String

a string representation of the object