Generic way of writing data in excel using apache poi

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

At least Java 1.8, Apache POI 3.12, 4.1.1, 4.1.2

Project Setup

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

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')
}

If you are creating maven based project then you can use below pom.xml file:

<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>
		<jdk.version>at least 1.8</jdk.version>
		<poi.version>3.1.2</poi.version>
	</properties>
	
	<dependencies>
		<!-- apache poi for xlsx, docx etc reading/writing -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>${poi.version}</version>
		</dependency>
	</dependencies>
	
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>${jdk.version}</source>
					<target>${jdk.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

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;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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;

import java.util.ArrayList;
import java.util.List;

public class ExcelFileWriterTest {

	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 Application

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

Thanks for reading.