Esempio n. 1
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));
        }
Esempio n. 2
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);
        }
Esempio n. 3
0
 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;
 }
Esempio 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));
        }
Esempio n. 5
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);
        }
Esempio n. 6
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);
        }
Esempio n. 7
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;
        }