┌──────────┬──────────┬──────────┬────────────────┬───────────────────────┐
│ 所在地區(qū) │ 道路名稱 │ 管線類別 │ 手續(xù)             │ 財政資金(萬元)    │
│          │          │          ├──────────┬─────┼──────────┬──────────┤
│          │          │          │ 批復     │ 許可 │ 預算內   │          │
│          │          │          │ 編碼     │ 編碼 │ 投資     │ 國債     │
└──────────┴──────────┴──────────┴──────────┴─────┴──────────┴──────────┘

<!-- 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>

<!-- 流式處理大數(shù)據(jù)量Excel（關鍵：避免OOM） -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>4.1.2</version>
</dependency>

<!-- 日期工具依賴（可選，根據(jù)項目實際情況調整） -->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.20</version>
</dependency>

<!-- Lombok（可選，簡化DTO開發(fā)） -->
<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'
// 流式處理大數(shù)據(jù)量
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數(shù)據(jù)注解
 *
 * @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數(shù)字 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;

    /**
     * 【核心】是否分組表頭（用于標識該字段是否為分組表頭，不包含實際數(shù)據(jù)）
     */
    boolean isGroupHeader() default false;

    /**
     * 【核心】合并行
     * 參數(shù)1為合并基準列，其他列用逗號拼接，依據(jù)基準列進行當前單元行合并
     * 例如: "0" 表示以第0列為基準進行行合并
     */
    String mergeLine() default "";

    /**
     * 是否導出數(shù)據(jù),應對需求:有時我們需要導出一份模板,這是標題需要但內容需要用戶手工填寫
     */
    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;

/**
 * 導出管線實體類 - 現(xiàn)有道路儲備項目庫和年度計劃庫導出Excel模板
 */
@Data
public class ExportDTOSzd {

