JXLS: How to generate Excel documents using templates?
Introduction
Sometimes you need to export some information into an Excel document with a particular format (some specific fonts for the content or some colors for column headers). One option to solve this problem is to use JasperReport which provides a good interface and a lot of features that will allow you to design a report. A static report doesn’t change a lot you don’t have any problem but imagine that the users want some changes once a week because they don’t like the format, structure nor content.
To solve this situation you have 2 alternatives:
- Spend a lot of time each week to make the changes.
- Use a library that allows you to modify the format of the report just calling it from your project.
How can you solve this problem?
In order to solve the problem that anyone can modify it without knowing a lot of some specific technology appears JXLS. JXLS is a library that provides you the possibility of creating an Excel template with some specific logic inside i.e. you can define the color of the cells depending on the content of one attribute or hide one row if one attribute is null or empty.
Behind the scenes, JXLS uses Apache POI or JExcel as core and other libraries to translate some tags in the template into a new document with all the information.
To start using JXLS you will need to add the dependency in your pom file:
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.9.0</version>
</dependency>After that, you need to choose which implementation of JXLS you will use to generate the Excel document. You have two possible options:
- Apache POI is the most common library which most developers use to generate Excel documents in an artisanal way.
- JExcel is an old library that provides common features. The main problem with this library is the last version was launched in 2019.
<!-- Apache POI -->
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>2.9.0</version>
</dependency><!-- JExcel -->
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-jexcel</artifactId>
<version>1.0.9</version>
</dependency>
Above you will see both dependencies just to show you the name of them, but you need to add only one of them.
Populating the template
Now you have dependencies in your project, the next thing to do is to send the information to JXLS. You need to have a class that transforms all the information in a way that JXLS can understand it. The code below is an example of how you can implement it.
Let’s explain the code:
1- Load the template
2- The Context is a map with all the attributes you will send to the JXLS to generate the document. For this specific example, the method “createDocument” receives a Map so you can reuse this code on any template and send a dynamic number of attributes to it.
3- JXLS process all the information and generate a new report.
4- Close the outputStream.
The objective of this example is to reduce the lines of code to create multiples reports so for that reason the method receives the name of the template and the attributes to send to JXLS. Also, the method receives the outputStream so you can save the document in a file for a desktop application or download it in a web application in a simple way.
Generating a template
Now you need to create the template. Imagine that someone in your team sends this document and tells you he/she needs this particular format.
First, you need to clear the document, the focus is on the format of the template.
The second step is to add the tags to provide some logic in the document, there are two ways to do it:
- As comments in the cells
- Explicit in the document
The most common tags of JXLS are:
- jx:area this tag tells JXLS is the area to process.
— lastCell: in this attribute, you need to add which is the last cell to process. This is important because JXLS not process anything after that cell. - jx:each the idea behind this tag is to iterate a collection of elements.
— items: is a name of the variable in the context which is a collection.
— var: is the name of the variable to use
— lastCell: is the last cell to process for this particular tag - jx:if you can show the content with one format or another depending on the condition.
— condition: is the condition to validate
— lastCell: is the last cell to process for this particular tag
— areas: is a reference to an area to show in the case of the condition is valid or not.
This post only mentions some of the tags but there are more of them to do different things i.e. creating graphics or a multi-sheet. You can check all the available tags in the official documentation.
The third step is access to each element in the “Context” to populate the document, the way to do it is with the name of the object that you previously inserted in the “Context”. You can show any value of the objects using ${object} in any cell. One thing to mention is if you have an object with attributes you can access in this way “${object.attribute}” (i.e. ${client.firstName}).
Now it’s time to implement all the concepts in the post, after modifying the document you can have something like this:
Some comments about the document:
- The cell “Created At” have the tag jx:area
- The row below the headers has two tags: jx:each to iterate all the clients and jx:if to show the row with one format or another depending on if the client is active or not
- The formula has two elements because depending on if the condition is valid or not, JXLS uses one row or another to apply the formula.
The last step is to create a class that invokes the “Report” class and sends the location of the generated document. For simplicity in this post, the code of that class is in the repository of Github which appears after the conclusion.
Conclusion
JXLS is a library with a lot of features that allows you to generate an Excel document in a simple way. The most important thing to consider how easy is for anyone to change the structure, format, or content of the template.
For curious ones, here is the code on Github and the official documentation:
