private List <ValueAndFormat> GetMeaningfulValues(CellRangeAddress region, bool withText, Func <List <ValueAndFormat>, List <ValueAndFormat> > func)
        {
            if (meaningfulRegionValues.ContainsKey(region))
            {
                return(meaningfulRegionValues[region]);
            }
            List <ValueAndFormat> values    = new List <ValueAndFormat>();
            List <ValueAndFormat> allValues = new List <ValueAndFormat>((region.LastColumn - region.FirstColumn + 1) * (region.LastRow - region.FirstRow + 1));

            for (int r = region.FirstRow; r <= region.LastRow; r++)
            {
                IRow row = sheet.GetRow(r);
                if (row == null)
                {
                    continue;
                }
                for (int c = region.FirstColumn; c <= region.LastColumn; c++)
                {
                    ICell          cell = row.GetCell(c);
                    ValueAndFormat cv   = GetCellValue(cell);
                    if (withText || cv.IsNumber)
                    {
                        allValues.Add(cv);
                    }
                }
            }
            values = func(allValues);
            meaningfulRegionValues.Add(region, values);

            return(values);
        }
        private bool CheckFilter(ICell cell, CellReference reference, CellRangeAddress region)
        {
            ConditionFilterType?filterType = rule.ConditionFilterType;

            if (filterType == null)
            {
                return(false);
            }
            ValueAndFormat cv = GetCellValue(cell);

            switch (filterType)
            {
            case ConditionFilterType.FILTER:
                return(false);    // we don't evaluate HSSF filters yet

            case ConditionFilterType.TOP_10:
                // from testing, Excel only operates on numbers and dates (which are stored as numbers) in the range.
                // numbers stored as text are ignored, but numbers formatted as text are treated as numbers.

                return(GetMeaningfulValues(region, true, (allValues) => {
                    IConditionFilterData fc = rule.FilterConfiguration;

                    if (!fc.Bottom)
                    {
                        allValues.Sort();
                        allValues.Reverse();
                    }
                    else
                    {
                        allValues.Sort();
                    }

                    int limit = (int)fc.Rank;
                    if (fc.Percent)
                    {
                        limit = allValues.Count * limit / 100;
                    }
                    if (allValues.Count <= limit)
                    {
                        return allValues;
                    }
                    return allValues.GetRange(0, limit);
                }).Contains(cv));

            case ConditionFilterType.UNIQUE_VALUES:
                // Per Excel help, "duplicate" means matching value AND format
                // https://support.office.com/en-us/article/Filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2
                return(GetMeaningfulValues(region, true, (allValues) => {
                    allValues.Sort();
                    List <ValueAndFormat> unique = new List <ValueAndFormat>();

                    for (int i = 0; i < allValues.Count; i++)
                    {
                        ValueAndFormat v = allValues[i];
                        // skip this if the current value matches the next one, or is the last one and matches the previous one
                        if ((i < allValues.Count - 1 && v.Equals(allValues[i + 1])) || (i > 0 && i == allValues.Count - 1 && v.Equals(allValues[i - 1])))
                        {
                            // current value matches next value, skip both
                            i++;
                            continue;
                        }
                        unique.Add(v);
                    }

                    return unique;
                }).Contains(cv));

            case ConditionFilterType.DUPLICATE_VALUES:
                // Per Excel help, "duplicate" means matching value AND format
                // https://support.office.com/en-us/article/Filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2
                return(GetMeaningfulValues(region, true, (allValues) => {
                    allValues.Sort();
                    List <ValueAndFormat> dup = new List <ValueAndFormat>();

                    for (int i = 0; i < allValues.Count; i++)
                    {
                        ValueAndFormat v = allValues[i];
                        // skip this if the current value matches the next one, or is the last one and matches the previous one
                        if ((i < allValues.Count - 1 && v.Equals(allValues[i + 1])) || (i > 0 && i == allValues.Count - 1 && v.Equals(allValues[i - 1])))
                        {
                            // current value matches next value, add one
                            dup.Add(v);
                            i++;
                        }
                    }
                    return dup;
                }).Contains(cv));

            case ConditionFilterType.ABOVE_AVERAGE:
                // from testing, Excel only operates on numbers and dates (which are stored as numbers) in the range.
                // numbers stored as text are ignored, but numbers formatted as text are treated as numbers.

                IConditionFilterData conf = rule.FilterConfiguration;

                // actually ordered, so iteration order is predictable
                List <ValueAndFormat> values = GetMeaningfulValues(region, false, (allValues) => {
                    double total    = 0;
                    ValueEval[] pop = new ValueEval[allValues.Count];
                    for (int i = 0; i < allValues.Count; i++)
                    {
                        ValueAndFormat v = allValues[i];
                        total           += (double)v.Value;
                        pop[i]           = new NumberEval((double)v.Value);
                    }

                    List <ValueAndFormat> avgSet = new List <ValueAndFormat>(1);
                    avgSet.Add(new ValueAndFormat(allValues.Count == 0 ? 0 : total / allValues.Count, null, decimalTextFormat));

                    double stdDev2 = allValues.Count <= 1 ? 0 : ((NumberEval)AggregateFunction.STDEV.Evaluate(pop, 0, 0)).NumberValue;
                    avgSet.Add(new ValueAndFormat(stdDev2, null, decimalTextFormat));
                    return(avgSet);
                });
                double?val = cv.IsNumber ? cv.Value : null;
                if (val == null)
                {
                    return(false);
                }

                double avg    = (double)values[0].Value;
                double stdDev = (double)values[1].Value;

                /*
                 * use StdDev, aboveAverage, equalAverage to find:
                 * comparison value
                 * operator type
                 */

                double comp = conf.StdDev > 0 ? (avg + (conf.AboveAverage ? 1 : -1) * stdDev * conf.StdDev) : avg;

                OperatorEnum op;
                if (conf.AboveAverage)
                {
                    if (conf.EqualAverage)
                    {
                        op = OperatorEnum.GREATER_OR_EQUAL;
                    }
                    else
                    {
                        op = OperatorEnum.GREATER_THAN;
                    }
                }
                else
                {
                    if (conf.EqualAverage)
                    {
                        op = OperatorEnum.LESS_OR_EQUAL;
                    }
                    else
                    {
                        op = OperatorEnum.LESS_THAN;
                    }
                }
                return(OperatorEnumHelper.IsValid(op, val, comp, null));

            case ConditionFilterType.CONTAINS_TEXT:
                // implemented both by a cfRule "text" attribute and a formula.  Use the text.
                return(text == null ? false : cv.ToString().ToLowerInvariant().Contains(lowerText));

            case ConditionFilterType.NOT_CONTAINS_TEXT:
                // implemented both by a cfRule "text" attribute and a formula.  Use the text.
                return(text == null ? true : !cv.ToString().ToLowerInvariant().Contains(lowerText));

            case ConditionFilterType.BEGINS_WITH:
                // implemented both by a cfRule "text" attribute and a formula.  Use the text.
                return(cv.ToString().ToLowerInvariant().StartsWith(lowerText));

            case ConditionFilterType.ENDS_WITH:
                // implemented both by a cfRule "text" attribute and a formula.  Use the text.
                return(cv.ToString().ToLowerInvariant().EndsWith(lowerText));

            case ConditionFilterType.CONTAINS_BLANKS:
                try
                {
                    String v = cv.String;
                    // see TextFunction.TRIM for implementation
                    return(v == null || v.Trim().Length == 0);
                }
                catch (Exception e)
                {
                    // not a valid string value, and not a blank cell (that's checked earlier)
                    return(false);
                }

            case ConditionFilterType.NOT_CONTAINS_BLANKS:
                try
                {
                    String v = cv.String;
                    // see TextFunction.TRIM for implementation
                    return(v != null && v.Trim().Length > 0);
                }
                catch (Exception e)
                {
                    // not a valid string value, but not blank
                    return(true);
                }

            case ConditionFilterType.CONTAINS_ERRORS:
                return(cell != null && DataValidationEvaluator.IsType(cell, CellType.Error));

            case ConditionFilterType.NOT_CONTAINS_ERRORS:
                return(cell == null || !DataValidationEvaluator.IsType(cell, CellType.Error));

            case ConditionFilterType.TIME_PERIOD:
                // implemented both by a cfRule "text" attribute and a formula.  Use the formula.
                return(CheckFormula(reference, region));

            default:
                return(false);
            }
        }