Introduction
With this example I will show you how to create an excel file and write a list of objects to multiple sheets in excel file using Apache POI in Java language. In this file I am going to show generic way of writing data to multiple sheets in excel using Apache POI and Java. 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:
- Generic way of reading Excel file using Apache POI
- Generic way of Excel file writing using Apache POI
Prerequisites
At least Java 1.8, Apache POI 3.1.2 – 5.0.0
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:3.1.2' to 5.0.0
//required only for jdk 9 or above
implementation('com.fasterxml.jackson.core:jackson-databind:2.11.2') // 2.12.2
}If you are creating maven based project then you can use below 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>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>
<maven.compiler.source>12</maven.compiler.source>
<maven.compiler.target>12</maven.compiler.target>
</properties>
<dependencies>
<!-- apache poi for xlsx, docx etc reading/writing -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.1.2 to 5.0.0</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>
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", "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);
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 (Contact Details) below in the image.
That’s all about how to write generic way to multiple excel sheets using Java and Apache POI.
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.
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