예제 #1
0
파일: Lookup.cs 프로젝트: nxoxn/EPPlus
        private CompileResult HandleTwoRanges(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            var searchedValue = arguments.ElementAt(0).Value;
            var firstAddress  = arguments.ElementAt(1)?.ValueAsRangeInfo?.Address;
            var secondAddress = arguments.ElementAt(2)?.ValueAsRangeInfo?.Address;

            if (firstAddress == null)
            {
                throw new ArgumentNullException("firstAddress");
            }
            if (secondAddress == null)
            {
                throw new ArgumentNullException("secondAddress");
            }
            var lookupIndex     = (secondAddress._fromCol - firstAddress._fromCol) + 1;
            var lookupOffset    = secondAddress._fromRow - firstAddress._fromRow;
            var lookupDirection = base.GetLookupDirection(firstAddress);

            if (lookupDirection == LookupDirection.Horizontal)
            {
                lookupIndex  = (secondAddress._fromRow - firstAddress._fromRow) + 1;
                lookupOffset = secondAddress._fromCol - firstAddress._fromCol;
            }
            var lookupArgs = new LookupArguments(searchedValue, firstAddress.Address, lookupIndex, lookupOffset, true, arguments.ElementAt(1).ValueAsRangeInfo);
            var navigator  = LookupNavigatorFactory.Create(lookupDirection, lookupArgs, context);

            return(base.Lookup(navigator, lookupArgs, new LookupValueMatcher()));
        }
예제 #2
0
        private CompileResult HandleTwoRanges(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            var searchedValue = arguments.ElementAt(0).Value;

            Require.That(arguments.ElementAt(1).Value).Named("firstAddress").IsNotNull();
            Require.That(arguments.ElementAt(2).Value).Named("secondAddress").IsNotNull();
            var firstAddress        = ArgToString(arguments, 1);
            var secondAddress       = ArgToString(arguments, 2);
            var rangeAddressFactory = new RangeAddressFactory(context.ExcelDataProvider);
            var address1            = rangeAddressFactory.Create(firstAddress);
            var address2            = rangeAddressFactory.Create(secondAddress);
            var lookupIndex         = (address2.FromCol - address1.FromCol) + 1;
            var lookupOffset        = address2.FromRow - address1.FromRow;
            var lookupDirection     = GetLookupDirection(address1);

            if (lookupDirection == LookupDirection.Horizontal)
            {
                lookupIndex  = (address2.FromRow - address1.FromRow) + 1;
                lookupOffset = address2.FromCol - address1.FromCol;
            }
            var lookupArgs = new LookupArguments(searchedValue, firstAddress, lookupIndex, lookupOffset, true);
            var navigator  = LookupNavigatorFactory.Create(lookupDirection, lookupArgs, context);

            return(Lookup(navigator, lookupArgs));
        }
예제 #3
0
파일: HLookup.cs 프로젝트: Eagle-Chan/KIS
 public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
 {
     ValidateArguments(arguments, 3);
     var lookupArgs = new LookupArguments(arguments);
     ThrowExcelErrorValueExceptionIf(() => lookupArgs.LookupIndex < 1, eErrorType.Value);
     var navigator = LookupNavigatorFactory.Create(LookupDirection.Horizontal, lookupArgs, context);
     return Lookup(navigator, lookupArgs);
 }
예제 #4
0
 public ArrayLookupNavigator(LookupDirection direction, LookupArguments arguments, ParsingContext parsingContext)
     : base(direction, arguments, parsingContext)
 {
     Require.That(arguments).Named("arguments").IsNotNull();
     Require.That(arguments.DataArray).Named("arguments.DataArray").IsNotNull();
     _arrayData = arguments.DataArray.ToArray();
     Initialize();
 }
예제 #5
0
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            ValidateArguments(arguments, 3);
            var lookupArgs = new LookupArguments(arguments);
            var navigator  = LookupNavigatorFactory.Create(LookupDirection.Vertical, lookupArgs, context);

            return(Lookup(navigator, lookupArgs));
        }
예제 #6
0
 public ArrayLookupNavigator(LookupDirection direction, LookupArguments arguments, ParsingContext parsingContext)
     : base(direction, arguments, parsingContext)
 {
     Require.That(arguments).Named("arguments").IsNotNull();
     Require.That(arguments.DataArray).Named("arguments.DataArray").IsNotNull();
     _arrayData = arguments.DataArray.ToArray();
     Initialize();
 }
