easyExcel导出复杂表头，且自定义样式

江南_烟雨.

已于 2023-10-19 15:10:03 修改

阅读量1.2k

点赞数 3

分类专栏：笔记文章标签： windows java excel

于 2023-10-19 15:08:34 首次发布

本文链接：https://blog.csdn.net/a2120454642/article/details/133926643

版权

笔记专栏收录该内容

7 篇文章 0 订阅

订阅专栏

实现效果如下

在这里插入图片描述

具体代码：

动态表头数据

    /**
     * 导出房租物业表头
     *
     * @return
     */
 public List<List<String>> buildHead() {
        R<List<RentalProperty>> rentalPropertyR = smartPropertyClient.getRentalPropertyListByTenantId(AuthUtil.getTenantId());
        List<String> feeProjectNameList = new ArrayList<>();
        if (rentalPropertyR.isSuccess() && rentalPropertyR.getData().size() > 0) {
            for (RentalProperty rentalProperty : rentalPropertyR.getData()) {
                feeProjectNameList.add(rentalProperty.getName());
            }
        }
        List<List<String>> head = new ArrayList<>();
        String headFirst = "历史账单导入模版\n（统计起始时间：2018年5月 截止时间：填表月的上一个月）";
        String headSecond = "填表须知：\n" +
                "1.带*的为必填项，每个公司下的合同单独存一个文件\n" +
                "2.此数据为示例，缴费周期：按月\n" +
                "3.每个起始时间、终止时间为合同缴费周期的账单日期：如按季，则起始时间为2023/1/5 终止时间为历时月，以此类推。\n" +
                "4.支持批量导入，直接在表格中将所有合同的账单内容填写完成即可\n" +
                "5.请务必确认合同号及账单内容填写无误";
        head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*合同号", "*合同号"));
        head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "所属楼盘", "所属楼盘"));
        head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "房屋描述", "房屋描述"));
        head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*起始时间", "*起始时间"));
        head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*终止时间", "*终止时间"));
        feeProjectNameList.stream()
                .forEach(temp -> head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*应收项目", temp)));
        head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*实收项目", "转账实收（元）"));
        head.add(Lists.newArrayList(headFirst, headFirst, headSecond, "*未收租金（元）", "*未收租金（元）"));
        return head;
    }

下载方法

    /**
     * 导出execl
     *
     * @param response 输出流
     * @param fileName 文件名
     * @param headList 表头
     * @param data     数据
     * @throws IOException
     */
 public static void download2(HttpServletResponse response, String fileName, List<List<String>> headList,
                                 List data) throws IOException {
        String excelName = StringUtil.isNotBlank(fileName) ? fileName : "" + DateUtil.format(new java.util.Date(), "yyyy-MM");
        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        excelName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + excelName + ".xlsx");
        //主标题和副标题在excel中是0-4，一共五行
        List<Integer> columnIndexes = IntStream.range(0, 4).boxed().collect(Collectors.toList());

        //自定义标题和内容策略(具体定义在下文)
        CellStyleStrategy cellStyleStrategy = new CellStyleStrategy(columnIndexes, new WriteCellStyle(), new WriteCellStyle());
        EasyExcel.write(response.getOutputStream())
                .registerWriteHandler(getData2Style())
                .registerWriteHandler(cellStyleStrategy)
                .inMemory(true) // 富文本
                .registerWriteHandler(new WriteHandlerStrategy())
                .registerWriteHandler(new CellRowHeightStyleStrategy())
                .head(headList).excelType(ExcelTypeEnum.XLSX)
                .sheet("sheet1")
                .doWrite(data);
    }

    public static HorizontalCellStyleStrategy getData2Style() {
        // 创建一个样式，用于设置带有边框的内容单元格
        WriteCellStyle borderedCellStyle = new WriteCellStyle();
        borderedCellStyle.setBorderTop(BorderStyle.THIN);
        borderedCellStyle.setBorderBottom(BorderStyle.THIN);
        borderedCellStyle.setBorderLeft(BorderStyle.THIN);
        borderedCellStyle.setBorderRight(BorderStyle.THIN);
        borderedCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return new HorizontalCellStyleStrategy(borderedCellStyle, borderedCellStyle);
    }

最主要的方法

/**
 * Description： - 设置表头和填充内容的样式
 *
 * @author 林李
 * @date 2023/10/13 16:17
 */
public class CellStyleStrategy extends HorizontalCellStyleStrategy {

    private final WriteCellStyle headWriteCellStyle;
    private final WriteCellStyle contentWriteCellStyle;

    private final List<Integer> columnIndexes;

    public CellStyleStrategy(List<Integer> columnIndexes, WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
        this.columnIndexes = columnIndexes;
        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyle = contentWriteCellStyle;
    }

    @Override
    protected void setHeadCellStyle(CellWriteHandlerContext context) {
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);


        // 设置表头样式
        // 设置表头字体为粗体
        headWriteFont.setBold(false);
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        // 根据行索引为不同级别的表头应用不同样式
        if (context.getRowIndex() == 1 || (context.getRowIndex() >= 3 && context.getRowIndex() <= 4)) {
            // 一级、三级和四级表头样式（居中对齐）
            headWriteCellStyle.setWriteFont(headWriteFont);
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 居中对齐


        } else if (context.getRowIndex() == 2) {
            // 二级表头样式（居左对齐）
            headWriteCellStyle.setWriteFont(headWriteFont);
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT); // 居左对齐
        }

        if (stopProcessing(context)) {
            return;
        }

        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());
    }

设置*号为红色

/**
 * Description：
 *
 * @author 林李
 * @date 2023/10/16 16:14
 */
public class WriteHandlerStrategy implements CellWriteHandler {

    @Override
    public void beforeCellCreate(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
                                 final Row row, final Head head, final Integer columnIndex,
                                 final Integer relativeRowIndex, final Boolean isHead) {

    }

    @Override
    public void afterCellCreate(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
                                final Cell cell, final Head head, final Integer relativeRowIndex, final Boolean isHead) {

    }


    public void afterCellDispose(final WriteSheetHolder writeSheetHolder, final WriteTableHolder writeTableHolder,
                                 final List<WriteCellData<?>> list, final Cell cell, final Head head, final Integer integer,
                                 final Boolean aBoolean) {
        if (cell.getStringCellValue().contains("*")) {
            Sheet sheet = writeSheetHolder.getSheet();
            Workbook workbook = sheet.getWorkbook();
            // xlsx格式，如果是老版本格式的话就用 HSSFRichTextString
            XSSFRichTextString richString = new XSSFRichTextString(cell.getStringCellValue());
            Font font = workbook.createFont();
            font.setColor(Font.COLOR_RED);
            // 从哪到哪，你想设置成什么样的字体都行startIndex，endIndex
            richString.applyFont(0, 1, font);
            // 再设置回每个单元格里
            cell.setCellValue(richString);
        }

    }

}

设置行高

public class CellRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {

    }

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
        //设置主标题行高为17.7
        if (relativeRowIndex == 2) {
            //如果excel需要显示行高为15，那这里就要设置为15*20=300
            row.setHeight((short) (3200));
        }
    }
}