Apache POI: Elegant way to set borders to a column which contains different styles

Question

I'm dynamically creating a new xlsx file using apache-poi. Any column can contain different value types (Numbers, Strings, Booleans, ...). While inserting data into the poi document I set CellStyles depending on the type of the data:

public final XSSFCellStyle cellStyleString;
public final XSSFCellStyle cellStyleNumber;
public final XSSFCellStyle cellStyleDate;
public final XSSFCellStyle cellStyleHeader;

This is how my header rows look like:

|   |   |   | Shared Header |
| H1| H2| H3|SH1|SH2|SH3|SH4|

There are "simple" headers and "shared headers" which contain "sub headers". Shared headers reside in merged cells.

No I'd like to have a left border at column SH1 and a right border at column SH4 to emphasize the grouping. But as any column could contain a mix of all cellstyles, it seems like I have to create CellStyles like

public final XSSFCellStyle cellStyleString;
public final XSSFCellStyle cellStyleStringBorderLeft;
public final XSSFCellStyle cellStyleStringBorderRight;
//and so on for the other styles...

Furthermore there could be nested shared header which I'd like to distinguish by different border sizes. So I'd need something like

public final XSSFCellStyle cellStyleString;
public final XSSFCellStyle cellStyleStringBorderLeftThickLine;
public final XSSFCellStyle cellStyleStringBorderRightThickLine;
public final XSSFCellStyle cellStyleStringBorderLeftThinLine;
public final XSSFCellStyle cellStyleStringBorderRightThinLine;
//and so on for the other styles...

Is there a more elegant way to set the borders of the column regardless of the already existing style?

Edit

Although I prefer a clean and simple approach and that for to minimize the number of created styles, I stumpled upon the HSSFOptimiser which removes duplicate cellstyles. I didn't know about that class. Even though I prefer avoiding this utility it fits the problem and deserves to be mentioned here.

Answer 1

I am nearing the end of an enhancement to POI that will let you fill out the values with their specific styles, then draw borders around them without having to manually create all the necessary styles for that. In the mean-time, there is a way to do it using CellUtil.setCellStyleProperties(). This lets you add a set of properties to the CellStyle that already exists for a cell.

From the POI Quick Guide for HSSF/XSSF:

Workbook workbook = new XSSFWorkbook();  // OR new HSSFWorkbook()
Sheet sheet = workbook.createSheet("Sheet1");
Map<String, Object> properties = new HashMap<String, Object>();

// create your spreadsheet without borders
...

// create property set for vertical borders
properties.put(CellUtil.BORDER_LEFT, CellStyle.BORDER_MEDIUM);
properties.put(CellUtil.BORDER_RIGHT, CellStyle.BORDER_MEDIUM);

// Apply the borders to a 3x3 region starting at D4
for (int ix=3; ix <= 5; ix++) {
  row = sheet.createRow(ix);
  for (int iy = 3; iy <= 5; iy++) {
    cell = row.createCell(iy);
    CellUtil.setCellStyleProperties(cell, properties);
  }
}

This allows you to basically fill in your spreadsheet, then draw the borders one cell at a time. Note if all your borders are similar (all THIN) then this will work for your entire range. But, if you wanted to draw MEDIUM borders around the outside of the table, you would have to create some additional property sets. Note, you don't have to use createRow() and createCell() for rows and cells that are already in your spreadsheet. This will work around merged cells.

Note: CellUtil.setCellStyleProperties() appeared in POI 3.14 and allows you to add multiple cell properties in a single shot which avoids the creation of multiple unused styles. The older CellUtil.setCellStyleProperty() sets a single property at a time and, as an unintended consequence, creates intermediate CellStyle objects in the spreadsheet which turn out never being used. This can be a problem in larger sheets.

Edit: PropertyTemplate is a new object added in POI 3.15 which will allow you to define a group of borders for cells and stamp it onto any sheet you want to apply it to. This object is like creating a preprinted form to overlay the data. See the POI Spreadsheet quick guide for more information on how to use PropertyTemplate.

Answer 2

EDIT:

So how about utilizing the hash of the POI objects for caching and keeping track of decorated objects. The other created CellStyles that are not being used will be thrown away by the garbage collection.

Here is our cache:

private Map<Integer, MyCellStyle> styleCache = new HashMap<>();

And our own CellStyle class

final class MyCellStyle implements Cloneable {
    private XSSFCellStyle xssfCellStyle;

    public MyCellStyle(XSSFCellStyle xssfCellStyle) {
        this.xssfCellStyle = xssfCellStyle;
    }

    @Override
    public MyCellStyle clone() {
        MyCellStyle clone = new MyCellStyle(xssfCellStyle);
        return clone;
    }

    public final MyCellStyle borderLeftMedium() {
        MyCellStyle result = clone();
        result.xssfCellStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
        return result;
    }

    ... further decorations

    public XSSFCellStyle getXSSFCellStyle() {
        return xssfCellStyle;
    }

}

