jXLS 2.3 - add the listener to update a cell style depending on the cell content?

Question

There is an example of the dynamic cell styling for jXLS 1.x, but I can not find anything closer than this example for AreaListener.

I have a very basic template for the XLS generation, and the processing code as simple as

context.putVar("headers", columns);
context.putVar("data", cells);
context.getConfig().setCellStyleMap();
JxlsHelper.getInstance().processTemplate(is, result, context);

How can I add some listener that will allow me to modify the style for certain cells (like add the word-wrap for the text longer than N character, or change the background color if the value is of certain pattern)?

score 4 · Accepted Answer · answered Feb 18 '17 at 10:56

You can achieve it like this

In the main method:

        try(InputStream is = HighlightDemo.class.getResourceAsStream("highlight_template.xls")) {
        try (OutputStream os = new FileOutputStream("target/highlight_output.xls")) {
            PoiTransformer transformer = PoiTransformer.createTransformer(is, os);
            AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer, false);
            List<Area> xlsAreaList = areaBuilder.build();
            Area mainArea = xlsAreaList.get(0);
            Area loopArea = xlsAreaList.get(0).getCommandDataList().get(0).getCommand().getAreaList().get(0);
            loopArea.addAreaListener(new HighlightCellAreaListener(transformer));
            Context context = new Context();
            context.putVar("employees", employees);
            mainArea.applyAt(new CellRef("Result!A1"), context);
            mainArea.processFormulas();
            transformer.write();
        }
    }

The template used in this example is the same as in Object Collection Demo sample. The trickiest part is to find the area where do you want to apply the AreaListener. In this case I just traversed from the root area to the EachCommand area where I wish to highlight employees with payment over 2000.

The AreaListener implementation is similar to the one in AreaListener example

public class HighlightCellAreaListener implements AreaListener {
    private final CellRef paymentCell = new CellRef("Template!C4")
 ...
    public void afterTransformCell(CellRef srcCell, CellRef targetCell, Context context) {
    System.out.println("Source: " + srcCell.getCellName() + ", Target: " + targetCell.getCellName());
    if(paymentCell.equals(srcCell)){ // we are at employee payment cell
        Employee employee = (Employee) context.getVar("employee");
        if( employee.getPayment().doubleValue() > 2000 ){ // highlight payment when >= $2000
            logger.info("highlighting payment for employee " + employee.getName());
            highlightCell(targetCell);
            }
        }
    }
private void highlightCell(CellRef cellRef) {
    Workbook workbook = transformer.getWorkbook();
    Sheet sheet = workbook.getSheet(cellRef.getSheetName());
    Cell cell = sheet.getRow(cellRef.getRow()).getCell(cellRef.getCol());
    CellStyle cellStyle = cell.getCellStyle();
    CellStyle newCellStyle = workbook.createCellStyle();
    newCellStyle.setDataFormat( cellStyle.getDataFormat() );
    newCellStyle.setFont( workbook.getFontAt( cellStyle.getFontIndex() ));
    newCellStyle.setFillBackgroundColor( cellStyle.getFillBackgroundColor());
    newCellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
    newCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell.setCellStyle(newCellStyle);
}

jXLS 2.3 - add the listener to update a cell style depending on the cell content?

1 Answers1