Exemplo n.º 1
0
        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);
        }
Exemplo n.º 3
0
        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));
        }
Exemplo n.º 4
0
        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));
        }
Exemplo n.º 5
0
        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());
            }
        }
Exemplo n.º 6
0
        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);
        }
Exemplo n.º 7
0
        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);
        }
Exemplo n.º 8
0
 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;
 }
Exemplo n.º 9
0
        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;
        }