예제 #7
0
 public LookupNavigator(LookupDirection direction, LookupArguments arguments, ParsingContext parsingContext)
 {
     Require.That(arguments).Named("arguments").IsNotNull();
     Require.That(parsingContext).Named("parsingContext").IsNotNull();
     Require.That(parsingContext.ExcelDataProvider).Named("parsingContext.ExcelDataProvider").IsNotNull();
     Direction      = direction;
     Arguments      = arguments;
     ParsingContext = parsingContext;
 }
예제 #8
0
 public LookupNavigator(LookupDirection direction, LookupArguments arguments, ParsingContext parsingContext)
 {
     Require.That(arguments).Named("arguments").IsNotNull();
     Require.That(parsingContext).Named("parsingContext").IsNotNull();
     Require.That(parsingContext.ExcelDataProvider).Named("parsingContext.ExcelDataProvider").IsNotNull();
     Direction = direction;
     Arguments = arguments;
     ParsingContext = parsingContext;
 }
예제 #9
0
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            ValidateArguments(arguments, 3);
            var lookupArgs = new LookupArguments(arguments, context);

            ThrowExcelErrorValueExceptionIf(() => lookupArgs.LookupIndex < 1, eErrorType.Value);
            var navigator = LookupNavigatorFactory.Create(LookupDirection.Horizontal, lookupArgs, context);

            return(Lookup(navigator, lookupArgs));
        }
예제 #10
0
 public void GetLookupValueShouldReturnCorrespondingValueWithOffset()
 {
     var provider = MockRepository.GenerateStub<ExcelDataProvider>();
     provider.Stub(x => x.GetDimensionEnd(Arg<string>.Is.Anything)).Return(new ExcelCellAddress(5, 5));
     provider.Stub(x => x.GetCellValue(WorksheetName, 1, 1)).Return(3);
     provider.Stub(x => x.GetCellValue(WorksheetName, 3, 3)).Return(4);
     var args = new LookupArguments(3, "A1:A4", 3, 2, false);
     var navigator = LookupNavigatorFactory.Create(LookupDirection.Vertical, args, GetContext(provider));
     Assert.AreEqual(4, navigator.GetLookupValue());
 }
예제 #11
0
        /// <summary>
        /// Executes the function with the specified <paramref name="arguments"/> in the specified <paramref name="context"/>.
        /// </summary>
        /// <param name="arguments">The arguments with which to evaluate the function.</param>
        /// <param name="context">The context in which to evaluate the function.</param>
        /// <returns>An address range <see cref="CompileResult"/> if successful, otherwise an error result.</returns>
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            if (this.ArgumentsAreValid(arguments, 2, out eErrorType argumentError) == false)
            {
                return(new CompileResult(argumentError));
            }

            if (!this.TryGetSearchValue(arguments.ElementAt(0), context, out var searchValue, out var error))
            {
                return(error);
            }

            var lookupRange     = arguments.ElementAt(1).ValueAsRangeInfo;
            var matchType       = this.GetMatchType(arguments);
            var args            = new LookupArguments(searchValue, lookupRange.Address.Address, 0, 0, false, lookupRange);
            var lookupDirection = this.GetLookupDirection(lookupRange.Address);
            var navigator       = LookupNavigatorFactory.Create(lookupDirection, args, context);
            int?lastValidIndex  = null;

            do
            {
                if (navigator.CurrentValue == null && searchValue == null)
                {
                    return(this.CreateResult(ExcelErrorValue.Create(eErrorType.NA), DataType.ExcelError));
                }
                int?matchResult;
                if (matchType == MatchType.ExactMatch)
                {
                    matchResult = new WildCardValueMatcher().IsMatch(searchValue, navigator.CurrentValue);
                }
                else
                {
                    matchResult = new LookupValueMatcher().IsMatch(searchValue, navigator.CurrentValue);
                }
                // For all match types, if the match result indicated equality, return the index (1 based)
                if (matchResult == 0)
                {
                    return(this.CreateResult(navigator.Index + 1, DataType.Integer));
                }
                if ((matchType == MatchType.ClosestBelow && matchResult > 0) || (matchType == MatchType.ClosestAbove && matchResult < 0))
                {
                    lastValidIndex = navigator.Index + 1;
                }
                // If matchType is ClosestBelow or ClosestAbove and the match result test failed, no more searching is required
                else if (matchType == MatchType.ClosestBelow || matchType == MatchType.ClosestAbove)
                {
                    break;
                }
            }while (navigator.MoveNext());
            if (lastValidIndex == null)
            {
                return(this.CreateResult(ExcelErrorValue.Create(eErrorType.NA), DataType.ExcelError));
            }
            return(this.CreateResult(lastValidIndex, DataType.Integer));
        }
