Example #1
0
 /// <summary>
 /// Throws an <see cref="ExcelErrorValueException"/> with the type of given <paramref name="value"/> set.
 /// </summary>
 /// <param name="value"></param>
 protected void ThrowExcelErrorValueException(ExcelErrorValue value)
 {
     if (value != null)
     {
         throw new ExcelErrorValueException(value.Type);
     }
 }
Example #2
0
 public virtual IEnumerable <ExcelDoubleCellValue> ConvertArgs(bool ignoreHidden, bool ignoreErrors, IEnumerable <FunctionArgument> arguments, ParsingContext context)
 {
     return(base.FuncArgsToFlatEnumerable(arguments, (arg, argList) =>
     {
         if (arg.IsExcelRange)
         {
             foreach (var cell in arg.ValueAsRangeInfo)
             {
                 if (!ignoreErrors && cell.IsExcelError)
                 {
                     throw new ExcelErrorValueException(ExcelErrorValue.Parse(cell.Value.ToString()));
                 }
                 if (!CellStateHelper.ShouldIgnore(ignoreHidden, cell, context) && ConvertUtil.IsNumeric(cell.Value))
                 {
                     var val = new ExcelDoubleCellValue(cell.ValueDouble, cell.Row);
                     argList.Add(val);
                 }
             }
         }
         else
         {
             if (!ignoreErrors && arg.ValueIsExcelError)
             {
                 throw new ExcelErrorValueException(arg.ValueAsExcelErrorValue);
             }
             if (ConvertUtil.IsNumeric(arg.Value) && !CellStateHelper.ShouldIgnore(ignoreHidden, arg, context))
             {
                 var val = new ExcelDoubleCellValue(ConvertUtil.GetValueDouble(arg.Value));
                 argList.Add(val);
             }
         }
     }));
 }
Example #3
0
        public void ExternalWorkbookReferencesPoundRefAsNamedRanges()
        {
            var testFile = new FileInfo(@"externalreferences.xlsx");
            var tempFile = new FileInfo(Path.GetTempFileName());

            if (tempFile.Exists)
            {
                tempFile.Delete();
            }
            testFile.CopyTo(tempFile.FullName);
            try
            {
                using (var package = new ExcelPackage(tempFile))
                {
                    Assert.AreEqual(2, package.Workbook.ExternalReferences.References.Count);
                    var sheet = package.Workbook.Worksheets.First();
                    sheet.Cells["F15"].Calculate();
                    Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Ref), sheet.Cells["F15"].Value);
                }
            }
            finally
            {
                tempFile.Delete();
            }
        }
        public override object Parse(object obj)
        {
            Require.That(obj).Named("argument").IsNotNull();
            if (obj is ExcelDataProvider.IRangeInfo)
            {
                var r = ((ExcelDataProvider.IRangeInfo)obj).FirstOrDefault();
                return(r == null ? 0 : r.ValueDouble);
            }
            if (obj is double)
            {
                return(obj);
            }
            if (obj.IsNumeric())
            {
                return(util.ConvertUtil.GetValueDouble(obj));
            }
            var str = obj != null?obj.ToString() : string.Empty;

            try
            {
                double d;
                if (double.TryParse(str, NumberStyles.Any, CultureInfo.InvariantCulture, out d))
                {
                    return(d);
                }

                return(System.DateTime.Parse(str, CultureInfo.CurrentCulture, DateTimeStyles.None).ToOADate());
            }
            catch// (Exception e)
            {
                throw new ExcelErrorValueException(ExcelErrorValue.Create(eErrorType.Value));
            }
        }
Example #5
0
 /// <summary>
 /// If the supplied <paramref name="cell"/> contains an Excel error
 /// an <see cref="ExcelErrorValueException"/> with that errorcode will be thrown
 /// </summary>
 /// <param name="cell"></param>
 protected void CheckForAndHandleExcelError(ExcelDataProvider.ICellInfo cell)
 {
     if (cell.IsExcelError)
     {
         throw (new ExcelErrorValueException(ExcelErrorValue.Parse(cell.Value.ToString())));
     }
 }
