DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

Cover image for JXLS: How to generate Excel documents using templates?
Andres Sacco
Andres Sacco

Posted on

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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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.

The rows in orange are inactive clients
First, you need to clear the document, the focus is on the format of the template.
Empty document

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 jx:each in an explicit way

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:
Final document

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:

Top comments (6)

Collapse
drvicx profile image
Victor Nuzhdin • Edited on

Hi, everyone. Please help me with some problem. I need solution for catch and convert final output stream produced by "report.createDocument(outStream, templateName, data)" to HttpServletResponse in my Spring MVC Controller. Does anyone have a solution for intercepting the output stream? Thanks to Andres Sacco for such useful tool like a JXLS and thank to all for answers :)

Collapse
drvicx profile image
Victor Nuzhdin • Edited on

I found solution. Maybe useful for someone
//--step1: create intermediate stream
ByteArrayOutputStream jxlsOutStream = new ByteArrayOutputStream();
//--step2: call create Excel stream method and write result to jxlsOutStream
report.createDocument(jxlsOutStream, templateName, data);
//--step3: create HTTPServletResponse output stream
ServletOutputStream outputStream = response.getOutputStream();
//--step4: convert ByteArrayOutputStream to InputStream
byte[] bytes = jxlsOutStream.toByteArray();
InputStream inputStream = new ByteArrayInputStream(bytes);
//--step5: copy data from InputStream to OutputStream data types
IOUtils.copy(inputStream, outputStream);
//--step6: now we have JXLS streaming data in HttpServletResponse
public void getJXLSStream(HttpServletResponse response) {..}

..thanks to Me))

Collapse
pkatul profile image
Atul Kumar PK

I would like to know how to print columns of a row from an array?

For an example, how to write the template for the following object list?

clients: [
names[]
]

I wanna write the names of first client in first row.
How to access the name like ${names[index]} ?????
Could you please share if you know the solution?

Collapse
alekyaragipally profile image
alekyaragipally • Edited on

you need to specify your array name in items, and give any var name in var, and last cell is the last cell where your data ends
jx:each(items="arrayName" var="anyVarName" lastCell="E5")

Add it as a comment to the first cell of the excel where the dynamic data starts

Collapse
xuanlingchen profile image
Xuanling-Chen

Hi
I would like to know is there anyway to hidden the header when the condition dataset is empty.

Collapse
vedprakash profile image
vedprakash

Could someone can suggest solution for this.
stackoverflow.com/questions/704319...

Head to your account's Settings to...

🌚 Enable dark mode
πŸ”  Change your default font
πŸ“š Adjust your experience level to see more relevant content