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