基于Jav POI实现Excel多级表头导出的完整方案

┌──────────┬──────────┬──────────┬────────────────┬───────────────────────┐
│ 所在地区 │ 道路名称 │ 管线类别 │ 手续             │ 财政资金(万元)    │
│          │          │          ├──────────┬─────┼──────────┬──────────┤
│          │          │          │ 批复     │ 许可 │ 预算内   │          │
│          │          │          │ 编码     │ 编码 │ 投资     │ 国债     │
└──────────┴──────────┴──────────┴──────────┴─────┴──────────┴──────────┘

<!-- apache poi - excel核心依赖 -->
<dependency>
    <groupid>org.apache.poi</groupid>
    <artifactid>poi</artifactid>
    <version>4.1.2</version>
</dependency>

<!-- apache poi - xssf (excel 2007+) 支持 -->
<dependency>
    <groupid>org.apache.poi</groupid>
    <artifactid>poi-ooxml</artifactid>
    <version>4.1.2</version>
</dependency>

<!-- poi ooxml 依赖的xml解析库 -->
<dependency>
    <groupid>org.apache.poi</groupid>
    <artifactid>poi-ooxml-schemas</artifactid>
    <version>4.1.2</version>
</dependency>

<!-- 流式处理大数据量excel（关键：避免oom） -->
<dependency>
    <groupid>org.apache.poi</groupid>
    <artifactid>poi-scratchpad</artifactid>
    <version>4.1.2</version>
</dependency>

<!-- 日期工具依赖（可选，根据项目实际情况调整） -->
<dependency>
    <groupid>cn.hutool</groupid>
    <artifactid>hutool-all</artifactid>
    <version>5.8.20</version>
</dependency>

<!-- lombok（可选，简化dto开发） -->
<dependency>
    <groupid>org.projectlombok</groupid>
    <artifactid>lombok</artifactid>
    <version>1.18.30</version>
    <scope>provided</scope>
</dependency>

<!-- spring context（可选，项目已引入spring可忽略） -->
<dependency>
    <groupid>org.springframework</groupid>
    <artifactid>spring-context</artifactid>
    <version>5.3.29</version>
</dependency>

// apache poi 核心
implementation 'org.apache.poi:poi:4.1.2'
// excel 2007+ 支持
implementation 'org.apache.poi:poi-ooxml:4.1.2'
// ooxml 模式支持
implementation 'org.apache.poi:poi-ooxml-schemas:4.1.2'
// 流式处理大数据量
implementation 'org.apache.poi:poi-scratchpad:4.1.2'
// 工具类（可选）
implementation 'cn.hutool:hutool-all:5.8.20'
// lombok（可选）
compileonly 'org.projectlombok:lombok:1.18.30'
annotationprocessor 'org.projectlombok:lombok:1.18.30'

package com.tydt.framework.aspectj.lang.annotation;

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

/**
 * 自定义导出excel数据注解
 *
 * @author tydt
 */
@retention(retentionpolicy.runtime)
@target(elementtype.field)
public @interface excel
{
    /**
     * 导出时在excel中排序
     */
    int sort() default integer.max_value;

    /**
     * 导出到excel中的名字
     */
    string name() default "";

    /**
     * 日期格式, 如: yyyy-mm-dd
     */
    string dateformat() default "";

    /**
     * 读取内容转表达式 (如: 0=男,1=女,2=未知)
     */
    string readconverterexp() default "";

    /**
     * 导出类型（0数字 1字符串）
     */
    columntype celltype() default columntype.string;

    /**
     * 导出时在excel中每个列的高度 单位为字符
     */
    double height() default 14;

    /**
     * 导出时在excel中每个列的宽 单位为字符
     */
    double width() default 16;

    /**
     * 【核心】表头合并配置
     * 格式: "起始行,结束行,起始列,结束列"
     * 例如: "0,0,0,2" 表示第0行第0列到第0行第2列合并
     *      "0,1,0,0" 表示第0行第0列到第1行第0列合并（纵向合并）
     */
    string headermerge() default "";

    /**
     * 【核心】表头层级（从0开始，用于多级表头）
     */
    int headerlevel() default 0;

    /**
     * 【核心】是否分组表头（用于标识该字段是否为分组表头，不包含实际数据）
     */
    boolean isgroupheader() default false;

    /**
     * 【核心】合并行
     * 参数1为合并基准列，其他列用逗号拼接，依据基准列进行当前单元行合并
     * 例如: "0" 表示以第0列为基准进行行合并
     */
    string mergeline() default "";

    /**
     * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写
     */
    boolean isexport() default true;

    /**
     * 字段类型（0：导出导入；1：仅导出；2：仅导入）
     */
    type type() default type.all;

    // 其他辅助配置
    string pattern() default "";
    string suffix() default "";
    string defaultvalue() default "";
    string prompt() default "";
    string[] combo() default {};
    string targetattr() default "";
    int groupheaderrows() default 1;
    string separator() default "";
    string dicttype() default "";
    int scale() default 0;
    int roundingmode() default 0;
    boolean isstatistics() default false;
    string combodynamic() default "";

    public enum type
    {
        all(0), export(1), import(2);
        private final int value;
        type(int value) { this.value = value; }
        public int value() { return this.value; }
    }

    public enum columntype
    {
        numeric(0), string(1);
        private final int value;
        columntype(int value) { this.value = value; }
        public int value() { return this.value; }
    }
}

package com.itl.project.ssjs.gx.xy.snjhk.domain;

import com.itl.framework.aspectj.lang.annotation.excel;
import lombok.data;
import java.util.date;

/**
 * 导出管线实体类 - 现有道路储备项目库和年度计划库导出excel模板
 */
@data
public class exportdtoszd {

    // ========== 基础信息字段（纵向合并两行）==========
    // a列 (0) - 所在地区：0-1行纵向合并，作为二级表头
    @excel(name = "所在地区", headerlevel = 1, headermerge = "0,1,0,0", mergeline = "0")
    private string szdname;

    // b列 (1) - 道路名称：0-1行纵向合并
    @excel(name = "道路名称", headerlevel = 1, headermerge = "0,1,1,1")
    private string dlmc;

    // c列 (2) - 管线类别：0-1行纵向合并
    @excel(name = "管线类别", headerlevel = 1, headermerge = "0,1,2,2")
    private string gxlbname;

    // ... 省略其他基础信息字段 ...

    // ========== 手续分组（横向合并）==========
    // q-r-s列分组 (16-18) - 一级表头，横向合并3列
    @excel(name = "手续", headerlevel = 0, headermerge = "0,0,16,18", isgroupheader = true)
    private string qqscgroup;

    // q列 (16) - 批复编码：二级表头
    @excel(name = "批复编码", headerlevel = 1, headermerge = "1,1,16,16")
    private string lxpgbm;

    // r列 (17) - 许可编码：二级表头
    @excel(name = "许可编码", headerlevel = 1, headermerge = "1,1,17,17")
    private string ghxkbm;

    // s列 (18) - 挖掘编号：二级表头
    @excel(name = "挖掘编号", headerlevel = 1, headermerge = "1,1,18,18")
    private string dlwjbm;

    // ========== 财政资金分组 ==========
    // t-u列分组 (19-20) - 一级表头，横向合并2列
    @excel(name = "财政资金(万元)", headerlevel = 0, headermerge = "0,0,19,20", isgroupheader = true)
    private string zyczzjgroup;

    // t列 (19) - 预算内投资：二级表头
    @excel(name = "预算内投资", headerlevel = 1, headermerge = "1,1,19,19")
    private string ystz;

    // u列 (20) - 特别国债：二级表头
    @excel(name = "特别国债", headerlevel = 1, headermerge = "1,1,20,20")
    private string cztqtbgz;

    // ... 省略其他资金相关字段 ...

    // ========== 辅助字段（不导出）==========
    private string szd;
    private integer gxlb;
}

package com.itl.common.utils.poi;

