SpringBoot+EasyExcel实现自定义复杂样式导入导出

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

package com.example.springbootexcel.excel.base.model;

import com.alibaba.excel.annotation.excelproperty;
import lombok.data;

import java.util.list;
import java.util.map;

@data
public class baseexcel {

    @excelproperty( value = "序号")
    private string num;

    /**
     * 动态字段处理
     */
    private list<map<string, object>> dynamiclist;

}

package com.example.springbootexcel.excel.base.style;

import com.alibaba.excel.write.handler.sheetwritehandler;
import com.alibaba.excel.write.metadata.holder.writesheetholder;
import com.alibaba.excel.write.metadata.holder.writeworkbookholder;
import org.apache.poi.ss.usermodel.datavalidation;
import org.apache.poi.ss.usermodel.datavalidationconstraint;
import org.apache.poi.ss.usermodel.datavalidationhelper;
import org.apache.poi.ss.usermodel.sheet;
import org.apache.poi.ss.util.cellrangeaddresslist;

import java.util.map;

/**
 * 添加下拉选单
 *
 * @author jason
 */
public class dropdownhandler implements sheetwritehandler {
    private final map<integer, string[]> dropdownmap;  // key:列号(从0开始), value:下拉数据

    public dropdownhandler(map<integer, string[]> dropdownmap) {
        this.dropdownmap = dropdownmap;
    }

    @override
    public void beforesheetcreate(writeworkbookholder writeworkbookholder, writesheetholder writesheetholder) {
        // 不需要实现
    }

    @override
    public void aftersheetcreate(writeworkbookholder writeworkbookholder, writesheetholder writesheetholder) {
        if (dropdownmap == null || dropdownmap.isempty()) {
            return;
        }

        sheet sheet = writesheetholder.getsheet();
        datavalidationhelper helper = sheet.getdatavalidationhelper();

        dropdownmap.foreach((columnindex, dropdowndata) -> {
            // 设置下拉框数据范围 (这里设置从第2行到第10000行)
            cellrangeaddresslist addresslist = new cellrangeaddresslist(1, 9999, columnindex, columnindex);

            // 创建数据验证约束
            datavalidationconstraint constraint = helper.createexplicitlistconstraint(dropdowndata);

            // 创建数据验证
            datavalidation validation = helper.createvalidation(constraint, addresslist);

            // 阻止输入非下拉选项的值
            validation.seterrorstyle(datavalidation.errorstyle.stop);
            validation.setshowerrorbox(true);
            validation.setsuppressdropdownarrow(true);
            validation.createerrorbox("提示", "请从下拉选项中选择");

            // 添加验证到sheet
            sheet.addvalidationdata(validation);
        });
    }
}

@override
    public void aftercellcreate(cellwritehandlercontext context) {
        writesheetholder writesheetholder = context.getwritesheetholder();
        sheet sheet = writesheetholder.getsheet();
        workbook workbook = sheet.getworkbook();
        cell cell = context.getcell();
        int columnindex = cell.getcolumnindex();
        row row = cell.getrow();

        // 设置工作表保护
        if (!sheet.getprotect()) {
            xssfsheet xssfsheet = (xssfsheet) sheet;
            // 启用保护
            xssfsheet.protectsheet("1234");
            // 设置保护选项：允许删除未锁定行
            xssfsheet.lockdeleterows(false);
            // 设置保护选项：允许插入未锁定行
            xssfsheet.lockinsertrows(false);
        }

        // 设置工作表的默认单元格样式为不锁定
        cellstyle defaultstyle = workbook.createcellstyle();
        defaultstyle.setlocked(false);
        sheet.setdefaultcolumnstyle(columnindex, defaultstyle);
        row.setrowstyle(defaultstyle);
    }

sheet.addmergedregion(new cellrangeaddress(relativerowindex, relativerowindex, 0, 10));

package com.example.springbootexcel.excel.base.component;

import java.lang.annotation.elementtype;
import java.lang.annotation.retention;
import java.lang.annotation.retentionpolicy;
import java.lang.annotation.target;

/**
 * tips：非必填校验，填了就校验，不填不校验
 */
@retention(retentionpolicy.runtime)
@target({elementtype.field})
public @interface excelvalidation {

    /**
     * 日期校验
     *
     * @return true表示必须为日期，false表示不限制
     */
    boolean date() default false;

    /**
     * 是否必须为数字
     *
     * @return true表示必须为数字，false表示不限制
     */
    boolean numeric() default false;

    /**
     * 是否允许小数，且最多两位小数
     *
     * @return true表示允许最多两位小数，false表示不允许小数
     */
    boolean decimal() default false;

    /**
     * 是否允许斜杠
     *
     * @return true表示允许斜杠，false表示不允许
     */
    boolean allowslash() default false;

    /**
     * 校验失败时的错误提示信息
     *
     * @return 错误提示信息
     */
    string message() default "字段校验失败";

}

