public void TestGetRanges3()
{
var f = "=SUM(A1:B3)+AVERAGE(C2:C8)";
if (MockWorkbook.TestGetRanges(f) != 2)
{
throw new Exception("GetReferencesFromFormula should return 2 ranges for " + f);
}
}
public void TestGetRanges2()
{
var f = "=A1:B3";
if (MockWorkbook.TestGetRanges(f) != 1)
{
throw new Exception("GetReferencesFromFormula should return 1 range for " + f);
}
}
public void TestGetRanges1()
{
var f = "=A1";
if (MockWorkbook.TestGetRanges(f) != 0)
{
throw new Exception("GetReferencesFromFormula should return no ranges for " + f);
}
}
public static int TestGetRanges(string formula)
{
// mock workbook object
var mwb = new MockWorkbook();
Excel.Workbook wb = mwb.GetWorkbook();
Excel.Worksheet ws = mwb.GetWorksheet(1);
var path = Microsoft.FSharp.Core.FSharpOption <string> .None;
var ranges = ExcelParserUtility.GetRangeReferencesFromFormulaRaw(formula, path, wb, ws, ignore_parse_errors: false);
return(ranges.Count());
}
public void TestGetFormulaRanges()
{
var mwb = new MockWorkbook();
// rnd, for random formulae assignment
Random rand = new Random();
// gin up some formulae
Tuple<string, string>[] fs = {new Tuple<string,string>("B4", "=COUNT(A1:A5)"),
new Tuple<string,string>("A6", "=SUM(B5:B40)"),
new Tuple<string,string>("Z2", "=AVERAGE(A1:E1)"),
new Tuple<string,string>("B44", "=MEDIAN(D4:D9)")};
// to keep track of what we did
var d = new System.Collections.Generic.Dictionary<Excel.Worksheet, System.Collections.Generic.List<Tuple<string, string>>>();
// add the formulae to the worksheets, randomly
foreach (Excel.Worksheet w in mwb.GetWorksheets())
{
// init list for each worksheet
d[w] = new System.Collections.Generic.List<Tuple<string, string>>();
// add the formulae, randomly
foreach (var f in fs)
{
if (rand.Next(0, 2) == 0)
{
w.Range[f.Item1, f.Item1].Formula = f.Item2;
// keep track of what we did
d[w].Add(f);
}
}
// we need at least one formula, so add one if the above procedure did not
if (d[w].Count() == 0)
{
w.Range[fs[0].Item1, fs[0].Item1].Formula = fs[0].Item2;
d[w].Add(fs[0]);
}
}
// init DAG
var dag = new DAG(mwb.GetWorkbook(), mwb.GetApplication(), false);
// get the formulas; 1 formula per worksheet
var formulas = dag.getAllFormulaAddrs();
// there should be e.Count + 3 entries
// don't forget: workbooks have 3 blank worksheets by default
var expected = d.Values.Select(v => v.Count).Aggregate((acc, c) => acc + c);
if (formulas.Length != expected)
{
throw new Exception("DAG.getAllFormulaAddrs() should return " + expected + " elements but instead returns " + formulas.Length + ".");
}
bool all_ok = true;
// make sure that each worksheet's range has the formulas that it should
var f_wsgroups = formulas.GroupBy(f => f.GetCOMObject(mwb.GetApplication()).Worksheet);
foreach (var pair in f_wsgroups)
{
// get formulas in this worksheet
var r = pair.Key.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas);
// check that all formulae for this worksheet are accounted for
bool r_ok = d[r.Worksheet].Aggregate(true, (bool acc, Tuple<string, string> f) =>
{
bool found = false;
foreach (Excel.Range cell in r)
{
if (String.Equals((string)cell.Formula, f.Item2))
{
found = true;
}
}
return acc && found;
});
all_ok = all_ok && r_ok;
}
if (!all_ok)
{
throw new Exception("ConstructTree.GetFormulaRanges() failed to return all of the formulae that were added.");
}
}
public static int TestGetRanges(string formula)
{
// mock workbook object
var mwb = new MockWorkbook();
Excel.Workbook wb = mwb.GetWorkbook();
Excel.Worksheet ws = mwb.GetWorksheet(1);
var path = Microsoft.FSharp.Core.FSharpOption<string>.None;
var ranges = ExcelParserUtility.GetRangeReferencesFromFormulaRaw(formula, path, wb, ws, ignore_parse_errors: false);
return ranges.Count();
}
public void TestGetFormulaRanges()
{
var mwb = new MockWorkbook();
// rnd, for random formulae assignment
Random rand = new Random();
// gin up some formulae
Tuple <string, string>[] fs = { new Tuple <string, string>("B4", "=COUNT(A1:A5)"),
new Tuple <string, string>("A6", "=SUM(B5:B40)"),
new Tuple <string, string>("Z2", "=AVERAGE(A1:E1)"),
new Tuple <string, string>("B44", "=MEDIAN(D4:D9)") };
// to keep track of what we did
var d = new System.Collections.Generic.Dictionary <Excel.Worksheet, System.Collections.Generic.List <Tuple <string, string> > >();
// add the formulae to the worksheets, randomly
foreach (Excel.Worksheet w in mwb.GetWorksheets())
{
// init list for each worksheet
d[w] = new System.Collections.Generic.List <Tuple <string, string> >();
// add the formulae, randomly
foreach (var f in fs)
{
if (rand.Next(0, 2) == 0)
{
w.Range[f.Item1, f.Item1].Formula = f.Item2;
// keep track of what we did
d[w].Add(f);
}
}
// we need at least one formula, so add one if the above procedure did not
if (d[w].Count() == 0)
{
w.Range[fs[0].Item1, fs[0].Item1].Formula = fs[0].Item2;
d[w].Add(fs[0]);
}
}
// init DAG
var dag = new DAG(mwb.GetWorkbook(), mwb.GetApplication(), false);
// get the formulas; 1 formula per worksheet
var formulas = dag.getAllFormulaAddrs();
// there should be e.Count + 3 entries
// don't forget: workbooks have 3 blank worksheets by default
var expected = d.Values.Select(v => v.Count).Aggregate((acc, c) => acc + c);
if (formulas.Length != expected)
{
throw new Exception("DAG.getAllFormulaAddrs() should return " + expected + " elements but instead returns " + formulas.Length + ".");
}
bool all_ok = true;
// make sure that each worksheet's range has the formulas that it should
var f_wsgroups = formulas.GroupBy(f => f.GetCOMObject(mwb.GetApplication()).Worksheet);
foreach (var pair in f_wsgroups)
{
// get formulas in this worksheet
var r = pair.Key.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas);
// check that all formulae for this worksheet are accounted for
bool r_ok = d[r.Worksheet].Aggregate(true, (bool acc, Tuple <string, string> f) =>
{
bool found = false;
foreach (Excel.Range cell in r)
{
if (String.Equals((string)cell.Formula, f.Item2))
{
found = true;
}
}
return(acc && found);
});
all_ok = all_ok && r_ok;
}
if (!all_ok)
{
throw new Exception("ConstructTree.GetFormulaRanges() failed to return all of the formulae that were added.");
}
} // end test
