Java基于EasyExcel实现动态表头Excel导出工具类的完整示例代码

public class dynamicheadcolumn {

    /** 表头显示名称，例如“用户姓名” */
    private string headname;

    /** 字段名，对应数据行里的key，例如 username */
    private string fieldname;

    /** 列宽，单位是excel的字符宽度 */
    private integer width;

    /** 排序权重，数值小的排前面 */
    private integer order;

    /** 多级表头的父级名称，例如“基本信息” */
    private string groupname;

    /** 水平对齐方式：left、center、right */
    private excelcolumnalign align;

    /** 表头字体颜色，默认黑色 */
    private color headfontcolor;

    /** 表头背景色，默认浅灰 */
    private color headbackgroundcolor;
}

public interface dynamicexcelexporter {

    /**
     * 导出，数据行按表头列顺序传入
     */
    void export(outputstream outputstream,
                string sheetname,
                list<dynamicheadcolumn> columns,
                list<list<object>> datarows) throws ioexception;

    /**
     * 导出，数据行按map传入，内部按fieldname取值转换
     */
    void export(outputstream outputstream,
                string sheetname,
                list<dynamicheadcolumn> columns,
                list<map<string, object>> datamaps) throws ioexception;
}

<dependency>
    <groupid>com.alibaba</groupid>
    <artifactid>easyexcel</artifactid>
    <version>3.3.2</version>
</dependency>

private list<list<string>> buildhead(list<dynamicheadcolumn> columns) {
    // 先按order排序，保证显示顺序正确
    list<dynamicheadcolumn> sortedcolumns = new arraylist<>(columns);
    sortedcolumns.sort(comparator.comparingint(c -> c.getorder() == null ? 0 : c.getorder()));

    list<list<string>> head = new arraylist<>();
    for (dynamicheadcolumn column : sortedcolumns) {
        if (stringutils.isnotblank(column.getgroupname())) {
            // 多级表头：父级名称 + 子级名称
            head.add(arrays.aslist(column.getgroupname(), column.getheadname()));
        } else {
            // 单层表头
            head.add(collections.singletonlist(column.getheadname()));
        }
    }
    return head;
}

private void writerows(excelwriter writer, writesheet sheet, list<list<object>> datarows) {
    writer.write(datarows, sheet);
}

private list<list<object>> convertmapstorows(list<map<string, object>> datamaps,
                                              list<dynamicheadcolumn> columns) {
    list<list<object>> rows = new arraylist<>();
    if (collectionutils.isempty(datamaps)) {
        return rows;
    }

    // 这里columns已经是排序后的列表，由buildhead的调用方保证
    for (map<string, object> datamap : datamaps) {
        list<object> row = new arraylist<>(columns.size());
        for (dynamicheadcolumn column : columns) {
            row.add(datamap.get(column.getfieldname()));
        }
        rows.add(row);
    }
    return rows;
}

private writehandler buildcellstylestrategy(dynamicexportoptions options) {
    writecellstyle headcellstyle = new writecellstyle();
    headcellstyle.setfillforegroundcolor(indexedcolors.grey_25_percent.getindex());
    headcellstyle.setfillpatterntype(fillpatterntype.solid_foreground);
    headcellstyle.sethorizontalalignment(horizontalalignment.center);
    headcellstyle.setverticalalignment(verticalalignment.center);
    font headfont = new font();
    headfont.setbold(true);
    headcellstyle.setwrapped(true);
    headcellstyle.setfont(headfont);

    writecellstyle contentcellstyle = new writecellstyle();
    contentcellstyle.setwrapped(true);
    contentcellstyle.setverticalalignment(verticalalignment.center);

    return new horizontalcellstylestrategy(headcellstyle, contentcellstyle);
}

public class dynamiccolumnwidthstylehandler implements writehandler {

    private final map<integer, integer> columnwidthmap;

    public dynamiccolumnwidthstylehandler(list<dynamicheadcolumn> columns) {
        this.columnwidthmap = new hashmap<>();
        int index = 0;
        for (dynamicheadcolumn column : columns) {
            if (column.getwidth() != null) {
                columnwidthmap.put(index, column.getwidth());
            }
            index++;
        }
    }

