Apply multiple CellStyle on a cell with Apache POI

By | August 6, 2014

Today, I will talk about my way of managing styles with Apache POI .

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);

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 possibles combinations. What would have been nice is that you can add multiple styles in a cell.

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()
                    &amp;&amp; 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");
 
}

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.

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);
    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.

10

Leave a Reply

Your email address will not be published. Required fields are marked *

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