Hello Team,
Can we select specific values from Excel’s Pivot Table’s Column Field Dropdown as mentioned in the attached snaps. First one is where all the values are selected and second snap is where I need to select values of my choice programitically.
Also please find the attached excel.
Filter Selected All.png (157.2 KB)
Filter Selected Few.png (156.9 KB)
File.zip (10.8 KB)
See the following sample code to accomplish your task for your reference:
e.g.
Sample code:
Workbook wb_demo = new Workbook("f:\\files\\test.xlsx");
PivotTable pt_demo = wb_demo.getWorksheets().get(0).getPivotTables().get(0);
//Get the first Pivot column field
PivotField src_t_field = pt_demo.getColumnFields().get(0);
src_t_field.setMultipleItemSelectionAllowed(true);
System.out.println(src_t_field.getName());
System.out.println("Total Items: " + src_t_field.getItems().length);
System.out.println("Total Items: " + src_t_field.getPivotItems().getCount());
PivotItemCollection collection_src_t = src_t_field.getPivotItems();
System.out.println(collection_src_t.getCount());
for (int i = 0; i < collection_src_t.getCount(); i++)
{
PivotItem item = collection_src_t.get(i);
System.out.println(item.getName());
if(item.getName().equals("0.5 kg"))
{
item.setHidden(true);
}
else if(item.getName().equals("1 kg"))
{
item.setHidden(false);
}
else if(item.getName().equals("1.5 kg"))
{
item.setHidden(true);
}
else if(item.getName().equals("2.5 kg"))
{
item.setHidden(false);
}
else if (item.getName().equals("3 kg"))
{
item.setHidden(true);
}
else if(item.getName().equals("6.5 kg"))
{
item.setHidden(false);
}
}
pt_demo.calculateData();
wb_demo.save("f:\\files\\out1.xlsx");
Hope, this helps a bit.
