public ExcelPivotTableFilter AddValueFilter(ePivotTableValueFilterType type, int dataFieldIndex, object value1, object value2 = null)
{
if (dataFieldIndex < 0 || dataFieldIndex >= _table.DataFields.Count)
{
throw new ArgumentException("dataFieldIndex must point to an item in the pivot tables DataFields collection", "dataFieldIndex");
}
if (value2 == null &&
(type == ePivotTableValueFilterType.ValueBetween ||
type == ePivotTableValueFilterType.ValueNotBetween))
{
throw new ArgumentNullException("value2", "Between filters require two values");
}
ExcelPivotTableFilter filter = CreateFilter();
filter.Type = (ePivotTableFilterType)type;
filter.Value1 = value1;
filter.Value2 = value2;
filter.MeasureFldIndex = dataFieldIndex;
filter.CreateValueCustomFilter(type);
filter.Filter.Save();
_filters.Add(filter);
return(filter);
}
public ExcelPivotTableFilter AddValueFilter(ePivotTableValueFilterType type, ExcelPivotTableDataField dataField, object value1, object value2 = null)
{
var dfIx = _table.DataFields._list.IndexOf(dataField);
if (dfIx < 0)
{
throw new ArgumentException("This datafield is not in the pivot tables DataFields collection", "dataField");
}
return(AddValueFilter(type, dfIx, value1, value2));
}
internal void CreateValueCustomFilter(ePivotTableValueFilterType type)
{
_filterColumnNode.InnerXml = "<customFilters/>";
var cf = new ExcelCustomFilterColumn(NameSpaceManager, _filterColumnNode);
eFilterOperator t;
string v1 = GetFilterValueAsString(Value1);
switch (type)
{
case ePivotTableValueFilterType.ValueNotEqual:
t = eFilterOperator.NotEqual;
break;
case ePivotTableValueFilterType.ValueGreaterThan:
t = eFilterOperator.GreaterThan;
break;
case ePivotTableValueFilterType.ValueGreaterThanOrEqual:
case ePivotTableValueFilterType.ValueBetween:
t = eFilterOperator.GreaterThanOrEqual;
break;
case ePivotTableValueFilterType.ValueLessThan:
t = eFilterOperator.LessThan;
break;
case ePivotTableValueFilterType.ValueLessThanOrEqual:
case ePivotTableValueFilterType.ValueNotBetween:
t = eFilterOperator.LessThanOrEqual;
break;
default:
t = eFilterOperator.Equal;
break;
}
var item1 = new ExcelFilterCustomItem(v1, t);
cf.Filters.Add(item1);
if (type == ePivotTableValueFilterType.ValueBetween)
{
cf.And = true;
cf.Filters.Add(new ExcelFilterCustomItem(GetFilterValueAsString(Value2), eFilterOperator.LessThanOrEqual));
}
else if (type == ePivotTableValueFilterType.ValueNotBetween)
{
cf.And = false;
cf.Filters.Add(new ExcelFilterCustomItem(GetFilterValueAsString(Value2), eFilterOperator.GreaterThan));
}
_filter = cf;
}
