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()));
}
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));
}
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);
}
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();
}
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));
}
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();
}
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;
}
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;
}
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));
}
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());
}
/// <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));
}
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");
}
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");
}
/// <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()));
}
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));
}
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);
}
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));
}
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));
}
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);
}
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;
}
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);
}
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);
}
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));
}
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);
}
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));
}
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));
}
public ExcelLookupNavigator(LookupDirection direction, LookupArguments arguments, ParsingContext parsingContext)
: base(direction, arguments, parsingContext)
{
Initialize();
}
public void LookupArgumentsShouldSetRangeLookupToTrueWhenTrueIsSupplied()
{
var args = FunctionsHelper.CreateArgs(1, "A:B", 2, true);
var lookupArgs = new LookupArguments(args);
Assert.IsTrue(lookupArgs.RangeLookup);
}
public void LookupArgumentsShouldSetRangeLookupToTrueAsDefaultValue()
{
var args = FunctionsHelper.CreateArgs(1, "A:B", 2);
var lookupArgs = new LookupArguments(args);
Assert.IsTrue(lookupArgs.RangeLookup);
}
public void LookupArgumentsShouldSetColIndex()
{
var args = FunctionsHelper.CreateArgs(1, "A:B", 2);
var lookupArgs = new LookupArguments(args);
Assert.AreEqual(2, lookupArgs.LookupIndex);
}
public void LookupArgumentsShouldSetRangeAddress()
{
var args = FunctionsHelper.CreateArgs(1, "A:B", 2);
var lookupArgs = new LookupArguments(args);
Assert.AreEqual("A:B", lookupArgs.RangeAddress);
}
public void LookupArgumentsShouldSetSearchedValue()
{
var args = FunctionsHelper.CreateArgs(1, "A:B", 2);
var lookupArgs = new LookupArguments(args);
Assert.AreEqual(1, lookupArgs.SearchedValue);
}
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));
}
public ExcelLookupNavigator(LookupDirection direction, LookupArguments arguments, ParsingContext parsingContext)
: base(direction, arguments, parsingContext)
{
Initialize();
}
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);
}
