Writing To Excel Sheet

The example is about generic way of writing data in excel using apache poi library in Java programming language. With this example I will show you how to create an excel file and write any kind of objects using Java’s reflection API to excel file with the help of Apache POI. In this example I am writing to single sheet, you can also check how to write to multiple sheets of excel file.

The Apache POI is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2).

Prerequisites

Java 8/12/19, Apache POI 3.12/4.1.1/4.1.2/5.2.4

Project Setup

You can create either gradle or maven based project in your favorite IDE or tool. The name of the project is apache-poi-excel-write-data-generic-way.

For the maven based project you can use the following pom.xml file:

<?xml version="1.0" encoding="UTF-8"?>

<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-write-data-generic-way</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>19</maven.compiler.source>
		<maven.compiler.target>19</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.2.4</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
			</plugin>
		</plugins>
	</build>
</project>

If you are creating gradle based project then you can use below build.gradle script:

plugins {
    id 'java-library'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    jcenter()
}

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

VO Class

This is a simple value object, a POJO class called Person. I have added three attributes to the below class.

package com.roytuts.apache.poi.excel.write.data.generic.way;

public class Person {

	private String name;
	private String email;
	private String address;

	public Person(String name, String email, String address) {
		this.name = name;
		this.email = email;
		this.address = address;
	}

	//getters and setters

}

Write Data Generic Way

Create a generic method to handle any kind of data to be written to excel file through single method. I have created a file object followed by excel workbook and finally I have created a sheet on the workbook.

I retrieved the attribute names from the class object, for example, Person class using Java’s reflection API. Then I determined the getter methods from the class using the reflection API and finally write the value of different types from all getter methods to the excel sheet.

package com.roytuts.apache.poi.excel.write.data.generic.way;

public class ExcelFileWriter {

	public static <T> void writeToExcel(String fileName, List<T> data) {
		OutputStream fos = null;
		XSSFWorkbook workbook = null;
		try {
			File file = new File(fileName);
			workbook = new XSSFWorkbook();
			Sheet sheet = workbook.createSheet();
			List<String> fieldNames = getFieldNamesForClass(data.get(0).getClass());
			int rowCount = 0;
			int columnCount = 0;
			Row row = sheet.createRow(rowCount++);
			for (String fieldName : fieldNames) {
				Cell cell = row.createCell(columnCount++);
				cell.setCellValue(fieldName);
			}
			Class<? extends Object> classz = data.get(0).getClass();
			for (T t : data) {
				row = sheet.createRow(rowCount++);
				columnCount = 0;
				for (String fieldName : fieldNames) {
					Cell cell = row.createCell(columnCount);
					Method method = null;
					try {
						method = classz.getMethod("get" + capitalize(fieldName));
					} catch (NoSuchMethodException nme) {
						method = classz.getMethod("get" + fieldName);
					}
					Object value = method.invoke(t, (Object[]) null);
					if (value != null) {
						if (value instanceof String) {
							cell.setCellValue((String) value);
						} else if (value instanceof Long) {
							cell.setCellValue((Long) value);
						} else if (value instanceof Integer) {
							cell.setCellValue((Integer) value);
						} else if (value instanceof Double) {
							cell.setCellValue((Double) value);
						}
					}
					columnCount++;
				}
			}
			fos = new FileOutputStream(file);
			workbook.write(fos);
			fos.flush();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (fos != null) {
					fos.close();
				}
			} catch (IOException e) {
			}
			try {
				if (workbook != null) {
					workbook.close();
				}
			} catch (IOException e) {
			}
		}
	}

	// retrieve field names from a POJO class
	private static List<String> getFieldNamesForClass(Class<?> clazz) throws Exception {
		List<String> fieldNames = new ArrayList<String>();
		Field[] fields = clazz.getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			fieldNames.add(fields[i].getName());
		}
		return fieldNames;
	}

	// capitalize the first letter of the field name for retriving value of the
	// field later
	private static String capitalize(String s) {
		if (s.length() == 0)
			return s;
		return s.substring(0, 1).toUpperCase() + s.substring(1);
	}

}

Main Class

I am writing a class with main method to write to an excel file generic way using Java reflection. In the below class I have taken few dummy data which will be written into the excel file.

package com.roytuts.apache.poi.excel.write.data.generic.way;

public class ExcelFileWriterApp {

	public static void main(String[] args) {
		List<Person> persons = new ArrayList<>();

		Person p1 = new Person("A", "a@roytuts.com", "Kolkata");
		Person p2 = new Person("B", "b@roytuts.com", "Mumbai");
		Person p3 = new Person("C", "c@roytuts.com", "Delhi");
		Person p4 = new Person("D", "d@roytuts.com", "Chennai");
		Person p5 = new Person("E", "e@roytuts.com", "Bangalore");
		Person p6 = new Person("F", "f@roytuts.com", "Hyderabad");

		persons.add(p1);
		persons.add(p2);
		persons.add(p3);
		persons.add(p4);
		persons.add(p5);
		persons.add(p6);

		ExcelFileWriter.writeToExcel("excel-person.xlsx", persons);
	}

}

Testing the Generic Way of Writing

Now running the above class will give you the following output in excel file. The excel file is generated under the project’s root directory.

Hope you have enjoyed learning the example generic way of writing data in excel using apache poi.

Source Code

Download