protected CompileResult Lookup(LookupNavigator navigator, LookupArguments lookupArgs)
{
object lastValue = null;
object lastLookupValue = null;
int? lastMatchResult = null;
do
{
var matchResult = IsMatch(navigator.CurrentValue, lookupArgs.SearchedValue);
if (matchResult == 0)
{
return CreateResult(navigator.GetLookupValue(), DataType.String);
}
if (lookupArgs.RangeLookup)
{
if (lastValue != null && matchResult > 0 && lastMatchResult < 0)
{
return CreateResult(lastLookupValue, DataType.String);
}
lastMatchResult = matchResult;
lastValue = navigator.CurrentValue;
lastLookupValue = navigator.GetLookupValue();
}
}
while (navigator.MoveNext());
throw new ExcelFunctionException("Lookupfunction failed to lookup value", ExcelErrorCodes.NoValueAvaliable);
}
public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
{
ValidateArguments(arguments, 3);
var lookupArgs = new LookupArguments(arguments);
var navigator = new LookupNavigator(LookupDirection.Vertical, lookupArgs, context);
return Lookup(navigator, lookupArgs);
}
protected CompileResult Lookup(LookupNavigator navigator, LookupArguments lookupArgs)
{
object lastValue = null;
object lastLookupValue = null;
int? lastMatchResult = null;
do
{
var matchResult = IsMatch(navigator.CurrentValue, lookupArgs.SearchedValue);
if (matchResult == 0)
{
return(CreateResult(navigator.GetLookupValue(), DataType.String));
}
if (lookupArgs.RangeLookup)
{
if (lastValue != null && matchResult > 0 && lastMatchResult < 0)
{
return(CreateResult(lastLookupValue, DataType.String));
}
lastMatchResult = matchResult;
lastValue = navigator.CurrentValue;
lastLookupValue = navigator.GetLookupValue();
}
}while (navigator.MoveNext());
throw new ExcelFunctionException("Lookupfunction failed to lookup value", ExcelErrorCodes.NoValueAvaliable);
}
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 = new LookupNavigator(lookupDirection, lookupArgs, context);
return(Lookup(navigator, lookupArgs));
}
public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
{
ValidateArguments(arguments, 3);
var lookupArgs = new LookupArguments(arguments);
ThrowExcelFunctionExceptionIf(() => lookupArgs.LookupIndex < 1, ExcelErrorCodes.Value);
var navigator = new LookupNavigator(LookupDirection.Horizontal, lookupArgs, context);
return Lookup(navigator, lookupArgs);
}
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
ValidateArguments(arguments, 3);
var lookupArgs = new LookupArguments(arguments);
var navigator = new LookupNavigator(LookupDirection.Vertical, lookupArgs, context);
return(Lookup(navigator, lookupArgs));
}
public void CurrentValueShouldBeFirstCell()
{
var provider = MockRepository.GenerateStub<ExcelDataProvider>();
provider.Stub(x => x.GetCellValue(0, 0)).Return(new ExcelCell(3, null, 0, 0));
provider.Stub(x => x.GetCellValue(1, 0)).Return(new ExcelCell(4, null, 0, 0));
var args = GetArgs(3, "A1:B2", 1);
var navigator = new LookupNavigator(LookupDirection.Vertical, args, GetContext(provider));
Assert.AreEqual(3, navigator.CurrentValue);
}
public void HasNextShouldBeTrueIfNotLastCell()
{
var provider = MockRepository.GenerateStub<ExcelDataProvider>();
provider.Stub(x => x.GetCellValue(0, 0)).Return(new ExcelCell(3, null, 0, 0));
provider.Stub(x => x.GetCellValue(1, 0)).Return(new ExcelCell(4, null, 0, 0));
var args = GetArgs(3, "A1:B2", 1);
var navigator = new LookupNavigator(LookupDirection.Vertical, args, GetContext(provider));
Assert.IsTrue(navigator.MoveNext());
}
public void GetLookupValueShouldReturnCorrespondingValueWithOffset()
{
var provider = MockRepository.GenerateStub<ExcelDataProvider>();
provider.Stub(x => x.GetCellValue(0, 0)).Return(new ExcelCell(3, null, 0, 0));
provider.Stub(x => x.GetCellValue(2, 2)).Return(new ExcelCell(4, null, 0, 0));
var args = new LookupArguments(3, "A1:A4", 3, 2, false);
var navigator = new LookupNavigator(LookupDirection.Vertical, args, GetContext(provider));
Assert.AreEqual(4, navigator.GetLookupValue());
}
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
ValidateArguments(arguments, 3);
var lookupArgs = new LookupArguments(arguments);
ThrowExcelFunctionExceptionIf(() => lookupArgs.LookupIndex < 1, ExcelErrorCodes.Value);
var navigator = new LookupNavigator(LookupDirection.Horizontal, lookupArgs, context);
return(Lookup(navigator, lookupArgs));
}
public void MoveNextShouldIncreaseIndex()
{
var provider = MockRepository.GenerateStub<ExcelDataProvider>();
provider.Stub(x => x.GetCellValue(0, 0)).Return(new ExcelCell(3, null, 0, 0));
provider.Stub(x => x.GetCellValue(1, 0)).Return(new ExcelCell(4, null, 0, 0));
var args = GetArgs(6, "A1:B2", 1);
var navigator = new LookupNavigator(LookupDirection.Vertical, args, GetContext(provider));
Assert.AreEqual(0, navigator.Index);
navigator.MoveNext();
Assert.AreEqual(1, navigator.Index);
}
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 = new LookupNavigator(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 = new LookupNavigator(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);
}
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 = new LookupNavigator(lookupDirection, lookupArgs, context);
return Lookup(navigator, lookupArgs);
}
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 = new LookupNavigator(lookupDirection, lookupArgs, context);
return Lookup(navigator, lookupArgs);
}
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 = new LookupNavigator(lookupDirection, lookupArgs, context);
return(Lookup(navigator, lookupArgs));
}
public void NavigatorShouldEvaluateFormula()
{
var provider = MockRepository.GenerateStub<ExcelDataProvider>();
provider.Stub(x => x.GetCellValue(0, 0)).Return(new ExcelCell(3, null, 0, 0));
provider.Stub(x => x.GetCellValue(1, 0)).Return(new ExcelCell(null, "B5", 0, 0));
var args = GetArgs(4, "A1:B2", 1);
var context = GetContext(provider);
var parser = MockRepository.GenerateMock<FormulaParser>(provider);
context.Parser = parser;
var navigator = new LookupNavigator(LookupDirection.Vertical, args, context);
navigator.MoveNext();
parser.AssertWasCalled(x => x.Parse("B5"));
}
