JXLS生成excel并自定义单元格样式

　　　本人最近需要每天统计数据表，并每周一发送统计结果的邮件，所以写了个springboot老自动完成工作。项目地址为：https://github.com/707293891/springboot

　　　其中某些统计数据需要特殊标记：红色显示。

　　　如图：模版如下

　　　但是需要在某一处特殊显示为红色：如图

　　   现在写出实现过程：

　　　利用jxls的区域监听器实现.

1Transformer transformer = TransformerFactory.createTransformer(getFileInputStream(),
2                new FileOutputStream(
3                        new File(
4                                Thread.currentThread().getContextClassLoader().
5                                        getResource("excelTemplates/result").getFile()+"/result.xls")));
6        XlsArea xlsArea=new XlsArea("Sheet1!A1:F3",transformer);
7        XlsArea employeeArea = new XlsArea("Sheet1!A3:F3", transformer);
8        employeeArea.addAreaListener(new SimpleAreaListener(employeeArea));
9        EachCommand eachCommand=new EachCommand("item","items",employeeArea);
10        xlsArea.addCommand("A3:F3", eachCommand);
11        Context context = new Context();
12        Map map=new HashMap();
13        // map.put("list",getData());
14        map.put("week",CalendarUtil.getWeekNum());
15        map.put("month",CalendarUtil.getMonth());
16        context.putVar("print", map);
17        context.putVar("items",list);
18        xlsArea.applyAt(new CellRef("Sheet1!A1"), context);
19        transformer.write();
20
21　　SimpleAreaListener实现如下：　　
22
23package com.yinhai.yunwei.excel;
24
25import com.yinhai.yunwei.yunwei.mapper.YunweiInfo;
26import org.apache.poi.ss.usermodel.Cell;
27import org.apache.poi.ss.usermodel.CellStyle;
28import org.apache.poi.ss.usermodel.Font;
29import org.apache.poi.ss.usermodel.Workbook;
30import org.apache.poi.xssf.usermodel.XSSFFont;
31import org.jxls.area.Area;
32import org.jxls.area.XlsArea;
33import org.jxls.common.AreaListener;
34import org.jxls.common.CellRef;
35import org.jxls.common.Context;
36import org.jxls.transform.poi.PoiTransformer;
37
38import java.util.Map;
39
40/**
41 * @author 范超
42 * @version V1.0
43 * @Title SimpleAreaListener
44 * @Package jxls
45 * @Descript :TODO()
46 * @date : 2018/6/25  上午10:22
47 */
48public class SimpleAreaListener implements AreaListener {
49    private Area area;
50    PoiTransformer transformer;
51    public SimpleAreaListener(XlsArea xlsArea) {
52        this.area=xlsArea;
53        transformer= (PoiTransformer) xlsArea.getTransformer();
54    }
55
56    @Override
57    public void beforeApplyAtCell(CellRef cellRef, Context context) {
58
59    }
60
61    @Override
62    public void afterApplyAtCell(CellRef cellRef, Context context) {
63    }
64
65    @Override
66    public void beforeTransformCell(CellRef cellRef, CellRef cellRef1, Context context) {
67
68    }
69
70    @Override
71    public void afterTransformCell(CellRef cellRef, CellRef cellRef1, Context context) {
72        if (cellRef1.getCol()!=3&&cellRef1.getCol()!=5){
73            return;
74        }
75        Workbook workbook=transformer.getWorkbook();
76        Cell cell=workbook.getSheet(cellRef1.getSheetName()).getRow(cellRef1.getRow()).getCell(cellRef1.getCol());
77        CellStyle cellStyle=cell.getCellStyle();
78        Font font=workbook.createFont();
79        CellStyle resultCell=workbook.createCellStyle();
80        Object item=context.getVar("item");
81//需要显示红色的条件
82        if(item!=null&&item instanceof YunweiInfo &&"****".equals(((YunweiInfo) item).getName())){
83            font.setColor(XSSFFont.COLOR_RED);
84            resultCell.setFont(font);
85            cell.setCellStyle(resultCell);
86        }
87    }
88
89}

　　　　这样当满足条件时就会实现特殊的显示格式了。

　　　　其中jxls版本为如下

1<dependency>
2            <groupId>org.jxls</groupId>
3            <artifactId>jxls</artifactId>
4            <version>[2.4.3,)</version>
5        </dependency>
6        <dependency>
7            <groupId>org.jxls</groupId>
8            <artifactId>jxls-poi</artifactId>
9            <version>[1.0.14,)</version>
10        </dependency>
11        <dependency>
12            <groupId>org.jxls</groupId>
13            <artifactId>jxls-jexcel</artifactId>
14            <version>[1.0.6,)</version>
15        </dependency>
16        <dependency>
17            <groupId>org.jxls</groupId>
18            <artifactId>jxls-reader</artifactId>
19            <version>[2.0.3,)</version>
20        </dependency>