EasyExcel导出数据,自定义单元格样式(DEMO)

EasyExcel导出数据,自定义单元格样式(DEMO)

需求

对比AC, BD字段, 将不相同的的单元格标黄

实体类

@Data
public class EasyExcelTestEntity {
    
    /**
     * 字段a
     */
    private String a;
    
    /**
     * 字段b
     */
    private String b;
    
    /**
     * 字段c
     */
    private String c;
    
     /**
     * 字段d
     */
    private String d;
}

Excel.xlsx

业务流程

pulic void export(HttpServletResponse response) {
    String fileName = [文件名] + ".xlsx";
    
    try {
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setContentType("application/vnd.ms-excel");

        ExcelWriter build = EasyExcel.write(response.getOutputStream())
            .withTemplate(new ClassPathResource([Excel.xlsx路径]).getInputStream()).build();
        // 查库, 获取导出数据
        List<EasyExcelTestEntity> list = xxxService.selectXXX;
        WriteSheet sheet = EasyExcel.writerSheet(0).registerWriteHandler(new ExcelFillCellStyleStrategy()).build();
        build.write(list, sheet);
        build.finish();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

自定策略

public class ExcelFillCellStyleStrategy implements CellWriteHandler {

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                 Head head, Integer integer, Integer integer1, Boolean aBoolean) {
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                Head head, Integer integer, Boolean aBoolean) {
    }


    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                       CellData cellData, Cell cell, Head head, Integer integer, 
                                       Boolean aBoolean) {
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, 
                                 Boolean aBoolean) {
        // 当前行下标
        int curRowIndex = cell.getRowIndex();
        // 当前列下标
        int curColIndex = cell.getColumnIndex();
        // 下标大于1时,当前单元格和前面对应的单元格进行对比
        if (curRowIndex > 0 && curColIndex > 1) {
            addCellStyle(writeSheetHolder, cell, curColIndex);
        }
    }

    private void addCellStyle(WriteSheetHolder writeSheetHolder, Cell cell, int curColIndex) {
        // 当前单元格内容
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :
                cell.getNumericCellValue();
        
        // 对应单元格对象
        Cell preCell = cell.getRow().getCell(curColIndex - 2);

        // 对应单元格内容
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
                preCell.getNumericCellValue();

        // 当前单元格内容与对应单元格内容不相同
        if (!curData.equals(preData)) {
            // 新建单元格样式对象
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            // 将原始单元格样式克隆至新建的空的单元格样式对象
            cellStyle.cloneStyleFrom(cell.getCellStyle());
            // 设置背景色, 也可以设置其他样式
            cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 样式设置到当前单元格
            cell.setCellStyle(cellStyle);
            // 样式设置到对应单元格
            preCell.setCellStyle(cellStyle);
        }
    }
}

效果

yoojyn - 博客园