Example #1
0
        public CellDataFormatFlag GetCellDataFormat(string addressOrName, out object dataFormatArgs)
        {
            RangePosition namedRange;

            if (CellPosition.IsValidAddress(addressOrName))
            {
                return(this.GetCellDataFormat(new CellPosition(addressOrName), out dataFormatArgs));
            }
            else if (this.TryGetNamedRangePosition(addressOrName, out namedRange))
            {
                return(this.GetCellDataFormat(namedRange.StartPos, out dataFormatArgs));
            }
            else
            {
                throw new InvalidAddressException(addressOrName);
            }
        }
Example #2
0
        /// <summary>
        /// Get formula from cell specified by an address or registered name
        /// </summary>
        /// <param name="addressOrName">address or name used to locate a cell</param>
        /// <returns>formula as string returned from cell</returns>
        public string GetCellFormula(string addressOrName)
        {
            if (CellPosition.IsValidAddress(addressOrName))
            {
                return(GetCellFormula(new CellPosition(addressOrName)));
            }

            NamedRange namedRange;

            if (this.registeredNamedRanges.TryGetValue(addressOrName, out namedRange))
            {
                return(GetCellFormula(namedRange.StartPos));
            }

            throw new InvalidReferenceException(
                      "Specified reference neither is an valid address nor name registered in spreadsheet.");
        }
Example #3
0
        /// <summary>
        /// Recalculate specified cell formula.
        /// </summary>
        /// <param name="pos">Address on worksheet to locate the cell.</param>
        public void RecalcCell(string address)
        {
            RangePosition range;

            if (CellPosition.IsValidAddress(address))
            {
                this.RecalcCell(new CellPosition(address));
            }
            else if (this.TryGetNamedRangePosition(address, out range))
            {
                this.RecalcCell(range.StartPos);
            }
            else
            {
                throw new InvalidAddressException(address);
            }
        }
Example #4
0
        /// <summary>
        /// Set data of cell at specified position on worksheet.
        /// </summary>
        /// <param name="addressOrName">Address or name to locate the cell.</param>
        /// <param name="data">Data to be set.</param>
        public void SetCellData(string addressOrName, object data)
        {
            NamedRange range;

            if (CellPosition.IsValidAddress(addressOrName))
            {
                SetCellData(new CellPosition(addressOrName), data);
            }
            else if (this.registeredNamedRanges.TryGetValue(addressOrName, out range))
            {
                SetCellData(range.StartPos, data);
            }
            else
            {
                throw new InvalidAddressException(addressOrName);
            }
        }
Example #5
0
        /// <summary>
        /// Set formula into cell, calculate the value of formula and update referenced cells.
        /// </summary>
        /// <param name="addressOrName">Address or name to locate range on worksheet</param>
        /// <param name="formula">Formula to be set. Equal sign is not required.</param>
        public void SetCellFormula(string addressOrName, string formula)
        {
            if (CellPosition.IsValidAddress(addressOrName))
            {
                this.SetCellFormula(new CellPosition(addressOrName), formula);
                return;
            }

            if (this.registeredNamedRanges.TryGetValue(addressOrName, out var range))
            {
                this.SetCellFormula(range.StartPos, formula);
            }
            else
            {
                throw new InvalidAddressException(addressOrName);
            }
        }
Example #6
0
        /// <summary>
        /// Paste data from tabbed string into worksheet.
        /// </summary>
        /// <param name="startPos">Start position to fill data.</param>
        /// <param name="str">Tabbed string to be pasted.</param>
        /// <returns>Range position that indicates the actually filled range.</returns>
        public RangePosition PasteFromString(CellPosition startPos, string str)
        {
            int rows = 0, cols = 0;

            string[] lines = str.Split(new string[] { "\r\n" }, StringSplitOptions.None);
            for (int r = 0; r < lines.Length; r++)
            {
                string line = lines[r];
                if (line.EndsWith("\n"))
                {
                    line = line.Substring(0, line.Length - 1);
                }
                //line = line.Trim();

                if (line.Length > 0)
                {
                    string[] tabs = line.Split('\t');
                    cols = Math.Max(cols, tabs.Length);

                    for (int c = 0; c < tabs.Length; c++)
                    {
                        int toRow = selectionRange.Row + r;
                        int toCol = selectionRange.Col + c;

                        if (!this.IsValidCell(toRow, toCol))
                        {
                            throw new RangeIntersectionException(new RangePosition(toRow, toCol, 1, 1));
                        }

                        string text = tabs[c];

                        if (text.StartsWith("\"") && text.EndsWith("\""))
                        {
                            text = text.Substring(1, text.Length - 2);
                        }

                        SetCellData(toRow, toCol, text);
                    }

                    rows++;
                }
            }

            return(new RangePosition(startPos.Row, startPos.Col, rows, cols));
        }
