I am able to create Pivot Table and Pivot Chart using apache POI individually. But I am trying to create column chart form pivot table rather than directly from sheet data. I've tried look for guidance here but could not find any. If it is possible, Can you please guide me in the right direction? Thank you.
Update
Below is the sample code i had earlier that populates data in one sheet and then create pivot table and pivot chart in another sheet using the data populated in the first sheet.
But I would like to have the pivot table as source for pivot chart rather than the original data from other sheet. This way i can dynamically hide certain records. For example if I need to show only Asia countries in the chart, i can filter them in pivot table which would show only those countries in the chart. I am not sure how I can accomplish that.
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.main.CTLineProperties;
import org.openxmlformats.schemas.drawingml.x2006.main.CTSRgbColor;
import org.openxmlformats.schemas.drawingml.x2006.main.CTShapeProperties;
import org.openxmlformats.schemas.drawingml.x2006.main.CTSolidColorFillProperties;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
public class ColumnChart {
public static void main(String[] args) {
String workbookName = "Population.xlsx";
String dataSheetName = "DataSheet";
String pivotSheetName = "PivotSheet";
try (XSSFWorkbook workbook = new XSSFWorkbook();
FileOutputStream fos = new FileOutputStream(workbookName)) {
prepareDataSheet(workbook, dataSheetName);
createPivotTable(workbook, pivotSheetName, dataSheetName);
createPivotChart(workbook, pivotSheetName, dataSheetName);
workbook.write(fos);
} catch (IOException e) {
e.printStackTrace();
}
}
private static void prepareDataSheet(XSSFWorkbook workbook, String dataSheetName) {
List<String> continentList = Arrays.asList("Asia", "Asia", "America", "Asia", "Asia", "America", "Africa", "Asia");
List<String> countryList = Arrays.asList("China", "India", "United States", "Indonesia", "Pakistan", "Brazil", "Nigeria", "Bangladesh");
List<Long> populationList = Arrays.asList(1411778724L, 1386020955L, 332943364L, 271350000L, 225200000L, 214130142L, 211401000L, 171933178L);
XSSFSheet dataSheet = workbook.createSheet(dataSheetName);
Row row = dataSheet.createRow(0);
row.createCell(0).setCellValue("Continent");
row.createCell(1).setCellValue("Country");
row.createCell(2).setCellValue("Population");
int rowCounter = 1;
for (rowCounter = 1; rowCounter <= countryList.size(); rowCounter ) {
row = dataSheet.createRow(rowCounter);
row.createCell(0).setCellValue(continentList.get(rowCounter - 1));
row.createCell(1).setCellValue(countryList.get(rowCounter - 1));
row.createCell(2).setCellValue(populationList.get(rowCounter - 1));
}
}
private static void createPivotTable(XSSFWorkbook workbook, String pivotSheetName, String dataSheetName) {
XSSFSheet pivotSheet = workbook.createSheet(pivotSheetName);
XSSFSheet dataSheet = workbook.getSheet(dataSheetName);
CellReference leftTop = new CellReference(0, 0);
CellReference rightBottom = new CellReference(8, 2);
CellReference pivotLocation = new CellReference(1, 1);
AreaReference sourceDataAreaRef = new AreaReference(leftTop, rightBottom, SpreadsheetVersion.EXCEL2007);
XSSFPivotTable pivotTable = pivotSheet.createPivotTable(sourceDataAreaRef, pivotLocation, dataSheet);
pivotTable.addRowLabel(0);
pivotTable.addRowLabel(1);
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2);
}
private static void createPivotChart(XSSFWorkbook workbook, String pivotSheetName, String dataSheetName) {
XSSFSheet pivotSheet = workbook.getSheet(pivotSheetName);
XSSFSheet dataSheet = workbook.getSheet(dataSheetName);
XSSFDrawing drawing = pivotSheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 2, 10, 20);
XSSFChart chart = drawing.createChart(anchor);
chart.setTitleText("Population By Countries");
chart.setTitleOverlay(false);
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.BOTTOM);
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
bottomAxis.setTitle("Country");
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
leftAxis.setTitle("Population");
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(dataSheet,
new CellRangeAddress(0, 8, 1, 1));
XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet,
new CellRangeAddress(0, 8, 2, 2));
XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
XDDFChartData.Series series1 = data.addSeries(countries, values);
series1.setTitle("Country", null);
data.setVaryColors(false);
XDDFBarChartData bar = (XDDFBarChartData) data;
bar.setBarDirection(BarDirection.COL);
CTSolidColorFillProperties fillProp = CTSolidColorFillProperties.Factory.newInstance();
CTSRgbColor rgb = CTSRgbColor.Factory.newInstance();
rgb.setVal(new byte[]{(byte) 233, (byte) 87, (byte)162});
fillProp.setSrgbClr(rgb);
CTShapeProperties ctShapeProperties = CTShapeProperties.Factory.newInstance();
ctShapeProperties.setSolidFill(fillProp);
chart.getCTChart().getPlotArea().getBarChartList().get(0).getSerList().get(0).setSpPr(ctShapeProperties);
chart.plot(data);
}
}
CodePudding user response：
For usage using Microsoft Excel it is as simple as adding a pivot source to the chart. After that Excel renders the chart from the given pivot table.
In Office Open XML (XSSF) the pivot source is given by a PivotSource element having the qualified name of the pivot table set as name. Qualified name is: [workbookName]worksheetName!pivotTableName.
This is workbook name in square brackets followed by worksheet name followed by an exclamation mark followed by the pivot table name.
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 not even needs setting the source data range in the chart as excel takes it form the given pivot table. So only dummy data would must be given as so:
...
XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromArray(new String[]{"dummy"});
XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromArray(new Double[]{1d});
...
But note that this kind of chart only will be shown in Microsoft Excel. Other spreadsheet calculation software will not show this kind of chart properly. So one should let the source data in then chart as fallback.
And you need set AxisCrossBetween, so the left axis crosses the category axis between the categories. Else first and last category is exactly on cross points and the bars are only half visible.
And you should set the default, which is no using multi level labels, to false. So continents and countries will have multiple level axis labels.
I have updated your complete example:
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.main.CTLineProperties;
import org.openxmlformats.schemas.drawingml.x2006.main.CTSRgbColor;
import org.openxmlformats.schemas.drawingml.x2006.main.CTShapeProperties;
import org.openxmlformats.schemas.drawingml.x2006.main.CTSolidColorFillProperties;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
public class ColumnChart {
public static void main(String[] args) {
String pathToSave = "./";
String workbookName = "Population.xlsx";
String dataSheetName = "DataSheet";
String pivotSheetName = "PivotSheet";
try (XSSFWorkbook workbook = new XSSFWorkbook();
FileOutputStream fos = new FileOutputStream(pathToSave workbookName)) {
prepareDataSheet(workbook, dataSheetName);
XSSFPivotTable pivotTable = createPivotTable(workbook, pivotSheetName, dataSheetName);
createPivotChart(workbook, workbookName, pivotTable, dataSheetName);
workbook.getSheetAt(0).setSelected(false);
workbook.setActiveSheet(1);
workbook.write(fos);
} catch (IOException e) {
e.printStackTrace();
}
}
private static void prepareDataSheet(XSSFWorkbook workbook, String dataSheetName) {
List<String> continentList = Arrays.asList("Asia", "Asia", "America", "Asia", "Asia", "America", "Africa", "Asia");
List<String> countryList = Arrays.asList("China", "India", "United States", "Indonesia", "Pakistan", "Brazil", "Nigeria", "Bangladesh");
List<Long> populationList = Arrays.asList(1411778724L, 1386020955L, 332943364L, 271350000L, 225200000L, 214130142L, 211401000L, 171933178L);
XSSFSheet dataSheet = workbook.createSheet(dataSheetName);
Row row = dataSheet.createRow(0);
row.createCell(0).setCellValue("Continent");
row.createCell(1).setCellValue("Country");
row.createCell(2).setCellValue("Population");
int rowCounter = 1;
for (rowCounter = 1; rowCounter <= countryList.size(); rowCounter ) {
row = dataSheet.createRow(rowCounter);
row.createCell(0).setCellValue(continentList.get(rowCounter - 1));
row.createCell(1).setCellValue(countryList.get(rowCounter - 1));
row.createCell(2).setCellValue(populationList.get(rowCounter - 1));
}
}
private static XSSFPivotTable createPivotTable(XSSFWorkbook workbook, String pivotSheetName, String dataSheetName) {
XSSFSheet pivotSheet = workbook.createSheet(pivotSheetName);
XSSFSheet dataSheet = workbook.getSheet(dataSheetName);
CellReference leftTop = new CellReference(0, 0);
CellReference rightBottom = new CellReference(8, 2);
CellReference pivotLocation = new CellReference(1, 1);
AreaReference sourceDataAreaRef = new AreaReference(leftTop, rightBottom, SpreadsheetVersion.EXCEL2007);
XSSFPivotTable pivotTable = pivotSheet.createPivotTable(sourceDataAreaRef, pivotLocation, dataSheet);
pivotTable.addRowLabel(0);
pivotTable.addRowLabel(1);
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2);
return pivotTable;
}
private static void createPivotChart(XSSFWorkbook workbook, String workbookName, XSSFPivotTable pivotTable, String dataSheetName) {
XSSFSheet dataSheet = workbook.getSheet(dataSheetName);
XSSFSheet pivotSheet = (XSSFSheet)pivotTable.getParentSheet();
XSSFDrawing drawing = pivotSheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 2, 10, 20);
XSSFChart chart = drawing.createChart(anchor);
/*Set pivot source of the chart to a qualified name.
Qualified name is [workbookName]worksheetName!pivotTableName. */
String pivotTableName = pivotTable.getCTPivotTableDefinition().getName();
String qualifiedPivotSourceName = "[" workbookName "]" pivotSheet.getSheetName() "!" pivotTableName;
chart.getCTChartSpace().addNewPivotSource().setName(qualifiedPivotSourceName);
chart.setTitleText("Population By Countries");
chart.setTitleOverlay(false);
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.BOTTOM);
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
bottomAxis.setTitle("Country");
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
leftAxis.setTitle("Population");
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
/*You need set AxisCrossBetween, so the left axis crosses the category axis between the categories.
Else first and last category is exactly on cross points and the bars are only half visible.*/
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(dataSheet,
new CellRangeAddress(1, 8, 1, 1));
XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet,
new CellRangeAddress(1, 8, 2, 2));
//XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromArray(new String[]{"dummy"});
//XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromArray(new Double[]{1d});
XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
XDDFChartData.Series series1 = data.addSeries(countries, values);
series1.setTitle("Country", null);
data.setVaryColors(false);
XDDFBarChartData bar = (XDDFBarChartData) data;
bar.setBarDirection(BarDirection.COL);
CTSolidColorFillProperties fillProp = CTSolidColorFillProperties.Factory.newInstance();
CTSRgbColor rgb = CTSRgbColor.Factory.newInstance();
rgb.setVal(new byte[]{(byte) 233, (byte) 87, (byte)162});
fillProp.setSrgbClr(rgb);
CTShapeProperties ctShapeProperties = CTShapeProperties.Factory.newInstance();
ctShapeProperties.setSolidFill(fillProp);
chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(0).setSpPr(ctShapeProperties);
/*Set the default, which is no using multi level labels, to false.
So continents and countries will have multiple level axis labels.*/
chart.getCTChart().getPlotArea().getCatAxArray(0).addNewNoMultiLvlLbl().setVal(false);
chart.plot(data);
}
}