samjaf samjaf - 5 months ago 216
Java Question

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

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
and a right border at column
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?


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.


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.setCellProperties() 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.