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:

XSSFChart PIE3D

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

XSSFClientAnchor distances

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.

Check also...