public void Setup()
{
var provider = MockRepository.GenerateStub<ExcelDataProvider>();
_factory = new RangeAddressFactory(provider);
_lifeTimeEventHandler = MockRepository.GenerateStub<IParsingLifetimeEventHandler>();
_parsingScopes = MockRepository.GenerateStub<ParsingScopes>(_lifeTimeEventHandler);
}
public ExcelAddressExpression(string expression, ExcelDataProvider excelDataProvider, ParsingContext parsingContext, RangeAddressFactory rangeAddressFactory, bool negate)
: base(expression)
{
Require.That(excelDataProvider).Named("excelDataProvider").IsNotNull();
Require.That(parsingContext).Named("parsingContext").IsNotNull();
Require.That(rangeAddressFactory).Named("rangeAddressFactory").IsNotNull();
_excelDataProvider = excelDataProvider;
_parsingContext = parsingContext;
_rangeAddressFactory = rangeAddressFactory;
_negate = negate;
}
public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
{
if (arguments == null || arguments.Count() == 0)
{
return CreateResult(context.Scopes.Current.Address.FromCol, DataType.Integer);
}
var rangeAddress = ArgToString(arguments, 0);
if (!ExcelAddressUtil.IsValidAddress(rangeAddress))
throw new ArgumentException("An invalid argument was supplied");
var factory = new RangeAddressFactory(context.ExcelDataProvider);
var address = factory.Create(rangeAddress);
return CreateResult(address.FromCol, DataType.Integer);
}
private void Initialize()
{
_index = 0;
var factory = new RangeAddressFactory(ParsingContext.ExcelDataProvider);
if (Arguments.RangeInfo == null)
{
_rangeAddress = factory.Create(ParsingContext.Scopes.Current.Address.Worksheet, Arguments.RangeAddress);
}
else
{
_rangeAddress = factory.Create(Arguments.RangeInfo.Address.WorkSheet, Arguments.RangeInfo.Address.Address);
}
_currentCol = _rangeAddress.FromCol;
_currentRow = _rangeAddress.FromRow;
SetCurrentValue();
}
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);
}
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)
{
ValidateArguments(arguments, 1);
var r=arguments.ElementAt(0).ValueAsRangeInfo;
if (r != null)
{
return CreateResult(r.Address._toRow - r.Address._fromRow + 1, DataType.Integer);
}
else
{
var range = ArgToString(arguments, 0);
if (ExcelAddressUtil.IsValidAddress(range))
{
var factory = new RangeAddressFactory(context.ExcelDataProvider);
var address = factory.Create(range);
return CreateResult(address.ToRow - address.FromRow + 1, DataType.Integer);
}
}
throw new ArgumentException("Invalid range supplied");
}
public void CompileMultiCellReferenceAbsolute()
{
var parsingContext = ParsingContext.Create();
var file = new FileInfo("filename.xlsx");
using (var package = new ExcelPackage(file))
using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
using (var excelDataProvider = new EpplusExcelDataProvider(package))
{
var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
{
var expression = new ExcelAddressExpression("$A$1:$A$5", excelDataProvider, parsingContext);
var result = expression.Compile();
var rangeInfo = result.Result as ExcelDataProvider.IRangeInfo;
Assert.IsNotNull(rangeInfo);
Assert.AreEqual("$A$1:$A$5", rangeInfo.Address.Address);
// Enumerating the range still yields no results.
Assert.AreEqual(0, rangeInfo.Count());
}
}
}
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);
}
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 EpplusExcelDataProvider(ExcelPackage package)
{
_package = package;
_rangeAddressFactory = new RangeAddressFactory(this);
}
public void Setup()
{
var provider = MockRepository.GenerateStub<ExcelDataProvider>();
provider.Stub(x => x.ExcelMaxRows).Return(ExcelMaxRows);
_factory = new RangeAddressFactory(provider);
}
public void ColumnShouldReturnRowFromCurrentScopeIfNoAddressIsSupplied()
{
var func = new Column();
var parsingContext = ParsingContext.Create();
var rangeAddressFactory = new RangeAddressFactory(MockRepository.GenerateStub<ExcelDataProvider>());
parsingContext.Scopes.NewScope(rangeAddressFactory.Create("B2"));
var result = func.Execute(Enumerable.Empty<FunctionArgument>(), parsingContext);
Assert.AreEqual(2, result.Result);
}
public void CompileSingleCellReferenceResolveToRangeRowAbsolute()
{
var parsingContext = ParsingContext.Create();
var file = new FileInfo("filename.xlsx");
using (var package = new ExcelPackage(file))
using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
using (var excelDataProvider = new EpplusExcelDataProvider(package))
{
var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
{
var expression = new ExcelAddressExpression("$A1", excelDataProvider, parsingContext);
expression.ResolveAsRange = true;
var result = expression.Compile();
var rangeInfo = result.Result as ExcelDataProvider.IRangeInfo;
Assert.IsNotNull(rangeInfo);
Assert.AreEqual("$A1", rangeInfo.Address.Address);
}
}
}
public void CompileSingleCellReferenceWithValue()
{
var parsingContext = ParsingContext.Create();
var file = new FileInfo("filename.xlsx");
using (var package = new ExcelPackage(file))
using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
using (var excelDataProvider = new EpplusExcelDataProvider(package))
{
sheet.Cells[1, 1].Value = "Value";
var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
{
var expression = new ExcelAddressExpression("A1", excelDataProvider, parsingContext);
var result = expression.Compile();
Assert.AreEqual("Value", result.Result);
}
}
}
public void Setup()
{
var provider = MockRepository.GenerateStub<ExcelDataProvider>();
_factory = new RangeAddressFactory(provider);
}
public void CompileMultiCellReferenceWithValues()
{
var parsingContext = ParsingContext.Create();
var file = new FileInfo("filename.xlsx");
using (var package = new ExcelPackage(file))
using (var sheet = package.Workbook.Worksheets.Add("NewSheet"))
using (var excelDataProvider = new EpplusExcelDataProvider(package))
{
sheet.Cells[1, 1].Value = "Value1";
sheet.Cells[2, 1].Value = "Value2";
sheet.Cells[3, 1].Value = "Value3";
sheet.Cells[4, 1].Value = "Value4";
sheet.Cells[5, 1].Value = "Value5";
var rangeAddressFactory = new RangeAddressFactory(excelDataProvider);
using (parsingContext.Scopes.NewScope(rangeAddressFactory.Create("NewSheet", 3, 3)))
{
var expression = new ExcelAddressExpression("A1:A5", excelDataProvider, parsingContext);
var result = expression.Compile();
var rangeInfo = result.Result as ExcelDataProvider.IRangeInfo;
Assert.IsNotNull(rangeInfo);
Assert.AreEqual("A1:A5", rangeInfo.Address.Address);
Assert.AreEqual(5, rangeInfo.Count());
for (int i = 1; i <= 5; i++)
{
var rangeItem = rangeInfo.ElementAt(i - 1);
Assert.AreEqual("Value" + i, rangeItem.Value);
}
}
}
}
