/// <summary>
/// Shifts all comments based on their address and the location of deleted rows and columns.
/// </summary>
/// <param name="fromRow">The start row.</param>
/// <param name="fromCol">The start column.</param>
/// <param name="rows">The number of rows to deleted.</param>
/// <param name="columns">The number of columns to deleted.</param>
internal void Delete(int fromRow, int fromCol, int rows, int columns)
{
List <ExcelComment> deletedComments = new List <ExcelComment>();
ExcelAddress address = null;
foreach (ExcelComment comment in this.Comments)
{
address = new ExcelAddress(comment.Range);
if (fromCol > 0 && address._fromCol >= fromCol)
{
address = address.DeleteColumn(fromCol, columns);
}
if (fromRow > 0 && address._fromRow >= fromRow)
{
address = address.DeleteRow(fromRow, rows);
}
if (address == null || address.Address == "#REF!")
{
deletedComments.Add(comment);
}
else
{
comment.Reference = address.Address;
}
}
int i = -1;
List <int> deletedIndices = new List <int>();
foreach (var comment in deletedComments)
{
if (this.Worksheet._commentsStore.Exists(comment.Range._fromRow, comment.Range._fromCol, out i))
{
this.Remove(comment);
deletedIndices.Add(i);
}
}
this.Worksheet._commentsStore.Delete(fromRow, fromCol, rows, columns);
var commentEnumerator = this.Worksheet._commentsStore.GetEnumerator();
while (commentEnumerator.MoveNext())
{
int offset = deletedIndices.Count(di => commentEnumerator.Value > di);
commentEnumerator.Value -= offset;
}
}
/// <summary>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="originalFormula">The Excel formula</param>
/// <param name="rowIncrement">The amount to increment the cell reference by</param>
/// <param name="colIncrement">The amount to increment the cell reference by</param>
/// <param name="afterRow">Only change rows after this row</param>
/// <param name="afterColumn">Only change columns after this column</param>
/// <param name="currentSheet">The sheet that contains the formula currently being processed.</param>
/// <param name="modifiedSheet">The sheet where cells are being inserted or deleted.</param>
/// <param name="setFixed">Fixed address</param>
/// <param name="updateOnlyFixed">Indicates whether or not to only update fixed (absolute) references. Used for named range formula references.</param>
/// <returns>The updated version of the <paramref name="originalFormula"/>.</returns>
public string UpdateFormulaReferences(string originalFormula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, string currentSheet, string modifiedSheet, bool setFixed = false, bool updateOnlyFixed = false)
{
try
{
var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty);
var tokens = sct.Tokenize(originalFormula);
string formula = string.Empty;
foreach (var t in tokens)
{
if (t.TokenType == TokenType.ExcelAddress)
{
var address = new ExcelAddress(t.Value);
var referencesModifiedWorksheet = (string.IsNullOrEmpty(address._ws) && currentSheet.IsEquivalentTo(modifiedSheet)) || modifiedSheet.IsEquivalentTo(address._ws);
if (!setFixed && (!string.IsNullOrEmpty(address._wb) || !referencesModifiedWorksheet))
{
// This address is in a different worksheet or workbook; no update is required.
formula += address.Address;
continue;
}
// Persist fully-qualified worksheet references.
if (!string.IsNullOrEmpty(address._ws))
{
formula += $"'{address._ws}'!";
}
if (rowIncrement > 0)
{
address = address?.AddRow(afterRow, rowIncrement, setFixed, updateOnlyFixed);
}
else if (rowIncrement < 0)
{
address = address?.DeleteRow(afterRow, -rowIncrement, setFixed, updateOnlyFixed);
}
if (colIncrement > 0)
{
address = address?.AddColumn(afterColumn, colIncrement, setFixed, updateOnlyFixed);
}
else if (colIncrement < 0)
{
address = address?.DeleteColumn(afterColumn, -colIncrement, setFixed, updateOnlyFixed);
}
if (address == null || !address.IsValidRowCol())
{
formula += "#REF!";
}
else
{
// If the address was not shifted, then a.Address will still have the sheet name.
var splitAddress = address.Address.Split('!');
if (splitAddress.Length > 1)
{
formula += splitAddress[1];
}
else
{
formula += address.Address;
}
}
}
else
{
if (t.TokenType == TokenType.StringContent)
{
formula += t.Value.Replace("\"", "\"\"");
}
else
{
formula += t.Value;
}
}
}
return(formula);
}
catch //Invalid formula, skip updating addresses
{
return(originalFormula);
}
}
