public void worksheetNameQuoteEscaping()
{
var mwb = MockWorkbook.standardMockWorkbook();
String formula1 = "=Dan!H45";
String formula2 = "='Dan Stuff'!H45";
String formula3 = "='Dan''s Stuff'!H45";
String formula4 = "='Dan's Stuff'!H45";
// first, the good ones
try
{
Parcel.parseFormula(formula1, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
Parcel.parseFormula(formula2, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
Parcel.parseFormula(formula3, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
// a bad one
try
{
Parcel.parseFormula(formula4, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
}
catch (AST.ParseException)
{
// OK
}
}
public void basicIfExpression()
{
var mwb = MockWorkbook.standardMockWorkbook();
var e = mwb.envForSheet(1);
string f = "=IF(TRUE, 1, 2)";
ExprOpt asto = Parcel.parseFormula(f, e.Path, e.WorkbookName, e.WorksheetName);
Expr[] a =
{
Expr.NewReferenceExpr(new AST.ReferenceBoolean(e, true)),
Expr.NewReferenceExpr(new AST.ReferenceConstant(e, 1.0)),
Expr.NewReferenceExpr(new AST.ReferenceConstant(e, 2.0))
};
ArgList args = Utility.makeFSList <AST.Expression>(a);
Expr correct = Expr.NewReferenceExpr(new AST.ReferenceFunction(e, "IF", args, AST.Arity.NewFixed(3)));
try
{
Expr ast = asto.Value;
Assert.AreEqual(ast, correct);
} catch (NullReferenceException nre)
{
Assert.Fail("Parse error: " + nre.Message);
}
}
[Ignore] // temporarily ignore test
public void brutalEUSESTest()
{
var failures = new System.Collections.Concurrent.ConcurrentQueue <string>();
var mwb = MockWorkbook.standardMockWorkbook();
var formulas = System.IO.File.ReadAllLines(@"..\..\TestData\formulas_distinct.txt");
System.Threading.Tasks.Parallel.ForEach(formulas, f =>
{
try
{
Parcel.parseFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
}
catch (Exception e)
{
if (e is AST.IndirectAddressingNotSupportedException)
{
// OK
}
else if (e is AST.ParseException)
{
System.Diagnostics.Debug.WriteLine("Fail: " + f);
failures.Enqueue(f);
}
}
});
Assert.AreEqual(0, failures.Count());
if (failures.Count > 0)
{
String.Join("\n", failures);
}
}
public void UnaryPrecedenceTest()
{
var mwb = MockWorkbook.standardMockWorkbook();
var e = mwb.envForSheet(1);
var f = "=-1 * 2";
ExprOpt asto = Parcel.parseFormula(f, e.Path, e.WorkbookName, e.WorksheetName);
Expr correct =
Expr.NewBinOpExpr(
"*",
Expr.NewUnaryOpExpr(
'-',
Expr.NewReferenceExpr(new AST.ReferenceConstant(e, 1.0))
),
Expr.NewReferenceExpr(new AST.ReferenceConstant(e, 2.0))
);
try
{
Expr ast = asto.Value;
Assert.AreEqual(correct, ast);
}
catch (NullReferenceException nre)
{
Assert.Fail("Parse error: " + nre.Message);
}
}
public void SpacesInArgListTest()
{
var mwb = MockWorkbook.standardMockWorkbook();
var e = mwb.envForSheet(1);
var f = "=SUM(A1,A2,A3, A4)";
ExprOpt asto = Parcel.parseFormula(f, e.Path, e.WorkbookName, e.WorksheetName);
string[] addrs = { "A1", "A2", "A3", "A4" };
var rng = Utility.makeUnionRangeFromA1Addrs(addrs, e);
Expr[] a =
{
Expr.NewReferenceExpr(new AST.ReferenceRange(e, rng))
};
ArgList args = Utility.makeFSList(a);
Expr correct = Expr.NewReferenceExpr(new AST.ReferenceFunction(e, "SUM", args, AST.Arity.VarArgs));
try
{
Expr ast = asto.Value;
Assert.AreEqual(correct, ast);
}
catch (NullReferenceException nre)
{
Assert.Fail("Parse error: " + nre.Message);
}
}
public void Arity2Test2()
{
var mwb = MockWorkbook.standardMockWorkbook();
var e = mwb.envForSheet(1);
var f = "=SUMX2MY2(A$4:A$10,B$4:B$10)";
ExprOpt asto = Parcel.parseFormula(f, e.Path, e.WorkbookName, e.WorksheetName);
Expr[] a =
{
Expr.NewReferenceExpr(new AST.ReferenceRange(e, Utility.makeRangeForA1("A$4:A$10", e))),
Expr.NewReferenceExpr(new AST.ReferenceRange(e, Utility.makeRangeForA1("B$4:B$10", e)))
};
ArgList args = Utility.makeFSList <AST.Expression>(a);
Expr correct = Expr.NewReferenceExpr(new AST.ReferenceFunction(e, "SUMX2MY2", args, AST.Arity.NewFixed(2)));
try
{
Expr ast = asto.Value;
Assert.AreEqual(correct, ast);
}
catch (NullReferenceException nre)
{
Assert.Fail("Parse error: " + nre.Message);
}
}
public void RangeCase7Test()
{
var mwb = MockWorkbook.standardMockWorkbook();
var e = mwb.envForSheet(1);
String s = "A1,A2:A3,A4";
AddrPair[] addrpairs =
{
new AddrPair(
AST.Address.fromA1withMode(1, "A", AST.AddressMode.Relative, AST.AddressMode.Relative, e.WorksheetName, e.WorkbookName, e.Path),
AST.Address.fromA1withMode(1, "A", AST.AddressMode.Relative, AST.AddressMode.Relative, e.WorksheetName, e.WorkbookName, e.Path)
),
new AddrPair(
AST.Address.fromA1withMode(2, "A", AST.AddressMode.Relative, AST.AddressMode.Relative, e.WorksheetName, e.WorkbookName, e.Path),
AST.Address.fromA1withMode(3, "A", AST.AddressMode.Relative, AST.AddressMode.Relative, e.WorksheetName, e.WorkbookName, e.Path)
),
new AddrPair(
AST.Address.fromA1withMode(4, "A", AST.AddressMode.Relative, AST.AddressMode.Relative, e.WorksheetName, e.WorkbookName, e.Path),
AST.Address.fromA1withMode(4, "A", AST.AddressMode.Relative, AST.AddressMode.Relative, e.WorksheetName, e.WorkbookName, e.Path)
)
};
AST.Reference r = Parcel.simpleReferenceParser(s, e);
AST.Reference correct = new AST.ReferenceRange(e, new AST.Range(addrpairs));
Assert.AreEqual(r, correct);
}
public void standardAddress()
{
var mwb = MockWorkbook.standardMockWorkbook();
var e = mwb.envForSheet(1);
String s = "A3";
AST.Reference r = Parcel.simpleReferenceParser(s, e);
AST.Reference correct = new AST.ReferenceAddress(e, AST.Address.fromA1withMode(3, "A", AST.AddressMode.Relative, AST.AddressMode.Relative, e.WorksheetName, e.WorkbookName, e.Path));
Assert.AreEqual(r, correct);
}
public void standardRange()
{
var mwb = MockWorkbook.standardMockWorkbook();
var e = mwb.envForSheet(1);
String s = "A3:B22";
AST.Reference r = Parcel.simpleReferenceParser(s, e);
AST.Reference correct = new AST.ReferenceRange(e,
new AST.Range(Utility.makeAddressForA1("A", 3, e),
Utility.makeAddressForA1("B", 22, e))
);
Assert.AreEqual(r, correct);
}
public void ExtractConstantsTest()
{
var mwb = MockWorkbook.standardMockWorkbook();
var e = mwb.envForSheet(1);
var f = "=4/(3.2*SUM(A$4:A$10,B$4:B$10)-22)";
var constants = Parcel.constantsFromFormula(f, e.Path, e.WorkbookName, e.WorksheetName, ignore_parse_errors: false);
Assert.IsTrue(Array.Exists(constants, c => c.Equals(new AST.ReferenceConstant(e, 3.2))));
Assert.IsTrue(Array.Exists(constants, c => c.Equals(new AST.ReferenceConstant(e, 4))));
Assert.IsTrue(Array.Exists(constants, c => c.Equals(new AST.ReferenceConstant(e, 22))));
Assert.IsTrue(constants.Length == 3);
}
public void mixedRangeTest2()
{
var mwb = MockWorkbook.standardMockWorkbook();
var e = mwb.envForSheet(1);
String s = "A$1:B$1";
AST.Range range = new AST.Range(
AST.Address.fromA1withMode(1, "A", AST.AddressMode.Absolute, AST.AddressMode.Relative, e.WorkbookName, e.WorkbookName, e.Path),
AST.Address.fromA1withMode(1, "B", AST.AddressMode.Absolute, AST.AddressMode.Relative, e.WorkbookName, e.WorkbookName, e.Path)
);
AST.Reference r = Parcel.simpleReferenceParser(s, e);
AST.Reference correct = new AST.ReferenceRange(e, range);
Assert.AreEqual(r, correct);
}
public void indirectReferences()
{
var mwb = MockWorkbook.standardMockWorkbook();
String formula = "=TRANSPOSE(INDIRECT(ADDRESS(1,4,3,1,Menus!$K$10)):INDIRECT(ADDRESS(1,256,3,1,Menus!$K$10)))";
try
{
Parcel.parseFormula(formula, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
}
catch (Exception e)
{
if (e is AST.IndirectAddressingNotSupportedException)
{
// we pass
}
else
{
Assert.Fail(e.Message);
}
}
}
public void crossWorkbookAddrExtraction()
{
var mwb = MockWorkbook.standardMockWorkbook();
var xmwb = new MockWorkbook("C:\\FINRES\\FIRMAS\\FORCASTS\\MODELS\\", "models.xls", new[] { "Forecast Assumptions" });
var f1 = "=L66*('C:\\FINRES\\FIRMAS\\FORCASTS\\MODELS\\[models.xls]Forecast Assumptions'!J27)^0.25";
var f1a1 = Utility.makeAddressForA1("L", 66, mwb.envForSheet(1));
var f1a2 = Utility.makeAddressForA1("J", 27, xmwb.envForSheet(1));
// extract
try
{
var addrs = Parcel.addrReferencesFromFormula(f1, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1), false);
Assert.IsTrue(addrs.Contains(f1a1));
Assert.IsTrue(addrs.Contains(f1a2));
Assert.IsTrue(addrs.Length == 2);
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
public void ifStatement()
{
var mwb = MockWorkbook.standardMockWorkbook();
string s = "=IF(SUM(A1:A5) = 10, \"yes\", \"no\")";
IEnumerable <AST.Range> rngs = new List <AST.Range>();
try
{
rngs = Parcel.rangeReferencesFromFormula(s, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1), false);
}
catch (AST.ParseException)
{
Assert.Fail(String.Format("\"{0}\" should parse.", s));
}
Assert.AreEqual(1, rngs.Count());
var addr = rngs.First().A1Local();
Assert.AreEqual("A1:A5", addr);
}
public void unOpAndBinOp()
{
var mwb = MockWorkbook.standardMockWorkbook();
string formula = "=(+E6+E7)*0.28";
IEnumerable <AST.Address> addrs = new List <AST.Address>();
try
{
addrs = Parcel.addrReferencesFromFormula(formula, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1), false);
}
catch (AST.ParseException)
{
Assert.Fail(String.Format("\"{0}\" should parse.", formula));
}
Assert.AreEqual(2, addrs.Count());
var a1 = Utility.makeAddressForA1("E", 6, mwb.envForSheet(1));
var a2 = Utility.makeAddressForA1("E", 7, mwb.envForSheet(1));
Assert.AreEqual(true, addrs.Contains(a1));
Assert.AreEqual(true, addrs.Contains(a2));
}
