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.
