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));
}
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);
}
private static ExcelDatabase GetDatabase(ExcelPackage package)
{
var provider = new EpplusExcelDataProvider(package);
var sheet = package.Workbook.Worksheets.Add("test");
sheet.Cells["A1"].Value = "col1";
sheet.Cells["A2"].Value = 1;
sheet.Cells["B1"].Value = "col2";
sheet.Cells["B2"].Value = 2;
var database = new ExcelDatabase(provider, "A1:B2");
return database;
}
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 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);
}
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;
}