예제 #12
0
 public static LookupNavigator Create(LookupDirection direction, LookupArguments args, ParsingContext parsingContext)
 {
     if (args.ArgumentDataType == LookupArguments.LookupArgumentDataType.ExcelRange)
     {
         return(new ExcelLookupNavigator(direction, args, parsingContext));
     }
     else if (args.ArgumentDataType == LookupArguments.LookupArgumentDataType.DataArray)
     {
         return(new ArrayLookupNavigator(direction, args, parsingContext));
     }
     throw new NotSupportedException("Invalid argument datatype");
 }
예제 #13
0
 public static LookupNavigator Create(LookupDirection direction, LookupArguments args, ParsingContext parsingContext)
 {
     if (args.ArgumentDataType == LookupArguments.LookupArgumentDataType.ExcelRange)
     {
         return new ExcelLookupNavigator(direction, args, parsingContext);
     }
     else if (args.ArgumentDataType == LookupArguments.LookupArgumentDataType.DataArray)
     {
         return new ArrayLookupNavigator(direction, args, parsingContext);
     }
     throw new NotSupportedException("Invalid argument datatype");
 }
예제 #14
0
        /// <summary>
        /// Executes the function with the specified <paramref name="arguments"/> in the specified <paramref name="context"/>.
        /// </summary>
        /// <param name="arguments">The arguments with which to evaluate the function.</param>
        /// <param name="context">The context in which to evaluate the function.</param>
        /// <returns>The <see cref="CompileResult"/>.</returns>
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            if (this.ArgumentsAreValid(arguments, 3, out eErrorType argumentError) == false)
            {
                return(new CompileResult(argumentError));
            }
            var lookupArgs = new LookupArguments(arguments, context);
            var navigator  = LookupNavigatorFactory.Create(LookupDirection.Vertical, lookupArgs, context);

            if (arguments.Count() > 3 && arguments.ElementAt(3).Value is bool rangeLookup && !rangeLookup)
            {
                return(Lookup(navigator, lookupArgs, new WildCardValueMatcher()));
            }
예제 #15
0
        protected CompileResult Lookup(LookupNavigator navigator, LookupArguments lookupArgs)
        {
            object lastValue       = null;
            object lastLookupValue = null;
            int?   lastMatchResult = null;

            if (lookupArgs.SearchedValue == null)
            {
                throw new ExcelErrorValueException("Lookupfunction failed to lookup value", ExcelErrorValue.Create(eErrorType.NA));
            }
            do
            {
                var matchResult = IsMatch(navigator.CurrentValue, lookupArgs.SearchedValue);
                if (matchResult != 0)
                {
                    if (lastValue != null && navigator.CurrentValue == null)
                    {
                        break;
                    }

                    if (lookupArgs.RangeLookup)
                    {
                        if (lastValue == null && matchResult > 0)
                        {
                            ThrowExcelErrorValueException(eErrorType.NA);
                        }
                        if (lastValue != null && matchResult > 0 && lastMatchResult < 0)
                        {
                            return(_compileResultFactory.Create(lastLookupValue));
                        }
                        lastMatchResult = matchResult;
                        lastValue       = navigator.CurrentValue;
                        lastLookupValue = navigator.GetLookupValue();
                    }
                }
                else
                {
                    return(_compileResultFactory.Create(navigator.GetLookupValue()));
                }
            }while (navigator.MoveNext());

            if (lookupArgs.RangeLookup)
            {
                return(_compileResultFactory.Create(lastLookupValue));
            }

            throw new ExcelErrorValueException("Lookupfunction failed to lookup value", ExcelErrorValue.Create(eErrorType.NA));
        }
