Free Support Forum - aspose.com

Getting specific filtered values of Row field from Pivot table in Java

Hello Team, Can you please tell how to get filtered values from Pivot tables for only active users.

Currently am getting the complete list as response.

Attaching my excel sheet as well.

Book1.zip (10.3 KB)

Getting current output as : BOOK LIST is : [A, B, C, D, E, F, G, H, I, J, K, L, M, N]
Desired output : BOOK LIST IS : [A, C, D, F, H, J, K, M]

Below is my sample code.

  		ArrayList<String> bookList = new ArrayList<String>();
  		Worksheet workSheet1 = book.getWorksheets().get("Sheet2");
  		PivotTableCollection pivotTables1 = workSheet1.getPivotTables();
  		if(pivotTables1.getCount() != 0) {
  			PivotTable pivotTable = pivotTables1.get(0);	
  			
  			String[] bookListArray = pivotTable.getRowFields().get(0).getItems();
  			
  			for(String employeeNotesID : bookListArray) {
  				if(!employeeNotesID.equalsIgnoreCase("(blank)") && !employeeNotesID.equalsIgnoreCase("N/A")) {
  					employeeNotesID = employeeNotesID.trim();
  					bookList.add(employeeNotesID);
  				}
  			}
  		}
                       System.out.println("BOOK LIST is : "+bookList);

@sourav24,

Your code will extract all the pivot row field items. Please try the following sample code, it will suit your needs:
e.g.
Sample code:

        Workbook book = new Workbook("f:\\files\\Book1.xlsx");
        Worksheet workSheet1 = book.getWorksheets().get("Sheet2");
        PivotTableCollection pivotTables1 = workSheet1.getPivotTables();
        PivotTable pivotTable = null;
        if(pivotTables1.getCount() != 0) {
            pivotTable = pivotTables1.get(0);

            pivotTable.refreshData();
            pivotTable.calculateData();

            CellArea area = pivotTable.getRowRange();
            int start = area.StartColumn;
            for (int i = area.StartRow; i <= area.EndRow; i++)
            {
                //Get the first row field items. 
                System.out.println(workSheet1.getCells().get(i,start).getStringValue());

            }
        }

Hope, this helps a bit.

1 Like

@Amjad_Sahi Thanks a lot for the solution. This was exactly what I needed.

@sourav24,

Good to know that the suggested code segment works for your needs. Feel free to contact us any time if you need further help or have some other queries or issue, we will be happy to assist you soon.

1 Like