/* * 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. */ namespace HH.WMS.Utils.NPOI.SS.Formula.Functions { using System; using HH.WMS.Utils.NPOI.SS.Formula.Eval; using System.Text; /** * 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.
* * @author Josh Micich */ public class Offset : Function { // These values are specific to BIFF8 private static int LAST_VALID_ROW_INDEX = 0xFFFF; private static int LAST_VALID_COLUMN_INDEX = 0xFF; /** * Exceptions are used within this class to help simplify flow control when error conditions * are enCountered */ [Serializable] private class EvalEx : Exception { private 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 */ public class LinearOffsetRange { private int _offset; private int _Length; public LinearOffsetRange(int offset, int length) { if (length == 0) { // handled that condition much earlier throw new ArgumentException("Length may not be zero"); } _offset = offset; _Length = length; } public short FirstIndex { get { return (short)_offset; } } public short LastIndex { get { return (short)(_offset + _Length - 1); } } /** * 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.

* * @param translationAmount may be zero negative or positive * * @return the equivalent LinearOffsetRange 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 bool IsOutOfBounds(int lowValidIx, int highValidIx) { if (_offset < lowValidIx) { return true; } if (LastIndex > highValidIx) { return true; } return false; } public override String ToString() { StringBuilder sb = new StringBuilder(64); sb.Append(GetType().Name).Append(" ["); sb.Append(_offset).Append("...").Append(LastIndex); sb.Append("]"); return sb.ToString(); } } /** * Encapsulates either an area or cell reference which may be 2d or 3d. */ private class BaseRef { private const int INVALID_SHEET_INDEX = -1; private int _firstRowIndex; private int _firstColumnIndex; private int _width; private int _height; private RefEval _refEval; private AreaEval _areaEval; public BaseRef(RefEval re) { _refEval = re; _areaEval = null; _firstRowIndex = re.Row; _firstColumnIndex = re.Column; _height = 1; _width = 1; } public BaseRef(AreaEval ae) { _refEval = null; _areaEval = ae; _firstRowIndex = ae.FirstRow; _firstColumnIndex = ae.FirstColumn; _height = ae.LastRow - ae.FirstRow + 1; _width = ae.LastColumn - ae.FirstColumn + 1; } public int Width { get { return _width; } } public int Height { get { return _height; } } public int FirstRowIndex { get { return _firstRowIndex; } } public int FirstColumnIndex { get { return _firstColumnIndex; } } public AreaEval Offset(int relFirstRowIx, int relLastRowIx,int relFirstColIx, int relLastColIx) { if (_refEval == null) { return _areaEval.Offset(relFirstRowIx, relLastRowIx, relFirstColIx, relLastColIx); } return _refEval.Offset(relFirstRowIx, relLastRowIx, relFirstColIx, relLastColIx); } } public ValueEval Evaluate(ValueEval[] args, int srcCellRow, int srcCellCol) { 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.Height; int width = baseRef.Width; switch (args.Length) { case 5: width = EvaluateIntArg(args[4], srcCellRow, srcCellCol); break; case 4: height = EvaluateIntArg(args[3], srcCellRow, srcCellCol); break; } // 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); } catch (EvaluationException e) { return e.GetErrorEval(); } } private static AreaEval CreateOffset(BaseRef baseRef, LinearOffsetRange orRow, LinearOffsetRange orCol) { LinearOffsetRange absRows = orRow.NormaliseAndTranslate(baseRef.FirstRowIndex); LinearOffsetRange absCols = orCol.NormaliseAndTranslate(baseRef.FirstColumnIndex); if (absRows.IsOutOfBounds(0, LAST_VALID_ROW_INDEX)) { throw new EvaluationException(ErrorEval.REF_INVALID); } if (absCols.IsOutOfBounds(0, LAST_VALID_COLUMN_INDEX)) { throw new EvaluationException(ErrorEval.REF_INVALID); } return baseRef.Offset(orRow.FirstIndex, orRow.LastIndex, orCol.FirstIndex, orCol.LastIndex); } private static BaseRef EvaluateBaseRef(ValueEval eval) { if (eval is RefEval) { return new BaseRef((RefEval)eval); } if (eval is AreaEval) { return new BaseRef((AreaEval)eval); } if (eval is ErrorEval) { throw new EvalEx((ErrorEval)eval); } throw new EvalEx(ErrorEval.VALUE_INVALID); } /** * OFFSet's numeric arguments (2..5) have similar Processing rules */ public static int EvaluateIntArg(ValueEval eval, int srcCellRow, int srcCellCol) { double d = EvaluateDoubleArg(eval, srcCellRow, srcCellCol); return ConvertDoubleToInt(d); } /** * Fractional values are silently truncated by Excel. * Truncation Is toward negative infinity. */ /* package */ public 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(ValueEval eval, int srcCellRow, int srcCellCol) { ValueEval ve = OperandResolver.GetSingleValue(eval, srcCellRow, srcCellCol); if (ve is NumericValueEval) { return ((NumericValueEval)ve).NumberValue; } if (ve is StringEval) { StringEval se = (StringEval)ve; double d = OperandResolver.ParseDouble(se.StringValue); if (double.IsNaN(d)) { throw new EvalEx(ErrorEval.VALUE_INVALID); } return d; } if (ve is BoolEval) { // in the context of OFFSet, bools resolve to 0 and 1. if (((BoolEval)ve).BooleanValue) { return 1; } return 0; } throw new Exception("Unexpected eval type (" + ve.GetType().Name + ")"); } } }