/// <summary> /// Add a named range into current spreadsheet. /// </summary> /// <param name="namedRange">Named range to be added.</param> public void AddNamedRange(NamedRange namedRange) { if (namedRange == null) { throw new ArgumentNullException("namedRange", "Specified range object cannot be null"); } if (string.IsNullOrEmpty(namedRange.Name)) { throw new ArgumentNullException("namedRange.Name", "Name of range object cannot be empty"); } if (namedRange.Worksheet != null && namedRange.Worksheet != this) { throw new ArgumentException("Specified range is belong to another worksheet, remove from other worksheet firstly, or create a new range at same position."); } if (namedRange.Scope == NamedRangeScope.Workbook && this.workbook.worksheets.Any(s => s.registeredNamedRanges.Any(p => p.Value.Scope == NamedRangeScope.Workbook && p.Key.Equals(namedRange.Name, StringComparison.CurrentCultureIgnoreCase)))) { throw new NamedRangeAlreadyDefinedException(); } this.registeredNamedRanges[namedRange.Name] = namedRange; namedRange.Worksheet = this; if (NamedRangeDefined != null) { NamedRangeDefined(this, new NamedRangeAddedEventArgs(namedRange)); } }
/// <summary> /// Define named range registered into spreadsheet /// </summary> /// <param name="name">Name to identify a range in spreadsheet</param> /// <param name="range">Range to be added into spreadsheet</param> /// <param name="scope">The valid scope of this named range</param> /// <returns>Instance of named range added into spreadsheet</returns> public NamedRange DefineNamedRange(string name, RangePosition range, NamedRangeScope scope = NamedRangeScope.Workbook) { var namedRange = new NamedRange(this, name, FixRange(range)) { Scope = scope, }; AddNamedRange(namedRange); return(namedRange); }
/// <summary> /// Try get a named range by specified name /// </summary> /// <param name="name">name for range to be get</param> /// <param name="namedRange">output argument, range assoicated with specified name will be returned</param> /// <returns>true if specified name exists and the instance of range has been returned from spreadsheet, otherwise false</returns> public bool TryGetNamedRange(string name, out NamedRange namedRange) { if (!RGUtility.IsValidName(name)) { namedRange = null; return(false); } else { return(registeredNamedRanges.TryGetValue(name, out namedRange)); } }
public Worksheet TryGetNamedRange(string name, out NamedRange namedRange) { foreach (var worksheet in Worksheets) { if (worksheet.TryGetNamedRange(name, out namedRange) && namedRange.Scope == NamedRangeScope.Workbook) { return(worksheet); } } namedRange = null; return(null); }
/// <summary> /// Get named range which registered in current sheet /// </summary> /// <param name="name">string to name a range</param> /// <returns>referenced range of specified name</returns> public NamedRange GetNamedRange(string name) { if (registeredNamedRanges == null) { return(null); } NamedRange range = null; registeredNamedRanges.TryGetValue(name, out range); return(range); }
/// <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> /// Try get a named range by specified name /// </summary> /// <param name="name">name for range to be get</param> /// <param name="namedRange">output argument, range assoicated with specified name will be returned</param> /// <returns>true if specified name exists and the instance of range has been returned from spreadsheet, otherwise false</returns> public bool TryGetNamedRange(string name, out NamedRange namedRange) { return(registeredNamedRanges.TryGetValue(name, out namedRange)); }