记录工作上easyExcel给表头加颜色的操作,低版本easyExcel(2.0.4)

easyExcel版本(2.0.4)

前提:不要问我为啥不升级版本,升级有对应功能注解,由于之前的代码都是用这个版本,如果改版本号可能会造成之前的功能异常,而项目时间紧急不好去动!!!

1. 自定义颜色注解

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelCellFront {

    /**
     * 字体 颜色(默认红色)
     */
    short FrontColor() default 10;

    /**
     * 字体 大小
     */
    short FrontSize() default 14;

}

2.编写对应操作器

/**
 * 自定义单元格字体策略
 */
public class CustomFrontStrategy implements RowWriteHandler {

    /**
     * DTO数据类型
     */
    private Class<?> elementType;
 
    public CustomFrontStrategy(Class<?> elementType) {
        this.elementType = elementType;
    }

    /**
     * 设置字体
     * @param row
     */
    private void setFront(Row row){
        // 获取DTO的类型
        Class<?> eleType = this.elementType;

        // 获取DTO所有的属性
        Field[] fields = eleType.getDeclaredFields();

        for (Field field : fields) {
            // 或取字体颜色的注解
            ExcelCellFront annotation = field.getAnnotation(ExcelCellFront.class);
            if(ObjectUtils.isEmpty(annotation)){
                continue;
            }
            // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
            ExcelProperty easyExcelAnnotation = field.getAnnotation(ExcelProperty.class);
            // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
            if (null == easyExcelAnnotation) {
                continue;
            }
            for (int i = 0; i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if(cell.getStringCellValue().equals(easyExcelAnnotation.value()[1])){
                    Workbook workbook = cell.getSheet().getWorkbook();
                    // 单元格策略
                    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                    // 创建字体实例
                    WriteFont cellWriteFont = new WriteFont();
                    //设置字体颜色
                    cellWriteFont.setColor(annotation.FrontColor());
                    cellWriteFont.setFontHeightInPoints(annotation.FrontSize());
                    contentWriteCellStyle.setWriteFont(cellWriteFont);
                    CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }

    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, int i, int i1, boolean b) {

    }

    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, int i, boolean isHead) {
        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();
        // 如果是标题,则直接返回
        if (isHead) {
            // 获取标题行
            Row titleRow = sheet.getRow(0);
            //设置第一行高度为1800
            titleRow.setHeight((short) 1800);

        }
        //对固定位置的行做字体处理
        if(sheet.getLastRowNum() -1 == 1){
            Row sheetRow = sheet.getRow(1);
            setFront(sheetRow);
        }
    }
}

3.在对应字段头上添加注解

public class GradeStep1TemplateModel  extends BaseRowModel {

    @ExcelProperty(value = {"说明：测试1,3必填", "测试1"}, index = 0)
    @ColumnWidth(30)
    @ExcelCellFront(FrontColor = 10,FrontSize = 14)
    @CustomMerge(needMerge = true, isPk = true)
    private String test1;

    @ExcelProperty(value = {"说明：测试1,3必填", "测试2"}, index = 1)
    @ColumnWidth(40)
    @CustomMerge(needMerge = true)
    private String test2;

    @ColumnWidth(40)
    @CustomMerge(needMerge = true)
    @ExcelCellFront(FrontColor = 10,FrontSize = 14)
    @ExcelProperty(value = {"说明：测试1,3必填", "测试3"}, index = 2)
    private String test3;
}

4.写入对应文件进行操作器注册

    @Test
    public void testExcel() {
        //sheet名
        String sheetName = "测试文件";
        GradeStep1TemplateModel model = new GradeStep1TemplateModel();
        model.setCpCode("111");
        model.setCpName("111");
        List<GradeStep1TemplateModel> modelList = Lists.newArrayList(model);
        try (final ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
            EasyExcel.write(bos, modelList.get(0).getClass())
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(new CustomMergeStrategy(GradeStep1TemplateModel.class))
                    .sheet(sheetName).doWrite(modelList);
            FileOutputStream fileOutputStream = new FileOutputStream("/Users/hanli/vipsrv-logs/demo/test"+System.currentTimeMillis()+".xlsx");
            fileOutputStream.write(bos.toByteArray());
        } catch (Exception e) {
            throw new CustomException(PerformanceResponseCode.EXPORT_EXCEL_WRONG_DATA);
        }
    }
}

5.最后效果