예제 #16
0
파일: Match.cs 프로젝트: Eagle-Chan/KIS
        public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
        {
            ValidateArguments(arguments, 2);

            var searchedValue = arguments.ElementAt(0).Value;
            var address = ArgToString(arguments, 1);
            var rangeAddressFactory = new RangeAddressFactory(context.ExcelDataProvider);
            var rangeAddress = rangeAddressFactory.Create(address);
            var matchType = GetMatchType(arguments);
            var args = new LookupArguments(searchedValue, address, 0, 0, false);
            var lookupDirection = GetLookupDirection(rangeAddress);
            var navigator = LookupNavigatorFactory.Create(lookupDirection, args, context);
            int? lastMatchResult = default(int?);
            do
            {
                var matchResult = IsMatch(navigator.CurrentValue, searchedValue);
                if (matchType == MatchType.ClosestBelow && matchResult >= 0)
                {
                    if (!lastMatchResult.HasValue && matchResult > 0)
                    {
                        // TODO: error handling. This happens only if the first item is
                        // below the searched value.
                    }
                    var index = matchResult == 0 ? navigator.Index + 1 : navigator.Index;
                    return CreateResult(index, DataType.Integer);
                }
                if (matchType == MatchType.ClosestAbove && matchResult <= 0)
                {
                    if (!lastMatchResult.HasValue && matchResult < 0)
                    {
                        // TODO: error handling. This happens only if the first item is
                        // above the searched value
                    }
                    var index = matchResult == 0 ? navigator.Index + 1 : navigator.Index;
                    return CreateResult(index, DataType.Integer);
                }
                if (matchType == MatchType.ExactMatch && matchResult == 0)
                {
                    return CreateResult(navigator.Index + 1, DataType.Integer);
                }
                lastMatchResult = matchResult;
            }
            while (navigator.MoveNext());
            return CreateResult(null, DataType.Integer);
        }
예제 #17
0
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            ValidateArguments(arguments, 2);

            var searchedValue       = arguments.ElementAt(0).Value;
            var address             = ArgToString(arguments, 1);
            var rangeAddressFactory = new RangeAddressFactory(context.ExcelDataProvider);
            var rangeAddress        = rangeAddressFactory.Create(address);
            var matchType           = GetMatchType(arguments);
            var args            = new LookupArguments(searchedValue, address, 0, 0, false);
            var lookupDirection = GetLookupDirection(rangeAddress);
            var navigator       = LookupNavigatorFactory.Create(lookupDirection, args, context);
            int?lastMatchResult = default(int?);

            do
            {
                var matchResult = IsMatch(navigator.CurrentValue, searchedValue);
                if (matchType == MatchType.ClosestBelow && matchResult >= 0)
                {
                    if (!lastMatchResult.HasValue && matchResult > 0)
                    {
                        // TODO: error handling. This happens only if the first item is
                        // below the searched value.
                    }
                    var index = matchResult == 0 ? navigator.Index + 1 : navigator.Index;
                    return(CreateResult(index, DataType.Integer));
                }
                if (matchType == MatchType.ClosestAbove && matchResult <= 0)
                {
                    if (!lastMatchResult.HasValue && matchResult < 0)
                    {
                        // TODO: error handling. This happens only if the first item is
                        // above the searched value
                    }
                    var index = matchResult == 0 ? navigator.Index + 1 : navigator.Index;
                    return(CreateResult(index, DataType.Integer));
                }
                if (matchType == MatchType.ExactMatch && matchResult == 0)
                {
                    return(CreateResult(navigator.Index + 1, DataType.Integer));
                }
                lastMatchResult = matchResult;
            }while (navigator.MoveNext());
            return(CreateResult(null, DataType.Integer));
        }
예제 #18
0
        protected CompileResult Lookup(LookupNavigator navigator, LookupArguments lookupArgs)
        {
            object lastValue       = null;
            object lastLookupValue = null;
            int?   lastMatchResult = null;

            if (lookupArgs.SearchedValue == null)
            {
                return(new CompileResult(eErrorType.NA));
            }
            do
            {
                var matchResult = IsMatch(navigator.CurrentValue, lookupArgs.SearchedValue);
                if (matchResult != 0)
                {
                    if (lastValue != null && navigator.CurrentValue == null)
                    {
                        break;
                    }

                    if (!lookupArgs.RangeLookup)
                    {
                        continue;
                    }
                    if (lastValue == null && matchResult > 0)
                    {
                        return(new CompileResult(eErrorType.NA));
                    }
                    if (lastValue != null && matchResult > 0 && lastMatchResult < 0)
                    {
                        return(_compileResultFactory.Create(lastLookupValue));
                    }
                    lastMatchResult = matchResult;
                    lastValue       = navigator.CurrentValue;
                    lastLookupValue = navigator.GetLookupValue();
                }
                else
                {
                    return(_compileResultFactory.Create(navigator.GetLookupValue()));
                }
            }while (navigator.MoveNext());

            return(lookupArgs.RangeLookup ? _compileResultFactory.Create(lastLookupValue) : new CompileResult(eErrorType.NA));
        }
