public void ConvertFunction()
{
string Cell = "SUM(A1:B7)";
FSharpTransformationRule T = new FSharpTransformationRule();
FSharpTransform.Formula F = T.CreateFSharpTree(ExcelFormulaParser.Parse(Cell));
Assert.IsNotNull(F);
}
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 Can_Not_Apply_In_SubFormulas()
{
var Original = ExcelFormulaParser.Parse("(A1+A2)*A5");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = (ExcelFormulaParser.Parse("A1+A2"));
T.to = (ExcelFormulaParser.Parse("SUM(A1:A2)"));
Assert.IsFalse(T.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 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 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 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 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 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 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 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));
}
public void Transform_Dynamic_Cell_Calculation()
{
var Original = ExcelFormulaParser.Parse("A1");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("{i,j}");
T.to = T.ParseToTree("{i,j+1}");
FSharpTransform.Formula Result = T.ApplyOn(Original);
ParseTreeNode Expected = (ExcelFormulaParser.Parse("A2"));
Assert.AreEqual("A2", T.Print(Result));
}
private void SetDirtyFlagsIn(Excel.Workbook wb)
{
Logger.Debug($"Finding and setting dirty method cell candidates in workbook {wb?.Name}");
IsRecalculatingDirtyCells = true;
foreach (Excel.Worksheet sheet in wb.Sheets)
{
IEnumerable <Excel.Range> candidates = Enumerable.Empty <Excel.Range>();
foreach (var function_name in DirtyFunctionNames)
{
var new_candidates = FindAllCellsContainingFormulaCandidates(sheet, $"{function_name}(");
candidates = candidates.Concat(new_candidates);
}
candidates = candidates.Distinct();
foreach (Excel.Range candidate in candidates)
{
var formula = candidate.Formula as String ?? String.Empty;
bool formulaContanisCallToDirtyMethod = false;
try
{
var parse = ExcelFormulaParser.Parse(formula);
formulaContanisCallToDirtyMethod = DirtyFunctionParsePredicate.Invoke(parse);
}
catch (ArgumentException)
{
// parse failed. eg we're probably just parsing pure text or something.
formulaContanisCallToDirtyMethod = false;
}
if (formulaContanisCallToDirtyMethod)
{
Logger.Debug("Workbook {0}, Sheet {1}: Setting cell {2} to dirty calculation ({3})", wb?.Name, sheet?.Name, candidate?.Address, candidate?.Formula);
candidate.Dirty();
}
else
{
Logger.Debug("Workbook {0}, Sheet {1}: DISCARD cell {2} for dirty calculation ({3})", wb?.Name, sheet?.Name, candidate?.Address, candidate?.Formula);
}
}
}
Logger.Debug("Done finding and setting dirty method cell candidates");
IsRecalculatingDirtyCells = false;
}
public static ParseTreeNode Parse(this string formula)
{
if (UseParseCache && formulaCache.Contains(formula))
{
return((ParseTreeNode)formulaCache.Get(formula));
}
var parsed = ExcelFormulaParser.Parse(formula);
if (UseParseCache)
{
formulaCache.Add(formula, parsed, new CacheItemPolicy());
}
return(parsed);
}
public void SheetNamesWithSpacesCanBeExtractedCorrectly()
{
var strangeSheetNames = new[] { "\t", " ", " ", " A", " ''A", " A ", " ''A1'' " };
foreach (var sheetName in strangeSheetNames)
{
// Simple reference to another sheet
var sourceText = $"'{sheetName}'!A1";
ParseTreeNode node = ExcelFormulaParser.Parse(sourceText);
var actual = node.AllNodes(GrammarNames.Prefix).First().GetPrefixInfo().Sheet;
Assert.AreEqual(sheetName, actual);
}
}
public void Transform_in_Arguments()
{
var Original = ExcelFormulaParser.Parse("(A1+A2)/3");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = (ExcelFormulaParser.Parse("A1+A2"));
T.to = (ExcelFormulaParser.Parse("SUM(A1:A2)"));
bool x = T.CanBeAppliedonBool(Original);
FSharpTransform.Formula Result = T.ApplyOn(Original);
Assert.AreEqual("(SUM(A1:A2))/3", T.Print(Result));
}
public static string Translate(ISheet sheet, string formula, string coord, out List <CellCoord> about)
{
processingsheet = sheet;
processingcoord = coord;
processingformula = formula;
string script = specialFormulaCode();
if (string.IsNullOrEmpty(script))
{
script = ToCode(ExcelFormulaParser.Parse(formula));
}
about = new List <CellCoord>(aboutCells);
aboutCells.Clear();
return(script);
}
public void Merge_Ranges()
{
var Original = ExcelFormulaParser.Parse("SUM(A1,A2)");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("SUM({i,j}, {i,j+1})");
T.to = T.ParseToTree("SUM({i,j}:{i,j+1})"); //hier heb je een gewone range met dt=ynamische cellen
Assert.IsTrue(T.CanBeAppliedonBool(Original));
FSharpTransform.Formula Result = T.ApplyOn(Original);
Assert.AreEqual("SUM(A1:A2)", T.Print(Result));
}
public void Can_Apply_Dynamic_Cell_Reference()
{
var Original = ExcelFormulaParser.Parse("A1");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("{i,j}");
var Result = T.CanBeAppliedonBool(Original);
Assert.IsTrue(Result);
var Map = T.CanBeAppliedonMap(Original);
Assert.IsTrue(Map.ContainsKey('i'));
Assert.IsTrue(Map.ContainsKey('j'));
Assert.AreEqual(2, Map.Count);
}
public void Can_Apply_Dynamic_Range_On_Cell_Reference()
{
var Original = ExcelFormulaParser.Parse("SUM(A1)");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("SUM({r})");
T.to = null;
Assert.IsTrue(T.CanBeAppliedonBool(Original));
var Map = T.CanBeAppliedonMap(Original);
Assert.IsTrue(Map.ContainsKey('r'));
var ValueList = Map.TryFind('r').Value;
var IntList = (FSharpTransform.mapElement.Ints)ValueList;
Assert.IsTrue(IntList.Item.Contains(0));
Assert.AreEqual(2, IntList.Item.Count());
}
internal static void test(string formula, bool ignorewhitespace = true, ParseTreeNode parsed = null)
{
if (parsed == null)
{
parsed = ExcelFormulaParser.Parse(formula);
}
try
{
var printed = parsed.Print();
if (ignorewhitespace)
{
formula = formula.Replace(" ", "");
printed = printed.Replace(" ", "");
}
Assert.AreEqual(formula, printed, "Printed parsed formula differs from original.\nOriginal: '{0}'\nPrinted: '{1}'", formula, printed);
}
catch (ArgumentException e)
{
Assert.Fail("Parse Tree contains a node for which the Print function is not defined.\n{0}", e.Message);
}
}
public void Can_Apply_Double_Dynamic_Reference()
{
var Original = ExcelFormulaParser.Parse("SUM(A2:B5)+C5");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("SUM({r})+{i,j}");
T.to = null;
Assert.IsTrue(T.CanBeAppliedonBool(Original));
var Map = T.CanBeAppliedonMap(Original);
Assert.IsTrue(Map.ContainsKey('r'));
var ValueList = Map.TryFind('r').Value;
var IntList = (FSharpTransform.mapElement.Ints)ValueList;
Assert.IsTrue(IntList.Item.Contains(0));
Assert.IsTrue(IntList.Item.Contains(1));
Assert.IsTrue(IntList.Item.Contains(4));
Assert.IsTrue(Map.ContainsKey('i'));
var ValueListi = Map.TryFind('i').Value;
var IntListi = (FSharpTransform.mapElement.Ints)ValueListi;
Assert.IsTrue(IntListi.Item.Contains(2));
Assert.IsTrue(Map.ContainsKey('j'));
var ValueListj = Map.TryFind('j').Value;
var IntListj = (FSharpTransform.mapElement.Ints)ValueListj;
Assert.IsTrue(IntListj.Item.Contains(4));
}
public ContextNode Parse(string formula)
{
return(new ContextNode(this, ExcelFormulaParser.Parse(formula)));
}
private static ParseTreeNode Parse(string input)
{
return(ExcelFormulaParser.Parse(input));
}