示例#1
0
        /// <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 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();
 }
示例#3
0
        /// <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;
            }
        }
 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 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 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(new ExpressionConverter(), new CompileStrategyFactory());
            var result = compiler.Compile(expression.Expressions);
            Assert.AreEqual("Yes", result.Result);
        }
示例#7
0
 /// <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;
       }
 }
示例#8
0
        /// <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;
            }
        }