Spring Boot 使用FastExcel实现多级表头动态数据填充导出_Java

1：依赖（maven）

<!-- fastexcel 官网最新版 -->
<dependency>
    <groupid>cn.idev.excel</groupid>
    <artifactid>fastexcel</artifactid>
    <version>1.0.0</version>
</dependency>

2：动态表头实体类

package com.fantaibao.module.vo.target;
import cn.idev.excel.annotation.excelproperty;
import cn.idev.excel.annotation.write.style.columnwidth;
import cn.idev.excel.annotation.write.style.contentstyle;
import cn.idev.excel.annotation.write.style.headfontstyle;
import cn.idev.excel.annotation.write.style.headstyle;
import cn.idev.excel.enums.poi.fillpatterntypeenum;
import cn.idev.excel.enums.poi.horizontalalignmentenum;
import cn.idev.excel.enums.poi.verticalalignmentenum;
import lombok.allargsconstructor;
import lombok.builder;
import lombok.data;
import lombok.noargsconstructor;
@data
@builder
@noargsconstructor
@allargsconstructor
@headfontstyle(fontname = "黑体", fontheightinpoints = 12)
@headstyle(horizontalalignment = horizontalalignmentenum.center, verticalalignment = verticalalignmentenum.center,
        fillpatterntype = fillpatterntypeenum.solid_foreground, fillforegroundcolor = 44)
@contentstyle(horizontalalignment = horizontalalignmentenum.center, verticalalignment = verticalalignmentenum.center)
public class targetindexexportstorestavo {
    /**
     * 年份(2025)
     */
    @columnwidth(15)
    @excelproperty({"${indexname}", "年度"})
    private integer year;
    /**
     * 门店名称
     */
    @columnwidth(20)
    @excelproperty({"${indexname}", "门店名称"})
    private string storename;
    /**
     * 年目标
     */
    @columnwidth(15)
    @excelproperty({"${indexname}", "年目标"})
    private string totaltarget;
    /**
     * 当前进度
     */
    @columnwidth(20)
    @excelproperty({"${indexname}", "当前进度"})
    private string progress;
    /**
     * 目标完成率
     */
    @columnwidth(20)
    @excelproperty({"${indexname}", "目标完成率"})
    private string finishrate;
}

package com.fantaibao.module.vo.target;
import cn.idev.excel.annotation.excelproperty;
import cn.idev.excel.annotation.write.style.*;
import cn.idev.excel.enums.poi.fillpatterntypeenum;
import cn.idev.excel.enums.poi.horizontalalignmentenum;
import cn.idev.excel.enums.poi.verticalalignmentenum;
import lombok.allargsconstructor;
import lombok.builder;
import lombok.data;
import lombok.noargsconstructor;
@data
@builder
@noargsconstructor
@allargsconstructor
@headfontstyle(fontname = "黑体", fontheightinpoints = 12)
@headstyle(horizontalalignment = horizontalalignmentenum.center, verticalalignment = verticalalignmentenum.center,
        fillpatterntype = fillpatterntypeenum.solid_foreground, fillforegroundcolor = 44)
@contentstyle(horizontalalignment = horizontalalignmentenum.center, verticalalignment = verticalalignmentenum.center)
@contentfontstyle(fontname = "黑体", fontheightinpoints = 10)
public class targetindexexportstoremonthstavo {
    /**
     * 年份(2025)
     */
    @columnwidth(15)
    @excelproperty({"${indexname}", "年度"})
    private integer year;
    /**
     * 门店名称
     */
    @columnwidth(25)
    @excelproperty({"${indexname}", "门店名称"})
    private string storename;
    /**
     * 月目标
     */
    @columnwidth(15)
    @excelproperty({"${indexname}", "${month}月目标"})
    private string totaltarget;
    /**
     * 当前进度
     */
    @columnwidth(20)
    @excelproperty({"${indexname}", "当前进度"})
    private string progress;
    /**
     * 目标完成率
     */
    @columnwidth(20)
    @excelproperty({"${indexname}", "目标完成率"})
    private string finishrate;
}

3：动态表头数据填充策略

