Пример #1
0
 protected RefPtgBase(CellReference c)
 {
     Row = (c.Row);
     Column = (c.Col);
     IsColRelative = (!c.IsColAbsolute);
     IsRowRelative = (!c.IsRowAbsolute);
 }
Пример #2
0
 /**
  * Takes in a String representation of a cell reference and Fills out the
  * numeric fields.
  */
 protected RefPtgBase(String cellref)
 {
     CellReference c = new CellReference(cellref);
     Row = c.Row;
     Column = c.Col;
     IsColRelative = !c.IsColAbsolute;
     IsRowRelative = !c.IsRowAbsolute;
 }
Пример #3
0
 public Ref3DPtg(String cellref, int externIdx)
 {
     CellReference c = new CellReference(cellref);
     Row=c.Row;
     Column=c.Col;
     IsColRelative=!c.IsColAbsolute;
     IsRowRelative=!c.IsRowAbsolute;
     ExternSheetIndex=externIdx;
 }
Пример #4
0
        /**
         * Create an area ref from a string representation.  Sheet names containing special Chars should be
         * delimited and escaped as per normal syntax rules for formulas.<br/> 
         * The area reference must be contiguous (i.e. represent a single rectangle, not a Union of rectangles)
         */
        public AreaReference(String reference)
        {
            if (!IsContiguous(reference))
            {
                throw new ArgumentException(
                        "References passed to the AreaReference must be contiguous, " +
                        "use generateContiguous(ref) if you have non-contiguous references");
            }

            String[] parts = SeparateAreaRefs(reference);

            String part0 = parts[0];
            if (parts.Length == 1)
            {
                // TODO - probably shouldn't initialize area ref when text is really a cell ref
                // Need to fix some named range stuff to get rid of this
                _firstCell = new CellReference(part0);

                _lastCell = _firstCell;
                _isSingleCell = true;
                return;
            }
            if (parts.Length != 2)
            {
                throw new ArgumentException("Bad area ref '" + reference + "'");
            }
            String part1 = parts[1];
            if (IsPlainColumn(part0))
            {
                if (!IsPlainColumn(part1))
                {
                    throw new Exception("Bad area ref '" + reference + "'");
                }
                // Special handling for whole-column references
                // Represented internally as x$1 to x$65536
                //  which is the maximum range of rows

                bool firstIsAbs = CellReference.IsPartAbsolute(part0);
                bool lastIsAbs = CellReference.IsPartAbsolute(part1);

                int col0 = CellReference.ConvertColStringToIndex(part0);
                int col1 = CellReference.ConvertColStringToIndex(part1);

                _firstCell = new CellReference(0, col0, true, firstIsAbs);
                _lastCell = new CellReference(0xFFFF, col1, true, lastIsAbs);
                _isSingleCell = false;
                // TODO - whole row refs
            }
            else
            {
                _firstCell = new CellReference(part0);
                _lastCell = new CellReference(part1);
                _isSingleCell = part0.Equals(part1);
            }
        }
Пример #5
0
 public override String ToString()
 {
     CellReference cr = new CellReference(Row, Column);
     StringBuilder sb = new StringBuilder();
     sb.Append(GetType().Name).Append("[");
     sb.Append(_evaluator.SheetName);
     sb.Append('!');
     sb.Append(cr.FormatAsString());
     sb.Append("]");
     return sb.ToString();
 }
Пример #6
0
 public override String ToString()
 {
     CellReference cr = new CellReference(Row, Column, !IsRowRelative, !IsColRelative);
     StringBuilder sb = new StringBuilder();
     sb.Append(GetType().Name);
     sb.Append(" [");
     sb.Append("sheetIx=").Append(ExternSheetIndex);
     sb.Append(" ! ");
     sb.Append(cr.FormatAsString());
     sb.Append("]");
     return sb.ToString();
 }
