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