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