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 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 == 2);
}
catch (AST.ParseException e)
{
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 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 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);
}
}