Esempio n. 1
0
        public void Setup()
        {
            _package = new ExcelPackage();
            var excelDataProvider = new EpplusExcelDataProvider(_package);

            _parser = new FormulaParser(excelDataProvider);
        }
        public void CompileMultiCellReferenceWithValues()
        {
            var parsingContext = ParsingContext.Create();
            var file           = new FileInfo("filename.xlsx");

            using (var package = new ExcelPackage(file))
                using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
                    using (var excelDataProvider = new EpplusExcelDataProvider(package))
                    {
                        sheet.Cells[1, 1].Value = "Value1";
                        sheet.Cells[2, 1].Value = "Value2";
                        sheet.Cells[3, 1].Value = "Value3";
                        sheet.Cells[4, 1].Value = "Value4";
                        sheet.Cells[5, 1].Value = "Value5";
                        var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
                        using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
                        {
                            var expression = new ExcelAddressExpression("A1:A5", excelDataProvider, parsingContext);
                            var result     = expression.Compile();
                            var rangeInfo  = result.Result as ExcelDataProvider.IRangeInfo;
                            Assert.IsNotNull(rangeInfo);
                            Assert.AreEqual("A1:A5", rangeInfo.Address.Address);
                            Assert.AreEqual(5, rangeInfo.Count());
                            for (int i = 1; i <= 5; i++)
                            {
                                var rangeItem = rangeInfo.ElementAt(i - 1);
                                Assert.AreEqual("Value" + i, rangeItem.Value);
                            }
                        }
                    }
        }
Esempio n. 3
0
 public void Initialize()
 {
     _package        = new ExcelPackage();
     _provider       = new EpplusExcelDataProvider(_package);
     _parsingContext = ParsingContext.Create();
     _parsingContext.Scopes.NewScope(RangeAddress.Empty);
     _worksheet = _package.Workbook.Worksheets.Add("testsheet");
 }
 public void GetSinglePivotTableFromReferenceNoPivotTable()
 {
     using (var package = new ExcelPackage(new FileInfo("PivotTableColumnFields.xlsx")))
     {
         var provider = new EpplusExcelDataProvider(package);
         Assert.IsNull(provider.GetPivotTable(new ExcelAddress("Sheet1!ZZ999")));
     }
 }
 public void GetSinglePivotTableFromRangePartialCellReference()
 {
     using (var package = new ExcelPackage(new FileInfo("PivotTableColumnFields.xlsx")))
     {
         var provider = new EpplusExcelDataProvider(package);
         var pt       = provider.GetPivotTable(new ExcelAddress("NoSubtotals!H12:K15"));
         Assert.AreEqual("NoSubtotalsPivotTable1", pt.Name);
     }
 }
 public void GetSinglePivotTableFromRangeWithMultiplePivotTables()
 {
     using (var package = new ExcelPackage(new FileInfo("PivotTableColumnFields.xlsx")))
     {
         var provider = new EpplusExcelDataProvider(package);
         // RowItems!B3:M7 contains three pivot tables. The first one (closest to A1) should be returned.
         var pt = provider.GetPivotTable(new ExcelAddress("RowItems!B3:M7"));
         Assert.AreEqual("RowItemsPivotTable1", pt.Name);
     }
 }
Esempio n. 7
0
        private static ExcelDatabase GetDatabase(ExcelPackage package)
        {
            var provider = new EpplusExcelDataProvider(package);
            var sheet    = package.Workbook.Worksheets.Add("test");

            sheet.Cells["A1"].Value = "col1";
            sheet.Cells["A2"].Value = 1;
            sheet.Cells["B1"].Value = "col2";
            sheet.Cells["B2"].Value = 2;
            var database = new ExcelDatabase(provider, "A1:B2");

            return(database);
        }
Esempio n. 8
0
 public void FromCompileResultEnumerableIntegerUnknown()
 {
     using (var package = new ExcelPackage())
     {
         var sheet = package.Workbook.Worksheets.Add("Sheet1");
         sheet.Cells["C1"].Value = 1;
         sheet.Cells["C2"].Value = 2;
         sheet.Cells["C3"].Value = 3;
         var dataProvider  = new EpplusExcelDataProvider(package);
         var range         = dataProvider.GetRange("Sheet1", 1, 3, 3, 3);
         var compileResult = new CompileResult(range, DataType.Unknown);
         var result        = new ExpressionConverter().FromCompileResult(compileResult);
         Assert.IsInstanceOfType(result, typeof(IntegerExpression));
         Assert.AreEqual(1d, result.Compile().Result);
     }
 }
Esempio n. 9
0
        public void CriteriaShouldIgnoreEmptyFields2()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("test");
                sheet.Cells["A1"].Value = "Crit1";
                sheet.Cells["A2"].Value = 1;

                var provider = new EpplusExcelDataProvider(package);

                var criteria = new ExcelDatabaseCriteria(provider, "A1:B2");

                Assert.AreEqual(1, criteria.Items.Count);
                Assert.AreEqual("crit1", criteria.Items.Keys.First().ToString());
                Assert.AreEqual(1, criteria.Items.Values.Last());
            }
        }
