public void SheetWithUnderscore() { ParseTree parseResult = ExcelFormulaParser.ParseToTree("aap_noot!B12"); Assert.AreEqual(3, parseResult.Tokens.Count); Assert.AreEqual("aap_noot!", ((Token)parseResult.Tokens.First()).Text); Assert.AreEqual(GrammarNames.Formula, parseResult.Root.Term.Name); Assert.AreNotEqual(ParseTreeStatus.Error, parseResult.Status); Assert.AreEqual(1, parseResult.Root.ChildNodes.Count()); ParseTreeNode reference = parseResult.Root.ChildNodes.First(); Assert.AreEqual(GrammarNames.Reference, reference.Term.Name); Assert.AreEqual(2, reference.ChildNodes.Count()); ParseTreeNode prefix = reference.ChildNodes.First(); Assert.AreEqual(GrammarNames.Prefix, prefix.Term.Name); ParseTreeNode cellOrRange = reference.ChildNodes.ElementAt(1); Assert.AreEqual(GrammarNames.Cell, cellOrRange.Term.Name); Assert.AreEqual(1, prefix.ChildNodes.Count()); ParseTreeNode sheet = prefix.ChildNodes.First(); Assert.AreEqual(GrammarNames.TokenSheet, sheet.Term.Name); Assert.AreEqual("aap_noot!", sheet.Token.Value); }
public void ParseUdfNamesWithSpecialCharacters() { // See [#55](https://github.com/spreadsheetlab/XLParser/issues/55) Test("·()", "¡¢£¤¥¦§¨©«¬®¯°±²³´¶·¸¹»¼½¾¿×÷()"); Assert.ThrowsException <ArgumentException>(() => ExcelFormulaParser.ParseToTree("A↑()")); }
public void TestMethod2() { var tree = ExcelFormulaParser.ParseToTree(@"VLOOKUP(CONCATENATE($D$12,""_"",$D13),ZMReport!$F:$AG,MATCH('Stress Results'!J$7,ZMReport!$F$4:$AG$4,0),0)/1000"); ExcelVlookupRemoverHelpers.FormulaSanitizerWalker.MakeReferencesAbsolute(tree.Root, "Stress Results"); var r = tree.Root.Print(); Assert.AreEqual("VLOOKUP(CONCATENATE('Stress Results'!$D$12,\"_\",'Stress Results'!$D13),ZMReport!$F:$AG,MATCH('Stress Results'!J$7,ZMReport!$F$4:$AG$4,0),0) / 1000", r); }
public void TestMethod1() { var tree = ExcelFormulaParser.ParseToTree("VLOOKUP(E11,F:I,2,FALSE)+VLOOKUP(E11,$F:I,2,FALSE)+VLOOKUP(E11,F:$I,2,FALSE)+VLOOKUP(E11,1:5,2,FALSE)+VLOOKUP(E11,$1:5,2,FALSE)+VLOOKUP(E11,Sheet1!$1:5,2,FALSE)+VLOOKUP(E11,Sheet1!F:$I,2,FALSE)+VLOOKUP(E11,Sheet1!F1:$I2,2,FALSE)+VLOOKUP(E11,F1:$I2,2,FALSE)"); ExcelVlookupRemoverHelpers.FormulaSanitizerWalker.MakeReferencesAbsolute(tree.Root, "_test"); var r = tree.Root.Print(); Assert.AreEqual("VLOOKUP('_test'!E11,'_test'!F:I,2,FALSE) + VLOOKUP('_test'!E11,'_test'!$F:I,2,FALSE) + VLOOKUP('_test'!E11,'_test'!F:$I,2,FALSE) + VLOOKUP('_test'!E11,'_test'!1:5,2,FALSE) + VLOOKUP('_test'!E11,'_test'!$1:5,2,FALSE) + VLOOKUP('_test'!E11,Sheet1!$1:5,2,FALSE) + VLOOKUP('_test'!E11,Sheet1!F:$I,2,FALSE) + VLOOKUP('_test'!E11,Sheet1!F1:$I2,2,FALSE) + VLOOKUP('_test'!E11,'_test'!F1:$I2,2,FALSE)", r); }
////needs to be re-written in walker format (e.g. when find a vrange don't search child tokens) //private void MakeReferencesAbsolute(ParseTreeNode tree, string currentSheetName) //{ // //var allReferences = tree.GetReferenceNodes().ToArray();//.AllNodes().Where(node => node.Is(GrammarNames.Reference)); // var allReferences = tree.AllNodes().Where(node => node.Is(GrammarNames.Reference)).ToArray(); // foreach (var reference in allReferences) // { // var t = ExcelFormulaParser.ParseToTree("$D4:E16"); // var t2 = ExcelFormulaParser.ParseToTree("'ZmReport'!$D4:E16"); // if (reference.ChildNodes.Count() == 1 && reference.ChildNodes[0].IsFunction()) // continue; // //var sheetRefNode = reference.AllNodes().FirstOrDefault(node => node.Is(GrammarNames.TokenSheetQuoted)); // var sheetRefNode = reference.SkipToRelevant().ChildNodes.FirstOrDefault(node => node.Is(GrammarNames.Prefix)); // if (sheetRefNode == null) // { // //var parent = reference.Parent(tree); // var newRef = GetSheetRangeReference(currentSheetName, reference.Print()); // var parsedNewRef = ExcelFormulaParser.ParseToTree(newRef); // reference.ChildNodes.Clear(); // reference.ChildNodes.Add(parsedNewRef.Root); // //parent.ChildNodes.Prepend(sheetPrefix); // } // } //} public static string MakeReferencesAbsolute(ParseTreeNode parseTree, string currentSheetName) { //traverse tree and look for references var stack = new Stack <ParseTreeNode>(); stack.Push(parseTree); while (stack.Any()) { var next = stack.Pop(); string nextStr; try { nextStr = next.Print(); } catch (Exception e) { // } //if it's a vlookup then substitute reference and make a note of it if (next.IsRange() || next.IsBinaryReferenceOperation() || next.Is(GrammarNames.Reference)) { var sheetRefNode = next.AllNodes().FirstOrDefault(node => node.Is(GrammarNames.Prefix)); if (sheetRefNode == null) { //var parent = reference.Parent(tree); var newRef = GetSheetRangeReference(currentSheetName, next.Print()); var parsedNewRef = ExcelFormulaParser.ParseToTree(newRef); next.ChildNodes.Clear(); next.ChildNodes.Add(parsedNewRef.Root); //parent.ChildNodes.Prepend(sheetPrefix); } continue; } //not a vlookup node, so search children var children = next.ChildNodes.ToList(); for (var childId = children.Count - 1; childId >= 0; childId--) { stack.Push(children[childId]); } } //pretty-print modified parse tree return(parseTree.Print()); }
private void TestReplace(string subject, string replace, string replacement, string expected) { var Fsub = T.CreateFSharpTree(ExcelFormulaParser.ParseToTree(subject).Root); var Frep = T.CreateFSharpTree(ExcelFormulaParser.ParseToTree(replace).Root); var Frepmnt = T.CreateFSharpTree(ExcelFormulaParser.ParseToTree(replacement).Root); var Fexp = T.CreateFSharpTree(ExcelFormulaParser.ParseToTree(expected).Root); var result = Fsub.ReplaceSubTree(Frep, Frepmnt); Assert.AreEqual(Fexp, result); }
public void DoNotParseUdfNamesConsistingOnlyOfROrC() { // See [#56](https://github.com/spreadsheetlab/XLParser/issues/56) // UDF function names consisting of a single character "R" or "C" must be prefixed by the module name foreach (var disallowed in "RrCc") { Assert.ThrowsException <ArgumentException>(() => ExcelFormulaParser.ParseToTree($"{disallowed}()")); } Test("Module1.R()", "N()", "T()", "Ñ()"); }
private string ResubstituteCalculatedValues(ExcelRangeBase cell) { //var output = new StringBuilder(); var cellFormula = cell.Formula; //parse formula var parsedFormula = ExcelFormulaParser.ParseToTree(cellFormula); var allReferences = parsedFormula.Root.AllNodes().Where(node => node.Is(GrammarNames.Reference)).ToArray(); foreach (var reference in allReferences) { var sheetRefNode = reference.ChildNodes.FirstOrDefault(node => node.Is(GrammarNames.Prefix)); if (sheetRefNode != null) { //var parent = reference.Parent(tree); if (sheetRefNode.Print() != "'__data'!") { continue; } var cellAddress = reference.ChildNodes[1].Print(); tempWs.Cells[cellAddress].Calculate(); var newRef = tempWs.Cells[cellAddress].Value.ToString(); var parsedNewRef = ExcelFormulaParser.ParseToTree(newRef); reference.ChildNodes.Clear(); reference.ChildNodes.Add(parsedNewRef.Root); //parent.ChildNodes.Prepend(sheetPrefix); } } return(parsedFormula.Root.Print()); }
public void SheetNameIsReferenceError() { var formulas = new[] { "#REF!A1", "B1+#REF!A1" }; Test(formulas); // See [#76](https://github.com/spreadsheetlab/XLParser/issues/76) foreach (var formula in formulas) { Assert.AreEqual(0, ExcelFormulaParser.ParseToTree(formula).Tokens.Count(TokenIsIntersect)); } const string formulaWithOneIntersect = "#REF!A:A #REF!1:1"; Test(formulaWithOneIntersect); Assert.AreEqual(1, ExcelFormulaParser.ParseToTree(formulaWithOneIntersect).Tokens.Count(TokenIsIntersect)); bool TokenIsIntersect(Token token) { return(string.Equals(token.Terminal.Name, "INTERSECT", StringComparison.OrdinalIgnoreCase)); } }
public string SanitizeFormula(ExcelRangeBase cell, string currentSheetName) { //var output = new StringBuilder(); var cellFormula = cell.Formula; //parse formula var parsedFormula = ExcelFormulaParser.ParseToTree(cellFormula); //traverse tree and look for vlookup function calls, when one is found, add it to output list and clean formula var parseTree = parsedFormula.Root; var stack = new Stack <ParseTreeNode>(); stack.Push(parseTree); //var allNodes = Traverse(item2, node => node.ChildNodes).ToArray(); //EnumerableExtensions.WriteCsv(allNodes, @"C:\Users\Nathan Hollis\OneDrive\Consulting\Alm\FNP Liq\Nodes.csv"); while (stack.Any()) { var next = stack.Pop(); //if it's a vlookup then substitute reference and make a note of it if (next.IsFunction() || next.IsBuiltinFunction()) { var functionName = next.GetFunction(); if (functionName.ToLower() == "vlookup") { _i++; var newRefAddress = $"A{_i}"; var newRef = GetSheetRangeReference("__data", newRefAddress); var parsedNewRef = ExcelFormulaParser.ParseToTree(newRef); //var prefix = parsedNewRef.Root.ChildNodes[0].ChildNodes[0]; MakeReferencesAbsolute(next, currentSheetName); //substitute vlookup for __data sheet reference using parse trees var parent = next.Parent(parseTree); parent.ChildNodes.Clear(); parent.ChildNodes.Add(parsedNewRef.Root); var vlookupFormula = next.Print(); //add the source var v = new VlookupRangeSource() { Index = _i, VlookupFormula = vlookupFormula, SourceRange = cell }; _vlookups.Add(v); //also need to copy to the temp sheet tempWs.Cells[newRefAddress].Formula = vlookupFormula; continue; //don't visit children } } //not a vlookup node, so search children var children = next.ChildNodes.ToList(); for (var childId = children.Count - 1; childId >= 0; childId--) { stack.Push(children[childId]); } } //pretty-print modified parse tree return(parseTree.Print()); }
public void SheetAsString() { ParseTree parseResult = ExcelFormulaParser.ParseToTree("'[20]Algemene info + \"Overview\"'!T95"); Assert.AreNotEqual(ParseTreeStatus.Error, parseResult.Status); }
public void SheetWithPeriod() { ParseTree parseResult = ExcelFormulaParser.ParseToTree("vrr2011_Omz.!M84"); Assert.AreNotEqual(ParseTreeStatus.Error, parseResult.Status); }