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); } }