EasyExcel——自定义单元格样式

写 bug 的大耳朵图图
·

之前在开发系统的导出功能时需要对单元格增加不同的样式,过程有点曲折,记录一下以备后续用到

  • 创建java项目,引入以下依赖
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.2.1</version>
</dependency>
  • excel 导出代码
try (ExcelWriter excelWriter = EasyExcel.write(filePath).build()) {
        WriteSheet sheet = EasyExcel.writerSheet("自定义样式")
                // 设置表头
                .head(ExportHeaderDTO.class)
                // 不使用默认样式
                .useDefaultStyle(Boolean.FALSE)
                // 添加自定义单元格样式
                .registerWriteHandler(new CustomCellWriteHandler())
                // 添加单元格边框样式
.registerWriteHandler(CustomHorizontalCellStyleStrategy.cellBorder())
                .build();
        excelWriter.write(collect, sheet);
    } catch (Exception e) {
        e.printStackTrace();
    }
  • 自定义样式 CustomCellWriteHandler.java
public class CustomCellWriteHandler implements CellWriteHandler {
    /**
     * 设置拦截器顺序,需要 > 50000
     *
     * @return 拦截器顺序
     */
    @Override
    public int order() {
        return 60000;
    }

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        Cell cell = context.getCell();
        if (BooleanUtils.isNotTrue(context.getHead())) {
            Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
            XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 设置边框粗细
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            // 设置边框颜色
            cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
            cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
            cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
            cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Font font = workbook.createFont();
            if (cell.getColumnIndex() == 1) {
                // 设置字体颜色
                font.setColor(IndexedColors.DARK_TEAL.index);
                cellStyle.setFont(font);
                // 设置单元格颜色
                cellStyle.setFillForegroundColor(new XSSFColor(ColorConstant.CustomColor.PINK, CustomIndexedColorMap.fromColors(CTColors.Factory.newInstance())));
                cell.setCellStyle(cellStyle);
                // 这里要把 WriteCellData的样式清空, 不然后面还有一个拦截器 FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到cell里面去 会导致自己设置的不一样(很关键)
                context.getFirstCellData().setWriteCellStyle(null);
            }
        }
    }
}
  • 边框样式 CustomHorizontalCellStyleStrategy.java
public class CustomHorizontalCellStyleStrategy extends HorizontalCellStyleStrategy {
    @Override
    public int order() {
        return 6500;
    }

    /**
     * 设置单元格边框
     * @return 样式策略
     */
    public static HorizontalCellStyleStrategy cellBorder() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.index);
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
}
社区准则 博客 联系 社区 状态
主题