Пример #7
0
        /**
     * @return the text format of this range using specified sheet name.
     */
        public String FormatAsString(String sheetName, bool useAbsoluteAddress)
        {
            StringBuilder sb = new StringBuilder();
            if (sheetName != null)
            {
                sb.Append(SheetNameFormatter.Format(sheetName));
                sb.Append("!");
            }
            CellReference cellRefFrom = new CellReference(FirstRow, FirstColumn,
                    useAbsoluteAddress, useAbsoluteAddress);
            CellReference cellRefTo = new CellReference(LastRow, LastColumn,
                    useAbsoluteAddress, useAbsoluteAddress);
            sb.Append(cellRefFrom.FormatAsString());

            //for a single-cell reference return A1 instead of A1:A1
            if (!cellRefFrom.Equals(cellRefTo))
            {
                sb.Append(':');
                sb.Append(cellRefTo.FormatAsString());
            }
            return sb.ToString();
        }
Пример #8
0
 public RefPtg(CellReference cr):base(cr)
 {
     
 }
 public override String ToString()
 {
     StringBuilder sb = new StringBuilder(64);
     CellReference crA = new CellReference(_firstRowIndex, _firstColumnIndex, false, false);
     CellReference crB = new CellReference(_lastRowIndex, _lastColumnIndex, false, false);
     sb.Append(GetType().Name);
     sb.Append(" [").Append(crA.FormatAsString()).Append(':').Append(crB.FormatAsString()).Append("]");
     return sb.ToString();
 }
        /**
         * Resolves a cell or area reference dynamically.
         * @param workbookName the name of the workbook Containing the reference.  If <code>null</code>
         * the current workbook is assumed.  Note - to Evaluate formulas which use multiple workbooks,
         * a {@link CollaboratingWorkbooksEnvironment} must be set up.
         * @param sheetName the name of the sheet Containing the reference.  May be <code>null</code>
         * (when <c>workbookName</c> is also null) in which case the current workbook and sheet is
         * assumed.
         * @param refStrPart1 the single cell reference or first part of the area reference.  Must not
         * be <code>null</code>.
         * @param refStrPart2 the second part of the area reference. For single cell references this
         * parameter must be <code>null</code>
         * @param isA1Style specifies the format for <c>refStrPart1</c> and <c>refStrPart2</c>.
         * Pass <c>true</c> for 'A1' style and <c>false</c> for 'R1C1' style.
         * TODO - currently POI only supports 'A1' reference style
         * @return a {@link RefEval} or {@link AreaEval}
         */
        public ValueEval GetDynamicReference(String workbookName, String sheetName, String refStrPart1,
                String refStrPart2, bool isA1Style)
        {
            if (!isA1Style)
            {
                throw new Exception("R1C1 style not supported yet");
            }
            SheetRefEvaluator sre = CreateExternSheetRefEvaluator(workbookName, sheetName);
            if (sre == null)
            {
                return ErrorEval.REF_INVALID;
            }
            // ugly typecast - TODO - make spReadsheet version more easily accessible
            SpreadsheetVersion ssVersion = ((IFormulaParsingWorkbook)_workbook).GetSpreadsheetVersion();

            NameType part1refType = ClassifyCellReference(refStrPart1, ssVersion);
            switch (part1refType)
            {
                case NameType.BAD_CELL_OR_NAMED_RANGE:
                    return ErrorEval.REF_INVALID;
                case NameType.NAMED_RANGE:
                    IEvaluationName nm = ((IFormulaParsingWorkbook)_workbook).GetName(refStrPart1, _sheetIndex);
                    if (!nm.IsRange)
                    {
                        throw new Exception("Specified name '" + refStrPart1 + "' is not a range as expected.");
                    }
                    return _bookEvaluator.EvaluateNameFormula(nm.NameDefinition, this);
            }
            if (refStrPart2 == null)
            {
                // no ':'
                switch (part1refType)
                {
                    case NameType.COLUMN:
                    case NameType.ROW:
                        return ErrorEval.REF_INVALID;
                    case NameType.CELL:
                        CellReference cr = new CellReference(refStrPart1);
                        return new LazyRefEval(cr.Row, cr.Col, sre);
                }
                throw new InvalidOperationException("Unexpected reference classification of '" + refStrPart1 + "'.");
            }
            NameType part2refType = ClassifyCellReference(refStrPart1, ssVersion);
            switch (part2refType)
            {
                case NameType.BAD_CELL_OR_NAMED_RANGE:
                    return ErrorEval.REF_INVALID;
                case NameType.NAMED_RANGE:
                    throw new Exception("Cannot Evaluate '" + refStrPart1
                            + "'. Indirect Evaluation of defined names not supported yet");
            }

            if (part2refType != part1refType)
            {
                // LHS and RHS of ':' must be compatible
                return ErrorEval.REF_INVALID;
            }
            int firstRow, firstCol, lastRow, lastCol;
            switch (part1refType)
            {
                case NameType.COLUMN:
                    firstRow = 0;
                    lastRow = ssVersion.LastRowIndex;
                    firstCol = ParseColRef(refStrPart1);
                    lastCol = ParseColRef(refStrPart2);
                    break;
                case NameType.ROW:
                    firstCol = 0;
                    lastCol = ssVersion.LastColumnIndex;
                    firstRow = ParseRowRef(refStrPart1);
                    lastRow = ParseRowRef(refStrPart2);
                    break;
                case NameType.CELL:
                    CellReference cr;
                    cr = new CellReference(refStrPart1);
                    firstRow = cr.Row;
                    firstCol = cr.Col;
                    cr = new CellReference(refStrPart2);
                    lastRow = cr.Row;
                    lastCol = cr.Col;
                    break;
                default:
                    throw new InvalidOperationException("Unexpected reference classification of '" + refStrPart1 + "'.");
            }
            return new LazyAreaEval(firstRow, firstCol, lastRow, lastCol, sre);
        }
