public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
ValidateArguments(arguments, 3);
var dbAddress = arguments.ElementAt(0).ValueAsRangeInfo.Address.Address;
var field = ArgToString(arguments, 1).ToLower(CultureInfo.InvariantCulture);
var criteriaRange = arguments.ElementAt(2).ValueAsRangeInfo.Address.Address;
var db = new ExcelDatabase(context.ExcelDataProvider, dbAddress);
var criteria = new ExcelDatabaseCriteria(context.ExcelDataProvider, criteriaRange);
var nHits = 0;
object retVal = null;
while (db.HasMoreRows)
{
var dataRow = db.Read();
if (!RowMatcher.IsMatch(dataRow, criteria))
{
continue;
}
if (++nHits > 1)
{
return(CreateResult(ExcelErrorValue.Values.Num, DataType.ExcelError));
}
retVal = dataRow[field];
}
return(new CompileResultFactory().Create(retVal));
}
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
if (this.ArgumentsAreValid(arguments, 2, out eErrorType argumentError) == false)
{
return(new CompileResult(argumentError));
}
var dbAddress = arguments.ElementAt(0).ValueAsRangeInfo.Address.Address;
string field = null;
string criteriaRange = null;
if (arguments.Count() == 2)
{
criteriaRange = arguments.ElementAt(1).ValueAsRangeInfo.Address.Address;
}
else
{
field = ArgToString(arguments, 1).ToLower(CultureInfo.InvariantCulture);
criteriaRange = arguments.ElementAt(2).ValueAsRangeInfo.Address.Address;
}
var db = new ExcelDatabase(context.ExcelDataProvider, dbAddress);
var criteria = new ExcelDatabaseCriteria(context.ExcelDataProvider, criteriaRange);
var nHits = 0;
while (db.HasMoreRows)
{
var dataRow = db.Read();
if (RowMatcher.IsMatch(dataRow, criteria))
{
// if a fieldname is supplied, count only this row if the value
// of the supplied field is not blank.
if (!string.IsNullOrEmpty(field))
{
var candidate = dataRow[field];
if (ShouldCount(candidate))
{
nHits++;
}
}
else
{
// no fieldname was supplied, always count matching row.
nHits++;
}
}
}
return(CreateResult(nHits, DataType.Integer));
}
public void IsMatchShouldMatchStrings1()
{
var data = new ExcelDatabaseRow();
data["Crit1"] = "1";
data["Crit2"] = 2;
data["Crit3"] = 3;
var crit = new Dictionary<ExcelDatabaseCriteriaField, object>();
crit[new ExcelDatabaseCriteriaField("Crit1")] = "1";
crit[new ExcelDatabaseCriteriaField("Crit3")] = 3;
var matcher = new RowMatcher();
var criteria = GetCriteria(crit);
Assert.IsTrue(matcher.IsMatch(data, criteria));
}
public void IsMatchShouldReturnFalseIfCriteriasDoesNotMatch()
{
var data = new ExcelDatabaseRow();
data["Crit1"] = 1;
data["Crit2"] = 2;
data["Crit3"] = 3;
var crit = new Dictionary<ExcelDatabaseCriteriaField, object>();
crit[new ExcelDatabaseCriteriaField("Crit1")] = 1;
crit[new ExcelDatabaseCriteriaField("Crit3")] = 4;
var matcher = new RowMatcher();
var criteria = GetCriteria(crit);
Assert.IsFalse(matcher.IsMatch(data, criteria));
}