/// <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));
}
