How to generate excel documents/reports using templates in Java?

Many a times we need to export information into an excel with a particular design, font, color etc.

Using libraries like Apache POI etc are tedious. Every developer need a library which can save their precious time and which is easy to use.

JXLS is the library which makes excel generation very easy. JXLS uses a special markup in excel templates to define output formatting and data layout.

Let’s deep dive into the details.

Below is the maven dependency of the library.

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls</artifactId>
    <version>2.10.0</version>
</dependency>

JXLS provides us with 2 implementations

1. Apache POI — To use Apache POI API based transformer implementation

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-poi</artifactId>
    <version>2.10.0</version>
</dependency>

2. JExcel — To use Java Excel API based transformer implementation

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-jexcel</artifactId>
    <version>${jxlsJexcelVersion}</version>
</dependency>

Only one of the above dependency is needed.

Let’s prepare a sample excel template.

In the first cell of the excel you need to mention the last cell where your data ends.

jx: each — this acts like a for loop, this name should be the same as the collection name(employees in items) from the source code.

var — var name can be anything.

Let’s have a look into the source code.

List<Employee> employeeList = employeeService.getEmployees();
try (InputStream is =       getClass().getClassLoader().getResource(“excelTemplates/Employee.xlsx”).openStream()) {
 try (ByteArrayOutputStream os = new ByteArrayOutputStream()) {
 Context context = new Context();
 String fileName = “Employee”;
 context.putVar(“employees”, employeeList);
 JxlsHelper.getInstance().processTemplate(is, os, context);
}
}

context.putVar(“employees”, employeeList);

The collection name in the above line should match the name in items in the excel template.

You can even print individual properties instead of arrays in the context as below,

String createdDate = LocalDateTime.now().toString();

context.putVar(“createdDate”, createdDate);

This are just some the tags from the library, you can find many such tags from the official website of JXLS http://jxls.sourceforge.net/

NOTES:

(i)

i. The above tags are added as the comments to the cell in the excel.

(ii)

ii. Right click on the cell and you can see the option insert comment.

(iii)

iii. If you are unable to see the comment you added, right click on the cell and click on show/hide comment.

Please feel free to reach out in case of any queries.

Thank you.