Пример #11
0
 public Ref3DPtg(CellReference cr, int externIdx):base(cr)
 {
     ExternSheetIndex = externIdx;
 }
Пример #12
0
 /// <summary>
 /// Called when this cell is modified.
 /// The purpose of this method is to validate the cell state prior to modification.
 /// </summary>
 internal void NotifyArrayFormulaChanging()
 {
     CellReference ref1 = new CellReference(this);
     String msg = "Cell " + ref1.FormatAsString() + " is part of a multi-cell array formula. " +
             "You cannot change part of an array.";
     NotifyArrayFormulaChanging(msg);
 }
Пример #13
0
        /**
         * Creates an area ref from a pair of Cell References.
         */
        public AreaReference(CellReference topLeft, CellReference botRight)
        {
            //_firstCell = topLeft;
            //_lastCell = botRight;
            //_isSingleCell = false;

            bool swapRows = topLeft.Row > botRight.Row;
            bool swapCols = topLeft.Col > botRight.Col;
            if (swapRows || swapCols)
            {
                int firstRow;
                int lastRow;
                int firstColumn;
                int lastColumn;
                bool firstRowAbs;
                bool lastRowAbs;
                bool firstColAbs;
                bool lastColAbs;
                if (swapRows)
                {
                    firstRow = botRight.Row;
                    firstRowAbs = botRight.IsRowAbsolute;
                    lastRow = topLeft.Row;
                    lastRowAbs = topLeft.IsRowAbsolute;
                }
                else
                {
                    firstRow = topLeft.Row;
                    firstRowAbs = topLeft.IsRowAbsolute;
                    lastRow = botRight.Row;
                    lastRowAbs = botRight.IsRowAbsolute;
                }
                if (swapCols)
                {
                    firstColumn = botRight.Col;
                    firstColAbs = botRight.IsColAbsolute;
                    lastColumn = topLeft.Col;
                    lastColAbs = topLeft.IsColAbsolute;
                }
                else
                {
                    firstColumn = topLeft.Col;
                    firstColAbs = topLeft.IsColAbsolute;
                    lastColumn = botRight.Col;
                    lastColAbs = botRight.IsColAbsolute;
                }
                _firstCell = new CellReference(firstRow, firstColumn, firstRowAbs, firstColAbs);
                _lastCell = new CellReference(lastRow, lastColumn, lastRowAbs, lastColAbs);
            }
            else
            {
                _firstCell = topLeft;
                _lastCell = botRight;
            }
            _isSingleCell = false;
        }
