/// <summary> /// Remove all trace arrows from specified address /// </summary> /// <param name="address">Address of position to locate a cell</param> public void RemoveCellAllTraceArrows(string address) { if (CellPosition.IsValidAddress(address)) { RemoveCellAllTraceArrows(new CellPosition(address)); } }
/// <summary> /// Delete formula from specified cell. /// </summary> /// <param name="addressOrName">Address or name used to locate cell on worksheet.</param> public void DeleteCellFormula(string addressOrName) { if (CellPosition.IsValidAddress(addressOrName)) { var pos = new CellPosition(addressOrName); this.DeleteCellFormula(pos.Row, pos.Col); } else if (RangePosition.IsValidAddress(addressOrName)) { this.ClearRangeContent(new RangePosition(addressOrName), CellElementFlag.Formula); } else { if (this.TryGetNamedRange(addressOrName, out var namedRange)) { if (namedRange.IsMergedCell) { DeleteCellFormula(namedRange.StartPos); } else { ClearRangeContent(namedRange, CellElementFlag.Formula); } } } }
public void SetCellBody(string address, ICellBody body) { if (!CellPosition.IsValidAddress(address)) { throw new InvalidAddressException(address); } this.SetCellBody(new CellPosition(address), body); }
/// <summary> /// Paste data from tabbed string into worksheet. /// </summary> /// <param name="address">Start cell position to be filled.</param> /// <param name="content">Data to be pasted.</param> /// <returns>Range position that indicates the actually filled range.</returns> public RangePosition PasteFromString(string address, string content) { if (!CellPosition.IsValidAddress(address)) { throw new InvalidAddressException(address); } return(this.PasteFromString(new CellPosition(address), content)); }
/// <summary> /// Get a list of referenced ranges from formula of specified cell /// </summary> /// <param name="address">address of cell to find its reference list</param> /// <returns>a list of referenced cell</returns> public List <ReferenceRange> GetCellFormulaReferenceRanges(string address) { if (CellPosition.IsValidAddress(address)) { return(this.GetCellFormulaReferenceRanges(new CellPosition(address))); } else { return(null); } }
/// <summary> /// Get data from specified cell /// </summary> /// <param name="addressOrName">address or name to locate the cell</param> /// <returns>data of cell</returns> /// <exception cref="InvalidAddressException">throw if specified address or name is invalid</exception> public T GetCellData <T>(string addressOrName) { if (CellPosition.IsValidAddress(addressOrName)) { return(GetCellData <T>(new CellPosition(addressOrName))); } if (this.registeredNamedRanges.TryGetValue(addressOrName, out var range)) { return(GetCellData <T>(range.StartPos)); } throw new InvalidAddressException(addressOrName); }
/// <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))); } if (this.registeredNamedRanges.TryGetValue(addressOrName, out var namedRange)) { return(GetCellFormula(namedRange.StartPos)); } throw new InvalidReferenceException( "Specified reference neither is an valid address nor name registered in spreadsheet."); }
public CellDataFormatFlag GetCellDataFormat(string addressOrName, out object dataFormatArgs) { if (CellPosition.IsValidAddress(addressOrName)) { return(this.GetCellDataFormat(new CellPosition(addressOrName), out dataFormatArgs)); } else if (this.TryGetNamedRangePosition(addressOrName, out var namedRange)) { return(this.GetCellDataFormat(namedRange.StartPos, out dataFormatArgs)); } else { throw new InvalidAddressException(addressOrName); } }
/// <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) { if (CellPosition.IsValidAddress(addressOrName)) { SetCellData(new CellPosition(addressOrName), data); } else if (this.registeredNamedRanges.TryGetValue(addressOrName, out var range)) { SetCellData(range.StartPos, data); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Recalculate specified cell formula. /// </summary> /// <param name="pos">Address on worksheet to locate the cell.</param> public void RecalcCell(string address) { if (CellPosition.IsValidAddress(address)) { this.RecalcCell(new CellPosition(address)); } else if (this.TryGetNamedRangePosition(address, out var range)) { this.RecalcCell(range.StartPos); } else { throw new InvalidAddressException(address); } }
/// <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); } }
/// <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); } }
/// <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); }
/// <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); }
/// <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)); }
/// <summary> /// Create range position from a range or cell address. /// </summary> /// <param name="address">Address to locate the range position.</param> /// <exception cref="ArgumentException">Throw when specified address is invalid.</exception> public RangePosition(string address) { this.positionProperties = 0; Match m = RGUtility.RangeReferenceRegex.Match(address); if (!m.Success) { m = RGUtility.SingleAbsoulteRangeRegex.Match(address); } if (!m.Success && CellPosition.IsValidAddress(address)) { var pos = new CellPosition(address); this.row = pos.Row; this.col = pos.Col; this.rows = 1; this.cols = 1; this.StartRowProperty = pos.RowProperty; this.StartColumnProperty = pos.ColumnProperty; this.EndRowProperty = pos.RowProperty; this.EndColumnProperty = pos.ColumnProperty; } else { if (!m.Success //|| (m.Groups["to_col"].Length <= 0 || m.Groups["to_row"].Length <= 0 //|| m.Groups["from_col"].Length <= 0 || m.Groups["from_row"].Length <= 0) ) { throw new ArgumentException("range is invalid: " + address); } int fromCol = 0, fromRow = 0, toCol = 0, toRow = 0; bool fullRows = false, fullCols = false; if (m.Groups["from_row"].Success) { if (!int.TryParse(m.Groups["from_row"].Value, out fromRow)) { throw new ArgumentException("range is invalid: " + address); } fromRow--; } else { fullRows = true; } if (!fullRows && m.Groups["to_row"].Success) { if (!int.TryParse(m.Groups["to_row"].Value, out toRow)) { throw new ArgumentException("range is invalid: " + address); } toRow--; } if (m.Groups["from_col"].Success) { fromCol = RGUtility.GetNumberOfChar(m.Groups["from_col"].Value); } else { fullCols = true; } if (!fullCols && m.Groups["to_col"].Success) { toCol = RGUtility.GetNumberOfChar(m.Groups["to_col"].Value); } if (fullCols) { this.row = Math.Min(fromRow, toRow); this.rows = Math.Max(fromRow, toRow) - this.row + 1; this.col = 0; this.cols = -1; } else if (fullRows) { this.row = 0; this.rows = -1; this.col = Math.Min(fromCol, toCol); this.cols = Math.Max(fromCol, toCol) - this.col + 1; } else { this.row = Math.Min(fromRow, toRow); this.col = Math.Min(fromCol, toCol); this.rows = Math.Max(fromRow, toRow) - this.row + 1; this.cols = Math.Max(fromCol, toCol) - this.col + 1; } if (m.Groups["abs_from_row"].Success) { this.positionProperties |= PositionAbsoluteBits.StartRow; } if (m.Groups["abs_from_col"].Success) { this.positionProperties |= PositionAbsoluteBits.StartCol; } if (m.Groups["abs_to_row"].Success) { this.positionProperties |= PositionAbsoluteBits.EndRow; } if (m.Groups["abs_to_col"].Success) { this.positionProperties |= PositionAbsoluteBits.EndCol; } } }