/// <summary>
/// Adds a caption (label) filter for a pivot tabel field
/// </summary>
/// <param name="type"></param>
/// <param name="value1"></param>
/// <param name="value2"></param>
/// <returns></returns>
public ExcelPivotTableFilter AddCaptionFilter(ePivotTableCaptionFilterType type, string value1, string value2 = null)
{
ExcelPivotTableFilter filter = CreateFilter();
filter.Type = (ePivotTableFilterType)type;
filter.StringValue1 = value1;
filter.Value1 = value1;
filter.Value2 = value2;
if (!string.IsNullOrEmpty(value2))
{
filter.StringValue2 = value2;
}
switch (type)
{
case ePivotTableCaptionFilterType.CaptionEqual:
filter.CreateValueFilter();
break;
default:
filter.CreateCaptionCustomFilter(type);
break;
}
filter.Filter.Save();
_filters.Add(filter);
return(filter);
}
internal void CreateCaptionCustomFilter(ePivotTableCaptionFilterType type)
{
_filterColumnNode.InnerXml = "<customFilters/>";
var cf = new ExcelCustomFilterColumn(NameSpaceManager, _filterColumnNode);
eFilterOperator t;
var v = StringValue1;
switch (type)
{
case ePivotTableCaptionFilterType.CaptionNotBeginsWith:
case ePivotTableCaptionFilterType.CaptionNotContains:
case ePivotTableCaptionFilterType.CaptionNotEndsWith:
case ePivotTableCaptionFilterType.CaptionNotEqual:
t = eFilterOperator.NotEqual;
break;
case ePivotTableCaptionFilterType.CaptionGreaterThan:
t = eFilterOperator.GreaterThan;
break;
case ePivotTableCaptionFilterType.CaptionGreaterThanOrEqual:
case ePivotTableCaptionFilterType.CaptionBetween:
t = eFilterOperator.GreaterThanOrEqual;
break;
case ePivotTableCaptionFilterType.CaptionLessThan:
case ePivotTableCaptionFilterType.CaptionNotBetween:
t = eFilterOperator.LessThan;
break;
case ePivotTableCaptionFilterType.CaptionLessThanOrEqual:
t = eFilterOperator.LessThanOrEqual;
break;
default:
t = eFilterOperator.Equal;
break;
}
switch (type)
{
case ePivotTableCaptionFilterType.CaptionBeginsWith:
case ePivotTableCaptionFilterType.CaptionNotBeginsWith:
v += "*";
break;
case ePivotTableCaptionFilterType.CaptionContains:
case ePivotTableCaptionFilterType.CaptionNotContains:
v = $"*{v}*";
break;
case ePivotTableCaptionFilterType.CaptionEndsWith:
case ePivotTableCaptionFilterType.CaptionNotEndsWith:
v = $"*{v}";
break;
}
var item1 = new ExcelFilterCustomItem(v, t);
cf.Filters.Add(item1);
if (type == ePivotTableCaptionFilterType.CaptionBetween)
{
cf.And = true;
cf.Filters.Add(new ExcelFilterCustomItem(StringValue2, eFilterOperator.LessThanOrEqual));
}
else if (type == ePivotTableCaptionFilterType.CaptionNotBetween)
{
cf.And = false;
cf.Filters.Add(new ExcelFilterCustomItem(StringValue2, eFilterOperator.GreaterThan));
}
_filter = cf;
}
