/// <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 all the references to a renamed sheet in a formula. /// </summary> /// <param name="formula">The formula to updated.</param> /// <param name="oldSheetName">The old sheet name.</param> /// <param name="newSheetName">The new sheet name.</param> /// <returns>The formula with all cross-sheet references updated.</returns> internal static string UpdateFormulaSheetReferences(string formula, string oldSheetName, string newSheetName) { if (string.IsNullOrEmpty(oldSheetName)) { throw new ArgumentNullException(nameof(oldSheetName)); } if (string.IsNullOrEmpty(newSheetName)) { throw new ArgumentNullException(nameof(newSheetName)); } 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 (a == null || !a.IsValidRowCol()) { f += "#REF!"; } else { a.ChangeWorksheet(oldSheetName, newSheetName); f += a.Address; } } else { f += t.Value; } } return(f); } catch //Invalid formula, skip updating addresses { return(formula); } }
/// <summary> /// Updates all formulas after a worksheet has been renamed /// </summary> /// <param name="formula">The formula to be updated.</param> /// <param name="oldName">The old sheet name.</param> /// <param name="newName">The new sheet name.</param> /// <returns>The formula to be updated.</returns> internal static string UpdateSheetNameInFormula(string formula, string oldName, string newName) { if (string.IsNullOrEmpty(oldName) || string.IsNullOrEmpty(newName)) { throw new ArgumentNullException("Sheet name can't be empty"); } try { var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty); var retFormula = ""; foreach (var token in sct.Tokenize(formula)) { if (token.TokenTypeIsSet(TokenType.ExcelAddress)) //Address { var address = new ExcelAddressBase(token.Value); if (address == null || !address.IsValidRowCol()) { retFormula += "#REF!"; } else { address.ChangeWorksheet(oldName, newName); retFormula += address.Address; } } else { retFormula += token.Value; } } return(retFormula); } catch //if we have an exception, return the original 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); } }
/// <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> /// <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 all the references to a renamed sheet in a formula. /// </summary> /// <param name="formula">The formula to updated.</param> /// <param name="oldSheetName">The old sheet name.</param> /// <param name="newSheetName">The new sheet name.</param> /// <returns>The formula with all cross-sheet references updated.</returns> internal static string UpdateFormulaSheetReferences(string formula, string oldSheetName, string newSheetName) { if (string.IsNullOrEmpty(oldSheetName)) throw new ArgumentNullException(nameof(oldSheetName)); if (string.IsNullOrEmpty(newSheetName)) throw new ArgumentNullException(nameof(newSheetName)); 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 (a == null || !a.IsValidRowCol()) { f += "#REF!"; } else { a.ChangeWorksheet(oldSheetName, newSheetName); 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="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; } }