예제 #19
0
        protected CompileResult Lookup(LookupNavigator navigator, LookupArguments lookupArgs)
        {
            object lastValue = null;
            object lastLookupValue = null;
            int? lastMatchResult = null;
            if (lookupArgs.SearchedValue == null)
            {
                return new CompileResult(ExcelErrorValue.Create(eErrorType.NA), DataType.ExcelError);
            }
            do
            {
                var matchResult = IsMatch(navigator.CurrentValue, lookupArgs.SearchedValue);
                if (matchResult != 0)
                {
                    if (lastValue != null && navigator.CurrentValue == null) break;
                    
                    if (lookupArgs.RangeLookup)
                    {
                        if (lastValue == null && matchResult > 0)
                        {
                            ThrowExcelErrorValueException(eErrorType.NA);
                        }
                        if (lastValue != null && matchResult > 0 && lastMatchResult < 0)
                        {
                            return _compileResultFactory.Create(lastLookupValue);
                        }
                        lastMatchResult = matchResult;
                        lastValue = navigator.CurrentValue;
                        lastLookupValue = navigator.GetLookupValue();
                    }
                }
                else
                {
                    return _compileResultFactory.Create(navigator.GetLookupValue());
                }
            }
            while (navigator.MoveNext());

            if (lookupArgs.RangeLookup)
            {
                return _compileResultFactory.Create(lastLookupValue);
            }
            return new CompileResult(ExcelErrorValue.Create(eErrorType.NA), DataType.ExcelError);
        }
예제 #20
0
파일: VLookup.cs 프로젝트: acinep/epplus
 public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
 {
     Stopwatch sw = null;
     if (context.Debug)
     {
         sw = new Stopwatch();
         sw.Start();
     }
     ValidateArguments(arguments, 3);
     var lookupArgs = new LookupArguments(arguments);
     var navigator = LookupNavigatorFactory.Create(LookupDirection.Vertical, lookupArgs, context);
     var result = Lookup(navigator, lookupArgs);
     if (context.Debug)
     {
         sw.Stop();
         context.Configuration.Logger.LogFunction("VLOOKUP", sw.ElapsedMilliseconds);
     }
     return result;
 }
예제 #21
0
 private CompileResult HandleSingleRange(IEnumerable<FunctionArgument> arguments, ParsingContext context)
 {
     var searchedValue = arguments.ElementAt(0).Value;
     Require.That(arguments.ElementAt(1).Value).Named("firstAddress").IsNotNull();
     var firstAddress = ArgToString(arguments, 1);
     var rangeAddressFactory = new RangeAddressFactory(context.ExcelDataProvider);
     var address = rangeAddressFactory.Create(firstAddress);
     var nRows = address.ToRow - address.FromRow;
     var nCols = address.ToCol - address.FromCol;
     var lookupIndex = nCols + 1;
     var lookupDirection = LookupDirection.Vertical;
     if (nCols > nRows)
     {
         lookupIndex = nRows + 1;
         lookupDirection = LookupDirection.Horizontal;
     }
     var lookupArgs = new LookupArguments(searchedValue, firstAddress, lookupIndex, 0, true, arguments.ElementAt(1).ValueAsRangeInfo);
     var navigator = LookupNavigatorFactory.Create(lookupDirection, lookupArgs, context);
     return Lookup(navigator, lookupArgs);
 }
예제 #22
0
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            Stopwatch sw = null;

            if (context.Debug)
            {
                sw = new Stopwatch();
                sw.Start();
            }
            ValidateArguments(arguments, 3);
            var lookupArgs = new LookupArguments(arguments, context);
            var navigator  = LookupNavigatorFactory.Create(LookupDirection.Vertical, lookupArgs, context);
            var result     = Lookup(navigator, lookupArgs);

            if (context.Debug)
            {
                sw.Stop();
                context.Configuration.Logger.LogFunction("VLOOKUP", sw.ElapsedMilliseconds);
            }
            return(result);
        }
