Is it possible to create Pivot Chart with source data as Pivot Table using Apache POI

Question

Using apache POI separately, I can make a pivot table and a pivot chart. However, rather than starting with data from a sheet, I'm trying to generate a column chart from a pivot table. I tried searching for advice here but was unsuccessful. If you can, will you kindly point me in the appropriate direction?

Answer 1

It is as simple as adding a pivot source to the chart for use with Microsoft Excel. Excel then creates the chart using the provided pivot table.

The qualified name of the pivot table is set as the name of the PivotSource element in Office Open XML (XSSF), which provides the pivot source. [WorkbookName] is an acceptable name. worksheetName!pivotTableName.

This is the name of the worksheet in square brackets, followed by an exclamation point, the name of the pivot table, and then the name of the workbook.

In code this would look as so:

...
String pivotTableName = pivotTable.getCTPivotTableDefinition().getName();
String qualifiedPivotSourceName = "[" + workbookName + "]" + pivotSheet.getSheetName() + "!" + pivotTableName;
chart.getCTChartSpace().addNewPivotSource().setName(qualifiedPivotSourceName);
...
    

Using that one does not even need to set the source data range in the chart as excel takes it from the given pivot table. So only dummy data would be given as so:

...
XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromArray(new String[]{"dummy"});
XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromArray(new Double[]{1d});
...