Example #6
0
        public void DAverageShouldReturnCorrectResult()
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("test");
                // database
                sheet.Cells["A1"].Value = "crit1";
                sheet.Cells["B1"].Value = "crit2";

                sheet.Cells["A2"].Value = "test";
                sheet.Cells["B2"].Value = 2;

                sheet.Cells["A3"].Value = "tesst";
                sheet.Cells["B3"].Value = 1;
                // criteria
                sheet.Cells["D1"].Value = "crit1";
                sheet.Cells["D2"].Value = "t*t";

                // function
                sheet.Cells["F1"].Formula = "DAVERAGE(A1:B3,\"Crit2\",D1:E2)";
                sheet.Cells["F2"].Formula = "DAVERAGE(\"Too few arguments\")";
                sheet.Workbook.Calculate();

                Assert.AreEqual(1.5d, sheet.Cells["F1"].Value);
                Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Value), sheet.Cells["F2"].Value);
            }
        }
Example #7
0
File: Find.cs Project: nxoxn/EPPlus
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            var functionArguments = arguments as FunctionArgument[] ?? arguments.ToArray();

            if (this.ArgumentsAreValid(functionArguments, 2, out eErrorType argumentError) == false)
            {
                return(new CompileResult(argumentError));
            }
            var search     = ArgToString(functionArguments, 0);
            var searchIn   = ArgToString(functionArguments, 1);
            var startIndex = 0;

            if (functionArguments.Count() > 2)
            {
                startIndex = ArgToInt(functionArguments, 2);
            }
            var result = searchIn.IndexOf(search, startIndex, System.StringComparison.Ordinal);

            if (result == -1)
            {
                return(CreateResult(ExcelErrorValue.Create(eErrorType.Value), DataType.ExcelError));
            }
            // Adding 1 because Excel uses 1-based index
            return(CreateResult(result + 1, DataType.Integer));
        }
Example #8
0
        public override object Parse(object obj)
        {
            Require.That(obj).Named("argument").IsNotNull();
            int result;

            if (obj is ExcelDataProvider.IRangeInfo)
            {
                var r = ((ExcelDataProvider.IRangeInfo)obj).FirstOrDefault();
                return(r == null ? 0 : Convert.ToInt32(r.ValueDouble));
            }
            var objType = obj.GetType();

            if (objType == typeof(int))
            {
                return((int)obj);
            }
            if (objType == typeof(double) || objType == typeof(decimal))
            {
                return(Convert.ToInt32(obj));
            }
            if (!int.TryParse(obj.ToString(), out result))
            {
                throw new ExcelErrorValueException(ExcelErrorValue.Create(eErrorType.Value));
            }
            return(result);
        }
Example #9
0
        public override CompileResult Compile(IEnumerable <Expression> children)
        {
            if (children.Count() != 2)
            {
                return(new CompileResult(eErrorType.Value));
            }
            var args = new List <FunctionArgument>();

            Function.BeforeInvoke(Context);
            var firstChild = children.First();
            var lastChild  = children.ElementAt(1);

            try
            {
                var result = firstChild.Compile();
                if (result.DataType == DataType.ExcelError && (Equals(result.Result,
                                                                      ExcelErrorValue.Create(eErrorType.NA))))
                {
                    args.Add(new FunctionArgument(lastChild.Compile().Result));
                }
                else
                {
                    args.Add(new FunctionArgument(result.Result));
                }
            }
            catch (ExcelErrorValueException)
            {
                args.Add(new FunctionArgument(lastChild.Compile().Result));
            }
            return(Function.Execute(args, Context));
        }
Example #10
0
 internal CompileResult Execute(string dateString)
 {
     System.DateTime.TryParse(dateString, out System.DateTime result);
     return(result != System.DateTime.MinValue ?
            CreateResult(result.ToOADate(), DataType.Date) :
            CreateResult(ExcelErrorValue.Create(eErrorType.Value), DataType.ExcelError));
 }