package com.fantaibao.handler;
import cn.hutool.core.collection.collutil;
import cn.idev.excel.metadata.head;
import cn.idev.excel.write.handler.cellwritehandler;
import cn.idev.excel.write.metadata.holder.writesheetholder;
import cn.idev.excel.write.metadata.holder.writetableholder;
import org.apache.poi.ss.usermodel.cell;
public class customheaderwritehandler implements cellwritehandler {
    private final string indexname;
    public customheaderwritehandler(string indexname) {
        this.indexname = indexname;
    }
    @override
    public void aftercellcreate(writesheetholder writesheetholder, writetableholder writetableholder,
                                cell cell, head head, integer relativerowindex, boolean ishead) {
        // 处理表头
        if (collutil.isnotempty(head.getheadnamelist())) {
            // 处理一级表头
            if (collutil.isnotempty(head.getheadnamelist())) {
                string replace = head.getheadnamelist().get(0).replace("${indexname}", indexname);
                head.getheadnamelist().set(0, replace);
            }
        }
    }
}
public class custommonthheaderwritehandler implements cellwritehandler {
    private final string indexname;
    private final int month;
    public custommonthheaderwritehandler(string indexname, int month) {
        this.indexname = indexname;
        this.month = month;
    }
    @override
    public void aftercellcreate(writesheetholder writesheetholder, writetableholder writetableholder,
                                cell cell, head head, integer relativerowindex, boolean ishead) {
        if (ishead && head != null) {
            // 处理表头
            if (collutil.isnotempty(head.getheadnamelist())) {
                // 处理一级表头
                string indexname = head.getheadnamelist().get(0).replace("${indexname}", this.indexname);
                head.getheadnamelist().set(0, indexname);
                // 处理二级表头
                string monthstr = head.getheadnamelist().get(1);
                if (!monthstr.contains("${month}")) {
                    return;
                }
                string month = monthstr.replace("${month}", string.valueof(this.month));
                head.getheadnamelist().set(1, month);
            }
        }
    }
}

4：导出工具类

/**
     * 按单个指标生成年度-月度汇总数据
     *
     * @param zipout               zip输出流
     * @param filenameprefix        文件名前缀
     * @param storetargetyearlist  门店年目标集合
     * @param storemonthtargetlist 门店月目标集合
     * @param enums                指标
     * @param storemap             门店集合
     * @throws ioexception 抛出的异常
     */
    public static void generatesingleindexstaexcelfile(zipoutputstream zipout, string filenameprefix, list<storeindexyearstavo> storetargetyearlist,
                                                       list<storeindexmonthstavo> storemonthtargetlist, indicatorstypeenums enums,
                                                       map<string, storeinfolistvo> storemap) throws ioexception {
        zipentry zipentry = new zipentry(enums.getname() + "门店" + filenameprefix + "目标汇总.xlsx");
        zipout.putnextentry(zipentry);
        try (excelwriter excelwriter = easyexcel.write(zipout).autoclosestream(false).build()) {
            // sheet1: 门店指标年目标汇总
            writesheet yearsheet = easyexcel.writersheet(0, enums.getname() + "门店年目标")
                    .head(targetindexexportstorestavo.class)
                    .registerwritehandler(new customheaderwritehandler(strutil.isnotblank(enums.getunit()) ? enums.getname() + "(" + enums.getunit() + ")" : enums.getname()))
                    .build();
            excelwriter.write(getyearsheetdata(enums, storetargetyearlist, storemap), yearsheet);
            // sheet2: 门店指标月目标汇总
            for (int month = 1; month <= 12; month++) {
                writesheet monthsheet = easyexcel.writersheet(month, "门店" + month + "月目标")
                        .head(targetindexexportstoremonthstavo.class)
                        .registerwritehandler(new custommonthheaderwritehandler(strutil.isnotblank(enums.getunit()) ? enums.getname() + "(" + enums.getunit() + ")" : enums.getname(), month))
                        .build();
                excelwriter.write(getmonthsheetdatabymonth(enums, storemonthtargetlist, month, storemap), monthsheet);
            }
            excelwriter.finish();
        } catch (exception e) {
            try {
                zipout.closeentry();
            } catch (ioexception ignored) {
            }
            throw e;
        }
        zipout.closeentry();
    }