protected IEnumerable <double> GetMatchingValues(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
var dbAddress = arguments.ElementAt(0).ValueAsRangeInfo.Address.Address;
//var field = ArgToString(arguments, 1).ToLower(CultureInfo.InvariantCulture);
var field = arguments.ElementAt(1).Value;
var criteriaRange = arguments.ElementAt(2).ValueAsRangeInfo.Address.Address;
var db = new ExcelDatabase(context.ExcelDataProvider, dbAddress);
var criteria = new ExcelDatabaseCriteria(context.ExcelDataProvider, criteriaRange);
var values = new List <double>();
while (db.HasMoreRows)
{
var dataRow = db.Read();
if (!RowMatcher.IsMatch(dataRow, criteria))
{
continue;
}
var candidate = ConvertUtil.IsNumeric(field) ? dataRow[(int)ConvertUtil.GetValueDouble(field)] : dataRow[field.ToString().ToLower(CultureInfo.InvariantCulture)];
if (ConvertUtil.IsNumeric(candidate))
{
values.Add(ConvertUtil.GetValueDouble(candidate));
}
}
return(values);
}
public bool IsMatch(ExcelDatabaseRow row, ExcelDatabaseCriteria criteria)
{
var retVal = true;
foreach (var c in criteria.Items)
{
var candidate = c.Key.FieldIndex.HasValue ? row[c.Key.FieldIndex.Value] : row[c.Key.FieldName];
var crit = c.Value;
if (candidate.IsNumeric() && crit.IsNumeric())
{
if (System.Math.Abs(ConvertUtil.GetValueDouble(candidate) - ConvertUtil.GetValueDouble(crit)) > double.Epsilon)
{
return(false);
}
}
else
{
var criteriaString = crit.ToString();
if (!Evaluate(candidate, criteriaString))
{
return(false);
}
}
}
return(retVal);
}
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 CriteriaShouldIgnoreEmptyFields2()
{
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("test");
sheet.Cells["A1"].Value = "Crit1";
sheet.Cells["A2"].Value = 1;
var provider = new EpplusExcelDataProvider(package);
var criteria = new ExcelDatabaseCriteria(provider, "A1:B2");
Assert.AreEqual(1, criteria.Items.Count);
Assert.AreEqual("crit1", criteria.Items.Keys.First().ToString());
Assert.AreEqual(1, criteria.Items.Values.Last());
}
}
public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
{
ValidateArguments(arguments, 2);
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 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 bool IsMatch(ExcelDatabaseRow row, ExcelDatabaseCriteria criteria)
{
var retVal = true;
foreach (var c in criteria.Items)
{
var candidate = c.Key.FieldIndex.HasValue ? row[c.Key.FieldIndex.Value] : row[c.Key.FieldName];
var crit = c.Value;
if (candidate.IsNumeric() && crit.IsNumeric())
{
if(System.Math.Abs(ConvertUtil.GetValueDouble(candidate) - ConvertUtil.GetValueDouble(crit)) > double.Epsilon) return false;
}
else
{
var criteriaString = crit.ToString();
if (!Evaluate(candidate, criteriaString))
{
return false;
}
}
}
return retVal;
}
protected IEnumerable<double> GetMatchingValues(IEnumerable<FunctionArgument> arguments, ParsingContext context)
{
var dbAddress = arguments.ElementAt(0).ValueAsRangeInfo.Address.Address;
//var field = ArgToString(arguments, 1).ToLower(CultureInfo.InvariantCulture);
var field = arguments.ElementAt(1).Value;
var criteriaRange = arguments.ElementAt(2).ValueAsRangeInfo.Address.Address;
var db = new ExcelDatabase(context.ExcelDataProvider, dbAddress);
var criteria = new ExcelDatabaseCriteria(context.ExcelDataProvider, criteriaRange);
var values = new List<double>();
while (db.HasMoreRows)
{
var dataRow = db.Read();
if (!RowMatcher.IsMatch(dataRow, criteria)) continue;
var candidate = ConvertUtil.IsNumeric(field) ? dataRow[(int)ConvertUtil.GetValueDouble(field)] : dataRow[field.ToString().ToLower(CultureInfo.InvariantCulture)];
if (ConvertUtil.IsNumeric(candidate))
{
values.Add(ConvertUtil.GetValueDouble(candidate));
}
}
return values;
}