コード例 #1
0
        public void AverageAArray()
        {
            // For arrays, AverageA completely ignores booleans.  It divides by strings and numbers, but only
            // numbers are added to the total.  Real dates cannot be specified and string dates are not parsed.
            AverageA average = new AverageA();
            var      date    = new DateTime(2013, 1, 15);

            double[] values =
            {
                0,
                2000,
                0,
                0,
                0
            };
            var result = average.Execute(new FunctionArgument[]
            {
                new FunctionArgument(new FunctionArgument[]
                {
                    new FunctionArgument(1000.ToString("n")),
                    new FunctionArgument(2000),
                    new FunctionArgument(6000.ToString("n")),
                    new FunctionArgument(true),
                    new FunctionArgument(date.ToString("d")),
                    new FunctionArgument("test")
                })
            }, ParsingContext.Create());

            Assert.AreEqual(values.Average(), result.Result);
        }
コード例 #2
0
        public void AverageALiterals()
        {
            // For literals, AverageA always parses and include numeric strings, date strings, bools, etc.
            // The only exception is unparsable string literals, which cause a #VALUE.
            AverageA average = new AverageA();
            var      date1   = new DateTime(2013, 1, 5);
            var      date2   = new DateTime(2013, 1, 15);
            double   value1  = 1000;
            double   value2  = 2000;
            double   value3  = 6000;
            double   value4  = 1;
            double   value5  = date1.ToOADate();
            double   value6  = date2.ToOADate();
            var      result  = average.Execute(new FunctionArgument[]
            {
                new FunctionArgument(value1.ToString("n")),
                new FunctionArgument(value2),
                new FunctionArgument(value3.ToString("n")),
                new FunctionArgument(true),
                new FunctionArgument(date1),
                new FunctionArgument(date2.ToString("d"))
            }, ParsingContext.Create());

            Assert.AreEqual((value1 + value2 + value3 + value4 + value5 + value6) / 6, result.Result);
        }
コード例 #3
0
        public void AverageAWithArraysWorksAsExpected()
        {
            // For arrays, AverageA completely ignores booleans. It divides by strings and numbers, but only
            // numbers are added to the total. Real dates cannot be specified and string dates are not parsed.
            var date = new DateTime(2013, 1, 15);

            double[] expectedIndividualValues =
            {
                0,
                2000,
                0,
                0,
                0
            };
            var function         = new AverageA();
            var argumentsInArray = new FunctionArgument[]
            {
                new FunctionArgument(1000.ToString("n")),
                new FunctionArgument(2000),
                new FunctionArgument(6000.ToString("n")),
                new FunctionArgument(true),
                new FunctionArgument(date.ToString("d")),
                new FunctionArgument("test")
            };
            var argumentsInArrayInArray = new FunctionArgument[]
            {
                new FunctionArgument(argumentsInArray)
            };
            var result = function.Execute(argumentsInArrayInArray, this.ParsingContext);

            Assert.AreEqual(expectedIndividualValues.Average(), result.Result);
        }
コード例 #4
0
        public void AverageAWithFourNegativeNumbersReturnsCorrectResult()
        {
            var function  = new AverageA();
            var arguments = FunctionsHelper.CreateArgs(-1.5, -2, -3.5, -7);
            var result    = function.Execute(arguments, this.ParsingContext);

            Assert.AreEqual((-1.5 + -2 + -3.5 + -7) / 4, result.Result);
        }
コード例 #5
0
        public void AverageAShouldCountNumericStringWithValue()
        {
            var func   = new AverageA();
            var args   = FunctionsHelper.CreateArgs(4d, 2d, "9");
            var result = func.Execute(args, _parsingContext);

            Assert.AreEqual(5d, result.Result);
        }
コード例 #6
0
        public void AverageAWithOneNumericStringReturnsCorrectResult()
        {
            var function  = new AverageA();
            var arguments = FunctionsHelper.CreateArgs("2");
            var result    = function.Execute(arguments, this.ParsingContext);

            Assert.AreEqual(2d, result.Result);
        }
コード例 #7
0
        public void AverageAShouldCountValueAs0IfNonNumericTextIsSuppliedInArray()
        {
            var func   = new AverageA();
            var args   = FunctionsHelper.CreateArgs(FunctionsHelper.CreateArgs(1d, 2d, 3d, "ABC"));
            var result = func.Execute(args, _parsingContext);

            Assert.AreEqual(1.5d, result.Result);
        }