Пример #14
0
        /**
         * Also collects any loose MergeCellRecords and puts them in the supplied
         * mergedCellsTable
         */
        public RowBlocksReader(RecordStream rs)
        {
            ArrayList plainRecords = new ArrayList();
            ArrayList shFrmRecords = new ArrayList();
            ArrayList arrayRecords = new ArrayList();
            ArrayList tableRecords = new ArrayList();
            ArrayList mergeCellRecords = new ArrayList();
            List<CellReference> firstCellRefs = new List<CellReference>();
            Record prevRec = null;

            while (!RecordOrderer.IsEndOfRowBlock(rs.PeekNextSid()))
            {
                // End of row/cell records for the current sheet
                // Note - It is important that this code does not inadvertently add any sheet 
                // records from a subsequent sheet.  For example, if SharedFormulaRecords 
                // are taken from the wrong sheet, this could cause bug 44449.
                if (!rs.HasNext())
                {
                    throw new InvalidOperationException("Failed to find end of row/cell records");

                }
                Record rec = rs.GetNext();
                ArrayList dest;
                switch (rec.Sid)
                {
                    case MergeCellsRecord.sid:
                        dest = mergeCellRecords;
                        break;
                    case SharedFormulaRecord.sid:
                        dest = shFrmRecords;
                        if (!(prevRec is FormulaRecord))
                        {
                            throw new Exception("Shared formula record should follow a FormulaRecord");
                        }
                        FormulaRecord fr = (FormulaRecord)prevRec;
                        firstCellRefs.Add(new CellReference(fr.Row, fr.Column));

                        break;
                    case ArrayRecord.sid:
                        dest = arrayRecords;
                        break;
                    case TableRecord.sid:
                        dest = tableRecords;
                        break;
                    default: dest = plainRecords;
                        break;
                }
                dest.Add(rec);
                prevRec = rec;
            }
            SharedFormulaRecord[] sharedFormulaRecs = new SharedFormulaRecord[shFrmRecords.Count];
            List<ArrayRecord> arrayRecs = new List<ArrayRecord>(arrayRecords.Count);
            List<TableRecord> tableRecs = new List<TableRecord>(tableRecords.Count);
            sharedFormulaRecs = (SharedFormulaRecord[])shFrmRecords.ToArray(typeof(SharedFormulaRecord));

            CellReference[] firstCells = new CellReference[firstCellRefs.Count];
            firstCells=firstCellRefs.ToArray();
            arrayRecs = new List<ArrayRecord>((ArrayRecord[])arrayRecords.ToArray(typeof(ArrayRecord)));
            tableRecs = new List<TableRecord>((TableRecord[])tableRecords.ToArray(typeof(TableRecord)));

            _plainRecords = plainRecords;
            _sfm = SharedValueManager.Create(sharedFormulaRecs,firstCells, arrayRecs, tableRecs);
            _mergedCellsRecords = new MergeCellsRecord[mergeCellRecords.Count];
            _mergedCellsRecords = (MergeCellsRecord[])mergeCellRecords.ToArray(typeof(MergeCellsRecord));
        }
Пример #15
0
        /// <summary>
        /// Sets the print area.
        /// </summary>
        /// <param name="sheetIndex">Zero-based sheet index (0 = First Sheet)</param>
        /// <param name="startColumn">Column to begin printarea</param>
        /// <param name="endColumn">Column to end the printarea</param>
        /// <param name="startRow">Row to begin the printarea</param>
        /// <param name="endRow">Row to end the printarea</param>
        public void SetPrintArea(int sheetIndex, int startColumn, int endColumn,
                                  int startRow, int endRow)
        {

            //using absolute references because they don't Get copied and pasted anyway
            CellReference cell = new CellReference(startRow, startColumn, true, true);
            String reference = cell.FormatAsString();

            cell = new CellReference(endRow, endColumn, true, true);
            reference = reference + ":" + cell.FormatAsString();

            SetPrintArea(sheetIndex, reference);
        }