Example #11
0
        public void ObjectMatchesCriteriaWithInequalitiesAndErrorValues()
        {
            var nullError        = ExcelErrorValue.Create(eErrorType.Null);
            var div0Error        = ExcelErrorValue.Create(eErrorType.Div0);
            var valueError       = ExcelErrorValue.Create(eErrorType.Value);
            var refError         = ExcelErrorValue.Create(eErrorType.Ref);
            var nameError        = ExcelErrorValue.Create(eErrorType.Name);
            var numError         = ExcelErrorValue.Create(eErrorType.Num);
            var naError          = ExcelErrorValue.Create(eErrorType.NA);
            var nullLessThanDiv  = IfHelper.ObjectMatchesCriterion(nullError, "<#DIV/0!");
            var divLessThanValue = IfHelper.ObjectMatchesCriterion(div0Error, "<#VALUE!");;
            var valueLessThanRef = IfHelper.ObjectMatchesCriterion(valueError, "<#REF!");;
            var refLessThanName  = IfHelper.ObjectMatchesCriterion(refError, "<#NAME?");;
            var nameLessThanNum  = IfHelper.ObjectMatchesCriterion(nameError, "<#NUM!");;
            var numLessThanNA    = IfHelper.ObjectMatchesCriterion(numError, "<#N/A");
            var naGreaterThanNum = IfHelper.ObjectMatchesCriterion(naError, ">#NUM!");;

            Assert.AreEqual(nullLessThanDiv, true);
            Assert.AreEqual(divLessThanValue, true);
            Assert.AreEqual(valueLessThanRef, true);
            Assert.AreEqual(refLessThanName, true);
            Assert.AreEqual(nameLessThanNum, true);
            Assert.AreEqual(numLessThanNA, true);
            Assert.AreEqual(naGreaterThanNum, true);
        }
Example #12
0
        public override CompileResult Compile()
        {
            try
            {
                var funcName = ExpressionString;

                // older versions of Excel (pre 2007) adds "_xlfn." in front of some function names for compatibility reasons.
                // EPPlus implements most of these functions, so we just remove this.
                if (funcName.StartsWith("_xlfn."))
                {
                    funcName = funcName.Replace("_xlfn.", string.Empty);
                }

                var function = _parsingContext.Configuration.FunctionRepository.GetFunction(funcName);
                if (function == null)
                {
                    // Handle unrecognized func name
                    var pipeline = new FunctionsPipeline(_parsingContext, Children);
                    function = pipeline.FindFunction(funcName);
                    if (function == null)
                    {
                        if (_parsingContext.Debug)
                        {
                            _parsingContext.Configuration.Logger.Log(_parsingContext, string.Format("'{0}' is not a supported function", funcName));
                        }
                        return(new CompileResult(ExcelErrorValue.Create(eErrorType.Name), DataType.ExcelError));
                    }
                }
                if (_parsingContext.Debug)
                {
                    _parsingContext.Configuration.Logger.LogFunction(funcName);
                }
                var compiler = _functionCompilerFactory.Create(function);
                var result   = compiler.Compile(HasChildren ? Children : Enumerable.Empty <Expression>());
                if (_isNegated)
                {
                    if (!result.IsNumeric)
                    {
                        if (_parsingContext.Debug)
                        {
                            var msg = string.Format("Trying to negate a non-numeric value ({0}) in function '{1}'",
                                                    result.Result, funcName);
                            _parsingContext.Configuration.Logger.Log(_parsingContext, msg);
                        }
                        return(new CompileResult(ExcelErrorValue.Create(eErrorType.Value), DataType.ExcelError));
                    }
                    return(new CompileResult(result.ResultNumeric * -1, result.DataType));
                }
                return(result);
            }
            catch (ExcelErrorValueException e)
            {
                if (_parsingContext.Debug)
                {
                    _parsingContext.Configuration.Logger.Log(_parsingContext, e);
                }
                return(new CompileResult(e.ErrorValue, DataType.ExcelError));
            }
        }
Example #13
0
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            ValidateArguments(arguments, 1);
            double result = 0d;
            List <List <double> > results = new List <List <double> >();

            foreach (var arg in arguments)
            {
                results.Add(new List <double>());
                var currentResult = results.Last();
                if (arg.Value is IEnumerable <FunctionArgument> )
                {
                    foreach (var val in (IEnumerable <FunctionArgument>)arg.Value)
                    {
                        AddValue(val.Value, currentResult);
                    }
                }
                else if (arg.Value is FunctionArgument)
                {
                    AddValue(arg.Value, currentResult);
                }
                else if (arg.IsExcelRange)
                {
                    var r = arg.ValueAsRangeInfo;
                    for (int col = r.Address._fromCol; col <= r.Address._toCol; col++)
                    {
                        for (int row = r.Address._fromRow; row <= r.Address._toRow; row++)
                        {
                            AddValue(r.GetValue(row, col), currentResult);
                        }
                    }
                }
                else if (IsNumeric(arg.Value))
                {
                    AddValue(arg.Value, currentResult);
                }
            }
            // Validate that all supplied lists have the same length
            var arrayLength = results.First().Count;

            foreach (var list in results)
            {
                if (list.Count != arrayLength)
                {
                    throw new ExcelErrorValueException(ExcelErrorValue.Create(eErrorType.Value));
                    //throw new ExcelFunctionException("All supplied arrays must have the same length", ExcelErrorCodes.Value);
                }
            }
            for (var rowIndex = 0; rowIndex < arrayLength; rowIndex++)
            {
                double rowResult = 1;
                for (var colIndex = 0; colIndex < results.Count; colIndex++)
                {
                    rowResult *= results[colIndex][rowIndex];
                }
                result += rowResult;
            }
            return(CreateResult(result, DataType.Decimal));
        }
