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