예제 #23
0
        private CompileResult HandleSingleRange(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            var searchedValue = arguments.ElementAt(0).Value;

            Require.That(arguments.ElementAt(1).Value).Named("firstAddress").IsNotNull();
            var firstAddress        = ArgToString(arguments, 1);
            var rangeAddressFactory = new RangeAddressFactory(context.ExcelDataProvider);
            var address             = rangeAddressFactory.Create(firstAddress);
            var nRows           = address.ToRow - address.FromRow;
            var nCols           = address.ToCol - address.FromCol;
            var lookupIndex     = nCols + 1;
            var lookupDirection = LookupDirection.Vertical;

            if (nCols > nRows)
            {
                lookupIndex     = nRows + 1;
                lookupDirection = LookupDirection.Horizontal;
            }
            var lookupArgs = new LookupArguments(searchedValue, firstAddress, lookupIndex, 0, true);
            var navigator  = LookupNavigatorFactory.Create(lookupDirection, lookupArgs, context);

            return(Lookup(navigator, lookupArgs));
        }
예제 #24
0
        public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
        {
            ValidateArguments(arguments, 2);

            var searchedValue = arguments.ElementAt(0).Value;
            var address =  arguments.ElementAt(1).IsExcelRange ? arguments.ElementAt(1).ValueAsRangeInfo.Address.FullAddress : ArgToString(arguments, 1);
            var rangeAddressFactory = new RangeAddressFactory(context.ExcelDataProvider);
            var rangeAddress = rangeAddressFactory.Create(address);
            var matchType = GetMatchType(arguments);
            var args = new LookupArguments(searchedValue, address, 0, 0, false, arguments.ElementAt(1).ValueAsRangeInfo);
            var lookupDirection = GetLookupDirection(rangeAddress);
            var navigator = LookupNavigatorFactory.Create(lookupDirection, args, context);
            int? lastValidIndex = null;
            do
            {
                var matchResult = IsMatch(navigator.CurrentValue, searchedValue);

                // For all match types, if the match result indicated equality, return the index (1 based)
                if (matchResult == 0)
                {
                    return CreateResult(navigator.Index + 1, DataType.Integer);
                }

                if ((matchType == MatchType.ClosestBelow && matchResult < 0) || (matchType == MatchType.ClosestAbove && matchResult > 0))
                {
                    lastValidIndex = navigator.Index + 1;
                }
                // If matchType is ClosestBelow or ClosestAbove and the match result test failed, no more searching is required
                else if (matchType == MatchType.ClosestBelow || matchType == MatchType.ClosestAbove)
                {
                    break;
                }
            }
            while (navigator.MoveNext());
            return CreateResult(lastValidIndex, DataType.Integer);
        }
예제 #25
0
        public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
        {
            ValidateArguments(arguments, 2);

            var searchedValue       = arguments.ElementAt(0).Value;
            var address             = ArgToAddress(arguments, 1, context);
            var rangeAddressFactory = new RangeAddressFactory(context.ExcelDataProvider);
            var rangeAddress        = rangeAddressFactory.Create(address);
            var matchType           = GetMatchType(arguments);
            var args            = new LookupArguments(searchedValue, address, 0, 0, false, arguments.ElementAt(1).ValueAsRangeInfo);
            var lookupDirection = GetLookupDirection(rangeAddress);
            var navigator       = LookupNavigatorFactory.Create(lookupDirection, args, context);
            int?lastValidIndex  = null;

            do
            {
                var matchResult = IsMatch(searchedValue, navigator.CurrentValue);

                // For all match types, if the match result indicated equality, return the index (1 based)
                if (matchResult == 0)
                {
                    return(CreateResult(navigator.Index + 1, DataType.Integer));
                }

                if ((matchType == MatchType.ClosestBelow && matchResult < 0) || (matchType == MatchType.ClosestAbove && matchResult > 0))
                {
                    lastValidIndex = navigator.Index + 1;
                }
                // If matchType is ClosestBelow or ClosestAbove and the match result test failed, no more searching is required
                else if (matchType == MatchType.ClosestBelow || matchType == MatchType.ClosestAbove)
                {
                    break;
                }
            }while (navigator.MoveNext());
            return(CreateResult(lastValidIndex, DataType.Integer));
        }
