Pivot Chart

Pivot chart represents the data of associated pivot table in a chart. Like a normal chart, the pivot chart displays data series, categories, legends, data markers and axes. You can change the titles, legend placement, data labels, chart location etc.

A pivot chart is interactive as it reflects the changes made in its associated pivot table. The pivot table fields are displayed on a pivot chart as buttons. You can configure whether to display the legend, axis, value field buttons or expanding or collapsing entire field buttons by using the getPivotOptions method. When a field button is clicked, its filter pane appears. It helps you to sort and filter pivot chart's underlying data.

Excel files with pivot charts can be loaded, modified and saved back to Excel. The below image displays a pivot chart with legend, axis and value field buttons.

Create Pivot Chart

The below mentioned steps explain how to create a pivot chart:

Create a pivot table.
Add a normal chart by using addChartInPixel method of IShapes interface.
Use setSourceData method of IChart interface to turn a normal chart into a PivotChart by providing the source range inside the pivot table's range.

//create a new workbook
Workbook workbook = new Workbook();
Object sourceData = new Object[][]{
{"Order ID", "Product", "Category", "Amount", "Date", "Country"},
{1, "Bose 785593-0050", "Consumer Electronics", 4270, new GregorianCalendar(2018, 0, 6), "United States"},
{2, "Canon EOS 1500D", "Consumer Electronics", 8239, new GregorianCalendar(2018, 0, 7), "United Kingdom"},
{3, "Haier 394L 4Star", "Consumer Electronics", 617, new GregorianCalendar(2018, 0, 8), "United States"},
{4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 8384, new GregorianCalendar(2018, 0, 10), "Canada"},
{5, "Mi LED 40inch", "Consumer Electronics", 2626, new GregorianCalendar(2018, 0, 10), "Germany"},
{6, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3610, new GregorianCalendar(2018, 0, 11), "United States"},
{7, "Iphone XR", "Mobile", 9062, new GregorianCalendar(2018, 0, 11), "Australia"},
{8, "OnePlus 7Pro", "Mobile", 6906, new GregorianCalendar(2018, 0, 16), "New Zealand"},
{9, "Redmi 7", "Mobile", 2417, new GregorianCalendar(2018, 0, 16), "France"},
{10, "Samsung S9", "Mobile", 7431, new GregorianCalendar(2018, 0, 16), "Canada"},
{11, "OnePlus 7Pro", "Mobile", 8250, new GregorianCalendar(2018, 0, 16), "Germany"},
{12, "Redmi 7", "Mobile", 7012, new GregorianCalendar(2018, 0, 18), "United States"},
{13, "Bose 785593-0050", "Consumer Electronics", 1903, new GregorianCalendar(2018, 0, 20), "Germany"},
{14, "Canon EOS 1500D", "Consumer Electronics", 2824, new GregorianCalendar(2018, 0, 22), "Canada"},
{15, "Haier 394L 4Star", "Consumer Electronics", 6946, new GregorianCalendar(2018, 0, 24), "France"},
};
                
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("A6:F21").setValue(sourceData);
worksheet.getRange("D6:D21").setNumberFormat("$#,##0.00");
// Create pivot cache
IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("A6:F21"));
// Create pivot table
IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, worksheet.getRange("A1"), "pivottable1");
                
//config pivot table's fields
pivottable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
pivottable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField);
pivottable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);
                
worksheet.getRange("A:I").autoFit();
                
// Add a column chart
IChart chart = worksheet.getShapes().addChartInPixel(ChartType.ColumnClustered, 0, 100, 689, 320).getChart();
                
// Set data source(use pivot table range).
chart.setSourceData(pivottable.getTableRange1());
                
//save to an excel file
workbook.save("CreatePivotChart.xlsx");

!type=note
Note: To turn a normal chart into a pivot chart, add any chart from the ones listed below. A NotSupportedException will be thrown if any other chart is added.
Area
Bar
Column
Pie/Doughnut
Line
Radar
Surface

Configure Pivot Chart's Buttons

Refer to the following example code to configure pivot chart's buttons.

// Set not to show legend and axis buttons
chart.getPivotOptions().setShowLegendFieldButtons(false);
chart.getPivotOptions().setShowAxisFieldButtons(false);

// Set legend position to bottom
chart.getLegend().setPosition(LegendPosition.Bottom);

Update Pivot Table to Reflect in Pivot Chart

Refer to the following example code to update pivot table to reflect in pivot chart.

// Drag row field to hidden
chart.getPivotTable().getRowFields().get(0).setOrientation(PivotFieldOrientation.Hidden);

Convert Pivot Chart to Normal Chart

Refer to the following example code to convert pivot chart to normal chart.

// Clear pivot table to turn a PivotChart into a normal chart.
pivottable.getTableRange2().clear();

Limitations

The pivot chart is exported as a normal chart in PDF or while doing JSON I/O.
If you add, change or delete the source range of a series, it will not reflect in pivot chart.

Create Pivot Chart
Configure Pivot Chart's Buttons
Update Pivot Table to Reflect in Pivot Chart
Convert Pivot Chart to Normal Chart
Limitations