JXLS 2.3-添加侦听器以根据单元格内容更新单元格样式

Question

有一个jxls 1.x的动态单元格式的示例，但是我找不到比这个示例的示例。

我有一个非常基本的模板，用于XLS生成，处理代码与

一样简单

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

我如何添加一些侦听器，以使我可以修改某些单元格的样式（例如，添加文本的单词包装比n个字符更长，或者如果值为某些模式，则更改背景颜色）？<<<<

Answer 1

您可以像这样实现

在主要方法中：

        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();
        }
    }

此示例中使用的模板与对象收集演示样本中相同。最棘手的部分是找到您要在何处使用Arealistener的区域。在这种情况下

AREALISTENER实现与Arealistener示例中的实现相似

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);
}