Overview

This section contains samples demonstrating how to dynamically change style of some cells during export

Row Style sample

This sample demonstrates how to use custom RowProcessor to higlight rows for all employees with payment greater than 2000. See also Customizing jXLS output reference section for details on writing custom RowProcessor.

In this sample we have StyleRowProcessor class implementing RowProcessor interface

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);
                            HSSFCell hssfCell = cell.getHssfCell();
                            if( hssfCell!=null ){
                                copyStyle( row.getHssfWorkbook(), customCell.getHssfCell(), hssfCell );
                            }
                        }
                    }
                }
            }
        }
    }

    private void copyStyle(HSSFWorkbook workbook, HSSFCell fromCell, HSSFCell toCell){
        HSSFCellStyle toStyle = toCell.getCellStyle();
        HSSFCellStyle fromStyle = fromCell.getCellStyle();
        if( fromStyle.getDataFormat() == toStyle.getDataFormat() ){
            toCell.setCellStyle( fromStyle );
        }else{
            HSSFCellStyle newStyle = workbook.createCellStyle();
            newStyle.setAlignment( toStyle.getAlignment() );
            newStyle.setBorderBottom( toStyle.getBorderBottom() );
            newStyle.setBorderLeft( toStyle.getBorderLeft() );
            newStyle.setBorderRight( toStyle.getBorderRight() );
            newStyle.setBorderTop( toStyle.getBorderTop() );
            newStyle.setBottomBorderColor( toStyle.getBottomBorderColor() );
            newStyle.setDataFormat( toStyle.getDataFormat() );
            newStyle.setFillBackgroundColor( fromStyle.getFillBackgroundColor() );
            newStyle.setFillForegroundColor( fromStyle.getFillForegroundColor() );
            newStyle.setFillPattern( fromStyle.getFillPattern() );
            newStyle.setFont( workbook.getFontAt( fromStyle.getFontIndex() ) );
            newStyle.setHidden( toStyle.getHidden() );
            newStyle.setIndention( toStyle.getIndention() );
            newStyle.setLeftBorderColor( toStyle.getLeftBorderColor() );
            newStyle.setLocked( toStyle.getLocked() );
            newStyle.setRightBorderColor( toStyle.getRightBorderColor() );
            newStyle.setTopBorderColor( toStyle.getTopBorderColor() );
            newStyle.setVerticalAlignment( toStyle.getVerticalAlignment() );
            newStyle.setWrapText( toStyle.getWrapText() );
            toCell.setCellStyle( newStyle );
        }
    }
}
            

In StyleRowSample class we create StyleRowProcessor object and register it with XLSTransformer.

            Map beans = new HashMap();
            beans.put("department", department);
            XLSTransformer transformer = new XLSTransformer();
            transformer.registerRowProcessor(new StyleRowProcessor("department.staff"));
            transformer.transformXLS(templateFileName, beans, destFileName);
            

The first row in XLS template is hidden. And its first cell contains custom style to use for highligting.

Take a look at screenshot of template

Screenshot of RowStyle sample XLS template file

See excel version rowstyle.xls

Sample output screenshot is

Screenshot of RowStyle sample output

Excel version is rowstyle_output.xls