The example is about generic way of writing data in excel using apache poi. With this example I will show you how to create an excel file and write any kind of list of objects to excel file using Apache POI in Java language.

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). You can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is the Java Excel solution (for Excel 97-2008).

If you already have an idea on how to create a maven project in Eclipse will be great otherwise I will tell you here how to create a maven project in Eclipse.

Recommended reading: Generic way of reading Excel file using Apache POI

Generic way of writing data to multiple sheets in excel using apache poi

Prerequisites

The following things are required in order to run the application

Eclipse Kepler
JDK 1.8
Have maven 3 installed and configured
Apache POI dependency in pom.xml

Now we will see the below steps how to create a maven based spring project in Eclipse.

Step 1. Create a standalone maven project in Eclipse.

Go to File -> New -> Other. On popup window under Maven select Maven Project. Then click on Next. Select the workspace location – either default or browse the location. Click on Next. Now in next window select the row as highlighted from the below list of archtypes and click on Next button.

maven-arctype-quickstart

Now enter the required fields (Group Id, Artifact Id) as shown below.

Group Id : com.roytuts
Artifact Id : java

Step 2. Modify the pom.xml file as shown below. Notice we have added only single dependency for apache poi.

<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>java</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>java</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<jdk.version>1.8</jdk.version>
		<poi.version>3.12</poi.version>
		<junit.version>4.11</junit.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>
		<!-- junit -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>${junit.version}</version>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<configuration>
					<source>${jdk.version}</source>
					<target>${jdk.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Step 3. Create a POJO class class called Person. We have added three attributes to the below class.

package com.roytuts.excel;

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

}

Step 4. Create a generic method to handle any kind of data to excel through single method. We create a file object first. Then we have created excel workbook and finally create a sheet on the workbook. We retrieve the attribute names from the class object, for example, Person class. Then we find the getter methods from the class and finally write the value from all getter methods to the excel sheet.

package com.roytuts.excel;

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 ExcelUtils {

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

}

Step 5. Create a junit test case to test the above class whether it writes to the excel file or not. In the below junit class we first setup few dummy data before the actual test to be executed. Then we write the excel file with those dummy data into D drive in Windows OS. This class shows generic way of writing data in excel using apache poi.

package com.roytuts.excel;

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

import org.junit.Before;
import org.junit.Test;

public class ExcelUtilsTest {

	private List<Person> persons;

	@Before
	public void setUp() throws Exception {
		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);
	}

	@Test
	public void testWriteToExcel() {
		ExcelUtils.writeToExcel("D:/person.xlsx", persons);
	}

}

Step 6. Now run the junit test class, you will get the excel output in “D” drive.

generic way of writing excel using apache poi

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

Recommended reading: Generic way of reading Excel file using Apache POI

Generic way of writing data to multiple sheets in excel using apache poi

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

3 thoughts on “Generic way of writing data in excel using apache poi

Leave a Reply

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