Example #7
0
 /// <summary>
 /// Get cell display text by specified address
 /// </summary>
 /// <param name="address">address to locate a cell</param>
 /// <returns>display text in string returned from specified cell</returns>
 public string GetCellText(string address)
 {
     if (CellPosition.IsValidAddress(address))
     {
         return(GetCellText(new CellPosition(address)));
     }
     else if (RangePosition.IsValidAddress(address))
     {
         return(GetCellText((new RangePosition(address)).StartPos));
     }
     else if (NamedRange.IsValidName(address) &&
              this.TryGetNamedRange(address, out var range))
     {
         return(GetCellText(range.StartPos));
     }
     else
     {
         throw new InvalidAddressException(address);
     }
 }
Example #8
0
 /// <summary>
 /// Check the boundary of cell position and return a safe position.
 /// </summary>
 /// <param name="pos">The cell position to check.</param>
 /// <returns>Safe cell position.</returns>
 public CellPosition FixPos(CellPosition pos)
 {
     if (pos.Row < 0)
     {
         pos.Row = 0;
     }
     if (pos.Col < 0)
     {
         pos.Col = 0;
     }
     if (pos.Row > this.rows.Count - 1)
     {
         pos.Row = this.rows.Count - 1;
     }
     if (pos.Col > this.cols.Count - 1)
     {
         pos.Col = this.cols.Count - 1;
     }
     return(pos);
 }
Example #9
0
        private CellPosition FindNextMovableCellRight(CellPosition pos, RangePosition moveRange, bool autoReturn = true)
        {
            int col = pos.Col;

            var endCol = this.selectionRange.Cols > 1 ? this.selectionRange.EndCol : this.cols.Count - 1;

            if (col >= endCol)
            {
                var newpos = FindNextMovableCellDown(new CellPosition(pos.Row, moveRange.Col), moveRange, false);
                if (pos == newpos)
                {
                    return(pos);
                }

                pos = newpos;
            }

            int row = pos.Row;

            // find next movable cell rightward
            while (col < moveRange.EndCol)
            {
                col++;

                var cell = this.cells[row, col];

                if (cell != null && !cell.MergeEndPos.IsEmpty &&
                    col <= cell.MergeEndPos.Col &&
                    col > cell.MergeStartPos.Col)
                {
                    continue;
                }

                if (this.cols[col].InnerWidth > 0)
                {
                    break;
                }
            }

            return(new CellPosition(pos.Row, col));
        }
