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