Overview

In this section you will see how you can customize result workbook during XLS transformation. For example you may want to change style of some cells dynamically depending on exported data. Or you may want to change values of some bean properties for example to scale them or do something else.

Many of this can be done using Excel conditional formatting or macros. jXLS provides alternative way to do this with custom cell and row processors.

Row Processor

You may implement RowProcessor interface if you need to set style for some row dynamically. The interface contains single method void processRow(Row row, Map namedCells). XLSTransformer invokes this method for every registered CellProcessor and passes to it two parameters:

  • Row row - contains all information about the current processed row
  • Map namedCells - contains all named cells that were found during processing of previous cells

With Row object you have access to related POI objects to modify cell style if required.

Let's take a look at StyleRowProcessor class from Row Style sample. This class allows to highlight some rows containing employee information for all employees with payment greater than 2000. 

public class StyleRowProcessor implements RowProcessor {
    String collectionName;
    String styleCellLabel = "customRow";

    public StyleRowProcessor(String collectionName) {
        this.collectionName = collectionName;
    }

    public void processRow(Row row, Map namedCells) {
        // check if processed row has a parent row
        if( row.getParentRow()!=null ){
            // Processed row has parent row. It means we are processing some collection item
            RowCollection rowCollection = row.getParentRow().getRowCollectionByCollectionName( collectionName );
            if( rowCollection.getIterateObject() instanceof Employee){
                Employee employee = (Employee) rowCollection.getIterateObject();
                if( employee.getPayment().doubleValue() >= 2000 ){
                    if( namedCells.containsKey( styleCellLabel ) ){
                        Cell customCell = (Cell) namedCells.get( styleCellLabel );
                        for (int i = 0; i < row.getCells().size(); i++) {
                            Cell cell = (Cell) row.getCells().get(i);
                            org.apache.poi.ss.usermodel.Cell hssfCell = cell.getHssfCell();
                            if( hssfCell!=null ){
                                copyStyle( row.getHssfWorkbook(), customCell.getHssfCell(), hssfCell );
                            }
                        }
                    }
                }
            }
        }
    }
}

You can see that in processRow() method we check does current row have parent row 

// check if processed row has a parent row
if( row.getParentRow()!=null ){
....

The point is that for every row that is being processed as collection item XLSTransformer sets parentRow property with information about base row containing current collection details.

Then we get RowCollection object related to our collection in question using getRowCollectionByCollectionName( collectionName ) method of Row class

// Processed row has parent row. It means we are processing some collection item
RowCollection rowCollection = row.getParentRow().getRowCollectionByCollectionName( collectionName );

After that we check that current iterated object is of required type and cast current collection item to Employee class. And then we check its payment property

if( rowCollection.getIterateObject() instanceof Employee){
    Employee employee = (Employee) rowCollection.getIterateObject();
    if( employee.getPayment().doubleValue() >= 2000 ){ ... }

Then we trying to find named cell with name styleCellLabel that is equal to "customRow"

if( namedCells.containsKey( styleCellLabel ) ){
    Cell customCell = (Cell) namedCells.get( styleCellLabel );

Then we walk through all Cell objects for current row and copy cell style from named cell to current cell

for (int i = 0; i < row.getCells().size(); i++) {
    Cell cell = (Cell) row.getCells().get(i);
    org.apache.poi.ss.usermodel.Cell hssfCell = cell.getHssfCell();
    if( hssfCell!=null ){
        copyStyle( row.getHssfWorkbook(), customCell.getHssfCell(), hssfCell );
    }
}

Method copyStyle(..) is rather simple and just copies cell style from one cell to another. See Row Style sample for the actual code.