Example #10
0
        private void ChangeSelectionRange(CellPosition start, CellPosition end)
        {
            var range = FixRangeSelection(new RangePosition(start, end));

            // compare to current selection, only do this when selection was really changed.
            if (this.selectionRange != range)
            {
                if (this.BeforeSelectionRangeChange != null)
                {
                    var arg = new BeforeSelectionChangeEventArgs(start, end);
                    this.BeforeSelectionRangeChange(this, arg);

                    if (arg.IsCancelled)
                    {
                        return;
                    }

                    if (start != arg.SelectionStart || end != arg.SelectionEnd)
                    {
                        start = arg.SelectionStart;
                        end   = arg.SelectionEnd;

                        range = FixRangeSelection(new RangePosition(start, end));
                    }
                }

                this.selectionRange = range;

                this.selStart = start;
                this.selEnd   = end;

                //if (!range.Contains(selStart)) selStart = range.StartPos;
                //if (!range.Contains(selEnd)) selEnd = range.EndPos;

                // focus pos validations:
                //   1. focus pos must be inside selection range
                //   2. focus pos cannot stop at invalid cell (any part of merged cell)
                if (this.focusPos.IsEmpty ||
                    !range.Contains(this.focusPos) ||
                    !IsValidCell(this.focusPos))
                {
                    var focusPos = selStart;

                    // find first valid cell as focus pos
                    for (int r = range.Row; r <= range.EndRow; r++)
                    {
                        for (int c = range.Col; c <= range.EndCol; c++)
                        {
                            var cell = this.cells[r, c];
                            if (cell != null && (cell.Colspan <= 0 || cell.Rowspan <= 0))
                            {
                                continue;
                            }

                            focusPos.Row = r;
                            focusPos.Col = c;
                            goto quit_loop;
                        }
                    }
quit_loop:

                    if (focusPos.Col < this.cols.Count &&
                        focusPos.Row < this.rows.Count)
                    {
                        FocusPos = focusPos;
                    }
                }

                // update focus return column
                this.focusReturnColumn = end.Col;

                if (this.operationStatus == OperationStatus.RangeSelect)
                {
                    this.SelectionRangeChanging?.Invoke(this, new RangeEventArgs(this.selectionRange));

#if EX_SCRIPT
                    // comment out this if you get performance problem when using script extension
                    RaiseScriptEvent("onselectionchanging");
#endif
                }
                else
                {
                    this.SelectionRangeChanged?.Invoke(this, new RangeEventArgs(this.selectionRange));

#if EX_SCRIPT
                    RaiseScriptEvent("onselectionchange");
#endif
                }

                RequestInvalidate();
            }
        }
Example #11
0
 /// <summary>
 /// Remove all trace arrows from specified position
 /// </summary>
 /// <param name="pos">Position to locate a cell</param>
 public void RemoveCellAllTraceArrows(CellPosition pos)
 {
     RemoveCellTraceDependents(pos);
     RemoveCellTracePrecedents(pos);
 }
Example #12
0
        /// <summary>
        /// Remove all trace dependent arrows from specified position
        /// </summary>
        /// <param name="pos">position to remove cell dependents</param>
        /// <returns></returns>
        public bool RemoveCellTraceDependents(CellPosition pos)
        {
            Cell cell = GetCell(pos);

            return(cell == null ? false : RemoveCellTraceDependents(cell));
        }
Example #13
0
        /// <summary>
        /// Start trace dependents to specified position
        /// </summary>
        /// <param name="pos">target position to trace dependents</param>
        /// <returns>true if trace arrow added</returns>
        public bool TraceCellDependents(CellPosition pos)
        {
            Cell cell = GetCell(pos);

            return(cell == null ? false : TraceCellPrecedents(cell));
        }
Example #14
0
        /// <summary>
        /// Get formula from cell specified by position
        /// </summary>
        /// <param name="pos">position to locate the cell to be get</param>
        /// <returns>formula as string returned from specified cell</returns>
        public string GetCellFormula(CellPosition pos)
        {
            var cell = GetCell(pos);

            return(cell == null ? string.Empty : cell.InnerFormula);
        }
Example #15
0
 /// <summary>
 /// Get data from specified cell
 /// </summary>
 /// <param name="pos">Position of cell to get data</param>
 /// <returns>Data of cell</returns>
 public object GetCellData(CellPosition pos)
 {
     return(GetCellData(pos.Row, pos.Col));
 }
Example #16
0
 /// <summary>
 /// Delete formula from specified cell.
 /// </summary>
 /// <param name="pos">Position to locate the cell on worksheet.</param>
 public void DeleteCellFormula(CellPosition pos)
 {
     DeleteCellFormula(pos.Row, pos.Col);
 }
Example #17
0
        /// <summary>
        /// Get a list of referenced ranges from formula of specified cell
        /// </summary>
        /// <param name="pos">position of cell to find its reference list</param>
        /// <returns>a list of referenced cell</returns>
        public List <ReferenceRange> GetCellFormulaReferenceRanges(CellPosition pos)
        {
            Cell cell = this.cells[pos.Row, pos.Col];

            return(cell == null ? null : GetCellFormulaReferenceRanges(cell));
        }
