CheckCellTests.CheckCellTests.TestGetFormulaRanges C# (CSharp) Method

        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.");
            }
        }