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