import com.itl.common.exception.businessexception;
import com.itl.common.utils.dateutils;
import com.itl.common.utils.stringutils;
import com.itl.common.utils.reflect.reflectutils;
import com.itl.common.utils.text.convert;
import com.itl.framework.aspectj.lang.annotation.excel;
import com.itl.framework.aspectj.lang.annotation.excel.columntype;
import com.itl.framework.aspectj.lang.annotation.excel.type;
import com.itl.framework.aspectj.lang.annotation.excels;
import com.itl.framework.config.sysconfig;
import com.itl.framework.web.domain.ajaxresult;
import org.apache.poi.hssf.usermodel.hssfdateutil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.cellrangeaddress;
import org.apache.poi.ss.util.cellrangeaddresslist;
import org.apache.poi.xssf.streaming.sxssfworkbook;
import org.apache.poi.xssf.usermodel.xssfdatavalidation;
import org.slf4j.logger;
import org.slf4j.loggerfactory;
import org.springframework.util.objectutils;

import java.io.*;
import java.lang.reflect.field;
import java.lang.reflect.method;
import java.math.bigdecimal;
import java.text.decimalformat;
import java.util.*;

/**
 * excel相关处理 - 修复多级表头问题
 *
 * @author itl
 */
public class excelutilmerge<t>
{
    private static final logger log = loggerfactory.getlogger(excelutilmerge.class);

    /**
     * excel sheet最大行数，默认65536
     */
    public static final int sheetsize = 65536;

    /**
     * 工作表名称
     */
    private string sheetname;

    /**
     * 导出类型（export:导出数据；import：导入模板）
     */
    private type type;

    /**
     * 工作薄对象
     */
    private workbook wb;

    /**
     * 工作表对象
     */
    private sheet sheet;

    /**
     * 样式列表
     */
    private map<string, cellstyle> styles;

    /**
     * 导入导出数据列表
     */
    private list<t> list;

    /**
     * 注解列表
     */
    private list<object[]> fields;

    /**
     * 实体对象
     */
    public class<t> clazz;

    /**
     * 统计列表
     */
    private map<integer, double> statistics = new hashmap<integer, double>();

    //是否进行合并行
    private boolean switchmearge = false;
    //合并行数  起始行，结束行
    private int mergeline_start = 0;
    private int mergeline_end = 0;

    // 多级表头相关
    private int maxheaderlevel = 0;
    private map<integer, row> headerrows = new hashmap<>();

    // 多级表头模式开关
    private boolean multilevelheadermode = false;

    public excelutilmerge(class<t> clazz)
    {
        this.clazz = clazz;
    }

    /**
     * 设置多级表头模式
     */
    public void setmultilevelheadermode(boolean multilevelheadermode) {
        this.multilevelheadermode = multilevelheadermode;
        if (multilevelheadermode) {
            log.debug("启用多级表头模式");
        }
    }

    public void init(list<t> list, string sheetname, type type)
    {
        if (list == null)
        {
            list = new arraylist<t>();
        }
        this.list = list;
        this.sheetname = sheetname;
        this.type = type;
        createexcelfield();
        createworkbook();
    }

    /**
     * 对excel表单默认第一个索引名转换成list
     *
     * @param is 输入流
     * @return 转换后集合
     */
    public list<t> importexcel(inputstream is) throws exception
    {
        return importexcel(stringutils.empty, is);
    }