Example #14
0
        public void IfErrorWithStringAndErrorReturnsCorrectResult()
        {
            var function  = new IfError();
            var arguments = FunctionsHelper.CreateArgs("word", ExcelErrorValue.Create(eErrorType.Num));
            var result    = function.Execute(arguments, this.ParsingContext);

            Assert.AreEqual("word", result.Result);
        }
Example #15
0
        public void IfErrorWithErrorAndNullArgumentReturnsCorrectResult()
        {
            var function  = new IfError();
            var arguments = FunctionsHelper.CreateArgs(ExcelErrorValue.Create(eErrorType.Name), null);
            var result    = function.Execute(arguments, this.ParsingContext);

            Assert.AreEqual(0, result.Result);
        }
Example #16
0
        public void FromCompileResultErrorTypeEnum()
        {
            var compileResult = new CompileResult(eErrorType.Value, DataType.ExcelError);
            var result        = new ExpressionConverter().FromCompileResult(compileResult);

            Assert.IsInstanceOfType(result, typeof(ExcelErrorExpression));
            Assert.AreEqual(ExcelErrorValue.Parse("#VALUE!"), result.Compile().Result);
        }
Example #17
0
        public void FromCompileResultExcelErrorUnknown()
        {
            var compileResult = new CompileResult(ExcelErrorValue.Parse("#N/A"), DataType.Unknown);
            var result        = new ExpressionConverter().FromCompileResult(compileResult);

            Assert.IsInstanceOfType(result, typeof(ExcelErrorExpression));
            Assert.AreEqual(ExcelErrorValue.Parse("#N/A"), result.Compile().Result);
        }
Example #18
0
        public void IsErrorShouldReturnTrueIfArgIsAnErrorCode()
        {
            var args   = FunctionsHelper.CreateArgs(ExcelErrorValue.Parse("#DIV/0!"));
            var func   = new IsError();
            var result = func.Execute(args, _context);

            Assert.IsTrue((bool)result.Result);
        }
Example #19
0
        public void IfErrorWithErrorAndEmptyStringReturnsCorrectResult()
        {
            var function  = new IfError();
            var arguments = FunctionsHelper.CreateArgs(ExcelErrorValue.Create(eErrorType.Div0), string.Empty);
            var result    = function.Execute(arguments, this.ParsingContext);

            Assert.AreEqual(string.Empty, result.Result);
        }
Example #20
0
        public void FloorWithOneArgumentPoundValues()
        {
            var func   = new Floor();
            var args   = FunctionsHelper.CreateArgs(9);
            var result = func.Execute(args, _parsingContext);

            Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Value), result.Result);
        }
Example #21
0
        public void FloorOfPositiveIntoNegativeNumberIsPoundNum()
        {
            var func   = new Floor();
            var args   = FunctionsHelper.CreateArgs(2.5, -2);
            var result = func.Execute(args, _parsingContext);

            Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Num), result.Result);
        }
Example #22
0
        public void FloorWithZeroSignificancePoundDivZeroes()
        {
            var func   = new Floor();
            var args   = FunctionsHelper.CreateArgs(9, 0);
            var result = func.Execute(args, _parsingContext);

            Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Div0), result.Result);
        }
Example #23
0
        public void CreateNameErrorStringResult()
        {
            string        value         = "#NAME?";
            CompileResult compileResult = new CompileResultFactory().Create(value);

            Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Name), compileResult.Result);
            Assert.AreEqual(DataType.ExcelError, compileResult.DataType);
        }
Example #24
0
 public void MatchExactNotFound()
 {
     this.Worksheet.Cells["A1"].Value   = 5d;
     this.Worksheet.Cells["A2"].Value   = 1d;
     this.Worksheet.Cells["A3"].Value   = 3d;
     this.Worksheet.Cells["A4"].Formula = "MATCH(2,A1:A3,0)";
     this.Worksheet.Calculate();
     Assert.AreEqual(ExcelErrorValue.Create(eErrorType.NA), this.Worksheet.Cells["A4"].Value);
 }
