Cell Listeners

For finer control over the content and formatting of the resultant spreadsheet cells, JETT allows the user to create CellListener objects that are notified every time a cell's content has been evaluated, whether it contains static content, Expressions, or bodiless tags whose content is evaluated. Cells with tag text have their tag text removed before processing. Any object that implements the CellListener interface may be registered with the ExcelTransformer prior to transformation. The CellListener interface contains two methods:

public boolean beforeCellProcessed(CellEvent event);
public void cellProcessed(CellEvent event);

Register a CellListener with the ExcelTransformer:

transformer.addCellListener(new MyCellListener());

When a Cell is processed, JETT generates a CellEvent and notifies all registered CellListeners. A CellListener can retrieve context information by retrieving properties of the CellEvent. The beforeCellProcessed method is called just prior to a cell being processed. Return true to process the cell as normal, and return false to prevent the processing of the cell, which stops cellProcessed from being called.

Cell cell = event.getCell();
Map<String, Object> beans = event.getBeans();
Object oldValue = event.getOldValue();
Object newValue = event.getNewValue();

The Cell is a reference to the POI Cell object representing the actual cell in the spreadsheet. The Map object is a reference to the beans map. It may have extra beans added, if the Cell is in the middle of a "forEach" tag, or for that matter, any tag that adds values to the beans map. The old value is the template value obtained from the template spreadsheet. The new value is the resultant value after the evaluation of any Expressions (null when passed to beforeCellProcessed). Any CellListener can use the Cell object to gain access to the POI Row object, then the POI Sheet object, and even the POI Workbook object.


A CellListener can be created to shade different rows alternately different colors within a result set. If a forEach tag defines the optional "indexVar" attribute to be "index", then an Integer bean "index" will be available in the beans map for the CellListener to observe.

This CellListener will shade alternating rows light gray. It uses POI objects and functionality to change the color of the alternating rows. It will change the color of alternating rows for ANY collection processing that defines its "indexVar" to be "index".

public void cellProcessed(CellEvent event)
   Map<String, Object> beans = event.getBeans();
   Object index = beans.get(“index”);
   if (index != null && ((Number) index).intValue() % 2 == 0)
      Cell cell = event.getCell();
      CellStyle style = cell.getSheet().getWorkbook().createCellStyle();

This example uses a forEach tag to process the "employees" collection.

Employee Salary Manager
<jt:forEach items="${employees}" var="employee" indexVar="index">${index + 1}. ${employee.lastName}, ${employee.firstName} ${employee.salary} <jt:if test="${employee.getManager() != null}" then="${employee.manager.lastName}, ${employee.manager.firstName}"/></jt:forEach>

...gets transformed into...

Employee Salary Manager
1. Stack, Robert $1000.00  
2. Queue, Suzie $900.00 Stack, Robert
3. Fudd, Elmer $800.00 Stack, Robert
4. Bunny, Bugs $1500.00