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

Introduction

With this example I will show you how to create an excel file and write a of list of objects to multiple sheets in excel file using Apache POI in Java language. In this example I am going to write to three different sheets of the same excel file with some information details.

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).

Related Posts:

Prerequisites

At least Java 1.8, Apache POI 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 java-apache-poi-excel-write-multiple-sheets-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>java-apache-poi-excel-write-multiple-sheets-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>

POJO Classes

Create few POJO classes. These POJO classes’ attributes will be written to the multiple sheets of the excel file.

package com.roytuts.java.apache.poi.excel.write.multiple.sheets.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

}
package com.roytuts.java.apache.poi.excel.write.multiple.sheets.generic.way;

public class User {

	private String userId;
	private String userPass;

	public User(String userId, String userPass) {
		this.userId = userId;
		this.userPass = userPass;
	}

	// getters and setters

}
package com.roytuts.java.apache.poi.excel.write.multiple.sheets.generic.way;

public class Contact {

	private String mobile;
	private String phone1;
	private String phone2;

	public Contact(String mobile, String phone1, String phone2) {
		this.mobile = mobile;
		this.phone1 = phone1;
		this.phone2 = phone2;
	}

	// getters and setters

}

Excel Writer

This class has few methods to write to multiple sheets in generic way. I am using Java’s reflection to set the value to the class attributes for writing data to multiple sheets.

package com.roytuts.java.apache.poi.excel.write.multiple.sheets.generic.way;

import java.io.File;
import java.io.FileInputStream;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelWriter {

	public <T> void writeToExcelInMultiSheets(final String fileName, final String sheetName, final List<T> data) {
		File file = null;
		OutputStream fos = null;
		XSSFWorkbook workbook = null;
		try {
			file = new File(fileName);
			Sheet sheet = null;
			if (file.exists()) {
				workbook = (XSSFWorkbook) WorkbookFactory.create(new FileInputStream(file));
			} else {
				workbook = new XSSFWorkbook();
			}
			sheet = workbook.createSheet(sheetName);
			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 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 String capitalize(String s) {
		if (s.length() == 0)
			return s;
		return s.substring(0, 1).toUpperCase() + s.substring(1);
	}

}

Testing the Application

Now finally I am writing below class with main method to test the excel sheet writing in generic way.

package com.roytuts.java.apache.poi.excel.write.multiple.sheets.generic.way;

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

public class ExcelWriterTest {

	public static void main(String[] args) {
		ExcelWriter writer = new ExcelWriter();

		List<Person> persons = new ArrayList<>();
		Person p1 = new Person("A", "[email protected]", "Kolkata");
		Person p2 = new Person("B", "[email protected]", "Mumbai");
		Person p3 = new Person("C", "[email protected]", "Delhi");
		Person p4 = new Person("D", "[email protected]", "Chennai");
		Person p5 = new Person("E", "[email protected]", "Bangalore");
		Person p6 = new Person("F", "[email protected]", "Hyderabad");

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

		List<User> users = new ArrayList<>();
		User u1 = new User("u1", "pwd1");
		User u2 = new User("u2", "pwd2");
		User u3 = new User("u3", "pwd3");
		User u4 = new User("u4", "pwd4");
		User u5 = new User("u5", "pwd5");

		users.add(u1);
		users.add(u2);
		users.add(u3);
		users.add(u4);
		users.add(u5);

		List<Contact> contacts = new ArrayList<>();
		Contact c1 = new Contact("9478512354", "24157853", "24578613");
		Contact c2 = new Contact("9478512354", "24157853", "24578613");
		Contact c3 = new Contact("9478512354", "24157853", "24578613");
		Contact c4 = new Contact("9478512354", "24157853", "24578613");

		contacts.add(c1);
		contacts.add(c2);
		contacts.add(c3);
		contacts.add(c4);

		writer.writeToExcelInMultiSheets("excel.xlsx", "Person Details", persons);
		writer.writeToExcelInMultiSheets("excel.xlsx", "User Details", users);
		writer.writeToExcelInMultiSheets("excel.xlsx", "Contact Details", contacts);
	}

}

Executing the above class will write to three sheets with different information as shown below in the image:

generic way of writing data to multiple sheets in excel

Source Code

Download

Thanks for reading.

Related posts

10 Thoughts to “Generic way of writing data to multiple sheets in excel using apache poi”

  1. madhu

    How do you execute the method for Java object having attribute type with custom class?
    I want to implement this for my java class having Embeddable id.

  2. Bost

    Thank you for the code!
    I tried implementing the same code for a non-void method to return a downloadable excel file using ByteArrayInputStream but I was unable to return more than one value. Kindly assist with a working solution

    1. I don’t know what error you get, but I have checked. It’s working fine. I have updated the post with source code download link at the bottom. You may check. I have not changed source code and I have got the same result when I tested. I don’t know what you mean by working solution.

  3. @Test
    public void testWriteToExcel() {
    ExcelUtils.writeToExcel(“D:/person.xlsx”, persons);
    }
    Hi I am seeing this method in the ExcelUtil and I am getting excel sheet only one tab that is Person Details.
    Can you show me where is this method defined in the ExcelUtils class

    1. This method might have been copied from my other post related to excel writing. I have removed this one.

  4. Manish

    How to use this code without any hard corded file path.It should take default browser download path from spring controller/service.

    1. You can use temp directory to write your file.

      1. Pranav

        I am able to create a file on my local system… but if i run on server env.. i get error below
        Caused by: java.lang.IllegalArgumentException: The workbook already contains a sheet named ‘Past Date’
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.validateSheetName(XSSFWorkbook.java:885)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:838)

  5. Annappa

    Great lesson sir….
    I just want to know how to generate excel in ReadOnly (non editable) format.
    will you please explain me???
    Thanku in advance

    1. Get the cell you want and set your cell style
      CellStyle unlockedCellStyle = wb.createCellStyle();
      unlockedCellStyle.setLocked(true); //true or false based on the cell.
      cell.setCellStyle(unlockedCellStyle);

Leave a Comment