    // ========== 基礎信息字段（縱向合并兩行）==========
    // A列 (0) - 所在地區(qū)：0-1行縱向合并，作為二級表頭
    @Excel(name = "所在地區(qū)", 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;

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

    // ========== 手續(xù)分組（橫向合并）==========
    // Q-R-S列分組 (16-18) - 一級表頭，橫向合并3列
    @Excel(name = "手續(xù)", 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最大行數(shù)，默認65536
     */
    public static final int sheetSize = 65536;

    /**
     * 工作表名稱
     */
    private String sheetName;

    /**
     * 導出類型（EXPORT:導出數(shù)據(jù)；IMPORT：導入模板）
     */
    private Type type;

    /**
     * 工作薄對象
     */
    private Workbook wb;

    /**
     * 工作表對象
     */
    private Sheet sheet;

    /**
     * 樣式列表
     */
    private Map<String, CellStyle> styles;

    /**
     * 導入導出數(shù)據(jù)列表
     */
    private List<T> list;

    /**
     * 注解列表
     */
    private List<Object[]> fields;

    /**
     * 實體對象
     */
    public Class<T> clazz;

    /**
     * 統(tǒng)計列表
     */
    private Map<Integer, Double> statistics = new HashMap<Integer, Double>();

    //是否進行合并行
    private boolean switchMearge = false;
    //合并行數(shù)  起始行，結束行
    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);
                }
            }
            // 有數(shù)據(jù)時才處理 得到類的所有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行開始取數(shù)據(jù),默認第一行是表頭.
                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());
                    // 取得類型,并根據(jù)對象類型設置值.
                    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數(shù)據(jù)源將其里面的數(shù)據(jù)導入到excel表單
     *
     * @param list 導出數(shù)據(jù)集合
     * @param sheetName 工作表的名稱
     * @return 結果
     */
    public AjaxResult exportExcel(List<T> list, String sheetName)
    {
        this.init(list, sheetName, Type.EXPORT);
        return exportExcel();
    }

    /**
     * 對list數(shù)據(jù)源將其里面的數(shù)據(jù)導入到excel表單
     *
     * @param sheetName 工作表的名稱
     * @return 結果
     */
    public AjaxResult importTemplateExcel(String sheetName)
    {
        this.init(null, sheetName, Type.IMPORT);
        return exportExcel();
    }

    /**
     * 對list數(shù)據(jù)源將其里面的數(shù)據(jù)導入到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);

                // 創(chuàng)建多級表頭
                createMultiLevelHeader();

                if (Type.EXPORT.equals(type))
                {
                    // 數(shù)據(jù)起始行 = 表頭行數(shù)
                    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失敗，請聯(lián)系網(wǎng)站管理員！");
        }
        finally
        {
            if (wb != null)
            {
                try
                {
                    wb.close();
                }
                catch (IOException e1)
                {
                    e1.printStackTrace();
                }
            }
            if (out != null)
            {
                try
                {
                    out.close();
                }
                catch (IOException e1)
                {
                    e1.printStackTrace();
                }
            }
        }
    }

    /**
     * 創(chuàng)建多級表頭 - 簡化版本
     */
    private void createMultiLevelHeader() {
        // 計算最大表頭層級
        calculateMaxHeaderLevel();

        // 創(chuàng)建所有表頭行
        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參數(shù)格式錯誤: {}", excel.headerMerge());
                    }
                }
            }
            // 同時檢查headerLevel
            if (excel.headerLevel() > maxHeaderLevel) {
                maxHeaderLevel = excel.headerLevel();
            }
        }

        // 確保至少有一級表頭
        if (maxHeaderLevel < 0) {
            maxHeaderLevel = 0;
        }
    }

    /**
     * 填充表頭內容 - 根據(jù)模式選擇不同的實現(xiàn)
     */
    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();

            // 解析合并參數(shù)
            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參數(shù)格式錯誤: {}", headerMerge);
                    }
                }
            }

            // 為每個層級的表頭行創(chuàng)建單元格
            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) {
                            // 只在合并區(qū)域的第一個單元格顯示內容
                            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;

            // 解析合并參數(shù)，確定實際列位置和跨度
            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參數(shù)格式錯誤: {}", headerMerge);
                    }
                }
            } else {
                log.debug("普通字段[{}]: 邏輯索引={}, 實際列={}",
                        excel.name(), logicalIndex, actualColumn);
            }

            // 為每個層級的表頭行創(chuàng)建單元格
            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) {
                            // 只在合并區(qū)域的第一個單元格顯示分組標題
                            cell.setCellValue(excel.name());
                            shouldShowContent = true;
                        }
                    } else if (level == excel.headerLevel()) {
                        // 分組字段不在自己的合并區(qū)域內，但在正確的層級，顯示空值
                        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("=== 結束多級表頭填充 ===");
    }*/

    /**
     * 處理表頭合并 - 根據(jù)模式選擇不同的實現(xiàn)
     */
    private void processHeaderMerge() {
        if (multiLevelHeaderMode) {
            processHeaderMergeMultiLevel();
        } else {
            processHeaderMergeOriginal();
        }
    }

    /**
     * 原有的合并區(qū)域處理邏輯
     */
    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]);

                        // 驗證合并區(qū)域的有效性
                        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("添加合并區(qū)域: {}-{}, {}-{}",
                                        region.getFirstRow(), region.getLastRow(),
                                        region.getFirstColumn(), region.getLastColumn());
                            } catch (Exception e) {
                                log.error("合并區(qū)域失敗: {}", region.formatAsString(), e);
                            }
                        }
                    } catch (NumberFormatException e) {
                        log.error("表頭合并參數(shù)格式錯誤: {}", headerMerge);
                    }
                }
            }
            column++;
        }
    }

    /**
     * 多級表頭專用的合并區(qū)域處理
     */
    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]);

                        // 驗證合并區(qū)域的有效性
                        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("添加合并區(qū)域: {}-{}, {}-{}",
                                        region.getFirstRow(), region.getLastRow(),
                                        region.getFirstColumn(), region.getLastColumn());
                            } catch (Exception e) {
                                log.error("合并區(qū)域失敗: {}", region.formatAsString(), e);
                            }
                        }
                    } catch (NumberFormatException e) {
                        log.error("表頭合并參數(shù)格式錯誤: {}", headerMerge);
                    }
                }
            }

            // 更新列索引（與fillHeaderContentMultiLevel保持一致）
            if (StringUtils.isNotEmpty(headerMerge)) {
                String[] mergeParams = headerMerge.split(",");
                int lastCol = Integer.parseInt(mergeParams[3]);
                physicalColumn = lastCol + 1;
            } else {
                physicalColumn++;
            }

            logicalIndex++;
        }
    }

    /**
     * 計算總列數(shù) - 多級表頭專用
     */
    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數(shù)據(jù)
     *
     * @param index 序號
     * @param startRow 數(shù)據(jù)起始行
     */
    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);
            }
            /**
             *取下一個對象   與當前對象對比，如果相同，記住當前列，再與下一個對比，一直對比到不相同，執(zhí)行合并代碼
             * 注解加入 合并行列標識
             */
            int column = 0;
            for (Object[] os : fields) {

                Field field = (Field) os[0];
                Excel excel = (Excel) os[1];
                // 關鍵修復：跳過分組字段，不創(chuàng)建單元格
                if (excel.isGroupHeader()) {
                   // log.debug("跳過分組字段: {} (isGroupHeader=true)", excel.name());
                    continue; // 直接跳過，不增加column索引
                }
                // 設置實體類私有屬性可訪問
                field.setAccessible(true);
                this.addCell(excel, row, vo, field, column++, vo_previous, thisLine);
            }
        }
    }

    /**
     * 創(chuàng)建表格樣式 - 簡化表頭樣式
     */
    private Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<>();

        // 數(shù)據(jù)樣式
        CellStyle dataStyle = createDataStyle(wb);
        styles.put("data", dataStyle);

        // 創(chuàng)建表頭樣式（所有表頭使用相同樣式）
        createHeaderStyles(wb, styles);

        return styles;
    }

    /**
     * 創(chuàng)建表頭樣式 - 所有表頭使用統(tǒng)一樣式
     */
    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);
        }
    }

    /**
     * 創(chuàng)建數(shù)據(jù)樣式
     */
    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));
            // 根據(jù)Excel中設置情況決定是否導出,有些情況需要保持為空,希望用戶填寫這一列.
            if (attr.isExport()) {
                // 創(chuàng)建cell
                cell = row.createCell(column);
                cell.setCellStyle(styles.get("data"));

                // 如果是分組表頭字段，不顯示實際數(shù)據(jù)
                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;
    }

    /**
     * 創(chuàng)建單元格
     */
    public Cell createCell(Excel attr, Row row, int column)
    {
        // 創(chuàng)建列
        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 + ""));
        }
    }

    /**
     * 創(chuàng)建表格樣式
     */
    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);
        }
    }

    /**
     * 合計統(tǒng)計信息
     */
    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);
    }

    /**
     * 設置某些列的值只能輸入預制的數(shù)據(jù),顯示下拉框.
     *
     * @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);
        // 設置數(shù)據(jù)有效性加載在哪個單元格上,四個參數(shù)分別是：起始行、終止行、起始列、終止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 數(shù)據(jù)有效性對象
        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 參數(shù)值
     * @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 參數(shù)值
     * @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 參數(shù)值
     * @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) {
            // 使用穩(wěn)定的排序算法
            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 });
        }
    }

    /**
     * 創(chuàng)建一個工作簿
     */
    public void createWorkbook()
    {
        this.wb = new SXSSFWorkbook(500);
    }

    /**
     * 創(chuàng)建工作表
     *
     * @param sheetNo sheet數(shù)量
     * @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;

            // 解析合并參數(shù)，確定實際列位置和跨度
            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參數(shù)格式錯誤: {}", headerMerge);
                    }
                }
            } else {
                log.debug("普通字段[{}]: 邏輯索引={}, 實際列={}",
                        excel.name(), logicalIndex, actualColumn);
            }

            // 為每個層級的表頭行創(chuàng)建單元格
            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]);

                    // 只在合并區(qū)域的起始位置顯示內容
                    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數(shù)據(jù)注解
 *
 * @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數(shù)字 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 {};

    /**
     * 是否導出數(shù)據(jù),應對需求:有時我們需要導出一份模板,這是標題需要但內容需要用戶手工填寫.
     */
    public boolean isExport() default true;

    /**
     * 另一個類中的屬性名稱,支持多級獲取,以小數(shù)點隔開
     */
    public String targetAttr() default "";

    /**
     * 字段類型（0：導出導入；1：僅導出；2：僅導入）
     */
    Type type() default Type.ALL;

    /**
     * 合并行
     * 參數(shù)1合并第一個參數(shù)為合并基準列，其他列用逗號拼接，依據(jù)基準列進行當前單元行合并 參數(shù):如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;

    /**
     * 是否分組表頭（用于標識該字段是否為分組表頭，不包含實際數(shù)據(jù)）
     */
    public boolean isGroupHeader() default false;

    /**
     * 分組表頭行數(shù)（默認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;

    /**
     * 設置只能選擇不能輸入的列內容.
     * 動態(tài)字典方式
     */
    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;
        }
    }
}

