EasyExcel：自定义拦截器，对特定单元格设定格式_invoke_徐同学的博客-CSDN博客_easyexcel 拦截器

安宇雨 - 随手采集
2021-08-24 12:16:53
随手采集
0000-未整理-等待研究

要求导出的excel按照百分比区间分配不同的颜色，上网找了很多都是对全局进行格式改变，那么如何对特定的某个单元格施加样式呢，参考easyExcel官方文档，这里使用自定义拦截器（文档原地址：https://alibaba-easyexcel.github.io/quickstart/write.html）

直接上例子：
1.首先设置一下通用样式

public class CellStyleUtil {
    /**
     *  通用样式
     * @param workbook
     * @return
     */
    public static CellStyle cellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        //居中
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        return cellStyle;
    }
}

2.自定义拦截器，实现CellWriteHandler 接口，并重写afterCellDispose() 方法

public class SignRecordExcelHandler implements CellWriteHandler    {
    
    private static NumberFormat nf = NumberFormat.getPercentInstance();

    @Override
    public void afterCellCreate(WriteSheetHolder arg0, WriteTableHolder arg1, Cell arg2, Head arg3, Integer arg4,
            Boolean arg5) {
        // TODO Auto-generated method stub
        
    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder arg0, WriteTableHolder arg1, CellData arg2, Cell arg3,
            Head arg4, Integer arg5, Boolean arg6) {
        // TODO Auto-generated method stub
        
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, 
            Cell cell,Head head, Integer relativeRowIndex, Boolean isHead) {
//        这里你可以设置其他的任意格式，我这边只是处理背景颜色
        Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
        CellStyle cellStyle = CellStyleUtil.cellStyle(workbook);
        try {
            //行数大于0，列数为6
            if(cell.getRowIndex()>0 && cell.getColumnIndex()==6) {
            //如果我第*行第6列的百分比小于50%,那么施加40度灰的颜色，下同
                if(nf.parse(cell.getStringCellValue()).doubleValue() <= 0.5) {
                    cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
                }else if(nf.parse(cell.getStringCellValue()).doubleValue() <= 1 && nf.parse(cell.getStringCellValue()).doubleValue() > 0.5) {
                    cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
                }else if(nf.parse(cell.getStringCellValue()).doubleValue() <=2 && nf.parse(cell.getStringCellValue()).doubleValue() > 1) {
                    cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
                }else if(nf.parse(cell.getStringCellValue()).doubleValue() <=6 && nf.parse(cell.getStringCellValue()).doubleValue() > 2) {
                    cellStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());
                }else if(nf.parse(cell.getStringCellValue()).doubleValue() > 6) {
                    cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                }
            }
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        cell.setCellStyle(cellStyle);
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder arg0, WriteTableHolder arg1, Row arg2, Head arg3, Integer arg4,
            Integer arg5, Boolean arg6) {
        // TODO Auto-generated method stub
        
    }
}

3.导出为excel

//写入的数据dataList(随便加几条数据）
//ExportExceedPower中定义了表头的内容，这里就不展示了
List<ExportExceedPower> dataList = new ArrayList<>();
dataList.add(new ExportExceedPower("aaa","aa","aa","a",20,20,"600%"));
dataList.add(new ExportExceedPower("aaa","aa","aa","a",20,20,"500%"));
dataList.add(new ExportExceedPower("aaa","aa","aa","a",20,20,"400%"));
dataList.add(new ExportExceedPower("aaa","aa","aa","a",20,20,"100%"));
dataList.add(new ExportExceedPower("aaa","aa","aa","a",20,20,"50%"));
dataList.add(new ExportExceedPower("aaa","aa","aa","a",20,20,"20%"));
//按照百分比对list进行排序
Collections.sort(dataList);
String fileName = "D:\\test.xlsx";
//这里需要指定写用哪个class去写，然后写到第一个sheet，名字为test 然后文件流会自动关闭
 EasyExcel.write(fileName, ExportExceedPower.class)
        .registerWriteHandler(new SignRecordExcelHandler())
        .sheet("test")
        .doWrite(dataList);

4.结果展示，对除去头部每行第六列的内容加上了背景颜色
在这里插入图片描述