Excel API Library for Java - Sample Browser | Document Solutions | Add slicers to Pivot Tables
[
フレーム]
src="bundle.js">
Refer to the following example code to see how to create slicer for a Pivot Table.
// Create a new workbook
Workbook workbook = new Workbook();
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"},
};
IWorksheet worksheet = workbook.getWorksheets().get(0);
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");
//Config 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, the slicers base the slicer cache just control 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, the slicers base the slicer cache will control pivot tabl1 and pivot table2.
cache.getPivotTables().addPivotTable(pivottable2);
// Save to an excel file
workbook.save("AddSlicersForPivotTable.xlsx");
// Create a new workbook
var workbook = Workbook()
val sourceData = arrayOf(arrayOf
("Order ID", "Product", "Category", "Amount", "Date", "Country"), arrayOf(1, "Carrots", "Vegetables", 4270, GregorianCalendar(2018, 0, 6), "United States"), arrayOf(2, "Broccoli", "Vegetables", 8239, GregorianCalendar(2018, 0, 7), "United Kingdom"), arrayOf(3, "Banana", "Fruit", 617, GregorianCalendar(2018, 0, 8), "United States"), arrayOf(4, "Banana", "Fruit", 8384, GregorianCalendar(2018, 0, 10), "Canada"), arrayOf(5, "Beans", "Vegetables", 2626, GregorianCalendar(2018, 0, 10), "Germany"), arrayOf(6, "Orange", "Fruit", 3610, GregorianCalendar(2018, 0, 11), "United States"), arrayOf(7, "Broccoli", "Vegetables", 9062, GregorianCalendar(2018, 0, 11), "Australia"), arrayOf(8, "Banana", "Fruit", 6906, GregorianCalendar(2018, 0, 16), "New Zealand"), arrayOf(9, "Apple", "Fruit", 2417, GregorianCalendar(2018, 0, 16), "France"), arrayOf(10, "Apple", "Fruit", 7431, GregorianCalendar(2018, 0, 16), "Canada"), arrayOf(11, "Banana", "Fruit", 8250, GregorianCalendar(2018, 0, 16), "Germany"), arrayOf(12, "Broccoli", "Vegetables", 7012, GregorianCalendar(2018, 0, 18), "United States"), arrayOf(13, "Carrots", "Vegetables", 1903, GregorianCalendar(2018, 0, 20), "Germany"), arrayOf(14, "Broccoli", "Vegetables", 2824, GregorianCalendar(2018, 0, 22), "Canada"), arrayOf(15, "Apple", "Fruit", 6946, GregorianCalendar(2018, 0, 24), "France"))
val worksheet = workbook.worksheets.get(0)
worksheet.getRange("A1:F16").value = sourceData
worksheet.getRange("A:F").columnWidth = 15.0
//Create pivot cache.
val pivotcache = workbook.pivotCaches.create(worksheet.getRange("A1:F16"))
//Create pivot tables.
val pivottable1 = worksheet.pivotTables.add(pivotcache, worksheet.getRange("K5"), "pivottable1")
val pivottable2 = worksheet.pivotTables.add(pivotcache, worksheet.getRange("N3"), "pivottable2")
worksheet.getRange("D2:D16").numberFormat = "$#,##0.00"
//Config pivot fields
val field_product1 = pivottable1.pivotFields.get(1)
field_product1.orientation = PivotFieldOrientation.RowField
val field_Amount1 = pivottable1.pivotFields.get(3)
field_Amount1.orientation = PivotFieldOrientation.DataField
val field_product2 = pivottable2.pivotFields.get(5)
field_product2.orientation = PivotFieldOrientation.RowField
val field_Amount2 = pivottable2.pivotFields.get(2)
field_Amount2.orientation = PivotFieldOrientation.DataField
field_Amount2.function = ConsolidationFunction.Count
//create slicer cache, the slicers base the slicer cache just control pivot table1.
val cache = workbook.slicerCaches.add(pivottable1, "Product")
val slicer1 = cache.slicers.add(workbook.worksheets.get("Sheet1"), "p1", "Product", 30.0, 550.0, 100.0, 200.0)
//add pivot table2 for slicer cache, the slicers base the slicer cache will control pivot tabl1 and pivot table2.
cache.pivotTables.addPivotTable(pivottable2)
// Save to an excel file
workbook.save("AddSlicersForPivotTable.xlsx")