/**
 * 計算最大表頭層級（確定需要創(chuàng)建多少行表頭）
 */
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參數(shù)格式錯誤: {}", excel.headerMerge());
                }
            }
        }
        // 處理headerLevel配置
        if (excel.headerLevel() > maxHeaderLevel) {
            maxHeaderLevel = excel.headerLevel();
        }
    }
    // 兜底：至少保留1級表頭
    if (maxHeaderLevel < 0) {
        maxHeaderLevel = 0;
    }
}

/**
 * 創(chuàng)建多級表頭 - 完整實現(xiàn)
 */
private void createMultiLevelHeader() {
    // 1. 計算最大表頭層級
    calculateMaxHeaderLevel();
    
    // 2. 創(chuàng)建所有表頭行并設置行高
    for (int level = 0; level <= maxHeaderLevel; level++) {
        Row row = sheet.createRow(level);
        headerRows.put(level, row);
        row.setHeight((short) (25 * 20)); // 統(tǒng)一設置表頭行高（25磅）
    }

    // 3. 填充表頭內容（根據(jù)模式選擇不同實現(xiàn)）
    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;

        // 解析合并參數(shù)，確定列位置和跨度
        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參數(shù)格式錯誤: {}", headerMerge);
                }
            }
        }

        // 為每個層級創(chuàng)建單元格
        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;

            // 分組表頭處理：只在合并區(qū)域起始位置顯示內容
            if (StringUtils.isNotEmpty(headerMerge)) {
                String[] mergeParams = headerMerge.split(",");
                int firstRow = Integer.parseInt(mergeParams[0]);
                int firstCol = Integer.parseInt(mergeParams[2]);
                
                // 僅在合并區(qū)域的左上角單元格顯示標題
                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 {
                // 解析合并參數(shù)
                int firstRow = Integer.parseInt(mergeParams[0]);
                int lastRow = Integer.parseInt(mergeParams[1]);
                int firstCol = Integer.parseInt(mergeParams[2]);
                int lastCol = Integer.parseInt(mergeParams[3]);

                // 驗證合并區(qū)域有效性（關鍵：避免越界）
                if (firstRow >= 0 && lastRow <= maxHeaderLevel &&
                        firstCol >= 0 && lastCol < getTotalColumns() &&
                        firstRow <= lastRow && firstCol <= lastCol) {

                    // 創(chuàng)建合并區(qū)域
                    CellRangeAddress region = new CellRangeAddress(
                        firstRow, lastRow, firstCol, lastCol);
                    
                    // 添加合并區(qū)域到工作表
                    sheet.addMergedRegion(region);
                    log.debug("添加合并區(qū)域: {}-{}, {}-{} → 字段[{}]",
                        firstRow, lastRow, firstCol, lastCol, excel.name());
                } else {
                    log.warn("合并區(qū)域無效: {}-{}, {}-{} → 字段[{}]",
                        firstRow, lastRow, firstCol, lastCol, excel.name());
                }
            } catch (NumberFormatException e) {
                log.error("表頭合并參數(shù)格式錯誤: {} → 字段[{}]", headerMerge, excel.name(), e);
            } catch (Exception e) {
                log.error("添加合并區(qū)域失敗: {} → 字段[{}]", headerMerge, excel.name(), e);
            }
        }

        // 更新列索引
        if (StringUtils.isNotEmpty(headerMerge)) {
            String[] mergeParams = headerMerge.split(",");
            int lastCol = Integer.parseInt(mergeParams[3]);
            physicalColumn = lastCol + 1;
        } else {
            physicalColumn++;
        }
        logicalIndex++;
    }
}

/**
 * 計算總列數(shù) - 多級表頭專用
 */
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;
}

/**
 * 創(chuàng)建表格樣式 - 統(tǒng)一管理表頭和數(shù)據(jù)樣式
 */
private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<>();

    // 1. 數(shù)據(jù)單元格樣式
    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;

/**
 * 導出地下管線-現(xiàn)有道路-年度計劃庫
 */
@RequiresPermissions("ssjs:xyndjhk:export")
@Log(title = "地下管線-現(xiàn)有道路-年度計劃庫", 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, "現(xiàn)有道路-儲備項目庫-按所在地導出");
        }  else {
            // 默認導出（普通表頭）
            List<ExportGxDTO> list = ssjsXyGxndjhkService.selectExportGxDTOList(snjhk);
            ExcelUtilMerge<ExportGxDTO> util = new ExcelUtilMerge<>(ExportGxDTO.class);
            return util.exportExcel(list, "現(xiàn)有道路-年度計劃庫-默認導出");
        }
    } catch (Exception e) {
        log.error("Excel導出失敗", e);
        return AjaxResult.error("導出失?。? + e.getMessage());
    }
}