How to generate Pie Chart in Excel using Apache POI

Introduction

In this example we will see how to generate pie chart in excel file using Apache POI library in Java. I will show you how to build this application using both maven and gradle tools. This is a standalone application and you can use this concept of pie chart generation in any Java based application. In this example we will create a 3D pie chart for displaying area-wise top seven countries.

A pie chart is a circular graphic with slices that illustrate the numerical proportions. In a pie chart the length of arch of each slice is proportional to the quantity it represents.

Pie charts are generally used to show percentage or proportional data in circular statistical graphic and usually the percentage represented by each category is provided next to the corresponding slice of pie. Pie charts are good for displaying data for around 6 categories or fewer.

Prerequisites

At least Java 8, Eclipse 4.12, Apache POI 4.1.1, Gradle 5.6, maven 3.6.1

Create Project

The name of the project is apache-poi-excel-pie-chart.

If you are creating maven based project then use below pom.xml file for standalone maven project in Eclipse.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	
	<groupId>com.roytuts</groupId>
	<artifactId>apache-poi-excel-pie-chart</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>1.8 or 12</java.version>
	</properties>
	
	<dependencies>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.1.1</version>
		</dependency>
		
		<!-- required only if you are using jdk 9 or above -->
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.10.1</version>
		</dependency>
	</dependencies>
	
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>${java.version}</source>
					<target>${java.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

If you are creating gradle based project in Eclipse, then use below build.gradle script to include the required dependencies.

plugins {
    id 'java-library'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    jcenter()
}

dependencies {
    implementation 'org.apache.poi:poi-ooxml:4.1.1'
    //required only for jdk 9 or above
    implementation('com.fasterxml.jackson.core:jackson-databind:2.10.1')
}

Create Pie Chart

Now we will create a Java class to create a pie chart in excel file using Apache POI library. The pie chart will display area-wise top seven countries in the world.

package com.roytuts.apache.poi.pie.chart;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
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 ApachePoiPieChart {

	public static void main(String[] args) throws IOException {
		pieChart();
	}

	public static void pieChart() throws FileNotFoundException, IOException {
		try (XSSFWorkbook wb = new XSSFWorkbook()) {

			XSSFSheet sheet = wb.createSheet("CountryPieChart");

			// Create row and put some cells in it. Rows and cells are 0 based.
			Row row = sheet.createRow((short) 0);

			Cell cell = row.createCell((short) 0);
			cell.setCellValue("Russia");

			cell = row.createCell((short) 1);
			cell.setCellValue("Canada");

			cell = row.createCell((short) 2);
			cell.setCellValue("USA");

			cell = row.createCell((short) 3);
			cell.setCellValue("China");

			cell = row.createCell((short) 4);
			cell.setCellValue("Brazil");

			cell = row.createCell((short) 5);
			cell.setCellValue("Australia");

			cell = row.createCell((short) 6);
			cell.setCellValue("India");

			row = sheet.createRow((short) 1);

			cell = row.createCell((short) 0);
			cell.setCellValue(17098242);

			cell = row.createCell((short) 1);
			cell.setCellValue(9984670);

			cell = row.createCell((short) 2);
			cell.setCellValue(9826675);

			cell = row.createCell((short) 3);
			cell.setCellValue(9596961);

			cell = row.createCell((short) 4);
			cell.setCellValue(8514877);

			cell = row.createCell((short) 5);
			cell.setCellValue(7741220);

			cell = row.createCell((short) 6);
			cell.setCellValue(3287263);

			XSSFDrawing drawing = sheet.createDrawingPatriarch();
			XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 4, 7, 20);

			XSSFChart chart = drawing.createChart(anchor);
			chart.setTitleText("Area-wise Top Seven Countries");
			chart.setTitleOverlay(false);

			XDDFChartLegend legend = chart.getOrAddLegend();
			legend.setPosition(LegendPosition.TOP_RIGHT);

			XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet,
					new CellRangeAddress(0, 0, 0, 6));

			XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
					new CellRangeAddress(1, 1, 0, 6));

			XDDFChartData data = chart.createData(ChartTypes.PIE3D, null, null);// chart.createData(ChartTypes.PIE,
																				// null, null);
			data.setVaryColors(true);
			data.addSeries(countries, values);
			chart.plot(data);

			// Write output to an excel file
			try (FileOutputStream fileOut = new FileOutputStream("pie-chart-top-seven-countries.xlsx")) {
				wb.write(fileOut);
			}
		}
	}

}

I will explain few important lines from the above class.

We create XSSFWorkbook object and create a sheet with name CountryPieChart.

Next we create two rows and 7 columns in the excel sheet. First row contains country names and second row contains each country’s area.

Then we create XSSFDrawing object and XSSFClientAnchor object.

In XSSFClientAnchor we set the range of the rows and columns or cells. The first two values indicate coordinates of x and y of first cell respectively. The next two values indicate coordinates of x and y of second cell respectively. The next two values indicate starting row index and column index of chart area. The next two values indicate ending row index and column index of chart area.

Now we create chart from the XSSFClientAnchor object using createChart(). We set title to the chart.

The legends are shown on right side of the pie chart.

Next we define data source for drawing chart. We set country names as string and area as numeric values.

Then we create data on 3D pie chart. If you want normal pie chart then you can change it to only ChartTypes.PIE instead of ChartTypes.PIE3D.

To get different colors on different slices on pie chart we set true to vary color.

Then we plot data onto pie chart and chart gets generated.

Finally we write to the excel file – pie-chart-top-seven-countries.xlsx.

Testing the Application

Running the above main class will generate the pie chart in excel similar to the following image:

generate pie chart in excel using apache poi

Source Code

Download

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *