private bool CheckValue(ICell cell, CellRangeAddress region) { if (cell == null || DataValidationEvaluator.IsType(cell, CellType.Blank) || DataValidationEvaluator.IsType(cell, CellType.Error) || (DataValidationEvaluator.IsType(cell, CellType.String) && string.IsNullOrEmpty(cell.StringCellValue) ) ) { return(false); } ValueEval eval = UnwrapEval(workbookEvaluator.Evaluate(rule.Formula1, ConditionalFormattingEvaluator.GetRef(cell), region)); String f2 = rule.Formula2; ValueEval eval2 = BlankEval.instance; if (f2 != null && f2.Length > 0) { eval2 = UnwrapEval(workbookEvaluator.Evaluate(f2, ConditionalFormattingEvaluator.GetRef(cell), region)); } // we assume the cell has been evaluated, and the current formula value stored if (DataValidationEvaluator.IsType(cell, CellType.Boolean) && (eval == BlankEval.instance || eval is BoolEval) && (eval2 == BlankEval.instance || eval2 is BoolEval) ) { return(OperatorEnumHelper.IsValid(@operator, cell.BooleanCellValue, eval == BlankEval.instance ? (bool?)null : ((BoolEval)eval).BooleanValue, eval2 == BlankEval.instance ? (bool?)null : ((BoolEval)eval2).BooleanValue)); } if (DataValidationEvaluator.IsType(cell, CellType.Numeric) && (eval == BlankEval.instance || eval is NumberEval) && (eval2 == BlankEval.instance || eval2 is NumberEval) ) { return(OperatorEnumHelper.IsValid(@operator, cell.NumericCellValue, eval == BlankEval.instance ? (double?)null : ((NumberEval)eval).NumberValue, eval2 == BlankEval.instance ? (double?)null : ((NumberEval)eval2).NumberValue)); } if (DataValidationEvaluator.IsType(cell, CellType.String) && (eval == BlankEval.instance || eval is StringEval) && (eval2 == BlankEval.instance || eval2 is StringEval) ) { return(OperatorEnumHelper.IsValid(@operator, cell.StringCellValue, eval == BlankEval.instance ? null : ((StringEval)eval).StringValue, eval2 == BlankEval.instance ? null : ((StringEval)eval2).StringValue)); } return(OperatorEnumHelper.IsValidForIncompatibleTypes(@operator)); }
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); } }