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