Automating Java POI Report Generation
As programmers, we find many ways to write code to generate Excel reports. Usually, these solutions require tons of boilerplate code.
In this article, I present a routine created using Java Reflection to build Java POI XLSX reports using just a template and a data source. While other implementations like this exist (some even better), this is open source, so you don’t have to pay a cent to use it. Just learn or provide some constructive feedback.
Without further ado, let’s dive into it.
The Excel Report Generator
Created as a means to save time and effort when generating reports, the Excel Report Generator tool (ERG) is a simple Java class with Java Reflection routines designed to read an Excel template’s columns and comments and convert them into instructions to parse a database and feed data into it, thus generating the real report.
My motivation for creating this was to look at messy routines using low-level instructions to build reports, row by row and line by line. If we have a template, we can focus on how to retrieve the data and not on how to render the final result for the user.
This is how we go from template to real report:
- The template, an Excel file, contains {placeholders} that describes properties of Java POJOs that the ERG tries to access and replace with real content, as well as comments to describe more advanced methods to fetch data from the data-source (mainly lists iteration).
- The first row and first column of the template has a special comment which indicates how many columns and rows the template contains in total. This helps ERG properly navigate through all the cells available in the template and process them accordingly.
- Constant [placeholders] can be replaced with system information. At this moment, now there is only one available [TODAY] and, as the name suggests, it writes the current date and time.
The comments can describe different ways to access lists within the Java POJOs:
- The :page comment is followed by two lines, representing the number of rows and columns respectively that each template comprises. It’s the only required comment that needs to be present for the ERG to properly process the template and generate the final report.
- The :for comment is followed by three lines, the first one describes the Java POJO’s list property to iterate, and the second and third indicates how many rows and region it has respectively, which is the region that each iteration comprises.
- The :mergeBy comment merges columns based on the length of the Java POJO’s list property indicated in the following line of the comment.
- The :iterateBy comment is similar to the :for one, the only difference being that it does a horizontal iteration in a single row instead of a vertical one in a region.
Placeholders can also contain pipes {property|pipe}. These indicate the type of information that the Java POJO property represents, and the types are described as follows:
- DATE: represents a Date Java POJO property. The written cell is of type String.
- INTEGER: represents an Integer Java POJO property. The written cell is of type Numeric.
- DECIMAL: represents a Double/Float Java POJO property. The written cell is of type Numeric.
Finally, whenever a property is null, we can provide a standard label to be shown {property?no data available|pipe}, this gives a hint to the user that nothing was found to fill the report.
A real use case scenario
To showcase ERG, we’ll consider a scenario in which we want to generate reports for an Academic Application, with Students that attend Classes, which are lectured by Teachers, as shown in figA.
We want a report to display the records of each student, showing classes attended and which teacher lectured these classes, as sown in figB.
First of all, we start by giving information about how many columns and how many rows we want in our template, by writing the :page comment in the first cell, as shown in figC.
:page
9
5
Later, we start to iterate through the Students list by adding the :for comment, as shown in figD, and specifying the properties of the Student class in each cell of the region defined. As shown in figE, the properties used are name, birthDate, address, mobile, and email.
:for
students
6
4
We also want to display each class attended by the Students, so we need to iterate within each Student list of Classes using again the :for comment, as shown in figF. As shown in figG, the properties used are: name, averageGrade, and teacherName. As the property averageGrade is a decimal, we need to use the DECIMAL pipe.
:for
classes
2
2
Finally, we want to display date information [TODAY] and give a name to the sheet {title}. The complete template is shown in figH.
The code to process this template and generate the report follows.
As can be seen in line 149, we provide a label for the ERG to show on the report whenever the given student’s list is empty. This situation is shown in figI (report filled with data) and figJ (empty report).
Another real use case scenario with :mergeBy and :iterateBy
Now, let’s look at this report from a different perspective. Say that, of all available classes, we want to know which ones each student is attending and which ones he’s skipping, like in figK:
To do this, we need to use two aforementioned special comments: :mergeBy will merge cells G2, H2, and I2 into one with the title “Classes” and :iterateBy will describe which classes the student attended (marked with an X) and which ones he didn’t.
To do this, we need the list of classes to be accessible from the data-source class for the :mergeBy comment, like in figL:
As in the first scenario, there’s a :for comment that iterates through the list of students, as shown in figM:
Let’s say that the mobile and e-mail information is optional for the student to provide, this means that their value can be null. In these cases, it’s interesting to be able to provide a label describing why we were unable to fetch the information, therefore, we use the optional parameter ? for this purpose, as shown in figN:
Finally, to get the list of classes names marked or not with an X, there’s a method on the Student class to cross-check the list of available classes with the attended ones:
We use this method on the :iterateBy comment to draw the list of classes marked with X, like in figO.
Conclusion
The Excel Report Generator provides an efficient way to handle and process excel reports in an automated way, discarding hundreds of hard-coded lines needed to generate reports. Developers are thus able to focus solely on how to retrieve the information (data-source) and not so much on how to display them in the report.
This is by no means the best project to get the job done, and there are many solutions out there, many of them paid. For sure, many flaws need to be fixed in the long run but this solution works well enough if you’re looking to automate your reports.
If you want to check ERG, you can go here.
Thanks for reading! : )
