Home » Java » Core Java » Flexible Styling with Varying Criteria for POI-created documents

About Vadim Korkin

Vadim Korkin
Vadim is a senior software engineer with lots of experience with different software technologies including Java, Javascript, databases (Oracle and Postgres), HTML/CSS and even machine learning. He likes learning new technologies and using the latest libraries in his work. Vadim also has some personal projects on Github

Flexible Styling with Varying Criteria for POI-created documents

Intro

This post explains the difficulties with applying styles to a document based on varying criteria and offers a solution. One of the common tasks in Java programming is to create an Excel report from data that is stored in the database. In these cases, Java programmers use POI :-).  These reports often have strict rules for styling and data formats. And the data is usually the result of a SQL query execution. 

The goal is usually to turn this:

into this:

Problem

The problem here is that styles can be applied based on varying criteria. For example, numbers may be formatted with different numbers of digits after the decimal separator. Dates may need to be formatted with a special pattern. And the last but not the least, some columns or rows may need to be selected with color. 

Applying these styles “directly” in the code will lead to a lot of code that is hard to read and maintain. Every time the style of the report changes a lot of code may need to be changed.

It would be great if styles could have a “pattern” with them like a predicate and could be applied depending on this predicate. But how to implement his?

Solution

The proposed solution solves the predicate problem. I used this solution with POI to generate reports so mostly I’ll assume a POI-like library is used.

Style Map Generation

Before any document-creating operations, a style map must be created. This style map will contain the styles mapped to keys. The key must include all values which may affect the style. For example the type of the value (in Java), the row number, the column number and the value itself. 

This map will look like this:

Key Sequence Generation

The idea is to generate a List (java.util.List) of keys to try in the correct order. This List is used to define the style of the object. Every key from the List is tried in the iteration order until one has a value in the style map. 

The general idea is to start with more detailed keys with all the values filled in and end with the most general style that can be applied to any cell. 

For example (ri is row index, ci column index):

List<CellStyleKey> allKeys = new ArrayList<>();
if (val != null) {
    if ((ri != null) || (ci != null)) {
        allKeys.add(new CellStyleKey(val, val.getClass(), ri, ci));
        allKeys.add(new CellStyleKey(val, val.getClass(), ri, null));
        allKeys.add(new CellStyleKey(val, val.getClass(), null, ci));
        allKeys.add(new CellStyleKey(val, val.getClass(), null, null));
        allKeys.add(new CellStyleKey(null, val.getClass(), ri, ci));
        allKeys.add(new CellStyleKey(null, val.getClass(), ri, null));
        allKeys.add(new CellStyleKey(null, val.getClass(), null, ci));
        allKeys.add(new CellStyleKey(null, val.getClass(), null, null));
    } else {
        allKeys.add(new CellStyleKey(val, val.getClass(), null, null));
        allKeys.add(new CellStyleKey(null, val.getClass(), null, null));
    }
}
allKeys.add(new CellStyleKey(null, null, ri, ci));
allKeys.add(new CellStyleKey(null, null, ri, null));
allKeys.add(new CellStyleKey(null, null, null, ci));
allKeys.add(new CellStyleKey(null, null, null, null));

In this code, the most detailed key is the one that contains all values: the value, the class, the row number, and the column number. The least detailed key contains no values. 

Style Map Generation

For every key in the list (the list should be as short as possible) the get operation is performed on the style map. If the style map has a value for this key it is the style. 

Conclusion

This scheme allows for a simple yet flexible assignment of styles to rows/columns/values/classes in an Excel document. 

Published on Java Code Geeks with permission by Vadim Korkin, partner at our JCG program. See the original article here: Flexible Styling with Varying Criteria for POI-created documents

Opinions expressed by Java Code Geeks contributors are their own.

(0 rating, 0 votes)
You need to be a registered member to rate this.
Start the discussion Views Tweet it!
Do you want to know how to develop your skillset to become a Java Rockstar?
Subscribe to our newsletter to start Rocking right now!
To get you started we give you our best selling eBooks for FREE!
1. JPA Mini Book
2. JVM Troubleshooting Guide
3. JUnit Tutorial for Unit Testing
4. Java Annotations Tutorial
5. Java Interview Questions
6. Spring Interview Questions
7. Android UI Design
and many more ....
I agree to the Terms and Privacy Policy
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments