Apply multiple CellStyle on a cell with Apache POI

Hi,

I will talk about my way of managing styles with Apache POI . Indeed to add a style to a cell with POI you must create a CellStyle:

Workbook wb = new HSSFWorkbook();
CellStyle grey25ForeGroundColor = wb.createCellStyle();
grey25ForeGroundColor.setFillForegroundColor(IndexedColors.Grey_40_PERCENT.getIndex());
grey25ForeGroundColor.setFillPattern(CellStyle.SOLID_FOREGROUND);

This create a style applying a light gray background color on a cell. You then apply the style to the cell as follows:

Sheet sheet = wb.createSheet("Feuille 1");
Row row = sheet.createRow((short)0);
Cell cell = row.createCell(0);
cell.setCellValue("Test");
cell.setCellStyle(grey25ForeGroundColor);

It works fine but I can produce several Excel file with lots of different styles in each file. Rather than create a style for each cell, I told myself that I would pool the code in an abstract class. But then, it poses a big problem. Imagine that I wanted a gray cell as the one we just created.
So I create my style in my abstract class. Now I want a gray cell with centered content and another with centered content and borders . I will not create a style for each possible combination. What would have been nice is that you can add multiple styles in a cell. Merely we can not put one.

Here is my solution that allows you to merge several styles together.

/**
* Merges multiple CellStyle each other. If two CellStyle have a style in common. The following takes over the former.
* @Param String A list of CellStyle to merge made from a list contained in a HashMap.
* @return CellStyle The CellStyle containing all styles..
*/
protected CellStyle mergeCellStyle(String... cellStyles) {
    // Vacuum style that will serve as a basis for comparison.
    CellStyle defaultStyle = wb.createCellStyle();
    // Base CellStyle that will serve to aggregate styles to merge.
    CellStyle currentStyle = wb.createCellStyle();
    try  {
        // We loop the CellStyles merge
        for(String cellStyleString:cellStyles) {
            // Is recovered CellStyle to merge with the PREV (s)
            CellStyle cellStyle = styles.get(cellStyleString)
            // We retrieves the list of methods of the CellStyle object  that we want to take into consideration when merging.
            Set<String> keys = mergeMethods.keySet();
            // We loops through the list of these methods.
            for(String key : keys)
                Method method = cellStyle.getClass().getMethod(key);
                Class<?> returnType = method.getReturnType();
                Object result = method.invoke(cellStyle);
                Object resultBase = methode.invoke(currentStyle);
                Object defaultStyle = method.invoke(defaultStyle);
                /* We compare if there is a change from the current style.
                * and if this change does not match the base style to avoid
                * backtracking.
                */
                if(result.hashCode() != resultBase.hashCode() && result.hashCode() != resultDefault.hashCode()) {
                    // If there is a change we modified the current style.
                    currentStyle.getClass().getMethod(mergeMethods.get(key), returnType).invoke(currentStyle, result);
                }
            }
        }
    }
    // The merged style is returned.
    return currentStyle;
    // Catch clause
}

Note here that the hashcode is used for comparison. Indeed, as we use reflexivity, the result of the invocation of a method is of type Object. So the difference with the “! =” will not give a relevant result. And as the types used by POI to change styles are primitive, a difference made with “” “. Equals ()” will not give the desired result either.

I give you the method to load the HashMap mergesMethods:

private void loadMergeMethod() {
    mergeMethods.put("getAlignment", "setAlignment");
    mergeMethods.put("getBorderBottom", "getBorderBottom");
    mergeMethods.put("getBorderLeft", "getBorderLeft");
    mergeMethods.put("getBorderRight", "getBorderRight");
    mergeMethods.put("getBorderTop", "getBorderTop");
    mergeMethods.put("getBottomBorderColor", "getBottomBorderColor");
    mergeMethods.put("getLeftBorderColor", "getLeftBorderColor");
    mergeMethods.put("getRightBorderColor", "getRightBorderColor");
    mergeMethods.put("getTopBorderColor", "getTopBorderColor");
    mergeMethods.put("getDataFormat", "getDataFormat");
    mergeMethods.put("getFillBackgroundColor", "getFillBackgroundColor");
    mergeMethods.put("getFillForegroundColor", "getFillForegroundColor");
    mergeMethods.put("getFillPattern", "getFillPattern");
    mergeMethods.put("getHidden", "getHidden");
    mergeMethods.put("getIndention", "getIndention");
    mergeMethods.put("getLocked", "getLocked");
    mergeMethods.put("getRotation", "getRotation");
    mergeMethods.put("getShrinkToFit", "getShrinkToFit");
    mergeMethods.put("getVerticalAlignment", "getVerticalAlignment");
    mergeMethods.put("getWrapText", "getWrapText");

}

Pour ceux qui se demanderais pourquoi je n’ai pas utiliser la récursivité pour récupérer les méthodes de manière dynamique? La réponse est que la méthode MaClasse.getMethods() retourne l’ensemble des méthodes public, privé, protected de la classe. Nous serions donc obligés de filtrer les méthodes à exclure. J’ai donc préféré filtrer les méthodes à inclure.

For those who ask why I do not use recursion to retrieve methods dynamically? The answer is that the MaClasse.getMethods () method returns the set of public, private ,_ protected methods of the class. We would therefore have to filter methods to exclude. So I preferred to include methods that i want to used.

</pre>
private void createDefaultStyle() {
    // CellStyle with border
    CellStyle cellBordered = wb.createCellStyle();
    // Add border
    cellBordered.setBorderBottom(CellStyle.BORDER_THIN);
    cellBordered.setBorderLeft(CellStyle.BORDER_THIN);
    cellBordered.setBorderRight(CellStyle.BORDER_THIN);
    cellBordered.setBorderTop(CellStyle.BORDER_THIN);
    // Set border color
    cellBordered.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    cellBordered.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    cellBordered.setRightBorderColor(IndexedColors.BLACK.getIndex());
    cellBordered.setTopBorderColor(IndexedColors.BLACK.getIndex());
    styles.put(STYLE_CELL_BORDERED, cellBordered);

    // CellStyle with text centered
    CellStyle cellCentered = wb.createCellStyle();
    cellCentered.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put(STYLE_CELL_CENTERED, cellCentered);

    // CellStyle with light grey background
    CellStyle grey25BackgroundColor = wb.createCellStyle();
    grey25BackgroundColor.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    grey25BackgroundColor .setFillPattern(CellStyle.SOLID_FOREGROUND);</li>
    styles.put(STYLE_CELL_GREY_25_BACKGROUND_COLOR, grey25BackgroundColor);

The variable styles is a hashmap<String, CellStyle> containing the list of available styles .
We just have to apply styles on the cell.

Sheet sheet = wb.createSheet("Feuille 1);
Row row = sheet.createRow((short)0);
Cell cell = row.createCell(0);
cell.setCellValue("Test");
cell.setCellStyle(mergeCellStyle(STYLE_CELL_GREY_25_BACKGROUND_COLOR, STYLE_CELL_CENTERED, STYLE_CELL_BORDERED));

And that is. A beautiful cell centered with border and gray background.
See you soon.