Esempio n. 10
0
 public void ExtractCriterionFromCellRangeWithObjectReturnsCorrectValue()
 {
     using (var package = new ExcelPackage())
     {
         var worksheet = package.Workbook.Worksheets.Add("Sheet1");
         var provider  = new EpplusExcelDataProvider(package);
         this.ParsingContext.Scopes.NewScope(RangeAddress.Empty);
         worksheet.Cells["E12"].Value   = 1;
         worksheet.Cells["E13"].Value   = 2;
         worksheet.Cells["E14"].Value   = 3;
         worksheet.Cells["F12"].Value   = 1;
         worksheet.Cells["F13"].Value   = ">2";
         worksheet.Cells["F14"].Value   = 3;
         worksheet.Cells["H13"].Formula = "SUMIF(E12:E14, F12:F14)";
         worksheet.Calculate();
         Assert.AreEqual(3d, worksheet.Cells["H13"].Value);
     }
 }
Esempio n. 11
0
 public void ExtractCriterionFromCellRangeWithNonMatchingColReturnsZero()
 {
     using (var package = new ExcelPackage())
     {
         var worksheet = package.Workbook.Worksheets.Add("Sheet1");
         var provider  = new EpplusExcelDataProvider(package);
         this.ParsingContext.Scopes.NewScope(RangeAddress.Empty);
         worksheet.Cells["E7"].Value = 5;
         worksheet.Cells["F7"].Value = 10;
         worksheet.Cells["G7"].Value = 15;
         IRangeInfo testRange  = provider.GetRange(worksheet.Name, 7, 5, 7, 7);
         IRangeInfo firstRange = provider.GetRange(worksheet.Name, 8, 8, 8, 8);
         var        address    = firstRange.Address;
         var        arguments  = FunctionsHelper.CreateArgs(firstRange, testRange);
         var        result     = IfHelper.ExtractCriterionFromCellRange(arguments.ElementAt(1), worksheet, address._fromRow, address._fromCol);
         Assert.AreEqual(0, result);
     }
 }
Esempio n. 12
0
 public void ExtractCriterionFromCellRangeWithSameRowCellReferenceReturnsCorrectValue()
 {
     using (var package = new ExcelPackage())
     {
         var worksheet = package.Workbook.Worksheets.Add("Sheet2");
         var provider  = new EpplusExcelDataProvider(package);
         this.ParsingContext.Scopes.NewScope(RangeAddress.Empty);
         worksheet.Cells["B1"].Value = 5;
         worksheet.Cells["B2"].Value = 10;
         worksheet.Cells["B3"].Value = 15;
         IRangeInfo testRange  = provider.GetRange(worksheet.Name, 1, 2, 3, 2);
         IRangeInfo firstRange = provider.GetRange(worksheet.Name, 2, 2, 2, 2);
         var        address    = firstRange.Address;
         var        arguments  = FunctionsHelper.CreateArgs(firstRange, testRange);
         var        result     = IfHelper.ExtractCriterionFromCellRange(arguments.ElementAt(1), worksheet, address._fromRow, address._fromCol);
         Assert.AreEqual(10, result);
     }
 }
        public void CompileSingleCellReference()
        {
            var parsingContext = ParsingContext.Create();
            var file           = new FileInfo("filename.xlsx");

            using (var package = new ExcelPackage(file))
                using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
                    using (var excelDataProvider = new EpplusExcelDataProvider(package))
                    {
                        var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
                        using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
                        {
                            var expression = new ExcelAddressExpression("A1", excelDataProvider, parsingContext);
                            var result     = expression.Compile();
                            Assert.IsNull(result.Result);
                        }
                    }
        }
        public void CompileSingleCellReferenceResolveToRangeAbsolute()
        {
            var parsingContext = ParsingContext.Create();
            var file           = new FileInfo("filename.xlsx");

            using (var package = new ExcelPackage(file))
                using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
                    using (var excelDataProvider = new EpplusExcelDataProvider(package))
                    {
                        var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
                        using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
                        {
                            var expression = new ExcelAddressExpression("$A$1", excelDataProvider, parsingContext);
                            expression.ResolveAsRange = true;
                            var result    = expression.Compile();
                            var rangeInfo = result.Result as ExcelDataProvider.IRangeInfo;
                            Assert.IsNotNull(rangeInfo);
                            Assert.AreEqual("$A$1", rangeInfo.Address.Address);
                        }
                    }
        }
        public void CompileMultiCellReferenceAbsolute()
        {
            var parsingContext = ParsingContext.Create();
            var file           = new FileInfo("filename.xlsx");

            using (var package = new ExcelPackage(file))
                using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
                    using (var excelDataProvider = new EpplusExcelDataProvider(package))
                    {
                        var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
                        using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
                        {
                            var expression = new ExcelAddressExpression("$A$1:$A$5", excelDataProvider, parsingContext);
                            var result     = expression.Compile();
                            var rangeInfo  = result.Result as ExcelDataProvider.IRangeInfo;
                            Assert.IsNotNull(rangeInfo);
                            Assert.AreEqual("$A$1:$A$5", rangeInfo.Address.Address);
                            // Enumerating the range still yields no results.
                            Assert.AreEqual(0, rangeInfo.Count());
                        }
                    }
        }