コード例 #8
0
        public void AverageAWithOneNonNumericStringReturnsPoundValue()
        {
            var function  = new AverageA();
            var arguments = FunctionsHelper.CreateArgs("word");
            var result    = function.Execute(arguments, this.ParsingContext);

            Assert.AreEqual(eErrorType.Value, ((ExcelErrorValue)result.Result).Type);
        }
コード例 #9
0
        public void AverageAWithNumericStringAndBooleanValueReturnsCorrectResult()
        {
            var function  = new AverageA();
            var arguments = FunctionsHelper.CreateArgs("2", true);
            var result    = function.Execute(arguments, this.ParsingContext);

            Assert.AreEqual(1.5, result.Result);
        }
コード例 #10
0
        public void AverageAWithOneIntegerAndNullArgumentReturnsCorrectResult()
        {
            var function  = new AverageA();
            var arguments = FunctionsHelper.CreateArgs(2, null);
            var result    = function.Execute(arguments, this.ParsingContext);

            Assert.AreEqual(1d, result.Result);
        }
コード例 #11
0
        public void AverageAShouldIncludeTrueAs1()
        {
            var expectedResult = (4d + 2d + 5d + 2d + 1d) / 5d;
            var func           = new AverageA();
            var args           = FunctionsHelper.CreateArgs(4d, 2d, 5d, 2d, true);
            var result         = func.Execute(args, _parsingContext);

            Assert.AreEqual(expectedResult, result.Result);
        }
コード例 #12
0
        public void AverageAShouldCalculateCorrectResult()
        {
            var expectedResult = (4d + 2d + 5d + 2d) / 4d;
            var func           = new AverageA();
            var args           = FunctionsHelper.CreateArgs(4d, 2d, 5d, 2d);
            var result         = func.Execute(args, _parsingContext);

            Assert.AreEqual(expectedResult, result.Result);
        }
コード例 #13
0
        public void AverageAShouldThrowValueExceptionIfNonNumericTextIsSupplied()
        {
            var func   = new AverageA();
            var args   = FunctionsHelper.CreateArgs(4d, 2d, 5d, 2d, "ABC");
            var result = func.Execute(args, _parsingContext);

            Assert.AreEqual(OfficeOpenXml.FormulaParsing.ExpressionGraph.DataType.ExcelError, result.DataType);
            Assert.AreEqual(eErrorType.Value, ((ExcelErrorValue)(result.Result)).Type);
        }
コード例 #14
0
 public void AverageAUnparsableLiteral()
 {
     // In the case of literals, any unparsable string literal results in a #VALUE.
     AverageA average = new AverageA();
     var      result  = average.Execute(new FunctionArgument[]
     {
         new FunctionArgument(1000),
         new FunctionArgument("Test")
     }, ParsingContext.Create());
 }
コード例 #15
0
        public void AverageACellReferences()
        {
            // For cell references, AverageA divides by all cells, but only adds actual numbers, dates, and booleans.
            ExcelPackage package   = new ExcelPackage();
            var          worksheet = package.Workbook.Worksheets.Add("Test");

            double[] values =
            {
                0,
                2000,
                0,
                1,
                new DateTime(2013, 1, 5).ToOADate(),
                0
            };
            ExcelRange range1 = worksheet.Cells[1, 1];

            range1.Formula = "\"1000\"";
            range1.Calculate();
            var range2 = worksheet.Cells[1, 2];

            range2.Value = 2000;
            var range3 = worksheet.Cells[1, 3];

            range3.Formula = $"\"{new DateTime(2013, 1, 5).ToString("d")}\"";
            range3.Calculate();
            var range4 = worksheet.Cells[1, 4];

            range4.Value = true;
            var range5 = worksheet.Cells[1, 5];

            range5.Value = new DateTime(2013, 1, 5);
            var range6 = worksheet.Cells[1, 6];

            range6.Value = "Test";
            AverageA average    = new AverageA();
            var      rangeInfo1 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 1, 1, 3);
            var      rangeInfo2 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 4, 1, 4);
            var      rangeInfo3 = new EpplusExcelDataProvider.RangeInfo(worksheet, 1, 5, 1, 6);
            var      context    = ParsingContext.Create();
            var      address    = new OfficeOpenXml.FormulaParsing.ExcelUtilities.RangeAddress();

            address.FromRow = address.ToRow = address.FromCol = address.ToCol = 2;
            context.Scopes.NewScope(address);
            var result = average.Execute(new FunctionArgument[]
            {
                new FunctionArgument(rangeInfo1),
                new FunctionArgument(rangeInfo2),
                new FunctionArgument(rangeInfo3)
            }, context);

            Assert.AreEqual(values.Average(), result.Result);
        }
