public void testTODAY2()
{
// a parse that should fail:
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "sheet1", "Calculations", "Status" });
var f = "=TODAY";
// parse
try
{
var ast = Parcel.parseFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
Assert.Fail("Should not parse.");
}
catch (AST.ParseException)
{
// pass
Assert.IsTrue(true);
}
}
public void testHLOOKUP()
{
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "sheet1", "Calculations", "Status" });
var f = "=HLOOKUP(J9,$C$45:$AH$46,2,TRUE)";
// parse
try
{
var ast = Parcel.parseFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
Assert.IsTrue(ExprOpt.get_IsSome(ast));
var expr = (Expr.ReferenceExpr)ast.Value;
var formula = (AST.ReferenceFunction)expr.Item;
Assert.IsTrue(formula.FunctionName == "HLOOKUP");
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
public void testCOUNTA()
{
// a parse that failed in the wild:
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "sheet1", "Calculations", "Status" });
var f = "=COUNTA(A6:A33)";
// parse
try
{
var ast = Parcel.parseFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
Assert.IsTrue(ExprOpt.get_IsSome(ast));
var expr = (Expr.ReferenceExpr)ast.Value;
var formula = (AST.ReferenceFunction)expr.Item;
Assert.IsTrue(formula.FunctionName == "COUNTA");
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
public void testMAXVarArgs()
{
// a parse that failed in the wild:
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "sheet1", "Calculations", "Status" });
var f = "=MAX(B6-10,0)";
// parse
try
{
var ast = Parcel.parseFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
Assert.IsTrue(ExprOpt.get_IsSome(ast));
var expr = (Expr.ReferenceExpr)ast.Value;
var formula = (AST.ReferenceFunction)expr.Item;
Assert.IsTrue(formula.FunctionName == "MAX");
Assert.AreEqual(formula.ArgumentList.Length, 2);
Assert.IsTrue(formula.ArgumentList.First().IsBinOpExpr);
Assert.IsTrue(formula.ArgumentList.Last().IsReferenceExpr);
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
public void crossWorksheetRangeExtraction()
{
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "sheet1", "One Country Charts", "One Country Data" });
var f = "=IF('One Country Charts'!F17=\"\",VLOOKUP('One Country Charts'!F11,'One Country Data'!M5:O187,3,FALSE),VLOOKUP('One Country Charts'!F17,'One Country Data'!M5:O187,3,FALSE))";
var data_env = mwb.envForSheet(3);
var rng = new AST.Range(Utility.makeAddressForA1("M", 5, data_env), Utility.makeAddressForA1("O", 187, data_env));
// extract
try
{
var rngs = Parcel.rangeReferencesFromFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1), false);
Assert.IsTrue(rngs.Contains(rng));
Assert.IsTrue(rngs.Length == 2);
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
public void multipleRangeExtraction()
{
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "sheet1", "Calculations", "Status" });
var f = "=IF(Status!G11=\"stand-alone hub\",SUMIF(Calculations!B7:P7,\"include\",Calculations!B163:P163),IF(Status!G11=\"remote\",SUMIF(Calculations!B7:P7,\"include\",Calculations!B184:P184),SUMIF(Calculations!B7:P7,\"include\",Calculations!B205:P205)))";
var calc_env = mwb.envForSheet(2);
var rng1 = new AST.Range(Utility.makeAddressForA1("B", 7, calc_env), Utility.makeAddressForA1("P", 7, calc_env));
var rng2 = new AST.Range(Utility.makeAddressForA1("B", 163, calc_env), Utility.makeAddressForA1("P", 163, calc_env));
var rng3 = new AST.Range(Utility.makeAddressForA1("B", 184, calc_env), Utility.makeAddressForA1("P", 184, calc_env));
var rng4 = new AST.Range(Utility.makeAddressForA1("B", 205, calc_env), Utility.makeAddressForA1("P", 205, calc_env));
// extract
try
{
var rngs = Parcel.rangeReferencesFromFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1), false);
Assert.IsTrue(rngs.Contains(rng1));
Assert.IsTrue(rngs.Contains(rng2));
Assert.IsTrue(rngs.Contains(rng3));
Assert.IsTrue(rngs.Contains(rng4));
Assert.IsTrue(rngs.Length == 6);
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
public void testTODAY2()
{
// a parse that should fail:
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "sheet1", "Calculations", "Status" });
var f = "=TODAY";
// parse
try
{
var ast = Parcel.parseFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
Assert.Fail("Should not parse.");
}
catch (AST.ParseException)
{
// pass
Assert.IsTrue(true);
}
}
public void missingWorkbookAddrExtraction()
{
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "budget" });
var f = "=budget!A43";
var addr = Utility.makeAddressForA1("A", 43, mwb.envForSheet(1));
// extract
try
{
var addrs = Parcel.addrReferencesFromFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1), false);
Assert.IsTrue(addrs.Contains(addr));
Assert.IsTrue(addrs.Length == 1);
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
public void testTODAY()
{
// a parse that failed in the wild:
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "sheet1", "Calculations", "Status" });
var f = "=TODAY()";
// parse
try
{
var ast = Parcel.parseFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
Assert.IsTrue(ExprOpt.get_IsSome(ast));
var expr = (Expr.ReferenceExpr)ast.Value;
var formula = (AST.ReferenceFunction)expr.Item;
Assert.IsTrue(formula.FunctionName == "TODAY");
Assert.AreEqual(formula.ArgumentList.Length, 0);
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
public void testHLOOKUP()
{
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "sheet1", "Calculations", "Status" });
var f = "=HLOOKUP(J9,$C$45:$AH$46,2,TRUE)";
// parse
try
{
var ast = Parcel.parseFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1));
Assert.IsTrue(ExprOpt.get_IsSome(ast));
var expr = (Expr.ReferenceExpr)ast.Value;
var formula = (AST.ReferenceFunction)expr.Item;
Assert.IsTrue(formula.FunctionName == "HLOOKUP");
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
public void multipleRangeExtraction()
{
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "sheet1", "Calculations", "Status" });
var f = "=IF(Status!G11=\"stand-alone hub\",SUMIF(Calculations!B7:P7,\"include\",Calculations!B163:P163),IF(Status!G11=\"remote\",SUMIF(Calculations!B7:P7,\"include\",Calculations!B184:P184),SUMIF(Calculations!B7:P7,\"include\",Calculations!B205:P205)))";
var calc_env = mwb.envForSheet(2);
var rng1 = new AST.Range(Utility.makeAddressForA1("B", 7, calc_env), Utility.makeAddressForA1("P", 7, calc_env));
var rng2 = new AST.Range(Utility.makeAddressForA1("B", 163, calc_env), Utility.makeAddressForA1("P", 163, calc_env));
var rng3 = new AST.Range(Utility.makeAddressForA1("B", 184, calc_env), Utility.makeAddressForA1("P", 184, calc_env));
var rng4 = new AST.Range(Utility.makeAddressForA1("B", 205, calc_env), Utility.makeAddressForA1("P", 205, calc_env));
// extract
try
{
var rngs = Parcel.rangeReferencesFromFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1), false);
Assert.IsTrue(rngs.Contains(rng1));
Assert.IsTrue(rngs.Contains(rng2));
Assert.IsTrue(rngs.Contains(rng3));
Assert.IsTrue(rngs.Contains(rng4));
Assert.IsTrue(rngs.Length == 4);
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
public void missingWorkbookAddrExtraction()
{
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "budget" });
var f = "=budget!A43";
var addr = Utility.makeAddressForA1("A", 43, mwb.envForSheet(1));
// extract
try
{
var addrs = Parcel.addrReferencesFromFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1), false);
Assert.IsTrue(addrs.Contains(addr));
Assert.IsTrue(addrs.Length == 1);
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
public void crossWorksheetRangeExtraction()
{
var mwb = new MockWorkbook("C:\\FOOBAR", "workbook.xls", new[] { "sheet1", "One Country Charts", "One Country Data" });
var f = "=IF('One Country Charts'!F17=\"\",VLOOKUP('One Country Charts'!F11,'One Country Data'!M5:O187,3,FALSE),VLOOKUP('One Country Charts'!F17,'One Country Data'!M5:O187,3,FALSE))";
var data_env = mwb.envForSheet(3);
var rng = new AST.Range(Utility.makeAddressForA1("M", 5, data_env), Utility.makeAddressForA1("O", 187, data_env));
// extract
try
{
var rngs = Parcel.rangeReferencesFromFormula(f, mwb.Path, mwb.WorkbookName, mwb.worksheetName(1), false);
Assert.IsTrue(rngs.Contains(rng));
Assert.IsTrue(rngs.Length == 1);
}
catch (AST.ParseException e)
{
Assert.Fail(e.Message);
}
}