Example #18
0
 /// <summary>
 /// Get data from specified cell
 /// </summary>
 /// <param name="pos">position to locate the cell</param>
 /// <returns>data of cell</returns>
 public T GetCellData <T>(CellPosition pos)
 {
     return(CellUtility.ConvertData <T>(this.GetCellData(pos)));
 }
Example #19
0
 /// <summary>
 /// Show cell formula precedent trace lines on worksheet
 /// </summary>
 /// <param name="address">address to trace the cell</param>
 /// <returns>true if trace is successful</returns>
 public bool TraceCellPrecedents(string address)
 {
     return(CellPosition.IsValidAddress(address) ? TraceCellPrecedents(new CellPosition(address)) : false);
 }
Example #20
0
 /// <summary>
 /// Get formatted cell text from spcified position
 /// </summary>
 /// <param name="pos">position to be get</param>
 /// <returns>formatted cell's text</returns>
 public string GetCellText(CellPosition pos)
 {
     return(GetCellText(pos.Row, pos.Col));
 }
Example #21
0
 /// <summary>
 /// Remove all trace dependent arrows from specified address
 /// </summary>
 /// <param name="address">address to remove cell dependents</param>
 /// <returns></returns>
 public bool RemoveCellTraceDependents(string address)
 {
     return(CellPosition.IsValidAddress(address) ? RemoveCellTraceDependents(new CellPosition(address)) : false);
 }
Example #22
0
 /// <summary>
 /// Set data of cell at specified position on worksheet.
 /// </summary>
 /// <param name="pos">Position of cell to set data.</param>
 /// <param name="data">Data of cell to be set.</param>
 public void SetCellData(CellPosition pos, object data)
 {
     SetCellData(pos.Row, pos.Col, data);
 }
Example #23
0
 /// <summary>
 /// Set formula into cell, calculate the value of formula and update referenced cells.
 /// </summary>
 /// <param name="pos">position of cell</param>
 /// <param name="formula">Formula to be set. Equal sign is not required.</param>
 public void SetCellFormula(CellPosition pos, string formula)
 {
     this.SetCellFormula(pos.Row, pos.Col, formula);
 }
Example #24
0
 /// <summary>
 /// Check whether specified string is an valid address to locate cell or range
 /// </summary>
 /// <param name="address">address for cell or range</param>
 public static bool IsValidAddress(string address)
 {
     return(CellPosition.IsValidAddress(address) || RangePosition.IsValidAddress(address));
 }
Example #25
0
 internal HighlightRange(Worksheet worksheet, CellPosition startPos, CellPosition endPos)
     : base(worksheet, startPos, endPos)
 {
     this.HighlightColor = worksheet.GetNextAvailableHighlightRangeColor();
 }
Example #26
0
 internal Rectangle GetRangeBounds(CellPosition startPos, CellPosition endPos)
 {
     return(GetRangePhysicsBounds(new RangePosition(startPos, endPos)));
 }
Example #27
0
        internal void SelectRangeEndByMouse(Point location)
        {
            if (this.viewportController == null || this.viewportController.View == null)
            {
                return;
            }

            var viewport = this.viewportController.View.GetViewByPoint(location) as IRangeSelectableView;

            if (viewport == null)
            {
                viewport = this.viewportController.FocusView as IRangeSelectableView;
            }

            if (viewport != null)
            {
                Point vp = viewport.PointToView(location);

                var startpos = this.selStart;
                var endpos   = this.selEnd;

                #region Each Operation Status
                switch (this.operationStatus)
                {
                case OperationStatus.FullColumnSelect:
                {
                    int col = -1;

                    this.FindColumnByPosition(vp.X, out col);

                    if (col > -1)
                    {
                        startpos = new CellPosition(0, startpos.Col);
                        endpos   = new CellPosition(this.rows.Count, col);
                    }
                }
                break;

                case OperationStatus.FullRowSelect:
                {
                    int row = -1;

                    this.FindRowByPosition(vp.Y, out row);

                    if (row > -1)
                    {
                        startpos = new CellPosition(startpos.Row, 0);
                        endpos   = new CellPosition(row, this.cols.Count);
                    }
                }
                break;

                default:
                    endpos = CellsViewport.GetPosByPoint(viewport, vp);
                    break;
                }
                #endregion                 // Each Operation Status

                this.ApplyRangeSelection(startpos, endpos, true);
            }
        }
