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 markPerturbability(AST.Range vector_rng)
{
// get inputs
var inputs = _v2i[vector_rng];
// count inputs that are formulas
int fcnt = inputs.Count(iaddr => _formulas.ContainsKey(iaddr));
// If there is at least one input that is not a formula
// mark the whole vector as perturbable.
// Note: all vectors marked as non-perturbable by default.
if (fcnt != inputs.Count)
{
_do_not_perturb[vector_rng] = false;
}
}
public void linkInputVector(AST.Address formula_addr, AST.Range vector_rng)
{
// add range to range-lookup-by-formula_addr dictionary
// (initialized in DAG constructor)
_f2v[formula_addr].Add(vector_rng);
// add formula_addr to faddr-lookup-by-range dictionary,
// initializing bucket if necessary
if (!_v2f.ContainsKey(vector_rng))
{
_v2f.Add(vector_rng, new HashSet <AST.Address>());
}
if (!_v2f[vector_rng].Contains(formula_addr))
{
_v2f[vector_rng].Add(formula_addr);
}
}
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 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 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 AST.COMRef makeInputVectorCOMRef(AST.Range rng)
{
// check for the range in the dictionary
AST.COMRef c;
if (!_all_vectors.TryGetValue(rng, out c))
{
// otherwise, create and cache it
Excel.Range com = rng.GetCOMObject(_app);
Excel.Worksheet ws = com.Worksheet;
Excel.Workbook wb = ws.Parent;
string wsname = ws.Name;
string wbname = wb.Name;
var path = new Microsoft.FSharp.Core.FSharpOption <string>(wb.Path);
int width = com.Columns.Count;
int height = com.Rows.Count;
c = new AST.COMRef(rng.getUniqueID(), wb, ws, com, path, wbname, wsname, Microsoft.FSharp.Core.FSharpOption <string> .None, width, height);
_all_vectors.Add(rng, c);
_do_not_perturb.Add(rng, true); // initially mark as not perturbable
}
return(c);
}
public void linkComponentInputCell(AST.Range input_range, AST.Address input_addr)
{
// add input_addr to iaddr-lookup-by-input_range dictionary,
// initializing bucket if necessary
if (!_v2i.ContainsKey(input_range))
{
_v2i.Add(input_range, new HashSet <AST.Address>());
}
if (!_v2i[input_range].Contains(input_addr))
{
_v2i[input_range].Add(input_addr);
}
// add input_range to irng-lookup-by-iaddr dictionary,
// initializing bucket if necessary
if (!_i2v.ContainsKey(input_addr))
{
_i2v.Add(input_addr, new HashSet <AST.Range>());
}
if (!_i2v[input_addr].Contains(input_range))
{
_i2v[input_addr].Add(input_range);
}
}
private IEnumerable <AST.Address> getVectorChildCellsRec(AST.Range vector_addr)
{
// get single-cell inputs (vectors only consist of single cells)
return(_v2i[vector_addr].SelectMany(rng => getChildCellsRec(rng)));
}
public AST.COMRef getCOMRefForRange(AST.Range rng)
{
return(_all_vectors[rng]);
}
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 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);
}
}
public void rangeCase8Test()
{
var mwb = MockWorkbook.standardMockWorkbook();
var e = mwb.envForSheet(1);
String s = "A1:B1";
AST.Range range = new AST.Range(
AST.Address.fromA1(1, "A", e.WorkbookName, e.WorkbookName, e.Path),
AST.Address.fromA1(1, "B", 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);
}