Пример #16
0
        protected String FormatReferenceAsString()
        {
            CellReference topLeft = new CellReference(FirstRow, FirstColumn, !IsFirstRowRelative, !IsFirstColRelative);
            CellReference botRight = new CellReference(LastRow, LastColumn, !IsLastRowRelative, !IsLastColRelative);

            if (AreaReference.IsWholeColumnReference(topLeft, botRight))
            {
                return (new AreaReference(topLeft, botRight)).FormatAsString();
            }
            return topLeft.FormatAsString() + ":" + botRight.FormatAsString();
        }
Пример #17
0
 /**
  * is the reference for a whole-column reference,
  *  such as C:C or D:G ?
  */
 public static bool IsWholeColumnReference(CellReference topLeft, CellReference botRight)
 {
     // These are represented as something like
     //   C$1:C$65535 or D$1:F$0
     // i.e. absolute from 1st row to 0th one
     if (topLeft.Row == 0 && topLeft.IsRowAbsolute &&
         (botRight.Row == -1 || botRight.Row == 65535) && botRight.IsRowAbsolute)
     {
         return true;
     }
     return false;
 }
Пример #18
0
        /**
         * Returns a reference to every cell covered by this area
         */
        public CellReference[] GetAllReferencedCells()
        {
            // Special case for single cell reference
            if (_isSingleCell)
            {
                return new CellReference[] { _firstCell, };
            }

            // Interpolate between the two
            int minRow = Math.Min(_firstCell.Row, _lastCell.Row);
            int maxRow = Math.Max(_firstCell.Row, _lastCell.Row);
            int minCol = Math.Min(_firstCell.Col, _lastCell.Col);
            int maxCol = Math.Max(_firstCell.Col, _lastCell.Col);
            String sheetName = _firstCell.SheetName;

            ArrayList refs = new ArrayList();
            for (int row = minRow; row <= maxRow; row++)
            {
                for (int col = minCol; col <= maxCol; col++)
                {
                    CellReference ref1 = new CellReference(sheetName, row, col, _firstCell.IsRowAbsolute, _firstCell.IsColAbsolute);
                    refs.Add(ref1);
                }
            }
            return (CellReference[])refs.ToArray(typeof(CellReference));
        }
Пример #19
0
 public override String ToString()
 {
     CellReference crA = new CellReference(FirstRow, FirstColumn);
     CellReference crB = new CellReference(LastRow, LastColumn);
     StringBuilder sb = new StringBuilder();
     sb.Append(GetType().Name).Append("[");
     sb.Append(_evaluator.SheetName);
     sb.Append('!');
     sb.Append(crA.FormatAsString());
     sb.Append(':');
     sb.Append(crB.FormatAsString());
     sb.Append("]");
     return sb.ToString();
 }
Пример #20
0
        /**
 * @param ref usually a standard area ref (e.g. "B1:D8").  May be a single cell
 *            ref (e.g. "B5") in which case the result is a 1 x 1 cell range.
 */
    public static CellRangeAddress ValueOf(String reference)
    {
        int sep = reference.IndexOf(":", StringComparison.Ordinal);
        CellReference a;
        CellReference b;
        if (sep == -1)
        {
            a = new CellReference(reference);
            b = a;
        }
        else
        {
            a = new CellReference(reference.Substring(0, sep));
            b = new CellReference(reference.Substring(sep + 1));
        }
        return new CellRangeAddress(a.Row, b.Row, a.Col, b.Col);
    }
Пример #21
0
        /// <summary>
        /// Remove a Array Formula from this sheet.  All cells contained in the Array Formula range are removed as well
        /// </summary>
        /// <param name="cell">any cell within Array Formula range</param>
        /// <returns>the <see cref="ICellRange{ICell}"/> of cells affected by this change</returns>
        public ICellRange<ICell> RemoveArrayFormula(ICell cell)
        {
            if (cell.Sheet != this)
            {
                throw new ArgumentException("Specified cell does not belong to this sheet.");
            }
            CellValueRecordInterface rec = ((HSSFCell)cell).CellValueRecord;
            if (!(rec is FormulaRecordAggregate))
            {
                String ref1 = new CellReference(cell).FormatAsString();
                throw new ArgumentException("Cell " + ref1 + " is not part of an array formula.");
            }
            FormulaRecordAggregate fra = (FormulaRecordAggregate)rec;
            CellRangeAddress range = fra.RemoveArrayFormula(cell.RowIndex, cell.ColumnIndex);

            ICellRange<ICell> result = GetCellRange(range);
            // clear all cells in the range
            foreach (ICell c in result)
            {
                c.SetCellType(CellType.BLANK);
            }
            return result;
        }
