/// <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); } } } }
/// <summary> /// Move cells from specified range to another range. /// </summary> /// <param name="fromRangeAddress"></param> /// <param name="toRangeAddress"></param> public void MoveRange(string fromRangeAddress, string toRangeAddress) { RangePosition fromRange, toRange; if (RangePosition.IsValidAddress(fromRangeAddress)) { fromRange = new RangePosition(fromRangeAddress); } else if (this.TryGetNamedRangePosition(fromRangeAddress, out fromRange)) { // nothing to do } else { throw new InvalidAddressException(fromRangeAddress); } if (RangePosition.IsValidAddress(toRangeAddress)) { toRange = new RangePosition(toRangeAddress); } else if (this.TryGetNamedRangePosition(toRangeAddress, out toRange)) { // nothing to do } else { throw new InvalidAddressException(toRangeAddress); } this.MoveRange(fromRange, toRange); }
/// <summary> /// Define named range from an address. /// </summary> /// <param name="name">Name to identify a range in spreadsheet</param> /// <param name="address">Address reference to a range</param> /// <param name="scope">The valid scope of this named range</param> /// <returns>Instance of named range added into spreadsheet</returns> /// <exception cref="InvalidAddressException">throw if specified address or name is illegal</exception> public NamedRange DefineNamedRange(string name, string address, NamedRangeScope scope) { if (!RangePosition.IsValidAddress(address)) { throw new InvalidAddressException(address); } return(DefineNamedRange(name, new RangePosition(address), scope)); }
/// <summary> /// Create named range from specified worksheet and address /// </summary> /// <param name="worksheet">worksheet to hold this named range</param> /// <param name="name">name to identify this range on spreadsheet</param> /// <param name="startPos">start position to locate range start on spreadsheet</param> /// <param name="endPos">start position to locate range end on spreadsheet</param> public NamedRange(Worksheet worksheet, string name, CellPosition startPos, CellPosition endPos) : base(worksheet, startPos, endPos) { if (RangePosition.IsValidAddress(name)) { throw new InvalidNamedRangeException(name); } this.internalName = name; }
/// <summary> /// Remove all trace arrows from specified range of address /// </summary> /// <param name="address">address to locate a range</param> /// <returns>true if address is valid</returns> public bool RemoveRangeAllTraceArrows(string address) { if (RangePosition.IsValidAddress(address)) { RemoveRangeAllTraceArrows(new RangePosition(address)); return(true); } else { return(false); } }
public string StringifyRange(string addressOrName) { if (RangePosition.IsValidAddress(addressOrName)) { return(this.StringifyRange(new RangePosition(addressOrName))); } else if (this.registeredNamedRanges.TryGetValue(addressOrName, out var namedRange)) { return(this.StringifyRange(namedRange)); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Set cell's data to fill specified range. /// </summary> /// <param name="addressOrName">Address or name to locate range on worksheet.</param> /// <param name="data">Data to be set.</param> public void SetRangeData(string addressOrName, object data) { if (RangePosition.IsValidAddress(addressOrName)) { this.SetRangeData(new RangePosition(addressOrName), data); } else if (this.registeredNamedRanges.TryGetValue(addressOrName, out var refRange)) { this.SetRangeData(refRange, data); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Export spreadsheet as CSV format from specified range. /// </summary> /// <param name="s">Stream to write CSV format as stream.</param> /// <param name="addressOrName">Range to be output from this worksheet, specified by address or name.</param> /// <param name="encoding">Text encoding during output text in CSV format.</param> public void ExportAsCSV(Stream s, string addressOrName, Encoding encoding = null) { if (RangePosition.IsValidAddress(addressOrName)) { ExportAsCSV(s, new RangePosition(addressOrName), encoding); } else if (this.TryGetNamedRange(addressOrName, out var namedRange)) { ExportAsCSV(s, namedRange, encoding); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Create highlight range at specified position /// </summary> /// <param name="addressOrName">Address or name to locate a range on worksheet</param> /// <param name="color">Color of the hihglight range displayed on worksheet</param> /// <returns>Instace of highlight range created in this worksheet</returns> public HighlightRange CreateHighlightRange(string addressOrName, SolidColor color) { if (RangePosition.IsValidAddress(addressOrName)) { return(CreateHighlightRange(new RangePosition(addressOrName), color)); } else if (this.registeredNamedRanges.TryGetValue(addressOrName, out var range)) { return(CreateHighlightRange(range.Position, color)); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Set data format for specified range. /// </summary> /// <param name="addressOrName">Address or name to locate the cell and range on spreadsheet to be set.</param> /// <param name="format">Flag specifies that which format will be used.</param> /// <param name="dataFormatArgs">Argument to specify the details for different format.</param> /// <exception cref="InvalidAddressException">Throw if specified address or name is illegal.</exception> public void SetRangeDataFormat(string addressOrName, CellDataFormatFlag format, object dataFormatArgs = null) { if (RangePosition.IsValidAddress(addressOrName)) { SetRangeDataFormat(new RangePosition(addressOrName), format, dataFormatArgs); } else if (this.registeredNamedRanges.TryGetValue(addressOrName, out var namedRange)) { SetRangeDataFormat(namedRange, format, dataFormatArgs); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Scroll view to make the specified range visible. /// </summary> /// <param name="addressOrName">address or name to locate a range</param> public void ScrollToRange(string addressOrName) { if (RangePosition.IsValidAddress(addressOrName)) { this.ScrollToRange(new RangePosition(addressOrName)); } else if (this.registeredNamedRanges.TryGetValue(addressOrName, out var refRange)) { this.ScrollToRange(refRange); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Copy a part of worksheet into current worksheet. /// </summary> /// <param name="addressOrName">The target range position specified by address or name.</param> /// <param name="data">A part of worksheet to be copied.</param> /// <returns>The range position that is the range filled actually.</returns> public RangePosition SetPartialGrid(string addressOrName, PartialGrid data) { if (RangePosition.IsValidAddress(addressOrName)) { return(this.SetPartialGrid(new RangePosition(addressOrName), data)); } else if (this.TryGetNamedRange(addressOrName, out var namedRange)) { return(this.SetPartialGrid(namedRange.Position, data)); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Remove a highlighted range from specified address /// </summary> /// <param name="address">address to remove highlighted range</param> /// <returns>true if range removed successfully</returns> public bool RemoveHighlightRange(string address) { if (RangePosition.IsValidAddress(address)) { return(RemoveHighlightRange(new RangePosition(address))); } else if (RGUtility.IsValidName(address)) { if (registeredNamedRanges.TryGetValue(address, out var refRange)) { return(RemoveHighlightRange(refRange)); } } return(false); }
/// <summary> /// Clear content inside specified range. /// </summary> /// <param name="addressOrName">Address or name to locate the range.</param> /// <param name="flags">Elements in cell specified by this flag to be removed.</param> public void ClearRangeContent(string addressOrName, CellElementFlag flags) { NamedRange namedRange; if (RangePosition.IsValidAddress(addressOrName)) { ClearRangeContent(new RangePosition(addressOrName), flags); } else if (this.TryGetNamedRange(addressOrName, out namedRange)) { ClearRangeContent(namedRange.Position, flags); } else { throw new InvalidAddressException(addressOrName); } }
public RangePosition SetPartialGridRepeatly(string addressOrName, PartialGrid grid) { NamedRange namedRange; if (RangePosition.IsValidAddress(addressOrName)) { return(this.SetPartialGridRepeatly(new RangePosition(addressOrName), grid)); } else if (this.TryGetNamedRange(addressOrName, out namedRange)) { return(this.SetPartialGridRepeatly(namedRange.Position, grid)); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Export spreadsheet as CSV format from specified range. /// </summary> /// <param name="path">File path to write CSV format as stream.</param> /// <param name="addressOrName">Range to be output from this worksheet, specified by address or name.</param> /// <param name="encoding">Text encoding during output text in CSV format.</param> public void ExportAsCSV(string path, string addressOrName, Encoding encoding = null) { NamedRange namedRange; if (RangePosition.IsValidAddress(addressOrName)) { this.ExportAsCSV(path, new RangePosition(addressOrName), encoding); } else if (this.TryGetNamedRange(addressOrName, out namedRange)) { this.ExportAsCSV(path, namedRange, encoding); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Copy a part of worksheet from specified range that identified by address or name. /// </summary> /// <param name="addressOrName">The address or name to locate a range position on worksheet.</param> /// <returns>A part of worksheet that is copied from original worksheet.</returns> public PartialGrid GetPartialGrid(string addressOrName) { NamedRange namedRange; if (RangePosition.IsValidAddress(addressOrName)) { return(this.GetPartialGrid(new RangePosition(addressOrName))); } else if (this.TryGetNamedRange(addressOrName, out namedRange)) { return(this.GetPartialGrid(namedRange.Position)); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Sort data inside specified range. /// </summary> /// <param name="columnIndex">Data will be sorted based on this column.</param> /// <param name="applyRange">Affect range.</param> /// <param name="order">Order of data sort.</param> /// <param name="cellDataComparer"></param> /// <returns></returns> public RangePosition SortColumn(int columnIndex, string applyRange, SortOrder order = SortOrder.Ascending, CellElementFlag moveElementFlag = CellElementFlag.Data, Func <int, int, object, object, int> cellDataComparer = null) { if (RangePosition.IsValidAddress(applyRange)) { return(this.SortColumn(columnIndex, new RangePosition(applyRange), order, moveElementFlag, cellDataComparer)); } else if (this.TryGetNamedRangePosition(applyRange, out var range)) { return(this.SortColumn(columnIndex, range, order, moveElementFlag, cellDataComparer)); } else { throw new InvalidAddressException(applyRange); } }
/// <summary> /// Remove all cell's data from specified range. /// </summary> /// <param name="addressOrName">Address or name to locate range on worksheet.</param> public void DeleteRangeData(string addressOrName) { NamedRange refRange; if (RangePosition.IsValidAddress(addressOrName)) { this.DeleteRangeData(new RangePosition(addressOrName), true); } else if (this.registeredNamedRanges.TryGetValue(addressOrName, out refRange)) { this.DeleteRangeData(refRange, true); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Select speicifed range on spreadsheet /// </summary> /// <param name="address">address or name of specified range to be selected</param> public void SelectRange(string address) { // range address if (RangePosition.IsValidAddress(address)) { SelectRange(new RangePosition(address)); } // named range else if (RGUtility.IsValidName(address)) { NamedRange refRange; if (registeredNamedRanges.TryGetValue(address, out refRange)) { SelectRange(refRange); } } }
/// <summary> /// Merge a range by specified address or name /// </summary> /// <param name="addressOrName">address or name to locate a range, if a name of range is specified, /// the name must be defined by DefineNamedRange first.</param> /// <exception cref="NamedRangeNotFoundException">throw when the specified name of range cannot be found.</exception> public void MergeRange(string addressOrName) { NamedRange refRange; if (RangePosition.IsValidAddress(addressOrName)) { MergeRange(new RangePosition(addressOrName)); } else if (this.registeredNamedRanges.TryGetValue(addressOrName, out refRange)) { MergeRange(refRange); } else { throw new InvalidAddressException(addressOrName); } }
/// <summary> /// Crearte and display a highlighted range at specified position on worksheet /// </summary> /// <param name="address">Address or name to locate a range on worksheet</param> /// <returns>Instance of highlight range on worksheet</returns> public HighlightRange AddHighlightRange(string address) { if (RangePosition.IsValidAddress(address)) { return(AddHighlightRange(new RangePosition(address))); } else if (RGUtility.IsValidName(address)) { NamedRange refRange; if (registeredNamedRanges.TryGetValue(address, out refRange)) { return(AddHighlightRange(refRange)); } } return(null); }
/// <summary> /// Try get range by specified address or range name. /// </summary> /// <param name="addressOrName">Address or range name used to find range on worksheet.</param> /// <param name="range">Range that was found by specified address or name on worksheet.</param> /// <returns>True if range was found; Otherwise return false.</returns> public ReferenceRange TryGetRangeByAddressOrName(string addressOrName) { ReferenceRange range = null; if (RangePosition.IsValidAddress(addressOrName)) { range = new ReferenceRange(this, addressOrName); } else if (NamedRange.IsValidName(addressOrName)) { NamedRange namedRange; if (this.registeredNamedRanges.TryGetValue(addressOrName, out namedRange)) { range = new ReferenceRange(namedRange.Worksheet, namedRange.Position); } } return(range); }
/// <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> /// Try get range by specified address or range name. /// </summary> /// <param name="addressOrName">Address or range name used to find range on worksheet.</param> /// <param name="range">Range that was found by specified address or name on worksheet.</param> /// <returns>True if range was found; Otherwise return false.</returns> public bool TryGetRangeByAddressOrName(string addressOrName, out RangePosition range) { if (RangePosition.IsValidAddress(addressOrName)) { range = new RangePosition(addressOrName); return(true); } else if (NamedRange.IsValidName(addressOrName)) { NamedRange namedRange; if (this.registeredNamedRanges.TryGetValue(addressOrName, out namedRange)) { range = (RangePosition)namedRange; return(true); } } range = RangePosition.Empty; return(false); }
/// <summary> /// Auto fill specified serial in range. /// </summary> /// <param name="fromAddressOrName">Range to read filling rules.</param> /// <param name="toAddressOrName">Range to be filled.</param> public void AutoFillSerial(string fromAddressOrName, string toAddressOrName) { NamedRange fromNRange, toNRange; RangePosition fromRange, toRange; #region fromRange if (this.TryGetNamedRange(fromAddressOrName, out fromNRange)) { fromRange = fromNRange.Position; } else if (RangePosition.IsValidAddress(fromAddressOrName)) { fromRange = new RangePosition(fromAddressOrName); } else { throw new InvalidAddressException(fromAddressOrName); } #endregion // fromRange #region toRange if (this.TryGetNamedRange(toAddressOrName, out toNRange)) { toRange = toNRange.Position; } else if (RangePosition.IsValidAddress(toAddressOrName)) { toRange = new RangePosition(toAddressOrName); } else { throw new InvalidAddressException(toAddressOrName); } #endregion // toRange this.AutoFillSerial(fromRange, toRange); }
/// <summary> /// Find name by specified address of range /// </summary> /// <param name="address">an address used to locate the range</param> /// <returns>name to target range, and null if not found</returns> public string GetNameByRange(string address) { return(RangePosition.IsValidAddress(address) ? GetNameByRange(new RangePosition(address)) : null); }
/// <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)); }