Documents for Excel, Java Edition Documentation
Features / Slicer / Add Slicer in Pivot Table
In This Topic
    Add Slicer in Pivot Table
    In This Topic

    Gcexcel enables users to efficiently organize data in pivot tables and multi pivot tables via slicers.  

    The methods of the ISlicerCaches interface, the ISlicerCache interface, the IPivotCache interface, IPivotCaches interface, IPivotField interface, IPivotFields interface, IPivotTable interface, IPivotTables interface and the IPivotItem interface can be used to insert slicers in pivot tables.

    In order to insert slicer in a pivot table, you can use the add method of the ISlicerCaches interface to create a new slicer cache for a pivot table, as shown in the example code shared below.

    Java
    Copy Code
    // Defining source data
    Object sourceData = new Object[][] 
    { 
      { "Order ID", "Product", "Category", "Amount", "Date", "Country" },
      { 1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2018, 0, 6), "United States" },
      { 2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2018, 0, 7), "United Kingdom" },
      { 3, "Banana", "Fruit", 617, new GregorianCalendar(2018, 0, 8), "United States" },
      { 4, "Banana", "Fruit", 8384, new GregorianCalendar(2018, 0, 10), "Canada" },
      { 5, "Beans", "Vegetables", 2626, new GregorianCalendar(2018, 0, 10), "Germany" },
      { 6, "Orange", "Fruit", 3610, new GregorianCalendar(2018, 0, 11), "United States" },
      { 7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2018, 0, 11), "Australia" },
      { 8, "Banana", "Fruit", 6906, new GregorianCalendar(2018, 0, 16), "New Zealand" },
      { 9, "Apple", "Fruit", 2417, new GregorianCalendar(2018, 0, 16), "France" },
      { 10, "Apple", "Fruit", 7431, new GregorianCalendar(2018, 0, 16), "Canada" },
      { 11, "Banana", "Fruit", 8250, new GregorianCalendar(2018, 0, 16), "Germany" },
      { 12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2018, 0, 18), "United States" },
      { 13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2018, 0, 20), "Germany" },
      { 14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2018, 0, 22), "Canada" },
      { 15, "Apple", "Fruit", 6946, new GregorianCalendar(2018, 0, 24), "France" }, 
    };
            
    // Initialize the workbook and fetch the default worksheet
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.getWorksheets().get(0);
            
    // Adding data to the pivot table
    worksheet.getRange("A1:F16").setValue(sourceData);
    worksheet.getRange("A:F").setColumnWidth(15);
    
    // Create pivot cache.
    IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("A1:F16"));
            
    // Create pivot tables.
    IPivotTable pivottable1 = worksheet.getPivotTables().add(pivotcache, 
    worksheet.getRange("K5"), "pivottable1");
    IPivotTable pivottable2 = worksheet.getPivotTables().add(pivotcache, 
    worksheet.getRange("N3"), "pivottable2");
    worksheet.getRange("D2:D16").setNumberFormat("$#,##0.00");
    
    // Configure pivot fields
    IPivotField field_product1 = pivottable1.getPivotFields().get(1);
    field_product1.setOrientation(PivotFieldOrientation.RowField);
    
    IPivotField field_Amount1 = pivottable1.getPivotFields().get(3);
    field_Amount1.setOrientation(PivotFieldOrientation.DataField);
    
    IPivotField field_product2 = pivottable2.getPivotFields().get(5);
    field_product2.setOrientation(PivotFieldOrientation.RowField);
    
    IPivotField field_Amount2 = pivottable2.getPivotFields().get(2);
    field_Amount2.setOrientation(PivotFieldOrientation.DataField);
    field_Amount2.setFunction(ConsolidationFunction.Count);
    
    // Create slicer cache and the slicers base. The slicer cache controls pivot table1
    ISlicerCache cache = workbook.getSlicerCaches().add(pivottable1, "Product");
    ISlicer slicer1 = cache.getSlicers().add(workbook.getWorksheets().get("Sheet1"), 
    "p1", "Product", 30, 550, 100, 200);
    
    // Add pivot table2 for slicer cache. Slicer cache will control pivot table1 and pivot table2
    cache.getPivotTables().addPivotTable(pivottable2);

    In order to add slicer in a multi pivot table, refer to the following example code.

    Java
    Copy Code
    ISlicerCache cache = workbook.getSlicerCaches().add(pivottable1, "Product");
    ISlicer slicer1 = cache.getSlicers().add(workbook.getWorksheets().get("Sheet1"), "p1", "Product", 
    20, 20, 100,200);
    cache.getPivotTables().addPivotTable(pivottable2);
          
    // Set slicer style to built-in style
    slicer1.setStyle(workbook.getTableStyles().get("SlicerStyleLight2"));