Apache POI: Use HSSFWorkbook to Create New Cell Style

The Busy Developer's Guide to HSSF Features (PDF format) is an excellent resource for learning how to use Apache POI (formerly Jakarta POI) in conjunction with Java applications for manipulating Microsoft Excel files. This document lives up to its name (for Busy Developers) and provides a concise but highly useful introduction to the POI HSSF API. It would be nice if all open source products had guides this easy for beginners to use.

This guide points out (in the Creating Date Cells section) that new cell styles (HSSFCellStyle) must be created from a workbook (HSSFWorkbook) or else any changes to the HSSFCellStyle will impact other (perhaps even ALL) cells in the spreadsheet generated with Apache POI.

For example, the following code may not work as one might think from looking at it:

/**
 * Set the style of the supplied cell to be default
 * header cell style.
 *
 * @param aHeaderCell Cell to which default header
 *                    style should be applied.
 */
public static void setDefaultHeaderCellStyle(
   final HSSFCell aHeaderCell )
{
   final HSSFCellStyle cellStyle = aHeaderCell.getCellStyle();
   cellStyle.setAlignment( HSSFCellStyle.ALIGN_CENTER );
   cellStyle.setFillPattern( HSSFCellStyle.SPARSE_DOTS );
   cellStyle.setBorderBottom( HSSFCellStyle.BORDER_DOUBLE );
   cellStyle.setBorderLeft( HSSFCellStyle.BORDER_DOUBLE );
   cellStyle.setBorderRight( HSSFCellStyle.BORDER_DOUBLE );
   cellStyle.setBorderTop( HSSFCellStyle.BORDER_DOUBLE );
}

From first glance at the above code, it is easy to believe that the various "set" methods called on the passed-in HSSFCell will only impact that particular HSSFCell instance because getCellStyle() was called on that instance. However, this code, as shown, actually impacts all cells on the sheet from which this cell came. To ensure that only specific cells are impacted by a custom cell style, one should get the HSSFCellStyle from the workbook as suggested in the Quick Start Guide.

The following example shows this in action:

/**
 * Create a new cell style designed for header-oriented cells.
 * The HSSFWorkbook is required to obtain a newly generated
 * cell style instance so that cells other than those to which
 * this style is set will not be impacted.
 *
 * @param aWorkbook Workbook for which cell style should apply.
 */
public static HSSFCellStyle createDefaultHeaderCellStyle(
   final HSSFWorkbook aWorkbook )
{
   final HSSFCellStyle cellStyle = aWorkbook.createCellStyle();
   cellStyle.setAlignment( HSSFCellStyle.ALIGN_CENTER );
   cellStyle.setFillPattern( HSSFCellStyle.SPARSE_DOTS );
   cellStyle.setBorderBottom( HSSFCellStyle.BORDER_DOUBLE );
   cellStyle.setBorderLeft( HSSFCellStyle.BORDER_DOUBLE );
   cellStyle.setBorderRight( HSSFCellStyle.BORDER_DOUBLE );
   cellStyle.setBorderTop( HSSFCellStyle.BORDER_DOUBLE );
   return cellStyle;
}

Notice that in the first example, a Cell Style instance is obtained using a getCellStyle method (HSSFCell.getCellStyle()). This gets an already instantiated cell style that applies to many more cells than just the cell upon which the getCellStyle() method was called. This means that any changes to the returned cell style impact all cells tied to that cell style.

In the second example, a completely new instantiation of cell style is obtained with a call to HSSFWorkbook.createCellStyle(). This creates a new instance independent of any cells. In fact, to make this newly instantiated cell style apply to any cells, one must call setCellStyle() on the cell (HSSFCell.setCellStyle()) to which the newly created style should apply.

In essence, there are two opposite approaches to updating cell styles here. The first, getting an existing style from any average cell, works with the assumption that all cells have an existing and same style. Changing that style once therefore changes all the cells' styles. The latter approach, creating a new cell style from a workbook, makes the opposite assumption (that no cells are affected by the newly created style) and cells to which the style should apply must be explicitly associated with the new style.

This significant distinction between acquiring a cell style from a cell or creating a new cell style instance via a workbook is documented in the Quick Start Guide as mentioned above and is not a big issue once it is understood. However, this is the type of issue that is a good example of how important it can be to know more about an API than simply what a favorite IDE lists as available through class and method name completion.

UPDATE (21 December 2007): The HSSFCellUtil Javadoc-based API documentation explains the reason for the different behaviors in acquiring and setting a Cell Style. According to the class description for HSSFCellUtil , Excel has a limitation on the number of styles it can support and so it makes sense to have a single style apply to all cells within a sheet and be controlled by that setting (HSSFCell.setCellStyle()). The HSSFWorkbook.createCellStyle() method actually creates a truly different cell style for individual cell styling needs. The Javadoc method documentation for the HSSFCell.setCellStyle(HSSFCellStyle) method does warn that the passed-in HSSFCellStyle "should be an HSSFCellStyle created/retreived from the HSSFWorkbook."