How to generate line chart in Excel using Apache POI

Introduction

In this tutorial I will show you how to create line chart in excel file using Java based Apache POI library. I will also show you how to build this application using both maven and gradle build tools. This is a standalone application and you can use this concept of line chart generation in any Java based application. In this example we will create two lines representing area of each country with marker and population of each country with marker.

A line chart or line plot or line graph or curve chart is a type of chart which displays information as a series of data points called ‘markers’ connected by straight line segments.

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-line-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-line-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 Line Chart

Now we will create a Java class to create line chart in excel file using Apache POI library. The chart will display area-wise top seven countries in the world. Another line will display population for each country. Each line has markers on the line to indicate the area and population for each country.

package com.roytuts.apache.poi.excel.line.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.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.MarkerStyle;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
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.XDDFLineChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
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 ApachePoiLineChart {

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

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

			String sheetName = "CountryLineChart";

			XSSFSheet sheet = wb.createSheet(sheetName);

			// Country Names
			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");

			// Country Area
			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);

			// Country Population
			row = sheet.createRow((short) 2);

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

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

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

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

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

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

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

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

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

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

			XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
			bottomAxis.setTitle("Country");
			XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
			leftAxis.setTitle("Area & Population");

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

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

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

			XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
			
			XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(countries, area);
			series1.setTitle("Area", null);
			series1.setSmooth(false);
			series1.setMarkerStyle(MarkerStyle.STAR);
			
			XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(countries, population);
			series2.setTitle("Population", null);
			series2.setSmooth(true);
			series2.setMarkerSize((short) 6);
			series2.setMarkerStyle(MarkerStyle.SQUARE);
			
			chart.plot(data);

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

}

We create XSSFWorkbook object and create a sheet with name CountryStackedBarChart or CountryStackedColumnChart.

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

Please note the population data are not correct and it’s just representational purpose.

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 bar chart or column chart.

Then we create bottom axis and left axis. Bottom axis adds label “Country” and left axis adds label “Area & Population”.

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

We add numeric values in series as lines.

Then we plot the data onto line chart.

Finally we write the chart into excel file.

Testing the Application

Running the above main class will generate the line chart in excel similar to the following image.

line chart in excel using apache poi

Source Code

Download

Thanks for reading.

Related posts

Leave a Comment