Example #28
0
 internal Rectangle GetCellBounds(CellPosition pos)
 {
     return(GetCellBounds(pos.Row, pos.Col));
 }
Example #29
0
 /// <summary>
 /// Select speicifed range on spreadsheet
 /// </summary>
 /// <param name="pos1">Start position of specified range</param>
 /// <param name="pos2">End position of specified range</param>
 public void SelectRange(CellPosition pos1, CellPosition pos2)
 {
     SelectRange(new RangePosition(pos1, pos2));
 }
Example #30
0
        internal void Clear()
        {
            // hidden edit textbox
            EndEdit(EndEditReason.Cancel);

            // clear editing flag
            endEditProcessing = false;

            // reset ActionManager
            if (controlAdapter != null)
            {
                var actionSupportedControl = controlAdapter.ControlInstance as IActionControl;

                if (actionSupportedControl != null)
                {
                    actionSupportedControl.ClearActionHistoryForWorksheet(this);
                }
            }

#if OUTLINE
            // clear row outlines
            if (this.outlines != null)
            {
                ClearOutlines(RowOrColumn.Row | RowOrColumn.Column);
            }
#endif // OUTLINE

            // clear named ranges
            registeredNamedRanges.Clear();

            // clear highlight ranges
            if (highlightRanges != null)
            {
                highlightRanges.Clear();
            }

#if PRINT
            // clear page breaks
            if (this.pageBreakRows != null)
            {
                this.pageBreakRows.Clear();
            }
            if (this.pageBreakCols != null)
            {
                this.pageBreakCols.Clear();
            }

            if (this.userPageBreakRows != null)
            {
                this.userPageBreakRows.Clear();
            }
            if (this.userPageBreakCols != null)
            {
                this.userPageBreakCols.Clear();
            }

            this.printableRange = RangePosition.Empty;
            this.printSettings  = null;
#endif // PRINT

#if DRAWING
            // drawing objects
            if (this.drawingCanvas != null)
            {
                this.drawingCanvas.Children.Clear();
            }
#endif // DRAWING

            // reset default width and height
            this.defaultColumnWidth = InitDefaultColumnWidth;
            this.defaultRowHeight   = InitDefaultRowHeight;

            // clear root style
            this.RootStyle = new WorksheetRangeStyle(DefaultStyle);

            // clear focus highlight ranges
            this.FocusHighlightRange = null;

            // restore to default operation mode
            this.operationStatus = OperationStatus.Default;

            // restore settings
            this.settings = WorksheetSettings.Default;

            if (this.SettingsChanged != null)
            {
                this.SettingsChanged(this, null);
            }

#if FORMULA
            // clear formula referenced cells and ranges
            formulaRanges.Clear();

            // clear trace lines
            if (this.traceDependentArrows != null)
            {
                this.traceDependentArrows.Clear();
            }
#endif // FORMULA

#if EX_SCRIPT
            if (Srm != null)
            {
                RaiseScriptEvent("unload");
            }
#endif // EX_SCRIPT

            // unfreeze rows and columns
            CellPosition pos = this.FreezePos;
            if (pos.Row > 0 || pos.Col > 0)
            {
                Unfreeze();
            }

            if (this.viewportController != null)
            {
                // reset viewport controller
                viewportController.Reset();
            }

            // TODO: release objects inside cells and borders
            cells    = new CellArray();
            hBorders = new HBorderArray();
            vBorders = new VBorderArray();

            // clear header & index
            this.rows.Clear();
            this.cols.Clear();

            // reset max row and column indexes
            this.maxRowHeader    = -1;
            this.maxColumnHeader = -1;

            // reset highlight range color counter
            this.rangeHighlightColorCounter = 0;
        }