public void RemoveDuplicateOperators2() { var ctx = ParsingContext.Create(); const string formula = "++-1--(---2)++-3+-1----3-+2"; var tokenizer = new SourceCodeTokenizer(ctx.Configuration.FunctionRepository, ctx.NameValueProvider); var tokens = tokenizer.Tokenize(formula).ToList(); }
private string UpdateFormulaWorksheetReferences(string originalFormula, string originalSheetName, string newSheetName) { 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); if (address == null || !address.IsValidRowCol()) { formula += "#REF!"; } else { // A null value for newSheetName is treated as a deletion of the originalSheetName worksheet. address.ChangeWorksheet(originalSheetName, newSheetName); formula += address.Address; } } else { formula += t.Value; } } return(formula); } catch //Invalid formula, skip updating addresses { return(originalFormula); } }
/// <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); } }
public void ShouldHandleInnerFunctionCall3() { var ctx = ParsingContext.Create(); const string formula = "IF(I10>=0;IF(O10>I10;((O10-I10)*$B10)/$C$27;IF(O10<0;(O10*$B10)/$C$27;\"\"));IF(O10<0;((O10-I10)*$B10)/$C$27;IF(O10>0;(O10*$B10)/$C$27;)))"; var tokenizer = new SourceCodeTokenizer(ctx.Configuration.FunctionRepository, ctx.NameValueProvider); var tokens = tokenizer.Tokenize(formula); var expression = _graphBuilder.Build(tokens); Assert.AreEqual(1, expression.Expressions.Count()); var exp1 = expression.Expressions.First(); Assert.AreEqual(3, exp1.Children.Count()); }
public void RemoveDuplicateOperators1() { var ctx = ParsingContext.Create(); const string formula = "++1--2++-3+-1----3-+2"; var tokenizer = new SourceCodeTokenizer(ctx.Configuration.FunctionRepository, ctx.NameValueProvider); var tokens = tokenizer.Tokenize(formula).ToList(); var expression = _graphBuilder.Build(tokens); Assert.AreEqual(11, tokens.Count()); Assert.AreEqual("+", tokens[1].Value); Assert.AreEqual("-", tokens[3].Value); Assert.AreEqual("-", tokens[5].Value); Assert.AreEqual("+", tokens[7].Value); Assert.AreEqual("-", tokens[9].Value); }
public void ShouldHandleInnerFunctionCall2() { var ctx = ParsingContext.Create(); const string formula = "IF(3>2;\"Yes\";\"No\")"; var tokenizer = new SourceCodeTokenizer(ctx.Configuration.FunctionRepository, ctx.NameValueProvider); var tokens = tokenizer.Tokenize(formula); var expression = _graphBuilder.Build(tokens); Assert.AreEqual(1, expression.Expressions.Count()); var compiler = new ExpressionCompiler(); var result = compiler.Compile(expression.Expressions); Assert.AreEqual("Yes", result.Result); }
/// <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); } }
public void Setup() { var context = ParsingContext.Create(); _tokenizer = new SourceCodeTokenizer(context.Configuration.FunctionRepository, null); }
/// <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="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="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); } }