    @override
    public void aftercelldispose(writesheetholder writesheetholder,
                                 writetableholder writetableholder,
                                 list<writecelldata<?>> celldatalist,
                                 cell cell,
                                 head head,
                                 integer relativerowindex,
                                 boolean ishead) {
        integer width = columnwidthmap.get(cell.getcolumnindex());
        if (width != null) {
            writesheetholder.getsheet().setcolumnwidth(cell.getcolumnindex(), width);
        }
    }
}

public void export(outputstream outputstream,
                   string sheetname,
                   list<dynamicheadcolumn> columns,
                   list<list<object>> datarows,
                   dynamicexportoptions options) throws ioexception {
    list<dynamicheadcolumn> sortedcolumns = sortcolumns(columns);
    list<list<string>> head = buildhead(sortedcolumns);
    writehandler stylestrategy = buildcellstylestrategy(options);

    try (excelwriter writer = easyexcel.write(outputstream).build()) {
        writesheet writesheet = easyexcel.writersheet(sheetname)
                .head(head)
                .registerwritehandler(stylestrategy)
                .registerwritehandler(new dynamiccolumnwidthstylehandler(sortedcolumns))
                .build();
        writer.write(datarows == null ? collections.emptylist() : datarows, writesheet);
    }
}

@getmapping("/export")
public void export(httpservletresponse response,
                   @requestparam long userid) throws ioexception {
    // 1. 从配置中心查当前用户定义要导出的列
    list<usercolumnconfig> configs = usercolumnconfigservice.listbyuserid(userid);

    // 2. 转成dynamicheadcolumn列表
    list<dynamicheadcolumn> columns = new arraylist<>();
    for (usercolumnconfig config : configs) {
        dynamicheadcolumn column = new dynamicheadcolumn();
        column.setfieldname(config.getfieldname());
        column.setheadname(config.getheadname());
        column.setwidth(config.getwidth());
        column.setorder(config.getsort());
        if (stringutils.isnotblank(config.getgroupname())) {
            column.setgroupname(config.getgroupname());
        }
        columns.add(column);
    }

    // 3. 查询数据
    list<map<string, object>> datamaps = userservice.queryexportdatabyuserid(userid);

    // 4. 调用工具类
    response.setcontenttype("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setcharacterencoding("utf-8");
    string filename = urlencoder.encode("用户列表.xlsx", "utf-8").replace("+", "%20");
    response.setheader("content-disposition", "attachment;filename*=utf-8''" + filename);
    dynamicexcelexporter.export(response.getoutputstream(), "用户列表", columns, datamaps);
}

public list<dynamicheadcolumn> translatecolumns(list<dynamicheadcolumn> columns,
                                                 locale locale) {
    for (dynamicheadcolumn column : columns) {
        string i18nkey = column.getheadname();
        column.setheadname(messagesource.getmessage(i18nkey, null, i18nkey, locale));
    }
    return columns;
}

list<map<string, object>> orders = orderstatisticsservice.querybydaterange(startdate, enddate);
for (map<string, object> order : orders) {
    bigdecimal todayamount = (bigdecimal) order.get("amount");
    order.put("amountdayonday", calcdayonday(order, "amount"));
}

list<dynamicheadcolumn> columns = defaultcolumns();
if (enablecompare) {
    columns.add(new dynamicheadcolumn("订单数环比", "ordercountdayonday", 16, 10));
    columns.add(new dynamicheadcolumn("销售额环比", "amountdayonday", 16, 11));
}

list<list<object>> datarows = querydata();
if (collectionutils.isempty(datarows)) {
    return; // 这里直接return，导出就没有表头了
}
export(outputstream, columns, datarows);

list<list<object>> saferows = datarows == null ? collections.emptylist() : datarows;
writer.write(saferows, writesheet);

// 每查5000条就flush一次，不能全部堆积在list里
list<list<object>> batch = fetchbatch();
while (!batch.isempty()) {
    writer.write(batch, writesheet);
    batch.clear();
    batch = fetchbatch();
}

bufferedoutputstream bufferedout = new bufferedoutputstream(outputstream, 8192);