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);
}
}