now to avoid creating new objects we write a small function

private MyCellStyle getCellStyle(MyCellStyle targetStyle) {
    int targetHash = targetStyle.hashCode();
    if (styleCache.keySet().contains(targetHash)) {
        return styleCache.get(targetHash);
    } else {
        return styleCache.put(targetHash, targetStyle);
    }
}

then we can create the cells themselves like this:

public void createCells() {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet();

    Row row = sheet.createRow(1);
    Cell cell = row.createCell(1);

    MyCellStyle baseStyle = new MyCellStyle(
            (XSSFCellStyle) wb.createCellStyle());

    MyCellStyle decoratedStyle = getCellStyle(baseStyle.borderLeftMedium());

    cell.setCellStyle(decoratedStyle.getXSSFCellStyle());

}

If the hashCode is not unique for the same properties of the MyCellStyle object we might have to override the hashCode function:

@Override
public int hashCode() {
    return hashValue;
}

and add the styles value within each of our decoration functions:

public final MyCellStyle borderLeftMedium() {
        MyCellStyle result = clone();
        result.xssfCellStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
        hashValue += XSSFCellStyle.BORDER_MEDIUM; // simplified hash
        return result;
    }

=======================

ORIGINAL:

I like to create decorating methods that add a certain aspect of a cell to a cell style. So at first you create your base style

public final XSSFCellStyle cellStyleStringBase = wb.createCellStyle();

and create decorator methods to create a certain style

public XSSFCellStyle addBorderLeft(XSSFCellStyle style) {
    XSSFCellStyle result = style.clone();
    result.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
    return result;
}

Now, if you want to avoid to create new objects, you will still have to keep the cellStyles in own variables, you will not be able to avoid that, but from my experience the performance is sufficient if you simply decorate your cells like this

cell1.setCellStyle(addBorderLeft(cellStyleStringBase);
cell2.setCellStyle(addBorderRight(addBorderRight(cellStyleStringBase));
...

If you decorate with a lot of styles it makes sense to create your own CellStyle Class

public final MyCellStyle implements Cloneable {

    private XSSFCellStyle xssfCellStyle;

    public MyCellStyle(XSSFCellStyle xssfCellStyle) {
         this.xssfCellStyle = xssfCellStyle;
    }

    @Override
    public MyCellStyle clone() {
        MyCellStyle clone = new MyCellStyle(this.xssfCellStyle);
        return clone;
    }

    public final MyCellStyle borderLeftMedium() {
        return this.clone().setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
    }

    public final MyCellStyle borderRightThick() {
        ...

}

you can then build your style in a better readable manner:

MyCellStyle base = new MyCellStyle(cellStyleStringBase);    
cell1.setCellStyle(base
    .addBorderLeftMedium()
    .addBorderRightThick()
    .addBorderBottomThin());

untested, but I hope it helps.

Answer 3

As you already mentioned it is not good to create thousands and thousands of similiar cell-style-objects. in my project i created a simple "style-helper" class which has a map in it, which is aware of all existing style-instances

private Workbook workbook;
private HashMap<String, CellStyle> styleMap = new HashMap<>();

public CellStyle getStyle(Font font, ExcelCellAlign hAlign, ExcelCellAlign vAlign, boolean wrapText, ExcelCellBorder border, Color color, ExcelCellFormat cellFormat) {

    //build unique which represents the style
    String styleKey = ((font != null) ? font.toString() : "") + "_" + hAlign + "_" + vAlign + (wrapText ? "_wrapText" : "") + ((border != null) ? "_" + border.toString() : "") + "_"
            + styleKeyColor + (cellFormat != null ? "_" + cellFormat.toString() : "");

    if (styleMap.containsKey(styleKey)) {
        //return existing instance from map
        return styleMap.get(styleKey);
    } else {
        //create new style from workbook
        CellStyle cellStyle = workbook.createCellStyle();


        // set all formattings to new cellStyle object
        if (font != null) {
            cellStyle.setFont(font);
        }

        // alignment
        if (vAlign != null) {
            cellStyle.setVerticalAlignment(vAlign.getAlign());
        }

        //... snip ...

        //border
        if (border != null) {
            if (border.getTop() > BorderFormatting.BORDER_NONE) {
                cellStyle.setBorderTop(border.getTop());
                cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
            }

            //... snip ...
        }

            if (color != null) {
                XSSFColor xssfColor = new XSSFColor(color);
               ((XSSFCellStyle)cellStyle).setFillForegroundColor(xssfColor);
            }
        }
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        styleMap.put(styleKey, cellStyle);

        return cellStyle;
    }
}

The parameter ExcelCellAlign is a simple enum which encapsulates the values for CellStyle.ALIGN_LEFT, CellStyle.ALIGN_RIGHT, ... ExcelCellBorder is similiar to the Align. Just hide the values :-) ExcelCellFormat is a enum which holds default-patterns for fortmatting the value.

I hope this is a good start for your own implementation. Feel free to ask if something is not clear