How to set PivotField's selected items?

Aspose.Cells Product Family
1

Filter.xlsx.zip (11.1 KB)

PivotTable table = workbook.getWorksheets().get(0).getPivotTables().get(0);
PivotField pivotField = table.getPageFields().get(0);

pivotField.setMultipleItemSelectionAllowed(true);

How to set pivotField with multiple values??

2

And how to get current selected items?

3

@xhaixia,

Thanks for the template file, sample code segment and details.

I evaluated your scenario/ case a bit and tried some sample codes but to no avail:
e.g
Sample code:

Workbook workbook = new Workbook("f:\\files\\filter.xlsx");
		PivotTable table = workbook.getWorksheets().get(0).getPivotTables().get(0);
		PivotField pivotField = table.getPageFields().get(0);

		pivotField.setMultipleItemSelectionAllowed(true);
		
		int pageItemCount = pivotField.getPivotItems().getCount();

          //Select a, e, i, oitems only 
          for (int i = 0; i < pageItemCount; i++)
          {
              PivotItem item = pivotField.getPivotItems().get(i);
              
              System.out.println(item.getName());
              System.out.println(item.getValue());
              
              if (item.getName().equals("a"))
              {
            	  pivotField.setCurrentPageItem((short)i);
              }
              
              if (item.getName().equals("e"))
              {
            	  pivotField.setCurrentPageItem((short)i);
              }
              
              if (item.getName().equals("i"))
              {
            	  pivotField.setCurrentPageItem((short)i);
              }

              if (item.getName().equals("o"))
              {
            	  pivotField.setCurrentPageItem((short)i);
              }
              
              //This also does not work
              /*
              if (item.getName().equals("b"))
              {
                  item.setHidden(true);
              }

              if (item.getName().equals("c"))
              {
                  item.setHidden(true);
              }
              
              if (item.getName().equals("d"))
              {
                  item.setHidden(true);
              }
              //......
              //....... 
             */
              
          }
          
          table.refreshData();
          table.calculateData();
          
          workbook.save("f:\\files\\out1.xlsx");

In short, either multiple items selection is not supported, or we have to evaluate it further and devise/refine the code segment to provide it to you.

Once we sort it out, we will update you with code segments for your needs.

4

@xhaixia,

Please forget my previous reply as it works fine with latest version/fix (Aspose.Cells for Java v20.4.x). Multiple items selection is supported in Aspose.Cells for Java. I refined/updated my code segment and it works fine and as expected. See the updated code segment that you may refer to and try:
e.g
Sample code:

Workbook workbook = new Workbook("f:\\files\\filter.xlsx");
		
		PivotTable table = workbook.getWorksheets().get(0).getPivotTables().get(0);
		PivotField pivotField = table.getPageFields().get(0);

		pivotField.setMultipleItemSelectionAllowed(true);
		
		int pageItemCount = pivotField.getPivotItems().getCount();

          //Select a, e, i, o items only, all other items will remain hidden/de-selected. 
          for (int i = 0; i < pageItemCount; i++)
          {
              PivotItem item = pivotField.getPivotItems().get(i);
              
              
              switch (item.getName())
              {
              case "a":
              case "e":
              case "i":
              case "o":
              item.setHidden(false);
              break;
              default:
              item.setHidden(true);
              break;
              }
          }  
          
          table.refreshData();
          table.calculateData();
          
          workbook.save("f:\\files\\asdfasdfasfasdf193111.xlsx");

Let us know your feedback.