Pivot table filters "Select multiple items"

Aspose.Cells Product Family
1

In excel pivot tables, you drag a column to the “Report Filter” area, then you can select that filter and check the “Select Multiple Items” box. I want the filter to select all the items EXCEPT “x” and “y”.


But I’ve searched the forums here and can’t see anything that works. Here’s what I’m trying, but it doesn’t have any effect:

//Is this the right way to get the field in the “Report Filter” section?
var x = pivotTable.Fields(Aspose.Cells.Pivot.PivotFieldType.Page);
var fieldToFilterOn = x[0];

//And is this the right way to filter out the two bad values?
int baseIndex = fieldToFilterOn.BaseIndex;
int index = pivotTable.PivotFilters.Add(baseIndex, Aspose.Cells.Pivot.PivotFilterType.CaptionNotEqual);
var filter = pivotTable.PivotFilters[index];
filter.Value1 = “x,y”;

Cause that doesn’t seem to set the filters right. Is there something else I should do?
2

Hi,


It would be of great help in understanding your exact requirements if you can share your sample spreadsheet containing the PivotTable in question and your desired results that you may create using the MS Excel application on the same spreadsheet.

As far as we understood your requirement, you probably wish to select a item from Report Filter dropdown, and based on that selection, you wish to dynamically populate the PivotTable. You can achieve this by switching the PivotItem’s IsHidden property. If set to false, the item will be selected and vice versa. For elaboration purposes, please check the below provided code snippet.

C#

public static void setFilterOptionByString(PivotTable table, string filterName, string value)
{
PivotField field = table.PageFields[filterName];
field.IsMultipleItemSelectionAllowed = true;
PivotItemCollection collection = field.PivotItems;
bool all = false;
if(value.Equals("(All)"))
{
all = true;
}
for (int i = 0; i < collection.Count; i++)
{
PivotItem item = collection[i];
if (all)
{
item.IsHidden = false;
}
else
{
if (item.Name.Equals(value))
{
item.IsHidden = false;
}
else
{
item.IsHidden = true;
}
}
}
table.RefreshData();
table.CalculateData();
table.RefreshDataOnOpeningFile = true;
}