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↑()"));
}
private void parseCSVDataSet(string filename, string knownfailsfile = null)
{
ISet <string> knownfails = new HashSet <string>(readFormulaCSV(knownfailsfile));
int parseErrors = 0;
var LOCK = new object();
Parallel.ForEach(readFormulaCSV(filename), (formula, control, linenr) =>
{
if (parseErrors > MaxParseErrors)
{
control.Stop();
return;
}
try
{
ExcelFormulaParser.Parse(formula);
}
catch (ArgumentException e)
{
if (!knownfails.Contains(formula))
{
lock (LOCK)
{
TestContext.WriteLine(String.Format("Failed parsing line {0} <<{1}>>", linenr, formula));
parseErrors++;
}
}
}
});
if (parseErrors > 0)
{
Assert.Fail("Parse Errors on file " + filename);
}
}
static void ImportTest()
{
SyntaxErrorListener errorListener = new SyntaxErrorListener();
string formula = "=MAX(B6-40;0)";
AntlrInputStream inputStream = new AntlrInputStream((string)formula);
ExcelFormulaLexer spreadsheetLexer = new ExcelFormulaLexer(inputStream);
spreadsheetLexer.AddErrorListener(errorListener);
CommonTokenStream commonTokenStream = new CommonTokenStream(spreadsheetLexer);
ExcelFormulaParser excelFormulaParser = new ExcelFormulaParser(commonTokenStream);
ExcelFormulaParser.ExcelExprContext context = excelFormulaParser.excelExpr();
if (errorListener.HasError)
{
Logger.DebugLine($"Found Lexer Error - Dont processing formula {(string)formula}", 10);
return;
}
if (excelFormulaParser.NumberOfSyntaxErrors > 0)
{
Logger.DebugLine($"Found Syntax Error - Dont processing formula {(string)formula}", 10);
return;
}
ExcelFormulaVisitor visitor = new ExcelFormulaVisitor();
string formulaText = visitor.Visit(context);
Logger.DebugLine($"FormulaText: {formulaText}", 10);
}
public void ConvertRange()
{
FSharpTransformationRule T = new FSharpTransformationRule();
FSharpTransform.Formula F = T.CreateFSharpTree(ExcelFormulaParser.Parse("A1:B7"));
Assert.IsNotNull(F);
}
public bool CanBeAppliedonBool(string formula)
{
var source = ExcelFormulaParser.Parse(formula);
var FFrom = fromFSharpTree;
var FSource = CreateFSharpTree(source);
return(FSharpTransform.CanBeAppliedonBool(FFrom, FSource));
}
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 Can_Apply_Normal_Cell_Reference()
{
string Cell = "A1";
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = ExcelFormulaParser.Parse(Cell);
Assert.IsTrue(T.CanBeAppliedonBool(ExcelFormulaParser.Parse(Cell)));
}
public void Dynamic_Argument_Can_Be_Formula()
{
var Original = ExcelFormulaParser.Parse("SUM(A1)");
FSharpTransformationRule S1 = new FSharpTransformationRule();
S1.from = S1.ParseToTree("[c]");
Assert.AreEqual(true, S1.CanBeAppliedonBool(Original));
}
public void Can_Not_Apply_Different_Dynamic_Range()
{
var Original = ExcelFormulaParser.Parse("SUM(A2:C7)+SUM(A2:C6)");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("SUM({r})+SUM({r})");
T.to = null;
Assert.IsFalse(T.CanBeAppliedonBool(Original));
}
public void Can_Not_Apply_Different_Dynamic_Cells()
{
var Original = ExcelFormulaParser.Parse("A3");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("{i,i}");
T.to = null;
Assert.IsFalse(T.CanBeAppliedonBool(Original));
}
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);
}
public void Can_not_Apply_Dynamic_Cell_Reference_Multiple_Places()
{
var Original = ExcelFormulaParser.Parse("A1+A2");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("{i,j}+{i+1,j}");
T.to = null;
Assert.IsFalse(T.CanBeAppliedonBool(Original));
}
public void ConvertFunction()
{
string Cell = "SUM(A1:B7)";
FSharpTransformationRule T = new FSharpTransformationRule();
FSharpTransform.Formula F = T.CreateFSharpTree(ExcelFormulaParser.Parse(Cell));
Assert.IsNotNull(F);
}
////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 TestIsParentheses()
{
// Can't use test() for this one, since test() invokes skipFormula()
Assert.IsTrue(ExcelFormulaParser.Parse("(1)").IsParentheses());
Assert.IsTrue(ExcelFormulaParser.Parse("(A1)").ChildNodes[0].IsParentheses());
// Make sure unions aren't recognized as parentheses
var union = ExcelFormulaParser.Parse("(A1,A2)");
Assert.IsFalse(union.IsParentheses());
Assert.IsFalse(union.ChildNodes[0].IsParentheses());
}
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()", "Ñ()");
}
public void Fancy_Merge()
{
var Original = ExcelFormulaParser.Parse("(SUM(K3:K4,K5,K6,K7))/COUNT(K3:K7)");
FSharpTransformationRule S1 = new FSharpTransformationRule();
S1.from = S1.ParseToTree("SUM({x,y}: {i,j}, {i,j+1},[k])");
S1.to = S1.ParseToTree("SUM({x,y}:{i,j+1},[k])");
Assert.AreEqual(true, S1.CanBeAppliedonBool(Original));
}
public void Can_not_Apply_Calculation()
{
var Original = ExcelFormulaParser.Parse("A1*A2");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = (ExcelFormulaParser.Parse("A1+A2"));
T.to = (ExcelFormulaParser.Parse("SUM(A1:A2)"));
Assert.IsFalse(T.CanBeAppliedonBool(Original));
}
public void Repeat_Merge()
{
var Original = ExcelFormulaParser.Parse("SUM(A1,A2,A3,A4)");
FSharpTransformationRule S1 = new FSharpTransformationRule();
S1.from = S1.ParseToTree("SUM({i,j}, {i,j+1})");
S1.to = S1.ParseToTree("SUM({i,j}:{i,j+1})");
Assert.AreEqual(false, S1.CanBeAppliedonBool(Original));
}
public void ConvertSheetReference()
{
string Cell = "Sheet!A1";
FSharpTransformationRule T = new FSharpTransformationRule();
FSharpTransform.Formula F = T.CreateFSharpTree(ExcelFormulaParser.Parse(Cell));
Assert.IsNotNull(F);
}
public void Dynamic_Range_Transform()
{
var Original = ExcelFormulaParser.Parse("SUM(A1:B1)");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("SUM({r})");
T.to = T.ParseToTree("SUM({r})+1");
FSharpTransform.Formula Result = T.ApplyOn(Original);
Assert.AreEqual("SUM(A1:B1)+1", T.Print(Result));
}
/// <summary>
/// Apply this transformation rule on a formula
/// </summary>
public string ApplyOn(string formula)
{
var source = ExcelFormulaParser.Parse(formula);
var FFrom = fromFSharpTree;
var FTo = toFSharpTree;
var FSource = CreateFSharpTree(source);
var result = FSharpTransform.ApplyOn(FTo, FFrom, FSource);
return(Print(result));
}
public void Can_Apply_In_SubFormulas()
{
var Original = ExcelFormulaParser.Parse("SUM(A1+A2)");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = (ExcelFormulaParser.Parse("SUM(A1+A2)"));
T.to = (ExcelFormulaParser.Parse("A1+A2"));
Assert.IsTrue(T.CanBeAppliedonBool(Original));
var map = (T.CanBeAppliedonMap(Original));
}
public void Super_Simple_Transform()
{
var Original = ExcelFormulaParser.Parse("A1");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = (ExcelFormulaParser.Parse("A1"));
T.to = (ExcelFormulaParser.Parse("A2"));
FSharpTransform.Formula Result = T.ApplyOn(Original);
Assert.AreEqual("A2", T.Print(Result));
}
public void If_error2()
{
var Original = ExcelFormulaParser.Parse("IF(ISERROR(A1+A2+B1),\"Error\",A1+A2+B1)");
FSharpTransformationRule S1 = new FSharpTransformationRule();
S1.from = S1.ParseToTree("IF(ISERROR([d]),[c],[d])");
S1.to = S1.ParseToTree("IFERROR([d],[c])");
Assert.IsTrue(S1.CanBeAppliedonBool(Original));
Assert.AreEqual("IFERROR(A1+A2+B1,\"Error\")", S1.ApplyOn(Original).Print());
}
public void SUM_COUNT_AVERAGE()
{
var Original = ExcelFormulaParser.Parse("SUM(A1:A5)/COUNT(A1:A5)");
FSharpTransformationRule S = new FSharpTransformationRule();
S.from = S.ParseToTree("SUM({r})/COUNT({r})");
S.to = S.ParseToTree("AVERAGE(A1:A5)");
FSharpTransform.Formula Result = S.ApplyOn(Original);
Assert.AreEqual("AVERAGE(A1:A5)", S.Print(Result));
}
public void Merge_Some_Ranges()
{
var Original = ExcelFormulaParser.Parse("SUM(A1,A2,A3)");
FSharpTransformationRule S = new FSharpTransformationRule();
S.from = S.ParseToTree("SUM({i,j}, {i,j+1}, {r})");
S.to = S.ParseToTree("SUM({i,j}:{i,j+1}, {r})");
FSharpTransform.Formula Result = S.ApplyOn(Original);
Assert.AreEqual("SUM(A1:A2,A3)", S.Print(Result));
}
public void Double_Transform()
{
var Original = ExcelFormulaParser.Parse("(A1+A2)*(A1+A2)");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = (ExcelFormulaParser.Parse("A1+A2"));
T.to = (ExcelFormulaParser.Parse("SUM(A1:A2)"));
FSharpTransform.Formula Result = T.ApplyOn(Original);
Assert.AreEqual("(SUM(A1:A2))*(SUM(A1:A2))", T.Print(Result));
}