Introduction
On many occasions, besides data tables, you need to create charts in Excel file you're exporting with Java. In this tutorial, we'll show you how to deal with charts using Apache POI library - how to set chart title, legend, provide numeric data and also how to set-up chart position on the sheet in use case with test results - outcomes could be Passed, Skipped and Failed.
In this example, we'll draw a Pie3D chart, but you can choose from a variety of types from org.apache.poi.xddf.usermodel.chart.ChartTypes class (AREA, AREA3D, BAR, BAR3D, DOUGHNUT, LINE, LINE3D, PIE, PIE3D, RADAR, SCATTER, SURFACE, SURFACE3D).
This is how our result will look like:
Dependencies
Apache POI has a lot of implicit dependencies you hear about when you try to export your data. I also had issues with orchestrating versions of those dependencies. Following is the maven dependency list needed for your code to work. Be aware that changing versions may cause artifact version incompatibility and you may end up with ClassNotFoundException or MethodNotFoundError. Currently, version 4.1 of Apache POI is the newest.
<dependencies>
<dependency>
<groupId>org.apache.servicemix.bundles</groupId>
<artifactId>org.apache.servicemix.bundles.poi</artifactId>
<version>4.1.2_1</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.4</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-compress</artifactId>
<version>1.20</version>
</dependency>
</dependencies>
Coding time - export chart to Excel file
This is a working code, and the complete project could be found on GitHub.
package exportdemo;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExportData {
// CONSTANTS
/*----------------------------------------------------------*/
static final String FILE_SAVE_LOCATION = "C:\\reports\\";
static final String FILE_NAME = "Test report.xlsx";
/*----------------------------------------------------------*/
public static void main(String[] args) throws IOException {
// creating workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// creating sheet with name "Report" in workbook
XSSFSheet sheet = workbook.createSheet("Report");
XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 10, 20, 30, 40);
XSSFChart chart = drawing.createChart(anchor);
chart.setTitleText("Test results");
chart.setTitleOverlay(false);
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.TOP_RIGHT);
String[] legendData = { "passed", "skipped", "failed" };
XDDFDataSource<String> testOutcomes = XDDFDataSourcesFactory.fromArray(legendData);
Integer[] numericData = { 10, 12, 30 };
XDDFNumericalDataSource<Integer> values = XDDFDataSourcesFactory.fromArray(numericData);
XDDFChartData data = chart.createData(ChartTypes.PIE3D, null, null);// for simple pie chart you can use
// ChartTypes.PIE
chart.displayBlanksAs(null);
data.setVaryColors(true);
data.addSeries(testOutcomes, values);
chart.plot(data);
try (FileOutputStream outputStream = new FileOutputStream(FILE_SAVE_LOCATION + FILE_NAME)) {
workbook.write(outputStream);
} finally {
// don't forget to close workbook to prevent memory leaks
workbook.close();
}
}
}
Line by line
First, you create a workbook and sheet and give that sheet a name.
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Report");
Then you create drawing and anchor where you define the location of chart drawing on your Excel sheet.
XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 10, 20, 30, 40);
XSSFChart chart = drawing.createChart(anchor);
XSSFClientAnchor constructor consists of 8 elements. Most important are the last four and these define the position of chart measured in cells. So, we'll define anchor with parameters a1 ... a8 and picture shows which parameter defines which distance.
XSSFClientAnchor anchor = drawing.createAnchor(a1, a2, a3, a4, a5, a6, a7, a8);
Then you create legend where you provide list od Strings that will represent labels for your values. In our case that will be {"passed", "skipped", "failed"}. Notice that you have control over placing legend on the chart, here we choose LegendPosition.TOP_RIGHT.
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.TOP_RIGHT);
String[] legendData = { "passed", "skipped", "failed" };
Then we provide numeric data that will be used for pie - in this case, we manually created a list of Integers.
XDDFDataSource<String> testOutcomes = XDDFDataSourcesFactory.fromArray(legendData);
Integer[] numericData = { 10, 12, 30 };
XDDFNumericalDataSource<Integer> values = XDDFDataSourcesFactory.fromArray(numericData);
Then we define type of chart (options are AREA, AREA3D, BAR, BAR3D, DOUGHNUT, LINE, LINE3D, PIE, PIE3D, RADAR, SCATTER, SURFACE, SURFACE3D).
XDDFChartData data = chart.createData(ChartTypes.PIE3D, null, null);
chart.displayBlanksAs(null);
data.setVaryColors(true);
And finally, we provide chart object with legend and values and draw it.
data.addSeries(testOutcomes, values);
chart.plot(data);
Finally, we write it to the workbook:
try (FileOutputStream outputStream = new FileOutputStream(FILE_SAVE_LOCATION + FILE_NAME)) {
workbook.write(outputStream);
} finally {
workbook.close();
}
And that's it. Complete code could be found on GitHub.