Example #25
0
        public void CeilingShouldThrowExceptionIfNumberIsPositiveAndSignificanceIsNegative()
        {
            var expectedValue = ExcelErrorValue.Parse("#NUM!");
            var func          = new Ceiling();
            var args          = FunctionsHelper.CreateArgs(22.35d, -1);
            var result        = func.Execute(args, _parsingContext).Result;

            Assert.AreEqual(expectedValue, result);
        }
Example #26
0
        public void ShouldIgnoreErrorInCriteriaRange()
        {
            _sheet.Cells["B3"].Value = ExcelErrorValue.Create(eErrorType.Div0);

            _sheet.Cells["A5"].Formula = "SUMIFS(A1:A4;B1:B5;\">5\";C1:C5;\">4\")";
            _sheet.Calculate();

            Assert.AreEqual(6d, _sheet.Cells["A5"].Value);
        }
Example #27
0
 /// <summary>
 /// Instantiates a new <see cref="CompileResult"/> for an error value.
 /// </summary>
 /// <param name="errorValue">The <see cref="ExcelErrorValue"/> result.</param>
 public CompileResult(ExcelErrorValue errorValue)
 {
     if (errorValue == null)
     {
         throw new ArgumentNullException(nameof(errorValue));
     }
     this.Result   = errorValue;
     this.DataType = DataType.ExcelError;
 }
Example #28
0
 public void IndexHandlesNAError()
 {
     this.Worksheet.Cells["A1"].Value   = 1d;
     this.Worksheet.Cells["A2"].Value   = 3d;
     this.Worksheet.Cells["A3"].Value   = 5d;
     this.Worksheet.Cells["A4"].Value   = ExcelErrorValue.Create(eErrorType.NA);
     this.Worksheet.Cells["A5"].Formula = "INDEX(A1:A3,A4)";
     this.Worksheet.Calculate();
     Assert.AreEqual(ExcelErrorValue.Create(eErrorType.NA), this.Worksheet.Cells["A5"].Value);
 }
Example #29
0
 public void CalculateWithInvalidDateValue()
 {
     using (ExcelPackage package = new ExcelPackage())
     {
         var sheet = package.Workbook.Worksheets.Add("Sheet1");
         sheet.Cells[2, 2].Formula = "YEAR(\"30/12/15\")";
         sheet.Calculate();
         Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Value), sheet.Cells[2, 2].Value);
     }
 }
Example #30
0
        internal CompileResult Execute(string dateString)
        {
            this.TryParseDateStringToDouble(dateString, out double resultAfterParse);

            var resultDecimalsOnly = resultAfterParse - System.Math.Truncate(resultAfterParse);

            return(resultAfterParse != -1 ?                     //The '-1' is used to throw an error if an invalid input is supplied.
                   CreateResult(resultDecimalsOnly, DataType.Decimal) :
                   CreateResult(ExcelErrorValue.Create(eErrorType.Value), DataType.ExcelError));
        }
Example #31
0
 public CompileResult(ExcelErrorValue errorValue)
 {
     Require.Argument(errorValue).IsNotNull("errorValue");
     Result = errorValue;
     DataType = DataType.ExcelError;
 }
Example #32
0
 public ExcelErrorExpression(ExcelErrorValue error)
     : this(error.ToString(), error)
 {
     
 }
Example #33
0
 public ExcelErrorExpression(string expression, ExcelErrorValue error)
     : base(expression)
 {
     _error = error;
 }
Example #34
0
 private static bool  EitherIsError(CompileResult l, CompileResult r, out ExcelErrorValue errorVal)
 {
     if (l.DataType == DataType.ExcelError)
     {
         errorVal = (ExcelErrorValue) l.Result;
         return true;
     }
     if (r.DataType == DataType.ExcelError)
     {
         errorVal = (ExcelErrorValue) r.Result;
         return true;
     }
     errorVal = null;
     return false;
 }
 public ExcelErrorValueException(string message, ExcelErrorValue error)
     : base(message)
 {
     ErrorValue = error;
 }
 public ExcelErrorValueException(ExcelErrorValue error)
     : this(error.ToString(), error)
 {
     
 }