コード例 #16
0
        public void AverageAWithUnparsableLiteralsWorksAsExpected()
        {
            // In the case of literals, any unparsable string literal results in a #VALUE.
            var function         = new AverageA();
            var argumentsInArray = new FunctionArgument[]
            {
                new FunctionArgument(1000),
                new FunctionArgument("Test")
            };
            var result = function.Execute(argumentsInArray, this.ParsingContext);

            Assert.AreEqual(OfficeOpenXml.FormulaParsing.ExpressionGraph.DataType.ExcelError, result.DataType);
            Assert.AreEqual(eErrorType.Value, ((ExcelErrorValue)(result.Result)).Type);
        }
コード例 #17
0
        public void AverageAWithLiteralsWorksAsExpected()
        {
            // For literals, AverageA always parses and includes numeric strings, date strings, bools, etc.
            // The only exception is unparsable string literals, which cause a #VALUE.
            var date1 = new DateTime(2013, 1, 5);
            var date2 = new DateTime(2013, 1, 15);

            double[] expectedIndividualValues =
            {
                1000,
                2000,
                6000,
                1,
                date1.ToOADate(),
                date2.ToOADate()
            };
            var function  = new AverageA();
            var arguments = FunctionsHelper.CreateArgs("1000", 2000, "6000", true, date1, date2.ToString("d"));
            var result    = function.Execute(arguments, this.ParsingContext);

            Assert.AreEqual(expectedIndividualValues.Average(), result.Result);
        }
コード例 #18
0
        public void AverageAFunctionWithErrorValuesAsInputReturnsTheInputErrorValue()
        {
            var func        = new AverageA();
            var argNA       = FunctionsHelper.CreateArgs(ExcelErrorValue.Create(eErrorType.NA), 1, 1, 1, 1);
            var argNAME     = FunctionsHelper.CreateArgs(ExcelErrorValue.Create(eErrorType.Name), 1, 1, 1, 1);
            var argVALUE    = FunctionsHelper.CreateArgs(ExcelErrorValue.Create(eErrorType.Value), 1, 1, 1, 1);
            var argNUM      = FunctionsHelper.CreateArgs(ExcelErrorValue.Create(eErrorType.Num), 1, 1, 1, 1);
            var argDIV0     = FunctionsHelper.CreateArgs(ExcelErrorValue.Create(eErrorType.Div0), 1, 1, 1, 1);
            var argREF      = FunctionsHelper.CreateArgs(ExcelErrorValue.Create(eErrorType.Ref), 1, 1, 1, 1);
            var resultNA    = func.Execute(argNA, this.ParsingContext);
            var resultNAME  = func.Execute(argNAME, this.ParsingContext);
            var resultVALUE = func.Execute(argVALUE, this.ParsingContext);
            var resultNUM   = func.Execute(argNUM, this.ParsingContext);
            var resultDIV0  = func.Execute(argDIV0, this.ParsingContext);
            var resultREF   = func.Execute(argREF, this.ParsingContext);

            Assert.AreEqual(eErrorType.NA, ((ExcelErrorValue)resultNA.Result).Type);
            Assert.AreEqual(eErrorType.Name, ((ExcelErrorValue)resultNAME.Result).Type);
            Assert.AreEqual(eErrorType.Value, ((ExcelErrorValue)resultVALUE.Result).Type);
            Assert.AreEqual(eErrorType.Num, ((ExcelErrorValue)resultNUM.Result).Type);
            Assert.AreEqual(eErrorType.Div0, ((ExcelErrorValue)resultDIV0.Result).Type);
            Assert.AreEqual(eErrorType.Ref, ((ExcelErrorValue)resultREF.Result).Type);
        }
コード例 #19
0
 public void AverageAShouldThrowValueExceptionIfNonNumericTextIsSupplied()
 {
     var func   = new AverageA();
     var args   = FunctionsHelper.CreateArgs(4d, 2d, 5d, 2d, "ABC");
     var result = func.Execute(args, _parsingContext);
 }