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