public void InsertDeleteTest() { var addr = new ExcelAddressBase("A1:B3"); Assert.AreEqual(addr.AddRow(2, 4).Address, "A1:B7"); Assert.AreEqual(addr.AddColumn(2, 4).Address, "A1:F3"); Assert.AreEqual(addr.DeleteColumn(2, 1).Address, "A1:A3"); Assert.AreEqual(addr.DeleteRow(2, 2).Address, "A1:B1"); Assert.AreEqual(addr.DeleteRow(1, 3), null); Assert.AreEqual(addr.DeleteColumn(1, 2), null); }
/// <summary> /// Shifts all comments based on their address and the location of inserted 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 insert.</param> /// <param name="columns">The number of columns to insert.</param> internal void Delete(int fromRow, int fromCol, int rows, int columns) { List <ExcelComment> deletedComments = new List <ExcelComment>(); ExcelAddressBase address = null; foreach (ExcelComment comment in _list) { address = new ExcelAddressBase(comment.Address); 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; } } foreach (var comment in deletedComments) { Remove(comment); } }
/// <summary> /// Shifts all comments based on their address and the location of inserted 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 insert.</param> /// <param name="columns">The number of columns to insert.</param> /// <param name="toRow">If the delete is in a range, this is the end row</param> /// <param name="toCol">If the delete is in a range, this the end column</param> internal void Delete(int fromRow, int fromCol, int rows, int columns, int toRow = ExcelPackage.MaxRows, int toCol = ExcelPackage.MaxColumns) { List <ExcelComment> deletedComments = new List <ExcelComment>(); ExcelAddressBase address = null; foreach (ExcelComment comment in _list.Where(x => x != null)) { address = new ExcelAddressBase(comment.Address); if (columns > 0 && address._fromCol >= fromCol && address._fromRow >= fromRow && address._toRow <= toRow) { address = address.DeleteColumn(fromCol, columns); } if (rows > 0 && address._fromRow >= fromRow && address._fromCol >= fromCol && address._toCol <= toCol) { address = address.DeleteRow(fromRow, rows); } if (address == null || address.Address == "#REF!") { deletedComments.Add(comment); } else { comment.Reference = address.Address; } } foreach (var comment in deletedComments) { comment.TopNode.ParentNode.RemoveChild(comment.TopNode); //Remove VML comment._commentHelper.TopNode.ParentNode.RemoveChild(comment._commentHelper.TopNode); //Remove Comment } }
/// <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="Formula">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="setFixed">Fixed address</param> /// <returns></returns> internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, bool setFixed = false) { //return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement); var d = new Dictionary <string, object>(); try { var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty); var tokens = sct.Tokenize(Formula); String f = ""; foreach (var t in tokens) { if (t.TokenType == TokenType.ExcelAddress) { var a = new ExcelAddressBase(t.Value); if (!string.IsNullOrEmpty(a._ws) || !string.IsNullOrEmpty(a._wb)) { // This address is in a different worksheet or workbook, thus no update is required f += a.Address; continue; } if (rowIncrement > 0) { a = a.AddRow(afterRow, rowIncrement, setFixed); } else if (rowIncrement < 0) { a = a.DeleteRow(afterRow, -rowIncrement, setFixed); } if (colIncrement > 0) { a = a.AddColumn(afterColumn, colIncrement, setFixed); } else if (colIncrement < 0) { a = a.DeleteColumn(afterColumn, -colIncrement, setFixed); } if (a == null || !a.IsValidRowCol()) { f += "#REF!"; } else { f += a.Address; } } else { f += t.Value; } } return(f); } catch //Invalid formula, skip updateing addresses { return(Formula); } }
/// <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="Formula">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> /// <returns></returns> internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn) { //return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement); var d = new Dictionary <string, object>(); try { var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty); var tokens = sct.Tokenize(Formula); String f = ""; foreach (var t in tokens) { if (t.TokenType == TokenType.ExcelAddress) { ExcelAddressBase a = new ExcelAddressBase(t.Value); if (rowIncrement > 0) { a = a.AddRow(afterRow, rowIncrement); } else if (rowIncrement < 0) { a = a.DeleteRow(afterRow, -rowIncrement); } if (colIncrement > 0) { a = a.AddColumn(afterColumn, colIncrement); } else if (colIncrement > 0) { a = a.DeleteColumn(afterColumn, -colIncrement); } if (a == null) { f += "#REF!"; } else { f += a.Address; } } else { f += t.Value; } } return(f); } catch //Invalid formula, skip updateing addresses { return(Formula); } }
/// <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="Formula">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> /// <returns></returns> internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn) { //return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement); var d=new Dictionary<string, object>(); try { var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty); var tokens = sct.Tokenize(Formula); String f = ""; foreach (var t in tokens) { if (t.TokenType == TokenType.ExcelAddress) { ExcelAddressBase a = new ExcelAddressBase(t.Value); if (rowIncrement > 0) { a = a.AddRow(afterRow, rowIncrement); } else if (rowIncrement < 0) { a = a.DeleteRow(afterRow, -rowIncrement); } if (colIncrement > 0) { a = a.AddColumn(afterColumn, colIncrement); } else if (colIncrement > 0) { a = a.DeleteColumn(afterColumn, -colIncrement); } if (a == null) { f += "#REF!"; } else { f += a.Address; } } else { f += t.Value; } } return f; } catch //Invalid formula, skip updateing addresses { return Formula; } }
/// <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="formula">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> /// <returns>The updated version of the <paramref name="formula"/>.</returns> internal static string UpdateFormulaReferences(string formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, string currentSheet, string modifiedSheet, bool setFixed = false) { var d = new Dictionary <string, object>(); try { var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty); var tokens = sct.Tokenize(formula); String f = ""; foreach (var t in tokens) { if (t.TokenType == TokenType.ExcelAddress) { var a = new ExcelAddressBase(t.Value); var referencesModifiedWorksheet = (string.IsNullOrEmpty(a._ws) && currentSheet.Equals(modifiedSheet, StringComparison.CurrentCultureIgnoreCase)) || modifiedSheet.Equals(a._ws, StringComparison.CurrentCultureIgnoreCase); if (!setFixed && (!string.IsNullOrEmpty(a._wb) || !referencesModifiedWorksheet)) { // This address is in a different worksheet or workbook; no update is required. f += a.Address; continue; } // Persist fully-qualified worksheet references. if (!string.IsNullOrEmpty(a._ws)) { f += $"'{a._ws}'!"; } if (rowIncrement > 0) { a = a.AddRow(afterRow, rowIncrement, setFixed); } else if (rowIncrement < 0) { a = a.DeleteRow(afterRow, -rowIncrement, setFixed); } if (colIncrement > 0) { a = a.AddColumn(afterColumn, colIncrement, setFixed); } else if (colIncrement < 0) { a = a.DeleteColumn(afterColumn, -colIncrement, setFixed); } if (a == null || !a.IsValidRowCol()) { f += "#REF!"; } else { // If the address was not shifted, then a.Address will still have the sheet name. var address = a.Address.Split('!'); if (address.Length > 1) { f += address[1]; } else { f += a.Address; } } } else { f += t.Value; } } return(f); } catch //Invalid formula, skip updating addresses { return(formula); } }
/// <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="Formula">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="setFixed">Fixed address</param> /// <returns></returns> internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, bool setFixed=false) { //return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement); var d=new Dictionary<string, object>(); try { var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty); var tokens = sct.Tokenize(Formula); String f = ""; foreach (var t in tokens) { if (t.TokenType == TokenType.ExcelAddress) { var a = new ExcelAddressBase(t.Value); if (!string.IsNullOrEmpty(a._ws) || !string.IsNullOrEmpty(a._wb)) { // This address is in a different worksheet or workbook, thus no update is required f += a.Address; continue; } if (rowIncrement > 0) { a = a.AddRow(afterRow, rowIncrement, setFixed); } else if (rowIncrement < 0) { a = a.DeleteRow(afterRow, -rowIncrement, setFixed); } if (colIncrement > 0) { a = a.AddColumn(afterColumn, colIncrement, setFixed); } else if (colIncrement < 0) { a = a.DeleteColumn(afterColumn, -colIncrement, setFixed); } if (a == null || !a.IsValidRowCol()) { f += "#REF!"; } else { f += a.Address; } } else { f += t.Value; } } return f; } catch //Invalid formula, skip updateing addresses { return Formula; } }
/// <summary> /// Shifts all comments based on their address and the location of inserted 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 insert.</param> /// <param name="columns">The number of columns to insert.</param> internal void Delete(int fromRow, int fromCol, int rows, int columns) { List<ExcelComment> deletedComments = new List<ExcelComment>(); ExcelAddressBase address = null; foreach (ExcelComment comment in _list) { address = new ExcelAddressBase(comment.Address); if (fromCol>0 && address._fromCol >= fromCol) { address = address.DeleteColumn(fromCol, columns); } if(fromRow > 0 && address._fromRow >= fromRow) { address = address.DeleteRow(fromRow, rows); } if(address.Address=="#REF!") { deletedComments.Add(comment); } else { comment.Reference = address.Address; } } foreach(var comment in deletedComments) { Remove(comment); } }
/// <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="formula">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> /// <returns>The updated version of the <paramref name="formula"/>.</returns> internal static string UpdateFormulaReferences(string formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, string currentSheet, string modifiedSheet, bool setFixed = false) { try { var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty); var tokens = sct.Tokenize(formula); var f = ""; foreach (var t in tokens) { if (t.TokenTypeIsSet(TokenType.ExcelAddress)) { var address = new ExcelAddressBase(t.Value); if ((!string.IsNullOrEmpty(address._wb) || !IsReferencesModifiedWorksheet(currentSheet, modifiedSheet, address)) && !setFixed) { f += address.Address; continue; } if (!string.IsNullOrEmpty(address._ws)) //The address has worksheet. { if (t.Value.IndexOf("'!") >= 0) { f += $"'{address._ws}'!"; } else { f += $"{address._ws}!"; } } if (!address.IsFullColumn) { if (rowIncrement > 0) { address = address.AddRow(afterRow, rowIncrement, setFixed); } else if (rowIncrement < 0) { if (address._fromRowFixed == false && (address._fromRow >= afterRow && address._toRow < afterRow - rowIncrement)) { address = null; } else { address = address.DeleteRow(afterRow, -rowIncrement, setFixed); } } } if (address != null && !address.IsFullRow) { if (colIncrement > 0) { address = address.AddColumn(afterColumn, colIncrement, setFixed); } else if (colIncrement < 0) { if (address._fromColFixed == false && (address._fromCol >= afterColumn && address._toCol < afterColumn - colIncrement)) { address = null; } else { address = address.DeleteColumn(afterColumn, -colIncrement, setFixed); } } } if (address == null || !address.IsValidRowCol()) { f += "#REF!"; } else { var ix = address.Address.LastIndexOf('!'); if (ix > 0) { f += address.Address.Substring(ix + 1); } else { f += address.Address; } } } else { f += t.Value; } } return(f); } catch //Invalid formula, return formula { return(formula); } }
/// <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="formula">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> /// <returns>The updated version of the <paramref name="formula"/>.</returns> internal static string UpdateFormulaReferences(string formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, string currentSheet, string modifiedSheet, bool setFixed = false) { var d = new Dictionary<string, object>(); try { var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty); var tokens = sct.Tokenize(formula); String f = ""; foreach (var t in tokens) { if (t.TokenType == TokenType.ExcelAddress) { var a = new ExcelAddressBase(t.Value); var referencesModifiedWorksheet = (string.IsNullOrEmpty(a._ws) && currentSheet.Equals(modifiedSheet, StringComparison.CurrentCultureIgnoreCase)) || modifiedSheet.Equals(a._ws, StringComparison.CurrentCultureIgnoreCase); if (!setFixed && (!string.IsNullOrEmpty(a._wb) || !referencesModifiedWorksheet)) { // This address is in a different worksheet or workbook; no update is required. f += a.Address; continue; } // Persist fully-qualified worksheet references. if (!string.IsNullOrEmpty(a._ws)) { f += $"'{a._ws}'!"; } if (rowIncrement > 0) { a = a.AddRow(afterRow, rowIncrement, setFixed); } else if (rowIncrement < 0) { a = a.DeleteRow(afterRow, -rowIncrement, setFixed); } if (colIncrement > 0) { a = a.AddColumn(afterColumn, colIncrement, setFixed); } else if (colIncrement < 0) { a = a.DeleteColumn(afterColumn, -colIncrement, setFixed); } if (a == null || !a.IsValidRowCol()) { f += "#REF!"; } else { // If the address was not shifted, then a.Address will still have the sheet name. var address = a.Address.Split('!'); if (address.Length > 1) f += address[1]; else f += a.Address; } } else { f += t.Value; } } return f; } catch //Invalid formula, skip updating addresses { return formula; } }