예제 #26
0
 public void Should_Return_ExcelLookupNavigator_When_Range_Is_Set()
 {
     var args = new LookupArguments(FunctionsHelper.CreateArgs(8, "A:B", 1));
     var navigator = LookupNavigatorFactory.Create(LookupDirection.Horizontal, args, _context);
     Assert.IsInstanceOfType(navigator, typeof(ExcelLookupNavigator));
 }
예제 #27
0
 public ExcelLookupNavigator(LookupDirection direction, LookupArguments arguments, ParsingContext parsingContext)
     : base(direction, arguments, parsingContext)
 {
     Initialize();
 }
예제 #28
0
 public void LookupArgumentsShouldSetRangeLookupToTrueWhenTrueIsSupplied()
 {
     var args = FunctionsHelper.CreateArgs(1, "A:B", 2, true);
     var lookupArgs = new LookupArguments(args);
     Assert.IsTrue(lookupArgs.RangeLookup);
 }
예제 #29
0
 public void LookupArgumentsShouldSetRangeLookupToTrueAsDefaultValue()
 {
     var args = FunctionsHelper.CreateArgs(1, "A:B", 2);
     var lookupArgs = new LookupArguments(args);
     Assert.IsTrue(lookupArgs.RangeLookup);
 }
예제 #30
0
 public void LookupArgumentsShouldSetColIndex()
 {
     var args = FunctionsHelper.CreateArgs(1, "A:B", 2);
     var lookupArgs = new LookupArguments(args);
     Assert.AreEqual(2, lookupArgs.LookupIndex);
 }
예제 #31
0
 public void LookupArgumentsShouldSetRangeAddress()
 {
     var args = FunctionsHelper.CreateArgs(1, "A:B", 2);
     var lookupArgs = new LookupArguments(args);
     Assert.AreEqual("A:B", lookupArgs.RangeAddress);
 }
예제 #32
0
 public void LookupArgumentsShouldSetSearchedValue()
 {
     var args = FunctionsHelper.CreateArgs(1, "A:B", 2);
     var lookupArgs = new LookupArguments(args);
     Assert.AreEqual(1, lookupArgs.SearchedValue);
 }
예제 #33
0
 public void Should_Return_ArrayLookupNavigator_When_Array_Is_Supplied()
 {
     var args = new LookupArguments(FunctionsHelper.CreateArgs(8, FunctionsHelper.CreateArgs(1,2), 1));
     var navigator = LookupNavigatorFactory.Create(LookupDirection.Horizontal, args, _context);
     Assert.IsInstanceOfType(navigator, typeof(ArrayLookupNavigator));
 }
예제 #34
0
 public ExcelLookupNavigator(LookupDirection direction, LookupArguments arguments, ParsingContext parsingContext)
     : base(direction, arguments, parsingContext)
 {
     Initialize();
 }
예제 #35
0
 private CompileResult HandleTwoRanges(IEnumerable<FunctionArgument> arguments, ParsingContext context)
 {
     var searchedValue = arguments.ElementAt(0).Value;
     Require.That(arguments.ElementAt(1).Value).Named("firstAddress").IsNotNull();
     Require.That(arguments.ElementAt(2).Value).Named("secondAddress").IsNotNull();
     var firstAddress = ArgToString(arguments, 1);
     var secondAddress = ArgToString(arguments, 2);
     var rangeAddressFactory = new RangeAddressFactory(context.ExcelDataProvider);
     var address1 = rangeAddressFactory.Create(firstAddress);
     var address2 = rangeAddressFactory.Create(secondAddress);
     var lookupIndex = (address2.FromCol - address1.FromCol) + 1;
     var lookupOffset = address2.FromRow - address1.FromRow;
     var lookupDirection = GetLookupDirection(address1);
     if (lookupDirection == LookupDirection.Horizontal)
     {
         lookupIndex = (address2.FromRow - address1.FromRow) + 1;
         lookupOffset = address2.FromCol - address1.FromCol;
     }
     var lookupArgs = new LookupArguments(searchedValue, firstAddress, lookupIndex, lookupOffset,  true, arguments.ElementAt(1).ValueAsRangeInfo);
     var navigator = LookupNavigatorFactory.Create(lookupDirection, lookupArgs, context);
     return Lookup(navigator, lookupArgs);
 }