Пример #22
0
        public ValueEval Evaluate(ValueEval[] args, int srcRowIndex,
                                  int srcColumnIndex)
        {
            if (args.Length < 2 || args.Length > 5)
            {
                return ErrorEval.VALUE_INVALID;
            }
            try
            {
                bool pAbsRow, pAbsCol;

                int row = (int)NumericFunction.SingleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
                int col = (int)NumericFunction.SingleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);

                int refType;
                if (args.Length > 2)
                {
                    refType = (int)NumericFunction.SingleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
                }
                else
                {
                    refType = REF_ABSOLUTE;
                }
                switch (refType)
                {
                    case REF_ABSOLUTE:
                        pAbsRow = true;
                        pAbsCol = true;
                        break;
                    case REF_ROW_ABSOLUTE_COLUMN_RELATIVE:
                        pAbsRow = true;
                        pAbsCol = false;
                        break;
                    case REF_ROW_RELATIVE_RELATIVE_ABSOLUTE:
                        pAbsRow = false;
                        pAbsCol = true;
                        break;
                    case REF_RELATIVE:
                        pAbsRow = false;
                        pAbsCol = false;
                        break;
                    default:
                        throw new EvaluationException(ErrorEval.VALUE_INVALID);
                }

                bool a1;
                if (args.Length > 3)
                {
                    ValueEval ve = OperandResolver.GetSingleValue(args[3], srcRowIndex, srcColumnIndex);
                    // TODO R1C1 style is not yet supported
                    a1 = ve == MissingArgEval.instance ? true : OperandResolver.CoerceValueToBoolean(ve, false).Value;
                }
                else
                {
                    a1 = true;
                }

                String sheetName;
                if (args.Length == 5)
                {
                    ValueEval ve = OperandResolver.GetSingleValue(args[4], srcRowIndex, srcColumnIndex);
                    sheetName = ve == MissingArgEval.instance ? null : OperandResolver.CoerceValueToString(ve);
                }
                else
                {
                    sheetName = null;
                }

                CellReference ref1 = new CellReference(row - 1, col - 1, pAbsRow, pAbsCol);
                StringBuilder sb = new StringBuilder(32);
                if (sheetName != null)
                {
                    SheetNameFormatter.AppendFormat(sb, sheetName);
                    sb.Append('!');
                }
                sb.Append(ref1.FormatAsString());

                return new StringEval(sb.ToString());

            }
            catch (EvaluationException e)
            {
                return e.GetErrorEval();
            }
        }
Пример #23
0
 public CellRangeAddress GetArrayFormulaRange()
 {
     if (cellType != CellType.FORMULA)
     {
         String ref1 = new CellReference(this).FormatAsString();
         throw new InvalidOperationException("Cell " + ref1
             + " is not part of an array formula.");
     }
     return ((FormulaRecordAggregate)record).GetArrayFormulaRange();
 }
Пример #24
0
 public String FormatReferenceAsString()
 {
     // Only make cell references as needed. Memory is an issue
     CellReference cr = new CellReference(Row, Column, !IsRowRelative, !IsColRelative);
     return cr.FormatAsString();
 }
 public override String ToString()
 {
     CellReference crA = new CellReference(_firstRow, _firstCol);
     CellReference crB = new CellReference(_lastRow, _lastCol);
     return GetType().Name + " [" + crA.FormatAsString() + ":" + crB.FormatAsString() + "]";
 }
Пример #26
0
 protected Ref2DPtgBase(CellReference cr):base(cr)
 {
     
 }