    /**
     * 对excel表单指定表格索引名转换成list
     *
     * @param sheetname 表格索引名
     * @param is 输入流
     * @return 转换后集合
     */
    public list<t> importexcel(string sheetname, inputstream is) throws exception
    {
        this.type = type.import;
        this.wb = workbookfactory.create(is);
        list<t> list = new arraylist<t>();
        sheet sheet = null;
        if (stringutils.isnotempty(sheetname))
        {
            // 如果指定sheet名,则取指定sheet中的内容.
            sheet = wb.getsheet(sheetname);
        }
        else
        {
            // 如果传入的sheet名不存在则默认指向第1个sheet.
            sheet = wb.getsheetat(0);
        }

        if (sheet == null)
        {
            throw new ioexception("文件sheet不存在");
        }

        int rows = sheet.getphysicalnumberofrows();

        if (rows > 0)
        {
            // 定义一个map用于存放excel列的序号和field.
            map<string, integer> cellmap = new hashmap<string, integer>();
            // 获取表头
            row heard = sheet.getrow(0);
            for (int i = 0; i < heard.getphysicalnumberofcells(); i++)
            {
                cell cell = heard.getcell(i);
                if (stringutils.isnotnull(cell))
                {
                    string value = this.getcellvalue(heard, i).tostring();
                    cellmap.put(value, i);
                }
                else
                {
                    cellmap.put(null, i);
                }
            }
            // 有数据时才处理 得到类的所有field.
            field[] allfields = clazz.getdeclaredfields();
            // 定义一个map用于存放列的序号和field.
            map<integer, field> fieldsmap = new hashmap<integer, field>();
            for (int col = 0; col < allfields.length; col++)
            {
                field field = allfields[col];
                excel attr = field.getannotation(excel.class);
                if (attr != null && (attr.type() == type.all || attr.type() == type))
                {
                    // 设置类的私有字段属性可访问.
                    field.setaccessible(true);
                    integer column = cellmap.get(attr.name());
                    fieldsmap.put(column, field);
                }
            }
            for (int i = 1; i < rows; i++)
            {
                // 从第2行开始取数据,默认第一行是表头.
                row row = sheet.getrow(i);
                t entity = null;
                for (map.entry<integer, field> entry : fieldsmap.entryset())
                {
                    object val = this.getcellvalue(row, entry.getkey());

                    // 如果不存在实例则新建.
                    entity = (entity == null ? clazz.newinstance() : entity);
                    // 从map中得到对应列的field.
                    field field = fieldsmap.get(entry.getkey());
                    // 取得类型,并根据对象类型设置值.
                    class<?> fieldtype = field.gettype();
                    if (string.class == fieldtype)
                    {
                        string s = convert.tostr(val);
                        if (stringutils.endswith(s, ".0"))
                        {
                            val = stringutils.substringbefore(s, ".0");
                        }
                        else
                        {
                            string dateformat = field.getannotation(excel.class).dateformat();
                            if (stringutils.isnotempty(dateformat))
                            {
                                val = dateutils.parsedatetostr(dateformat, (date) val);
                            }
                            else
                            {
                                val = convert.tostr(val);
                            }
                        }
                    }
                    else if ((integer.type == fieldtype) || (integer.class == fieldtype))
                    {
                        val = convert.toint(val);
                    }
                    else if ((long.type == fieldtype) || (long.class == fieldtype))
                    {
                        val = convert.tolong(val);
                    }
                    else if ((double.type == fieldtype) || (double.class == fieldtype))
                    {
                        val = convert.todouble(val);
                    }
                    else if ((float.type == fieldtype) || (float.class == fieldtype))
                    {
                        val = convert.tofloat(val);
                    }
                    else if (bigdecimal.class == fieldtype)
                    {
                        val = convert.tobigdecimal(val);
                    }
                    else if (date.class == fieldtype)
                    {
                        if (val instanceof string)
                        {
                            val = dateutils.parsedate(val);
                        }
                        else if (val instanceof double)
                        {
                            val = dateutil.getjavadate((double) val);
                        }
                    }
                    if (stringutils.isnotnull(fieldtype))
                    {
                        excel attr = field.getannotation(excel.class);
                        string propertyname = field.getname();
                        if (stringutils.isnotempty(attr.targetattr()))
                        {
                            propertyname = field.getname() + "." + attr.targetattr();
                        }
                        else if (stringutils.isnotempty(attr.readconverterexp()))
                        {
                            val = reversebyexp(string.valueof(val), attr.readconverterexp());
                        }else if(stringutils.isnotempty(attr.pattern())){
                            val = reversebypattern(string.valueof(val), attr.pattern());
                        }
                        reflectutils.invokesetter(entity, propertyname, val);
                    }
                }
                list.add(entity);
            }
        }
        return list;
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param list 导出数据集合
     * @param sheetname 工作表的名称
     * @return 结果
     */
    public ajaxresult exportexcel(list<t> list, string sheetname)
    {
        this.init(list, sheetname, type.export);
        return exportexcel();
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param sheetname 工作表的名称
     * @return 结果
     */
    public ajaxresult importtemplateexcel(string sheetname)
    {
        this.init(null, sheetname, type.import);
        return exportexcel();
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @return 结果
     */
    public ajaxresult exportexcel()
    {
        outputstream out = null;
        try
        {
            // 取出一共有多少个sheet.
            double sheetno = math.ceil(list.size() / sheetsize);
            for (int index = 0; index <= sheetno; index++)
            {
                createsheet(sheetno, index);

                // 创建多级表头
                createmultilevelheader();

                if (type.export.equals(type))
                {
                    // 数据起始行 = 表头行数
                    int datastartrow = maxheaderlevel + 1;
                    fillexceldata(index, datastartrow);
                }
            }
            string filename = encodingfilename(sheetname);
            out = new fileoutputstream(getabsolutefile(filename));
            wb.write(out);
            return ajaxresult.success(filename);
        }
        catch (exception e)
        {
            log.error("导出excel异常{}", e.getmessage());
            throw new businessexception("导出excel失败，请联系网站管理员！");
        }
        finally
        {
            if (wb != null)
            {
                try
                {
                    wb.close();
                }
                catch (ioexception e1)
                {
                    e1.printstacktrace();
                }
            }
            if (out != null)
            {
                try
                {
                    out.close();
                }
                catch (ioexception e1)
                {
                    e1.printstacktrace();
                }
            }
        }
    }

    /**
     * 创建多级表头 - 简化版本
     */
    private void createmultilevelheader() {
        // 计算最大表头层级
        calculatemaxheaderlevel();

        // 创建所有表头行
        for (int level = 0; level <= maxheaderlevel; level++) {
            row row = sheet.createrow(level);
            headerrows.put(level, row);

            // 设置行高（表头行高稍高）
            row.setheight((short) (25 * 20));
        }

        // 填充表头内容
        fillheadercontent();

        // 处理表头合并
        processheadermerge();

        // 设置列宽
        setcolumnwidths();
    }

    /**
     * 计算最大表头层级
     */
    private void calculatemaxheaderlevel() {
        maxheaderlevel = 0;
        for (object[] os : fields) {
            excel excel = (excel) os[1];
            // 检查是否有headermerge配置
            if (stringutils.isnotempty(excel.headermerge())) {
                string[] mergeparams = excel.headermerge().split(",");
                if (mergeparams.length == 4) {
                    try {
                        int lastrow = integer.parseint(mergeparams[1]);
                        if (lastrow > maxheaderlevel) {
                            maxheaderlevel = lastrow;
                        }
                    } catch (numberformatexception e) {
                        log.warn("headermerge参数格式错误: {}", excel.headermerge());
                    }
                }
            }
            // 同时检查headerlevel
            if (excel.headerlevel() > maxheaderlevel) {
                maxheaderlevel = excel.headerlevel();
            }
        }

        // 确保至少有一级表头
        if (maxheaderlevel < 0) {
            maxheaderlevel = 0;
        }
    }

    /**
     * 填充表头内容 - 根据模式选择不同的实现
     */
    private void fillheadercontent() {
        if (multilevelheadermode) {
            fillheadercontentmultilevel();
        } else {
            fillheadercontentoriginal();
        }
    }

    /**
     * 原有的表头填充逻辑
     */
    private void fillheadercontentoriginal() {
        int column = 0;
        for (object[] os : fields) {
            excel excel = (excel) os[1];
            string headermerge = excel.headermerge();

            // 解析合并参数
            int firstrow = 0;
            int lastrow = 0;
            int firstcol = column;
            int lastcol = column;

            if (stringutils.isnotempty(headermerge)) {
                string[] mergeparams = headermerge.split(",");
                if (mergeparams.length == 4) {
                    try {
                        firstrow = integer.parseint(mergeparams[0]);
                        lastrow = integer.parseint(mergeparams[1]);
                        firstcol = integer.parseint(mergeparams[2]);
                        lastcol = integer.parseint(mergeparams[3]);
                    } catch (numberformatexception e) {
                        log.warn("headermerge参数格式错误: {}", headermerge);
                    }
                }
            }

            // 为每个层级的表头行创建单元格
            for (int level = 0; level <= maxheaderlevel; level++) {
                row row = headerrows.get(level);
                cell cell = row.createcell(column);

                // 所有表头使用相同的样式
                cell.setcellstyle(styles.get("header0"));

                // 判断当前单元格是否应该显示内容
                boolean shouldshowcontent = false;

                if (stringutils.isnotempty(headermerge)) {
                    // 有合并配置的情况
                    if (level >= firstrow && level <= lastrow && column >= firstcol && column <= lastcol) {
                        if (level == firstrow && column == firstcol) {
                            // 只在合并区域的第一个单元格显示内容
                            cell.setcellvalue(excel.name());
                            shouldshowcontent = true;
                        }
                    }
                } else {
                    // 无合并配置的情况，使用headerlevel
                    if (excel.headerlevel() == level) {
                        cell.setcellvalue(excel.name());
                        shouldshowcontent = true;
                    }
                }

                if (!shouldshowcontent) {
                    cell.setcellvalue("");
                }
            }
            column++;
        }
    }

    /**
     * 多级表头专用逻辑
     */
   /* private void fillheadercontentmultilevel() {
        int logicalindex = 0;
        int physicalcolumn = 0;

        log.debug("=== 开始多级表头填充 ===");

        for (object[] os : fields) {
            excel excel = (excel) os[1];
            string headermerge = excel.headermerge();

            int actualcolumn = physicalcolumn;
            int columnspan = 1;

            // 解析合并参数，确定实际列位置和跨度
            if (stringutils.isnotempty(headermerge)) {
                string[] mergeparams = headermerge.split(",");
                if (mergeparams.length == 4) {
                    try {
                        int configfirstcol = integer.parseint(mergeparams[2]);
                        int configlastcol = integer.parseint(mergeparams[3]);
                        actualcolumn = configfirstcol;
                        columnspan = configlastcol - configfirstcol + 1;

                        log.debug("分组字段[{}]: 逻辑索引={}, 实际列={}, 跨度={}",
                                excel.name(), logicalindex, actualcolumn, columnspan);
                    } catch (numberformatexception e) {
                        log.warn("headermerge参数格式错误: {}", headermerge);
                    }
                }
            } else {
                log.debug("普通字段[{}]: 逻辑索引={}, 实际列={}",
                        excel.name(), logicalindex, actualcolumn);
            }

            // 为每个层级的表头行创建单元格
            for (int level = 0; level <= maxheaderlevel; level++) {
                row row = headerrows.get(level);
                cell cell = row.createcell(actualcolumn);
                cell.setcellstyle(styles.get("header0"));

                boolean shouldshowcontent = false;

                if (stringutils.isnotempty(headermerge)) {
                    // 分组字段的处理
                    string[] mergeparams = headermerge.split(",");
                    int firstrow = integer.parseint(mergeparams[0]);
                    int lastrow = integer.parseint(mergeparams[1]);
                    int firstcol = integer.parseint(mergeparams[2]);
                    int lastcol = integer.parseint(mergeparams[3]);

                    if (level >= firstrow && level <= lastrow &&
                            actualcolumn >= firstcol && actualcolumn <= lastcol) {
                        if (level == firstrow && actualcolumn == firstcol) {
                            // 只在合并区域的第一个单元格显示分组标题
                            cell.setcellvalue(excel.name());
                            shouldshowcontent = true;
                        }
                    } else if (level == excel.headerlevel()) {
                        // 分组字段不在自己的合并区域内，但在正确的层级，显示空值
                        cell.setcellvalue("");
                    }
                } else {
                    // 普通字段的处理
                    if (excel.headerlevel() == level) {
                        cell.setcellvalue(excel.name());
                        shouldshowcontent = true;
                    }
                }

                if (!shouldshowcontent) {
                    cell.setcellvalue("");
                }
            }

            // 更新列索引
            if (stringutils.isnotempty(headermerge)) {
                // 分组字段：跳转到配置的结束列的下一个位置
                string[] mergeparams = headermerge.split(",");
                int lastcol = integer.parseint(mergeparams[3]);
                physicalcolumn = lastcol + 1;
            } else {
                // 普通字段：正常递增
                physicalcolumn++;
            }

            logicalindex++;
        }

        log.debug("=== 结束多级表头填充 ===");
    }*/

    /**
     * 处理表头合并 - 根据模式选择不同的实现
     */
    private void processheadermerge() {
        if (multilevelheadermode) {
            processheadermergemultilevel();
        } else {
            processheadermergeoriginal();
        }
    }

    /**
     * 原有的合并区域处理逻辑
     */
    private void processheadermergeoriginal() {
        int column = 0;

        for (object[] os : fields) {
            excel excel = (excel) os[1];
            string headermerge = excel.headermerge();

            if (stringutils.isnotempty(headermerge)) {
                string[] mergeparams = headermerge.split(",");
                if (mergeparams.length == 4) {
                    try {
                        int firstrow = integer.parseint(mergeparams[0]);
                        int lastrow = integer.parseint(mergeparams[1]);
                        int firstcol = integer.parseint(mergeparams[2]);
                        int lastcol = integer.parseint(mergeparams[3]);

                        // 验证合并区域的有效性
                        if (firstrow >= 0 && lastrow <= maxheaderlevel &&
                                firstcol >= 0 && lastcol < fields.size() &&
                                firstrow <= lastrow && firstcol <= lastcol) {

                            cellrangeaddress region = new cellrangeaddress(firstrow, lastrow, firstcol, lastcol);

                            try {
                                sheet.addmergedregion(region);
                                log.debug("添加合并区域: {}-{}, {}-{}",
                                        region.getfirstrow(), region.getlastrow(),
                                        region.getfirstcolumn(), region.getlastcolumn());
                            } catch (exception e) {
                                log.error("合并区域失败: {}", region.formatasstring(), e);
                            }
                        }
                    } catch (numberformatexception e) {
                        log.error("表头合并参数格式错误: {}", headermerge);
                    }
                }
            }
            column++;
        }
    }

    /**
     * 多级表头专用的合并区域处理
     */
    private void processheadermergemultilevel() {
        int logicalindex = 0;
        int physicalcolumn = 0;

        for (object[] os : fields) {
            excel excel = (excel) os[1];
            string headermerge = excel.headermerge();

            if (stringutils.isnotempty(headermerge)) {
                string[] mergeparams = headermerge.split(",");
                if (mergeparams.length == 4) {
                    try {
                        int firstrow = integer.parseint(mergeparams[0]);
                        int lastrow = integer.parseint(mergeparams[1]);
                        int firstcol = integer.parseint(mergeparams[2]);
                        int lastcol = integer.parseint(mergeparams[3]);

                        // 验证合并区域的有效性
                        if (firstrow >= 0 && lastrow <= maxheaderlevel &&
                                firstcol >= 0 && lastcol < gettotalcolumns() &&
                                firstrow <= lastrow && firstcol <= lastcol) {

                            cellrangeaddress region = new cellrangeaddress(firstrow, lastrow, firstcol, lastcol);

                            try {
                                sheet.addmergedregion(region);
                                log.debug("添加合并区域: {}-{}, {}-{}",
                                        region.getfirstrow(), region.getlastrow(),
                                        region.getfirstcolumn(), region.getlastcolumn());
                            } catch (exception e) {
                                log.error("合并区域失败: {}", region.formatasstring(), e);
                            }
                        }
                    } catch (numberformatexception e) {
                        log.error("表头合并参数格式错误: {}", headermerge);
                    }
                }
            }

            // 更新列索引（与fillheadercontentmultilevel保持一致）
            if (stringutils.isnotempty(headermerge)) {
                string[] mergeparams = headermerge.split(",");
                int lastcol = integer.parseint(mergeparams[3]);
                physicalcolumn = lastcol + 1;
            } else {
                physicalcolumn++;
            }

            logicalindex++;
        }
    }

    /**
     * 计算总列数 - 多级表头专用
     */
    private int gettotalcolumns() {
        int total = 0;
        for (object[] os : fields) {
            excel excel = (excel) os[1];
            string headermerge = excel.headermerge();

            if (stringutils.isnotempty(headermerge)) {
                string[] mergeparams = headermerge.split(",");
                if (mergeparams.length == 4) {
                    try {
                        int firstcol = integer.parseint(mergeparams[2]);
                        int lastcol = integer.parseint(mergeparams[3]);
                        total = math.max(total, lastcol + 1);
                    } catch (numberformatexception e) {
                        total++;
                    }
                }
            } else {
                total++;
            }
        }
        return total;
    }

    /**
     * 设置列宽
     */
    private void setcolumnwidths() {
        int column = 0;
        for (object[] os : fields) {
            excel excel = (excel) os[1];
            // 设置列宽
            sheet.setcolumnwidth(column, (int) ((excel.width() + 0.72) * 256));
            column++;
        }
    }

    /**
     * 填充excel数据
     *
     * @param index 序号
     * @param startrow 数据起始行
     */
    public void fillexceldata(int index, int startrow)
    {
        int startno = index * sheetsize;
        int endno = math.min(startno + sheetsize, list.size());

        //当前行
        int thisline = 0;
        row row = null;

        for (int i = startno; i < endno; i++) {
            row = sheet.createrow(startrow + i - startno);

            thisline = startrow + i - startno;
            // 得到导出对象.
            t vo = (t) list.get(i);
            t vo_previous = null;
            //得到上一个导出对象
            if (i != startno) {
                vo_previous = (t) list.get(i - 1);
            }
            /**
             *取下一个对象   与当前对象对比，如果相同，记住当前列，再与下一个对比，一直对比到不相同，执行合并代码
             * 注解加入 合并行列标识
             */
            int column = 0;
            for (object[] os : fields) {

                field field = (field) os[0];
                excel excel = (excel) os[1];
                // 关键修复：跳过分组字段，不创建单元格
                if (excel.isgroupheader()) {
                   // log.debug("跳过分组字段: {} (isgroupheader=true)", excel.name());
                    continue; // 直接跳过，不增加column索引
                }
                // 设置实体类私有属性可访问
                field.setaccessible(true);
                this.addcell(excel, row, vo, field, column++, vo_previous, thisline);
            }
        }
    }

    /**
     * 创建表格样式 - 简化表头样式
     */
    private map<string, cellstyle> createstyles(workbook wb) {
        map<string, cellstyle> styles = new hashmap<>();

        // 数据样式
        cellstyle datastyle = createdatastyle(wb);
        styles.put("data", datastyle);

        // 创建表头样式（所有表头使用相同样式）
        createheaderstyles(wb, styles);

        return styles;
    }

    /**
     * 创建表头样式 - 所有表头使用统一样式
     */
    private void createheaderstyles(workbook wb, map<string, cellstyle> styles) {
        // 表头样式 - 灰色背景，黑色字体
        cellstyle headerstyle = wb.createcellstyle();
        headerstyle.clonestylefrom(styles.get("data"));

        // 设置对齐方式
        headerstyle.setalignment(horizontalalignment.center);
        headerstyle.setverticalalignment(verticalalignment.center);

        // 设置背景色 - 浅灰色
        headerstyle.setfillforegroundcolor(indexedcolors.grey_25_percent.getindex());
        headerstyle.setfillpattern(fillpatterntype.solid_foreground);

        // 设置字体 - 加粗
        font headerfont = wb.createfont();
        headerfont.setfontname("宋体");
        headerfont.setfontheightinpoints((short) 11);
        headerfont.setbold(true);
        headerfont.setcolor(indexedcolors.black.getindex());

        headerstyle.setfont(headerfont);

        // 所有层级的表头都使用同一个样式
        for (int i = 0; i <= 4; i++) {
            styles.put("header" + i, headerstyle);
        }
    }

    /**
     * 创建数据样式
     */
    private cellstyle createdatastyle(workbook wb) {
        cellstyle style = wb.createcellstyle();
        style.setalignment(horizontalalignment.center);
        style.setverticalalignment(verticalalignment.center);
        style.setborderright(borderstyle.thin);
        style.setrightbordercolor(indexedcolors.grey_50_percent.getindex());
        style.setborderleft(borderstyle.thin);
        style.setleftbordercolor(indexedcolors.grey_50_percent.getindex());
        style.setbordertop(borderstyle.thin);
        style.settopbordercolor(indexedcolors.grey_50_percent.getindex());
        style.setborderbottom(borderstyle.thin);
        style.setbottombordercolor(indexedcolors.grey_50_percent.getindex());

        font datafont = wb.createfont();
        datafont.setfontname("宋体");
        datafont.setfontheightinpoints((short) 10);
        style.setfont(datafont);

        return style;
    }

    /**
     * @param attr     excel
     * @param row
     * @param vo
     * @param field
     * @param column
     * @param thisline 当前行
     * @return org.apache.poi.ss.usermodel.cell
     * @author xkng
     * @creed: 合并单元行拓展
     * @date 2021/12/14 14:51
     */
    public cell addcell(excel attr, row row, t vo, field field, int column, t vo_previous, int thisline) {
        cell cell = null;
        try {
            // 设置行高
            row.setheight((short) (attr.height() * 20));
            // 根据excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
            if (attr.isexport()) {
                // 创建cell
                cell = row.createcell(column);
                cell.setcellstyle(styles.get("data"));

                // 如果是分组表头字段，不显示实际数据
                if (attr.isgroupheader()) {
                    cell.setcellvalue("");
                } else {
                    // 用于读取对象中的属性
                    object value = gettargetvalue(vo, field, attr);
                    object value_previous = null;
                    if (vo_previous != null) {
                        value_previous = gettargetvalue(vo_previous, field, attr);
                    }
                    string dateformat = attr.dateformat();
                    string readconverterexp = attr.readconverterexp();
                    string pattern = attr.pattern();

                    string mergelinestr = attr.mergeline();
                    string[] mergeline = mergelinestr.split(",");

                    if (stringutils.isnotempty(dateformat) && stringutils.isnotnull(value))
                    {
                        cell.setcellvalue(dateutils.parsedatetostr(dateformat, (date) value));
                    }
                    else if (stringutils.isnotempty(readconverterexp) && stringutils.isnotnull(value))
                    {
                        cell.setcellvalue(convertbyexp(string.valueof(value), readconverterexp));
                    }
                    else if(stringutils.isnotempty(pattern) && stringutils.isnotnull(value))
                    {
                        cell.setcellvalue(reversebypattern(string.valueof(value), pattern));
                    }
                    else
                    {
                        // 设置列类型
                        setcellvo(value, attr, cell);
                    }

                    addstatisticsdata(column, convert.tostr(value), attr);

                    //合并行  单列为基准进行合并
                    if (mergeline.length > 0 && mergeline[0] != null && !"".equals(mergeline[0])) {
                        if (!objectutils.isempty(value) && value.equals(value_previous)) {
                            if (this.mergeline_start == 0) {
                                this.mergeline_start = thisline - 1;
                            }
                            this.mergeline_end = thisline;
                        } else {
                            if (this.mergeline_start != 0 && this.mergeline_end != 0) {
                                if (this.mergeline_start != this.mergeline_end) {
                                    for (string ml : mergeline) {
                                        cellrangeaddress region = new cellrangeaddress(this.mergeline_start, this.mergeline_end, integer.parseint(ml), integer.parseint(ml));
                                        sheet.addmergedregion(region);
                                    }
                                }
                                this.mergeline_start = 0;
                                this.mergeline_end = 0;
                            }
                        }
                    }
                }
            }
        } catch (exception e) {
            log.error("导出excel失败{}", e);
        }
        return cell;
    }

    /**
     * 创建单元格
     */
    public cell createcell(excel attr, row row, int column)
    {
        // 创建列
        cell cell = row.createcell(column);

        // 设置表头样式
        cell.setcellvalue(attr.name());
        setdatavalidation(attr, row, column);
        cell.setcellstyle(styles.get("header0"));
        return cell;
    }

    /**
     * 设置单元格信息
     *
     * @param value 单元格值
     * @param attr 注解相关
     * @param cell 单元格信息
     */
    public void setcellvo(object value, excel attr, cell cell)
    {
        if (columntype.string == attr.celltype())
        {
            cell.setcelltype(celltype.numeric);
            cell.setcellvalue(stringutils.isnull(value) ? attr.defaultvalue() : value + attr.suffix());
        }
        else if (columntype.numeric == attr.celltype())
        {
            cell.setcelltype(celltype.numeric);
            cell.setcellvalue(integer.parseint(value + ""));
        }
    }

    /**
     * 创建表格样式
     */
    public void setdatavalidation(excel attr, row row, int column)
    {
        if (attr.name().indexof("注：") >= 0)
        {
            sheet.setcolumnwidth(column, 6000);
        }
        else
        {
            // 设置列宽
            sheet.setcolumnwidth(column, (int) ((attr.width() + 0.72) * 256));
            row.setheight((short) (attr.height() * 20));
        }
        // 如果设置了提示信息则鼠标放上去提示.
        if (stringutils.isnotempty(attr.prompt()))
        {
            // 这里默认设了2-101列提示.
            setxssfprompt(sheet, "", attr.prompt(), 1, 100, column, column);
        }
        // 如果设置了combo属性则本列只能选择不能输入
        if (attr.combo().length > 0)
        {
            // 这里默认设了2-101列只能选择不能输入.
            setxssfvalidation(sheet, attr.combo(), 1, 100, column, column);
        }
    }

    /**
     * 合计统计信息
     */
    private void addstatisticsdata(integer index, string text, excel entity) {
        if (entity != null && entity.isstatistics()) {
            double temp = 0d;
            if (!statistics.containskey(index)) {
                statistics.put(index, temp);
            }
            try {
                temp = double.valueof(text);
            } catch (numberformatexception e) {
            }
            statistics.put(index, statistics.get(index) + temp);
        }
    }

    /**
     * 设置 poi xssfsheet 单元格提示
     *
     * @param sheet 表单
     * @param prompttitle 提示标题
     * @param promptcontent 提示内容
     * @param firstrow 开始行
     * @param endrow 结束行
     * @param firstcol 开始列
     * @param endcol 结束列
     */
    public void setxssfprompt(sheet sheet, string prompttitle, string promptcontent, int firstrow, int endrow,
                              int firstcol, int endcol)
    {
        datavalidationhelper helper = sheet.getdatavalidationhelper();
        datavalidationconstraint constraint = helper.createcustomconstraint("dd1");
        cellrangeaddresslist regions = new cellrangeaddresslist(firstrow, endrow, firstcol, endcol);
        datavalidation datavalidation = helper.createvalidation(constraint, regions);
        datavalidation.createpromptbox(prompttitle, promptcontent);
        datavalidation.setshowpromptbox(true);
        sheet.addvalidationdata(datavalidation);
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     *
     * @param sheet 要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param firstrow 开始行
     * @param endrow 结束行
     * @param firstcol 开始列
     * @param endcol 结束列
     * @return 设置好的sheet.
     */
    public void setxssfvalidation(sheet sheet, string[] textlist, int firstrow, int endrow, int firstcol, int endcol)
    {
        datavalidationhelper helper = sheet.getdatavalidationhelper();
        // 加载下拉列表内容
        datavalidationconstraint constraint = helper.createexplicitlistconstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        cellrangeaddresslist regions = new cellrangeaddresslist(firstrow, endrow, firstcol, endcol);
        // 数据有效性对象
        datavalidation datavalidation = helper.createvalidation(constraint, regions);
        // 处理excel兼容性问题
        if (datavalidation instanceof xssfdatavalidation)
        {
            datavalidation.setsuppressdropdownarrow(true);
            datavalidation.setshowerrorbox(true);
        }
        else
        {
            datavalidation.setsuppressdropdownarrow(false);
        }

        sheet.addvalidationdata(datavalidation);
    }

    /**
     * 解析导出值 0=男,1=女,2=未知
     *
     * @param propertyvalue 参数值
     * @param converterexp 翻译注解
     * @return 解析后值
     * @throws exception
     */
    public static string convertbyexp(string propertyvalue, string converterexp) throws exception
    {
        try
        {
            string[] convertsource = converterexp.split(",");
            for (string item : convertsource)
            {
                string[] itemarray = item.split("=");
                if (itemarray[0].equals(propertyvalue))
                {
                    return itemarray[1];
                }
            }
        }
        catch (exception e)
        {
            throw e;
        }
        return propertyvalue;
    }

    /**
     * 反向解析值 男=0,女=1,未知=2
     *
     * @param propertyvalue 参数值
     * @param converterexp 翻译注解
     * @return 解析后值
     * @throws exception
     */
    public static string reversebyexp(string propertyvalue, string converterexp) throws exception
    {
        try
        {
            string[] convertsource = converterexp.split(",");
            for (string item : convertsource)
            {
                string[] itemarray = item.split("=");
                if (itemarray[1].equals(propertyvalue))
                {
                    return itemarray[0];
                }
            }
        }
        catch (exception e)
        {
            throw e;
        }
        return propertyvalue;
    }

    /**
     * 获取正则表达式内容
     * @param propertyvalue 参数值
     * @param converterexp 注解
     * @return
     */
    public static string reversebypattern(string propertyvalue, string converterexp){
        string result = propertyvalue.replaceall(converterexp,"").replaceall("\\&[a-za-z]{1,10};", "");
        return result;
    }

    /**
     * 编码文件名
     */
    public string encodingfilename(string filename)
    {
        filename = uuid.randomuuid().tostring() + "_" + filename + ".xlsx";
        return filename;
    }

    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public string getabsolutefile(string filename)
    {
        string downloadpath = sysconfig.getdownloadpath() + filename;
        file desc = new file(downloadpath);
        if (!desc.getparentfile().exists())
        {
            desc.getparentfile().mkdirs();
        }
        return downloadpath;
    }

    /**
     * 获取bean中的属性值
     *
     * @param vo 实体对象
     * @param field 字段
     * @param excel 注解
     * @return 最终的属性值
     * @throws exception
     */
    private object gettargetvalue(t vo, field field, excel excel) throws exception
    {
        object o = field.get(vo);
        if (stringutils.isnotempty(excel.targetattr()))
        {
            string target = excel.targetattr();
            if (target.indexof(".") > -1)
            {
                string[] targets = target.split("[.]");
                for (string name : targets)
                {
                    o = getvalue(o, name);
                }
            }
            else
            {
                o = getvalue(o, target);
            }
        }
        return o;
    }

    /**
     * 以类的属性的get方法方法形式获取值
     *
     * @param o
     * @param name
     * @return value
     * @throws exception
     */
    private object getvalue(object o, string name) throws exception
    {
        if (stringutils.isnotempty(name))
        {
            class<?> clazz = o.getclass();
            string methodname = "get" + name.substring(0, 1).touppercase() + name.substring(1);
            method method = clazz.getmethod(methodname);
            o = method.invoke(o);
        }
        return o;
    }

    /**
     * 得到所有定义字段 - 修复排序问题
     */
    private void createexcelfield()
    {
        this.fields = new arraylist<object[]>();
        list<field> tempfields = new arraylist<>();
        tempfields.addall(arrays.aslist(clazz.getsuperclass().getdeclaredfields()));
        tempfields.addall(arrays.aslist(clazz.getdeclaredfields()));

        for (field field : tempfields)
        {
            // 单注解
            if (field.isannotationpresent(excel.class))
            {
                puttofield(field, field.getannotation(excel.class));
            }

            // 多注解
            if (field.isannotationpresent(excels.class))
            {
                excels attrs = field.getannotation(excels.class);
                excel[] excels = attrs.value();
                for (excel excel : excels)
                {
                    puttofield(field, excel);
                }
            }
        }

        // 关键修复：按sort值对字段进行排序
        sortfieldsbyorder();
    }

    /**
     * 按sort值对字段进行排序 - 新增方法
     */
    private void sortfieldsbyorder() {
        if (this.fields != null && this.fields.size() > 0) {
            // 使用稳定的排序算法
            collections.sort(this.fields, new comparator<object[]>() {
                @override
                public int compare(object[] o1, object[] o2) {
                    excel excel1 = (excel) o1[1];
                    excel excel2 = (excel) o2[1];
                    return integer.compare(excel1.sort(), excel2.sort());
                }
            });

            // 调试信息：打印排序后的字段顺序
            if (log.isdebugenabled()) {
                stringbuilder sb = new stringbuilder("excel字段排序结果: ");
                for (object[] field : fields) {
                    excel excel = (excel) field[1];
                    sb.append(excel.name()).append("(sort=").append(excel.sort()).append(") ");
                }
                log.debug(sb.tostring());
            }
        }
    }

    /**
     * 放到字段集合中
     */
    private void puttofield(field field, excel attr)
    {
        if (attr != null && (attr.type() == type.all || attr.type() == type))
        {
            this.fields.add(new object[] { field, attr });
        }
    }

    /**
     * 创建一个工作簿
     */
    public void createworkbook()
    {
        this.wb = new sxssfworkbook(500);
    }

    /**
     * 创建工作表
     *
     * @param sheetno sheet数量
     * @param index 序号
     */
    public void createsheet(double sheetno, int index)
    {
        this.sheet = wb.createsheet();
        this.styles = createstyles(wb);
        // 设置工作表的名称.
        if (sheetno == 0)
        {
            wb.setsheetname(index, sheetname);
        }
        else
        {
            wb.setsheetname(index, sheetname + index);
        }
    }

    /**
     * 获取单元格值
     *
     * @param row 获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    public object getcellvalue(row row, int column)
    {
        if (row == null)
        {
            return row;
        }
        object val = "";
        try
        {
            cell cell = row.getcell(column);
            if (stringutils.isnotnull(cell))
            {
                if (cell.getcelltypeenum() == celltype.numeric || cell.getcelltypeenum() == celltype.formula)
                {
                    val = cell.getnumericcellvalue();
                    if (hssfdateutil.iscelldateformatted(cell))
                    {
                        val = dateutil.getjavadate((double) val); // poi excel 日期格式转换
                    }
                    else
                    {
                        if ((double) val % 1 > 0)
                        {
                            val = new decimalformat("0.00").format(val);
                        }
                        else
                        {
                            val = new decimalformat("0").format(val);
                        }
                    }
                }
                else if (cell.getcelltypeenum() == celltype.string)
                {
                    val = cell.getstringcellvalue();
                }
                else if (cell.getcelltypeenum() == celltype.boolean)
                {
                    val = cell.getbooleancellvalue();
                }
                else if (cell.getcelltypeenum() == celltype.error)
                {
                    val = cell.geterrorcellvalue();
                }

            }
        }
        catch (exception e)
        {
            return val;
        }
        return val;
    }



    /**
     * 多级表头专用逻辑 - 修复版本
     */
    private void fillheadercontentmultilevel() {
        int logicalindex = 0;
        int physicalcolumn = 0;

        log.debug("=== 开始多级表头填充 ===");

        for (object[] os : fields) {
            excel excel = (excel) os[1];
            string headermerge = excel.headermerge();

            int actualcolumn = physicalcolumn;
            int columnspan = 1;

            // 解析合并参数，确定实际列位置和跨度
            if (stringutils.isnotempty(headermerge)) {
                string[] mergeparams = headermerge.split(",");
                if (mergeparams.length == 4) {
                    try {
                        int configfirstcol = integer.parseint(mergeparams[2]);
                        int configlastcol = integer.parseint(mergeparams[3]);
                        actualcolumn = configfirstcol;
                        columnspan = configlastcol - configfirstcol + 1;

                        log.debug("分组字段[{}]: 逻辑索引={}, 实际列={}, 跨度={}",
                                excel.name(), logicalindex, actualcolumn, columnspan);
                    } catch (numberformatexception e) {
                        log.warn("headermerge参数格式错误: {}", headermerge);
                    }
                }
            } else {
                log.debug("普通字段[{}]: 逻辑索引={}, 实际列={}",
                        excel.name(), logicalindex, actualcolumn);
            }

            // 为每个层级的表头行创建单元格
            for (int level = 0; level <= maxheaderlevel; level++) {
                row row = headerrows.get(level);

                // 确保单元格存在
                cell cell = row.getcell(actualcolumn);
                if (cell == null) {
                    cell = row.createcell(actualcolumn);
                }

                cell.setcellstyle(styles.get("header0"));

                boolean shouldshowcontent = false;

                if (stringutils.isnotempty(headermerge)) {
                    // 分组字段的处理 - 修复逻辑
                    string[] mergeparams = headermerge.split(",");
                    int firstrow = integer.parseint(mergeparams[0]);
                    int lastrow = integer.parseint(mergeparams[1]);
                    int firstcol = integer.parseint(mergeparams[2]);
                    int lastcol = integer.parseint(mergeparams[3]);

                    // 只在合并区域的起始位置显示内容
                    if (level == firstrow && actualcolumn == firstcol) {
                        cell.setcellvalue(excel.name());
                        shouldshowcontent = true;
                        log.debug("设置分组标题[{}]在位置: 行={}, 列={}",
                                excel.name(), level, actualcolumn);
                    }
                } else {
                    // 普通字段的处理
                    if (excel.headerlevel() == level) {
                        cell.setcellvalue(excel.name());
                        shouldshowcontent = true;
                    }
                }

                if (!shouldshowcontent && cell.getcelltype() == celltype.blank.getcode()) {
                    cell.setcellvalue("");
                }
            }

            // 更新列索引
            if (stringutils.isnotempty(headermerge)) {
                // 分组字段：跳转到配置的结束列的下一个位置
                string[] mergeparams = headermerge.split(",");
                int lastcol = integer.parseint(mergeparams[3]);
                physicalcolumn = lastcol + 1;
            } else {
                // 普通字段：正常递增
                physicalcolumn++;
            }

            logicalindex++;
        }

        log.debug("=== 结束多级表头填充 ===");
    }
}

package com.itl.framework.aspectj.lang.annotation;

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

/**
 * 自定义导出excel数据注解
 *
 * @author itl
 */
@retention(retentionpolicy.runtime)
@target(elementtype.field)
public @interface excel
{
    /**
     * 导出时在excel中排序
     */
    public int sort() default integer.max_value;

    /**
     * 导出到excel中的名字.
     */
    public string name() default "";

    /**
     * 日期格式, 如: yyyy-mm-dd
     */
    public string dateformat() default "";

    /**
     * 读取内容转表达式 (如: 0=男,1=女,2=未知)
     */
    public string readconverterexp() default "";

    /**
     * 读取符合正则表达式内容
     */
    public string pattern() default "";

    /**
     * 导出类型（0数字 1字符串）
     */
    public columntype celltype() default columntype.string;

    /**
     * 导出时在excel中每个列的高度 单位为字符
     */
    public double height() default 14;

    /**
     * 导出时在excel中每个列的宽 单位为字符
     */
    public double width() default 16;

    /**
     * 文字后缀,如% 90 变成90%
     */
    public string suffix() default "";

    /**
     * 当值为空时,字段的默认值
     */
    public string defaultvalue() default "";

    /**
     * 提示信息
     */
    public string prompt() default "";

    /**
     * 设置只能选择不能输入的列内容.
     */
    public string[] combo() default {};

    /**
     * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
     */
    public boolean isexport() default true;

    /**
     * 另一个类中的属性名称,支持多级获取,以小数点隔开
     */
    public string targetattr() default "";

    /**
     * 字段类型（0：导出导入；1：仅导出；2：仅导入）
     */
    type type() default type.all;

    /**
     * 合并行
     * 参数1合并第一个参数为合并基准列，其他列用逗号拼接，依据基准列进行当前单元行合并 参数:如1,7, 8
     */
    public string mergeline() default "";

    /**
     * 表头合并配置
     * 格式: "起始行,结束行,起始列,结束列"
     * 例如: "0,0,0,2" 表示第0行第0列到第0行第2列合并
     */
    public string headermerge() default "";

    /**
     * 表头层级（从0开始，用于多级表头）
     */
    public int headerlevel() default 0;

    /**
     * 是否分组表头（用于标识该字段是否为分组表头，不包含实际数据）
     */
    public boolean isgroupheader() default false;

    /**
     * 分组表头行数（默认1行）
     */
    public int groupheaderrows() default 1;

//    columntype align();

    string separator() default "";

    string dicttype() default "";

    int scale() default 0;

    int roundingmode() default 0;

    boolean isstatistics() default false;

    /**
     * 设置只能选择不能输入的列内容.
     * 动态字典方式
     */
    public string combodynamic() default "";

    public enum type
    {
        all(0), export(1), import(2);
        private final int value;

        type(int value)
        {
            this.value = value;
        }

        public int value()
        {
            return this.value;
        }
    }

    public enum columntype
    {
        numeric(0), string(1);
        private final int value;

        columntype(int value)
        {
            this.value = value;
        }

        public int value()
        {
            return this.value;
        }
    }
}

/**
 * 计算最大表头层级（确定需要创建多少行表头）
 */
private void calculatemaxheaderlevel() {
    maxheaderlevel = 0;
    for (object[] os : fields) {
        excel excel = (excel) os[1];
        // 处理headermerge配置中的结束行
        if (stringutils.isnotempty(excel.headermerge())) {
            string[] mergeparams = excel.headermerge().split(",");
            if (mergeparams.length == 4) {
                try {
                    int lastrow = integer.parseint(mergeparams[1]);
                    if (lastrow > maxheaderlevel) {
                        maxheaderlevel = lastrow;
                    }
                } catch (numberformatexception e) {
                    log.warn("headermerge参数格式错误: {}", excel.headermerge());
                }
            }
        }
        // 处理headerlevel配置
        if (excel.headerlevel() > maxheaderlevel) {
            maxheaderlevel = excel.headerlevel();
        }
    }
    // 兜底：至少保留1级表头
    if (maxheaderlevel < 0) {
        maxheaderlevel = 0;
    }
}

/**
 * 创建多级表头 - 完整实现
 */
private void createmultilevelheader() {
    // 1. 计算最大表头层级
    calculatemaxheaderlevel();
    
    // 2. 创建所有表头行并设置行高
    for (int level = 0; level <= maxheaderlevel; level++) {
        row row = sheet.createrow(level);
        headerrows.put(level, row);
        row.setheight((short) (25 * 20)); // 统一设置表头行高（25磅）
    }

    // 3. 填充表头内容（根据模式选择不同实现）
    fillheadercontent();

    // 4. 处理表头合并（核心）
    processheadermerge();

    // 5. 设置列宽
    setcolumnwidths();
}

/**
 * 多级表头专用 - 填充表头内容
 */
private void fillheadercontentmultilevel() {
    int logicalindex = 0;
    int physicalcolumn = 0;

    log.debug("=== 开始多级表头填充 ===");

    for (object[] os : fields) {
        excel excel = (excel) os[1];
        string headermerge = excel.headermerge();

        int actualcolumn = physicalcolumn;
        int columnspan = 1;

        // 解析合并参数，确定列位置和跨度
        if (stringutils.isnotempty(headermerge)) {
            string[] mergeparams = headermerge.split(",");
            if (mergeparams.length == 4) {
                try {
                    int configfirstcol = integer.parseint(mergeparams[2]);
                    int configlastcol = integer.parseint(mergeparams[3]);
                    actualcolumn = configfirstcol;
                    columnspan = configlastcol - configfirstcol + 1;
                    
                    log.debug("分组字段[{}]: 逻辑索引={}, 实际列={}, 跨度={}",
                            excel.name(), logicalindex, actualcolumn, columnspan);
                } catch (numberformatexception e) {
                    log.warn("headermerge参数格式错误: {}", headermerge);
                }
            }
        }

        // 为每个层级创建单元格
        for (int level = 0; level <= maxheaderlevel; level++) {
            row row = headerrows.get(level);
            // 确保单元格存在（避免空指针）
            cell cell = row.getcell(actualcolumn);
            if (cell == null) {
                cell = row.createcell(actualcolumn);
            }
            // 应用表头样式
            cell.setcellstyle(styles.get("header0"));

            boolean shouldshowcontent = false;

            // 分组表头处理：只在合并区域起始位置显示内容
            if (stringutils.isnotempty(headermerge)) {
                string[] mergeparams = headermerge.split(",");
                int firstrow = integer.parseint(mergeparams[0]);
                int firstcol = integer.parseint(mergeparams[2]);
                
                // 仅在合并区域的左上角单元格显示标题
                if (level == firstrow && actualcolumn == firstcol) {
                    cell.setcellvalue(excel.name());
                    shouldshowcontent = true;
                    log.debug("设置标题[{}]在位置: 行={}, 列={}",
                            excel.name(), level, actualcolumn);
                }
            } else {
                // 普通表头：按层级显示
                if (excel.headerlevel() == level) {
                    cell.setcellvalue(excel.name());
                    shouldshowcontent = true;
                }
            }

            // 非显示单元格置空
            if (!shouldshowcontent && cell.getcelltype() == celltype.blank.getcode()) {
                cell.setcellvalue("");
            }
        }

        // 更新列索引
        if (stringutils.isnotempty(headermerge)) {
            string[] mergeparams = headermerge.split(",");
            int lastcol = integer.parseint(mergeparams[3]);
            physicalcolumn = lastcol + 1;
        } else {
            physicalcolumn++;
        }
        logicalindex++;
    }
    log.debug("=== 结束多级表头填充 ===");
}

/**
 * 处理表头合并（多级表头专用）
 */
private void processheadermergemultilevel() {
    int logicalindex = 0;
    int physicalcolumn = 0;

    for (object[] os : fields) {
        excel excel = (excel) os[1];
        string headermerge = excel.headermerge();

        // 跳过无合并配置的字段
        if (stringutils.isempty(headermerge)) {
            physicalcolumn++;
            logicalindex++;
            continue;
        }

        string[] mergeparams = headermerge.split(",");
        if (mergeparams.length == 4) {
            try {
                // 解析合并参数
                int firstrow = integer.parseint(mergeparams[0]);
                int lastrow = integer.parseint(mergeparams[1]);
                int firstcol = integer.parseint(mergeparams[2]);
                int lastcol = integer.parseint(mergeparams[3]);

                // 验证合并区域有效性（关键：避免越界）
                if (firstrow >= 0 && lastrow <= maxheaderlevel &&
                        firstcol >= 0 && lastcol < gettotalcolumns() &&
                        firstrow <= lastrow && firstcol <= lastcol) {

                    // 创建合并区域
                    cellrangeaddress region = new cellrangeaddress(
                        firstrow, lastrow, firstcol, lastcol);
                    
                    // 添加合并区域到工作表
                    sheet.addmergedregion(region);
                    log.debug("添加合并区域: {}-{}, {}-{} → 字段[{}]",
                        firstrow, lastrow, firstcol, lastcol, excel.name());
                } else {
                    log.warn("合并区域无效: {}-{}, {}-{} → 字段[{}]",
                        firstrow, lastrow, firstcol, lastcol, excel.name());
                }
            } catch (numberformatexception e) {
                log.error("表头合并参数格式错误: {} → 字段[{}]", headermerge, excel.name(), e);
            } catch (exception e) {
                log.error("添加合并区域失败: {} → 字段[{}]", headermerge, excel.name(), e);
            }
        }

        // 更新列索引
        if (stringutils.isnotempty(headermerge)) {
            string[] mergeparams = headermerge.split(",");
            int lastcol = integer.parseint(mergeparams[3]);
            physicalcolumn = lastcol + 1;
        } else {
            physicalcolumn++;
        }
        logicalindex++;
    }
}

/**
 * 计算总列数 - 多级表头专用
 */
private int gettotalcolumns() {
    int total = 0;
    for (object[] os : fields) {
        excel excel = (excel) os[1];
        string headermerge = excel.headermerge();

        if (stringutils.isnotempty(headermerge)) {
            string[] mergeparams = headermerge.split(",");
            if (mergeparams.length == 4) {
                try {
                    int firstcol = integer.parseint(mergeparams[2]);
                    int lastcol = integer.parseint(mergeparams[3]);
                    total = math.max(total, lastcol + 1);
                } catch (numberformatexception e) {
                    total++;
                }
            }
        } else {
            total++;
        }
    }
    return total;
}

/**
 * 创建表格样式 - 统一管理表头和数据样式
 */
private map<string, cellstyle> createstyles(workbook wb) {
    map<string, cellstyle> styles = new hashmap<>();

    // 1. 数据单元格样式
    cellstyle datastyle = wb.createcellstyle();
    // 居中对齐
    datastyle.setalignment(horizontalalignment.center);
    datastyle.setverticalalignment(verticalalignment.center);
    // 边框样式
    datastyle.setborderright(borderstyle.thin);
    datastyle.setrightbordercolor(indexedcolors.grey_50_percent.getindex());
    datastyle.setborderleft(borderstyle.thin);
    datastyle.setleftbordercolor(indexedcolors.grey_50_percent.getindex());
    datastyle.setbordertop(borderstyle.thin);
    datastyle.settopbordercolor(indexedcolors.grey_50_percent.getindex());
    datastyle.setborderbottom(borderstyle.thin);
    datastyle.setbottombordercolor(indexedcolors.grey_50_percent.getindex());
    // 字体
    font datafont = wb.createfont();
    datafont.setfontname("宋体");
    datafont.setfontheightinpoints((short) 10);
    datastyle.setfont(datafont);
    styles.put("data", datastyle);

    // 2. 表头样式（所有层级共用）
    cellstyle headerstyle = wb.createcellstyle();
    headerstyle.clonestylefrom(datastyle);
    // 表头居中对齐
    headerstyle.setalignment(horizontalalignment.center);
    headerstyle.setverticalalignment(verticalalignment.center);
    // 浅灰色背景
    headerstyle.setfillforegroundcolor(indexedcolors.grey_25_percent.getindex());
    headerstyle.setfillpattern(fillpatterntype.solid_foreground);
    // 加粗字体
    font headerfont = wb.createfont();
    headerfont.setfontname("宋体");
    headerfont.setfontheightinpoints((short) 11);
    headerfont.setbold(true);
    headerfont.setcolor(indexedcolors.black.getindex());
    headerstyle.setfont(headerfont);
    // 所有层级表头共用一个样式
    for (int i = 0; i <= 4; i++) {
        styles.put("header" + i, headerstyle);
    }

    return styles;
}

import com.tydt.framework.web.domain.ajaxresult;
import org.apache.shiro.authz.annotation.requirespermissions;
import org.springframework.web.bind.annotation.postmapping;
import org.springframework.web.bind.annotation.requestbody;
import org.springframework.web.bind.annotation.responsebody;

/**
 * 导出地下管线-现有道路-年度计划库
 */
@requirespermissions("ssjs:xyndjhk:export")
@log(title = "地下管线-现有道路-年度计划库", businesstype = businesstype.export)
@postmapping("/export")
@responsebody
public ajaxresult export(@requestbody ssjsgxsnjhk snjhk) {
    try {
        if ("1".equals(snjhk.getexporttype())) {
            // 按所在地导出（启用多级表头）
            list<exportsnjhkgxndjhkdtobyszd> list = xygxsnjhkservice.selectexportsnjhkgxndjhkdtobyszdlist(snjhk);
            excelutilmerge<exportsnjhkgxndjhkdtobyszd> util = new excelutilmerge<>(exportsnjhkgxndjhkdtobyszd.class);
            util.setmultilevelheadermode(true); // 关键：启用多级表头模式
            return util.exportexcel(list, "现有道路-储备项目库-按所在地导出");
        }  else {
            // 默认导出（普通表头）
            list<exportgxdto> list = ssjsxygxndjhkservice.selectexportgxdtolist(snjhk);
            excelutilmerge<exportgxdto> util = new excelutilmerge<>(exportgxdto.class);
            return util.exportexcel(list, "现有道路-年度计划库-默认导出");
        }
    } catch (exception e) {
        log.error("excel导出失败", e);
        return ajaxresult.error("导出失败：" + e.getmessage());
    }
}