Select multiple values in Page filter in a pivot table

Aspose.Cells Product Family
1

Hello team,

I am using Aspose.cells 7.0.2 for .NET. I am having a page filter in a pivot table and want to hide few values from the selection i.e. I want to unselect few values in the filter. In order to do this, I can use the HideItems function by using the appropriate index. However, I don’t know the index of the item, as the data is dynamic. Hence, I wanted to know if we can iterate through the items in a pivotfieldcollection.

I am doing the following:

PivotFieldCollection pvt_field = pivotTable.PageFields;
pvt_field[0].IsMultipleItemSelectionAllowed = true;

pvt_field[0].HideItem(0, true);

But I want to iterate through pvt_field[0].Items. I tried using a foreach statement, but it keeps erroring out. something like this:
foreach (PivotItem pvt in pvt_field[0].Items) { }

Could you please let me know how to do it? I want to check whether the pvt_fields[0].Items value is equal to some variable. If yes, hide it.

I also want to the same in a column filter.

Thanks in advance

2

Hi,

We think you may use this sample code:
e.g

PivotItem item = pvt_field[0].PivotItems["itemname"];

//or use "for" statement like this:

for (int i = 0; i < items.Count; i++)
{

}

Thank you.

3

Hi,

I tried the sample code but it looks like it would not work.
You have used a pivotitem ‘Item’ but are using items.count. May I know what is ‘item’?

Could you please let me know how to get the various values of the Pagefield?

Thanks,

4

Hi,



See the updated code for reference:

PivotItemCollection items = pivotField.PivotItems;
//Get the pivot item
PivotItem item = items[“itemname”];
//…

//or use “for” statement like this:
for (int i = 0; i < items.Count; i++)
{
//Use your own code here.
}
5

Could you please let me know what is the “itemname”?
Thanks,

6

Hi,


Well “itemname” is just a sample pivot item for your understanding. I have written a sample example to make you understand it better. The example will generate an Excel file with a pivot table, but he “Golf” pivot item will not be displayed in the pivot table report as I have made it hidden.

Sample code:

//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
//Setting the value to the cells
Aspose.Cells.Cell cell = cells[“A1”];
cell.PutValue(“Sport”);
cell = cells[“B1”];
cell.PutValue(“Quarter”);
cell = cells[“C1”];
cell.PutValue(“Sales”);
cell = cells[“A2”];
cell.PutValue(“Golf”);
cell = cells[“A3”];
cell.PutValue(“Golf”);
cell = cells[“A4”];
cell.PutValue(“Tennis”);
cell = cells[“A5”];
cell.PutValue(“Tennis”);
cell = cells[“A6”];
cell.PutValue(“Tennis”);
cell = cells[“A7”];
cell.PutValue(“Tennis”);
cell = cells[“A8”];
cell.PutValue(“Golf”);
cell = cells[“B2”];
cell.PutValue(“Qtr3”);
cell = cells[“B3”];
cell.PutValue(“Qtr4”);
cell = cells[“B4”];
cell.PutValue(“Qtr3”);
cell = cells[“B5”];
cell.PutValue(“Qtr4”);
cell = cells[“B6”];
cell.PutValue(“Qtr3”);
cell = cells[“B7”];
cell.PutValue(“Qtr4”);
cell = cells[“B8”];
cell.PutValue(“Qtr3”);
cell = cells[“C2”];
cell.PutValue(1500);
cell = cells[“C3”];
cell.PutValue(2000);
cell = cells[“C4”];
cell.PutValue(600);
cell = cells[“C5”];
cell.PutValue(1500);
cell = cells[“C6”];
cell.PutValue(4070);
cell = cells[“C7”];
cell.PutValue(5000);
cell = cells[“C8”];
cell.PutValue(6430);
PivotTableCollection pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", “E3”, “PivotTable1”);
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];
//Unshowing grand totals for rows.
pivotTable.RowGrand = false;
//Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
//Draging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1);
//Draging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);

//Get the first (the only) row field
PivotField pivotField = pivotTable.RowFields[0];
PivotItemCollection items = pivotField.PivotItems;
//Get the pivot item
PivotItem item = items[“Golf”];
//hide it.
item.IsHidden = true;

workbook.Save(“e:\test2\outputwithhiddenGolf.xlsx”);

7

Thanks. I fixed the issue.
I used the following code:

PivotFieldCollection pvt_field = pivotTable9.PageFields;
pvt_field[0].IsMultipleItemSelectionAllowed = true;
for(int j=0; j<pvt_field[0].ItemCount;j++)
{
if (pvt_field[0].Items[j].Equals(“1-Outstanding”) || pvt_field[0].Items[j].Equals(“2-Highly Effective”) || pvt_field[0].Items[j].Equals("(blank)"))
{
pvt_field[0].HideItem(j, true);
}
}

Thanks for all your help!

8

Hi,

Thanks for your feedback and sample code.

It’s good to know that your issue is now resolved.

If you face any other issue, please feel free to post, we will be glad to assist you further.