DEV Community: Andres Sacco

JXLS: How to generate Excel documents using templates?

Andres Sacco — Wed, 03 Feb 2021 12:32:48 +0000

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:

Load the template
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.
JXLS process all the information and generate a new report.
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:

Archunit: Validate the architecture of our projects

Andres Sacco — Sat, 30 Jan 2021 17:53:56 +0000

Introduction

When you create a microservice or a library, you start defining the location of each group of elements (enums, classes, interfaces) in one or more packages and the best practices, i.e. if you use Spring Boot the controllers should be annotated with @RestController. At the beginning, everything will be fine because a little group of developers works on the project. However, when you start adding developers of your team or from another team in the company add new functionalities is when the error appears.

To prevent any error in the code of the project, someone in your team needs to review all the changes before merging in master. This approach looks good at the beginning but there are some problems:

Really time-consuming tasks
The rules could change in the future so everyone needs to know the new rules before checking any code.

How can you resolve this problem in a simple way?

At the back of your mind, the first solution would be tools like Checkstyle, PMD, or Findbugs which does a statical analysis of the code but these tools do not have the possibility to check the structure of the project.

Some years ago Archunit appeared which is a free and extensible library for checking the architecture of your project using just a unit test. This library gives you the chance to check the layer’s structure (which package/class depends on another), valid annotations in each class, check for cyclic dependencies, and more.

To start your Archunit tests you will need to add the dependency related to the version of Junit that you use.

<dependency>
    <groupId>com.tngtech.archunit</groupId>
    <artifactId>archunit-junitX</artifactId> //"X" could be 4/5
    <version>0.15.0</version>
    <scope>test</scope>
</dependency>

After adding the dependency your first Archunit test should look like the example below:

Let me explain in detail each block of code:

With this annotation, you tell Archunit which package needs to validate all the rules, a good practice is to put the package which contains all the objects of your project. Also, practice is telling to Archunit not to validate the test just the code that does something.
You need to indicate the field or method is an Archunit Test.
This is the test with all the conditions to validate. The example checks that all the fields inside of any class in this particular package aren’t public. Just to clarify, “fields()” is a static import of “ com.tngtech.archunit.lang.syntax.ArchRuleDefinition.fields”

In this post, you can see some examples of which things you can validate with Archunit.

General rules of coding

Archunit defines some general rules of coding you can use to validate your project, most of them are defined in the class “GeneralCodingRules”. To use these rules you need to add a test to your project, some examples of these rules are:

Naming conventions

You can validate all the objects in one particular package contain a suffix, i.e. xxxController or xxxService.

Layered architecture

Good architecture needs to have different layers and each of them will only be able to access some other particular layers. You can define the layers and the relation between them, i.e. the controllers in one microservices can not be accessed for any other layer.

Return type

In the controller’s endpoints, you can validate the type of response. For example in the case of Spring Boot, a good practice is to return a “ReponseEntity”.

Annotations

Sometimes you need to validate that some classes/interfaces/methods or, fields use a particular annotation.

Custom rules

There are some cases when the methods to validate some rules are not cover with Archunit by default so you can create custom conditions like validating that your entities/model objects contain “equals” and “hashcode” methods.

To use this condition in one particular test you can do this:

Best practices

There are some good practices in order to reduce the lines of code and organize the test depending on the types of rules. Here are some of them:

Define the package of the classes to analyze, the name of the packages or any constant in one class to have a place with all the constants you use in all the tests.
Define the rules in a way you can reuse them in different places, i.e. you can create a rule that checks that private methods are not allowed and call these methods from different places.
Try to put all the rules related to one validation group in one class, i.e. create a class that contains all the rules related to the controller validations in the case of one microservice.
Split each test class to find all the validations related to classes, fields, constructors, and methods in a simpler way. The important thing at this point is to be able to identify each group easier and to be able to prevent the same tests to be checked in a different way.

Conclusion

Archunit is a powerful library that gives you the chance to validate every rule you can imagine in your architecture and reduce the number of errors in your projects.

You need to understand if you add Archunit into an existing project, you would find some issues because most of your projects have some mistakes related to the structure of the names of the classes. It’s key to add the general rules at the beginning and then add the specific ones.

For curious ones, here is the code on Github:

Solve the problems with dependencies in Java

Andres Sacco — Fri, 29 Jan 2021 11:48:18 +0000

Introduction

When we work with projects which use Java, the first thing that we do is add dependencies, libraries or frameworks, that we need to use. To do this, we use a dependency management tool like Maven or Gradle, depending on the benefits that we consider are the best of our projects.

Each dependency that we add for our project contains other dependencies; most of the developers know this concept under the name of transitive dependencies. In many cases, this situation doesn’t present any problem because our dependency manager resolves in a good way the conflicts of versions between different libraries. Still, there are other cases when these conflicts produce some errors when we use our API/library.

Please let me clarify the stated above with the following examples:

Figure 1 — Our API/library imports some libraries which use a particular library with the same version.

Figure 2 — Our API/library imports some libraries which use a particular library with different versions.

In the case of figure 1, there isn’t a problem because all the libraries use the same version, so our dependency manager doesn’t need to do anything to resolve it. The problem appears in figure 2 because the libraries use different versions of the same library so Maven/Gradle tries to resolve the conflict between versions in the best way. In some cases, the resolution of the conflict with the version produces an error, the most common are: “ClassNotFoundException” — “MethodNotSupportedException” — “NoClassDefNotFound”, which appears when our dependency management tool takes a version without this particular class or method.

How can I resolve this problem?

To resolve this problem, you have different alternatives to find and fix it. Here are the steps that I follow to find the solution:

We need to find which libraries produce the error. In most cases, the error in the console or the IDE (Eclipse/IntelliJ) shows the entire package of the class so you can find the jar which contains this class. For example: java.lang.NoClassDefFoundError: ch/qos/logback/core/status/WarnStatus 2. In the case that the error is related to some class which not exists, you can use the entire package and use Findjar. This online search engine helps you to find a library that contains one particular Class. 3. With the name of the dependency which contains the class, you can find all the imports of this library. To do this there are some options depending on your dependency management tool: Maven To see all the tree of the dependencies in our project, you can run the following command: mvn dependency:tree If you want to find just the libraries which include a certain dependency you can run: mvn dependency:tree Gradle To see all the tree of the dependencies in our project you can run the following command: gradle dependencies — scan 4. When you detect which libraries contain the dependency that causes the problem, you need to exclude from our dependency management file (pom.xml or build.gradle) all the libraries which contain a different version of the dependency. In some cases, the library which produces the error not exists in the dependency tree because some of the libraries that you added have dependency marks as “provided” so you need to have this library in your pom to fix the problem.

You can use this solution when you run your application/library and the problem appears, but you have another way to detect this problem before that happens. There is a plugin which we can use in our dependency manager (Maven or Gradle) to check the problem with the dependencies; the following are the implementation for each dependency manager:

These plugins have a lot of features to use but the important in our case is DependencyConvergence, which shows all the possible problems with the libraries.

When you add in your project one of these plugins and run the validation if something is wrong the message that will appear in the console looks like this:

This output means that the project has two dependencies “junit-jupiter-engine” but with different versions. As I mentioned before you can resolve the problem exclude the dependency in “spring-boot-start-test” or tell to dependency management tool which version need to use it.

Conclusion

If you have a very old project and add the enforce plugin maybe you will discover a lot of conflicts to resolve which in some cases it’s impossible to resolve in a very short period but if you create a new project you can add the plugin from the beginning and prevent possible problems in the future.
You can see there are different alternatives to solve this problem. Now it’s up to you to decide which solution is the best one.