Introduction

With this example I will show you how to create an excel file and write any kind of list of objects to multiple sheets in 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 Excel file writing 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

Example with Source Code

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 : excel-apache-poi

Step 2. Modify the pom.xml file as shown below. we have added apache poi library and junit library.

<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>excel-apache-poi</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<name>excel-apache-poi</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.12</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>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.0</version>
				<configuration>
					<source>${jdk.version}</source>
					<target>${jdk.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

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

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;
	}
}
package com.roytuts.excel;
public class User {
	private String userId;
	private String userPass;
	public User(String userId, String userPass) {
		this.userId = userId;
		this.userPass = userPass;
	}
	public String getUserId() {
		return userId;
	}
	public void setUserId(String userId) {
		this.userId = userId;
	}
	public String getUserPass() {
		return userPass;
	}
	public void setUserPass(String userPass) {
		this.userPass = userPass;
	}
}
package com.roytuts.excel;
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;
	}
	public String getMobile() {
		return mobile;
	}
	public void setMobile(String mobile) {
		this.mobile = mobile;
	}
	public String getPhone1() {
		return phone1;
	}
	public void setPhone1(String phone1) {
		this.phone1 = phone1;
	}
	public String getPhone2() {
		return phone2;
	}
	public void setPhone2(String phone2) {
		this.phone2 = phone2;
	}
}

Step 4. Create a generic method to handle any kind of data type to excel through single method.

package com.roytuts.excel;
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 ExcelUtils {
	public static <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 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 write the data to multiple sheets of the excel file. You may also create main class to achieve the same output.

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;
	private List<User> users;
	private List<Contact> contacts;
	@Before
	public void setUp() throws Exception {
		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);
		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);
		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);
	}
	@Test
	public void testWriteToExcelInMultiSheets() {
		ExcelUtils.writeToExcelInMultiSheets("D:/excel.xlsx", "Person Details", persons);
		ExcelUtils.writeToExcelInMultiSheets("D:/excel.xlsx", "User Details", users);
		ExcelUtils.writeToExcelInMultiSheets("D:/excel.xlsx", "Contact Details", contacts);
	}
}

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

generic way of writing data to multiple sheets in excel

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

Generic way of Excel file writing using Apache POI

Download the source code from the link here.

Thanks for reading.

Tags:

9 thoughts on “Generic way of writing data to multiple sheets in excel using apache poi

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

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

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

  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

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

Leave a Reply

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