예제 #1
0
        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);
            }
        }
예제 #2
0
        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);
            }
        }
예제 #3
0
        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);
            }
        }
예제 #4
0
        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);
            }
        }
예제 #5
0
        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);
            }
        }
예제 #6
0
        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);
            }
        }
예제 #7
0
        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);
            }
        }
예제 #8
0
        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);
            }
        }