// 基本样式设置
        cellstyle.setbordertop(borderstyle.thin);
        cellstyle.setborderbottom(borderstyle.thin);
        cellstyle.setborderleft(borderstyle.thin);
        cellstyle.setborderright(borderstyle.thin);
        // 设置水平对齐为左对齐
        cellstyle.setalignment(horizontalalignment.left);
        // 设置垂直对齐为垂直居中
        cellstyle.setverticalalignment(verticalalignment.center);
        // 设置自动换行
        cellstyle.setwraptext(true);

        // 创建默认字体
        font defaultfont = workbook.createfont();
        defaultfont.setfontname(default_font_name);
        defaultfont.setfontheightinpoints(default_font_points);
        defaultfont.setcolor(indexedcolors.black.getindex());
        defaultfont.setbold(false);

        // 创建红色字体
        font redfont = workbook.createfont();
        redfont.setfontname(default_font_name);
        redfont.setfontheightinpoints(default_font_points);
        redfont.setcolor(indexedcolors.red.getindex());
        redfont.setbold(true);

        // 自定义列宽
        string cellvalue = cell.getstringcellvalue();
        integer columnwidth = column_widths.get(cellvalue);
        if (objectutil.isnotnull(columnwidth) && !collectionutil.contains(column_widths_exist, context.getcolumnindex())) {
            sheet.setcolumnwidth(context.getcolumnindex(), columnwidth);
            column_widths_exist.add(context.getcolumnindex());
        }
        // 设置默认宽度
        if (!collectionutil.contains(column_widths_exist, context.getcolumnindex())) {
            sheet.setcolumnwidth(context.getcolumnindex(), default_column_width);
        }

        // 提示词
        if (collectionutil.contains(tips_list, relativerowindex)) {
            defaultfont = redfont;
            // 合并单元格
            sheet.addmergedregion(new cellrangeaddress(relativerowindex, relativerowindex, 0, 10));
        }
        // 表头
        if (collectionutil.contains(head_list, relativerowindex)) {
            defaultfont.setbold(true);
            // 背景色
            cellstyle.setfillforegroundcolor(indexedcolors.grey_25_percent.getindex());
            cellstyle.setfillpattern(fillpatterntype.solid_foreground);

            // 动态字段标红
            if (collectionutil.contains(head_read_color, cell.getcolumnindex())) {
                defaultfont = redfont;
            } else {
                // 星号标红
                richtextstring richtext = cell.getrichstringcellvalue();
                if (strutil.startwith(cellvalue, "*")) {
                    richtext.applyfont(0, 1, redfont);
                    if (cellvalue.length() > 1) {
                        richtext.applyfont(1, cellvalue.length(), defaultfont);
                    }
                    cell.setcellvalue(richtext);
                }
            }
        }
        cellstyle.setfont(defaultfont);

package com.example.springbootexcel.excel.base.style;

import com.alibaba.excel.write.handler.sheetwritehandler;
import com.alibaba.excel.write.metadata.holder.writesheetholder;
import com.alibaba.excel.write.metadata.holder.writeworkbookholder;
import org.apache.poi.ss.usermodel.sheet;

/**
 * 冻结窗格
 *
 * @author jason
 */
public class freezepanehandler implements sheetwritehandler {
    private final int row;  // 需要冻结的行

    public freezepanehandler(int row) {
        this.row = row;
    }

    @override
    public void beforesheetcreate(writeworkbookholder writeworkbookholder, writesheetholder writesheetholder) {
    }

    @override
    public void aftersheetcreate(writeworkbookholder writeworkbookholder, writesheetholder writesheetholder) {
        sheet sheet = writesheetholder.getsheet();
        // 冻结首行
        // sheet.createfreezepane(0, 1, 0, 1);

        sheet.createfreezepane(0, row, 0, row);
    }

}

        // 创建 excelwriter 对象
        excelwriter excelwriter = easyexcel.write(filepath).inmemory(true).build();

        // 写入第1个 sheet
        writesheet sheet1 = easyexcel.writersheet("sheet1")
                .registerwritehandler(new commonstylehandler(mockdatautil.getheadreadcolor(headlist, dynamiclist)))
                .registerwritehandler(new dropdownhandler(dropdownmap))
                .registerwritehandler(new freezepanehandler(2))
                .build();
        excelwriter.write(sheet1datalist, sheet1);

        // 写入第2个 sheet
        writesheet sheet2 = easyexcel.writersheet("sheet2")
                .head(brandmodelexcel.class)
                .registerwritehandler(new freezepanehandler(1))
                .build();
        excelwriter.write(mockdatautil.brandmodelexcellist(), sheet2);

        // 写入第3个 sheet
        writesheet sheet3 = easyexcel.writersheet("sheet3")
                .head(vehiclenameexcel.class)
                .registerwritehandler(new freezepanehandler(1))
                .build();
        excelwriter.write(mockdatautil.vehiclenameexcellist(), sheet3);

        // 非常重要：最后一定要关闭 excelwriter
        excelwriter.finish();

        log.info("导出成功：{}", filepath);