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