package com.hotent.enterpriseDeclare.controller.material;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.hotent.base.annotation.ApiGroup;
import com.hotent.base.constants.ApiGroupConsts;
import com.hotent.base.model.CommonResult;
import com.hotent.base.util.StringUtil;
import com.hotent.baseInfo.manager.CostCatalogSurveyManager;
import com.hotent.baseInfo.manager.CostDictTypeManager;
import com.hotent.baseInfo.model.CostCatalogSurvey;
import com.hotent.baseInfo.model.CostDictType;
import com.hotent.enterpriseDeclare.manager.CostAuditPeriodRecordManager;
import com.hotent.enterpriseDeclare.manager.CostSurveyTemplateUploadDataManager;
import com.hotent.enterpriseDeclare.model.CostAuditPeriodRecord;
import com.hotent.enterpriseDeclare.model.CostSurveyTemplateUploadData;
import com.hotent.project.manager.CostProjectApprovalManager;
import com.hotent.project.manager.CostProjectTaskManager;
import com.hotent.project.manager.CostProjectTaskMaterialManager;
import com.hotent.project.model.CostProjectApproval;
import com.hotent.project.model.CostProjectTask;
import com.hotent.project.model.CostProjectTaskMaterial;
import com.hotent.surveyinfo.dao.*;
import com.hotent.surveyinfo.manager.*;
import com.hotent.surveyinfo.model.*;
import com.hotent.surveyinfo.model.dto.CostItemData;
import com.hotent.sys.persistence.manager.DataDictManager;
import com.hotent.sys.persistence.manager.SysTypeManager;
import com.hotent.sys.persistence.model.DataDict;
import com.hotent.sys.persistence.model.SysType;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.nfunk.jep.JEP;
import org.nfunk.jep.function.Str;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 通用调查表接口（整合成本调查表和财务数据表）
 *
 * @author 超级管理员
 * @company 山西清众科技股份有限公司
 * @since 2025-01-27
 */
@RestController
@RequestMapping("/api/surveyTemplate/v1/")
@Api(tags = "通用调查表接口")
@ApiGroup(group = {ApiGroupConsts.GROUP_COST})
public class CostProjectTaskSurveyGenericController {

    // 成本调查表相关
    @Autowired
    private CostSurveyTemplateManager costSurveyTemplateManager;

    @Autowired
    private CostSurveyTemplateVersionManager costSurveyTemplateVersionManager;

    @Autowired
    private CostSurveyTemplateHeadersDao costSurveyTemplateHeadersDao;

    @Autowired
    private CostSurveyTemplateItemsDao costSurveyTemplateItemsDao;
    @Resource
    DataDictManager dataDictManager;
    @Resource
    SysTypeManager sysTypeManager;



    // 财务数据表相关
    @Autowired
    private CostSurveyFdTemplateManager costSurveyFdTemplateManager;

    @Autowired
    private CostSurveyFdTemplateVersionManager costSurveyFdTemplateVersionManager;

    @Autowired
    private CostSurveyFdTemplateHeadersDao costSurveyFdTemplateHeadersDao;

    @Autowired
    private CostSurveyFdTemplateItemsDao costSurveyFdTemplateItemsDao;

    // 核定表
    @Autowired
    private CostVerifyTemplateManager costVerifyTemplateManager;

    @Autowired
    private CostVerifyTemplateHeadersDao costVerifyTemplateHeadersDao;

    @Autowired
    private CostVerifyTemplateItemsDao costVerifyTemplateItemsDao;


    // 通用组件
    @Autowired
    private CostSurveyTemplateUploadManager costSurveyTemplateUploadManager;
    @Autowired
    private CostSurveyTemplateUploadDataManager costSurveyTemplateUploadDataManager;

    @Autowired
    private CostProjectTaskMaterialManager costProjectTaskMaterialManager;

    @Autowired
    private CostAuditPeriodRecordManager costAuditPeriodRecordManager;

    @Autowired
    private CostProjectApprovalManager costProjectApprovalManager;

    @Autowired
    private CostProjectTaskManager costProjectTaskManager;

    @Autowired
    private CostSurveyTemplateHeadersManager costSurveyTemplateHeadersManager;

    @Autowired
    private CostSurveyFdTemplateHeadersManager costSurveyFdTemplateHeadersManager;


    /**
     * 企业报送-调查表-列表
     *
     * @param taskId    任务ID
     * @param catalogId 目录ID（成本调查表使用）
     * @param type      类型：1-成本调查表 2-财务数据表
     * @return 上传记录列表
     * @throws Exception
     */
    @GetMapping(value = "/listByTaskId")
    @ApiOperation(value = "根据任务ID获取调查表列表", httpMethod = "GET")
    public CommonResult<?> listByTaskId(
            @ApiParam(name = "taskId", value = "任务ID", required = true)
            @RequestParam(required = true) String taskId,
            @ApiParam(name = "catalogId", value = "目录ID（成本调查表使用）")
            @RequestParam(required = false) String catalogId,
            @ApiParam(name = "type", value = "类型：1-成本调查表 2-财务数据表", required = true)
            @RequestParam(required = true) String type) throws Exception {

        switch (type) {
            case "1": {
                // 成本调查表逻辑
                List<CostSurveyTemplateUpload> uploadList = costSurveyTemplateUploadManager.listByTaskId(taskId);
                //创建记录
                if (uploadList.isEmpty()) {
                    CostProjectTask task = costProjectTaskManager.getById(taskId);
                    CostProjectApproval approval = costProjectApprovalManager.getById(task.getProjectId());
                    List<CostSurveyTemplate> costSurveyTemplates = costSurveyTemplateManager.taskListByCatalogId(approval.getCatalogId());
                    for (CostSurveyTemplate template : costSurveyTemplates) {
                        CostSurveyTemplateUpload upload = new CostSurveyTemplateUpload();
                        upload.setSurveyTemplateId(template.getSurveyTemplateId());
                        upload.setSurveyTemplateName(template.getSurveyTemplateName());
                        upload.setTemplateType(template.getTemplateType());
                        upload.setId(null);
                        upload.setTaskId(taskId);
                        upload.setIsUpload("0");
                        upload.setAuditedStatus("0");
                        upload.setCreateTime(java.time.LocalDateTime.now());
                        upload.setUploadTime(java.time.LocalDateTime.now());
                        costSurveyTemplateUploadManager.save(upload);
                    }
                }
                List<CostSurveyTemplateUpload> costSurveyTemplateUploads = costSurveyTemplateUploadManager.listByTaskId(taskId);
                for (CostSurveyTemplateUpload costSurveyTemplateUpload : costSurveyTemplateUploads) {
                    CostProjectTask task = costProjectTaskManager.getById(taskId);
                    costSurveyTemplateUpload.setAuditedUnitId(task.getAuditedUnitId());
                }
                return CommonResult.ok().value(costSurveyTemplateUploads);
            }
            case "2": {
                // 财务数据表逻辑
                QueryWrapper<CostProjectTaskMaterial> wrapper = new QueryWrapper<>();
                wrapper.eq("task_id", taskId);
                List<CostProjectTaskMaterial> materialList = costProjectTaskMaterialManager.list(wrapper);
                for (CostProjectTaskMaterial costProjectTaskMaterial : materialList) {
                    CostProjectTask task = costProjectTaskManager.getById(taskId);
                    costProjectTaskMaterial.setAuditedUnitId(task.getAuditedUnitId()); ;
                }
                return CommonResult.ok().value(materialList);
            }
            default:
                return CommonResult.error().message("不支持的类型");
        }
    }

    /**
     * 企业报送-调查表-获取所需表格字段
     *
     * @param surveyTemplateId 调查表模板ID
     * @param type             类型：1-成本调查表 2-财务数据表
     * @return 指标项数据列表（带key-value拼接）
     */
    @GetMapping(value = "/listItemsByCurrentTemplateId")
    @ApiOperation(value = "根据调查表现行模板ID获取所有指标数据", httpMethod = "GET")
    public CommonResult<CostItemData> listItemsByCurrentTemplateId(
            @ApiParam(name = "surveyTemplateId", value = "关联的调查表ID", required = true)
            @RequestParam(required = true) String surveyTemplateId,
            @ApiParam(name = "type", value = "类型：1-成本调查表 2-财务数据表", required = true)
            @RequestParam(required = true) String type) throws Exception {

        switch (type) {
            case "1": {
                // 成本调查表逻辑
                CostSurveyTemplateVersion versionTemplate = costSurveyTemplateVersionManager.selectCurrentVersion(surveyTemplateId);
                if (versionTemplate == null) {
                    return CommonResult.<CostItemData>ok().value(new CostItemData());
                }

                List<CostSurveyTemplateItems> items = costSurveyTemplateItemsDao.selectBySurveyTemplateIdAndVersion(surveyTemplateId, versionTemplate.getId());

                List<CostSurveyTemplateHeaders> headList = costSurveyTemplateHeadersManager.listVisibleBySurveyTemplateIdAndVersion(surveyTemplateId, versionTemplate.getId());
                CostItemData costItemData = buildCostItemData(items, headList);
                return CommonResult.<CostItemData>ok().value(costItemData);
            }
            case "2": {
                // 财务数据表逻辑
                CostSurveyFdTemplateVersion versionTemplate = costSurveyFdTemplateVersionManager.selectCurrentVersion(surveyTemplateId);
                if (versionTemplate == null) {
                    return CommonResult.<CostItemData>ok().value(new CostItemData());
                }

                List<CostSurveyFdTemplateItems> items = costSurveyFdTemplateItemsDao.selectBySurveyTemplateIdAndVersion(surveyTemplateId, versionTemplate.getId());

                List<CostSurveyFdTemplateHeaders> headList = costSurveyFdTemplateHeadersManager.listVisibleBySurveyTemplateIdAndVersion(surveyTemplateId, versionTemplate.getId());
                CostItemData costItemData = buildCostItemDataFd(items, headList);
                return CommonResult.<CostItemData>ok().value(costItemData);
            }
            default:
                return CommonResult.<CostItemData>error().message("不支持的类型");
        }
    }

    /**
     * 企业报送-调查表-在线填报
     *
     * @param dataList 数据列表
     * @return 保存结果
     */
    @PostMapping(value = "/saveUploadData")
    @ApiOperation(value = "保存调查表模板上传数据", httpMethod = "POST")
    public CommonResult<String> saveUploadData(
            @ApiParam(name = "dataList", value = "数据列表", required = true)
            @RequestBody List<CostSurveyTemplateUploadData> dataList) throws Exception {
        System.err.println(JSON.toJSON(dataList));
        if (dataList == null || dataList.isEmpty()) {
            return CommonResult.<String>error().message("数据列表不能为空");
        }

        // 从第一条数据中提取信息
        CostSurveyTemplateUploadData firstData = dataList.get(0);
        String refId = firstData.getRefId();
        if (refId == null) {
            refId = firstData.getUploadId();
        }
        String surveyTemplateId = firstData.getSurveyTemplateId();
        String type = firstData.getType();

        // 判断模板类型
        String templateType = null;
        if (StringUtil.isNotEmpty(surveyTemplateId)) {
            if ("1".equals(type)) {
                CostSurveyTemplate template = costSurveyTemplateManager.getDetail(surveyTemplateId);
                if (template != null) {
                    templateType = template.getTemplateType();
                }
            } else if ("2".equals(type)) {
                CostSurveyFdTemplate template = costSurveyFdTemplateManager.getDetail(surveyTemplateId);
                if (template != null) {
                    templateType = template.getTemplateType();
                }
            }
        } else if (StringUtil.isNotEmpty(refId)) {
            if ("1".equals(type)) {
                CostSurveyTemplateUpload upload = costSurveyTemplateUploadManager.get(refId);
                if (upload != null && StringUtil.isNotEmpty(upload.getSurveyTemplateId())) {
                    CostSurveyTemplate template = costSurveyTemplateManager.getDetail(upload.getSurveyTemplateId());
                    if (template != null) {
                        templateType = template.getTemplateType();
                    }
                }
            } else if ("2".equals(type)) {
                CostProjectTaskMaterial material = costProjectTaskMaterialManager.getById(refId);
                if (material != null && StringUtil.isNotEmpty(material.getTemplateId())) {
                    CostSurveyFdTemplate template = costSurveyFdTemplateManager.getDetail(material.getTemplateId());
                    if (template != null) {
                        templateType = template.getTemplateType();
                    }
                }
            }
        }

        // 如果是动态表(3)，需要中间表
        if ("3".equals(templateType)) {
            String periodRecordId = firstData.getPeriodRecordId();
            if (StringUtil.isEmpty(periodRecordId)) {
                return CommonResult.<String>error().message("动态表需要先创建监审期间记录，periodRecordId不能为空");
            }
        }

        for (CostSurveyTemplateUploadData data : dataList) {
            data.setType(type);
        }

        // 计算并设置 orderNum（仅针对核定表 type=3）
        if ("3".equals(type) && StringUtil.isNotEmpty(surveyTemplateId)) {
            // 收集所有 rowId 和 parentId
            Map<String, String> rowIdToParentIdMap = new HashMap<>();
            for (CostSurveyTemplateUploadData data : dataList) {
                if (StringUtil.isNotEmpty(data.getRowid())) {
                    rowIdToParentIdMap.put(data.getRowid(), data.getParentId());
                }
            }

            // 计算 orderNum
            Map<String, Integer> rowIdToOrderNum = calculateOrderNumForVerify(surveyTemplateId, rowIdToParentIdMap);

            // 为每条数据设置 orderNum
            for (CostSurveyTemplateUploadData data : dataList) {
                Integer orderNum = rowIdToOrderNum.get(data.getRowid());
                if (orderNum != null) {
                    data.setOrderNum(orderNum);
                }
            }
        }

        costSurveyTemplateUploadDataManager.saveData(dataList);

        // 更新成本调查表
        if ("1".equals(type) && refId != null && !refId.isEmpty()) {
            CostSurveyTemplateUpload upload = costSurveyTemplateUploadManager.getById(refId);
            if (upload != null) {
                upload.setIsUpload("1");
                if ("2".equals(upload.getAuditedStatus())) {
                    upload.setAuditedStatus("0");
                }
                costSurveyTemplateUploadManager.updateById(upload);
            }
        }
        // 如果是财务数据表，更新材料上传状态
        if ("2".equals(type) && StringUtil.isNotEmpty(refId)) {
            CostProjectTaskMaterial material = costProjectTaskMaterialManager.getById(refId);
            if (material != null) {
                material.setIsUpload("1");
                material.setUploadTime(LocalDateTime.now());
                costProjectTaskMaterialManager.updateById(material);
            }
        }
        return CommonResult.<String>ok().message("保存数据成功");
    }

    /**
     * 企业报送-调查表-在线填报记录查看
     *
     * @return 数据列表
     */
    @PostMapping(value = "/getUploadData")
    @ApiOperation(value = "查询调查表模板上传数据", httpMethod = "POST")
    public CommonResult<List<CostSurveyTemplateUploadData>> getUploadData(
            @ApiParam(name = "queryData", value = "查询条件", required = true)
            @RequestBody CostSurveyTemplateUploadData queryData) throws Exception {

        if (queryData == null) {
            return CommonResult.<List<CostSurveyTemplateUploadData>>error().message("uploadId不能为空");
        }


        // 根据refId查询数据
        QueryWrapper<CostSurveyTemplateUploadData> wrapper = new QueryWrapper<>();
        wrapper.eq("type", queryData.getType())
                .eq("is_deleted", "0");
        if (queryData.getType().equals("3")) {
            wrapper.eq("task_id", queryData.getTaskId());
        } else {
            wrapper.eq("ref_id", queryData.getUploadId());
        }
        // 如果指定了监审期间记录ID，添加过滤条件
        if (StringUtil.isNotEmpty(queryData.getPeriodRecordId())) {
            wrapper.eq("period_record_id", queryData.getPeriodRecordId());
        }

        List<CostSurveyTemplateUploadData> dataList = costSurveyTemplateUploadDataManager.list(wrapper);
        for (CostSurveyTemplateUploadData costSurveyTemplateUploadData : dataList) {
            costSurveyTemplateUploadData.setUploadId(costSurveyTemplateUploadData.getRefId());
        }

        return CommonResult.<List<CostSurveyTemplateUploadData>>ok().value(dataList);
    }

    /**
     * 企业报送-调查表-动态表-新增监审期间记录
     *
     * @param record 监审期间记录
     * @return 保存结果
     */
    @PostMapping(value = "/addPeriodRecord")
    @ApiOperation(value = "新增监审期间记录（动态表使用）", httpMethod = "POST")
    public CommonResult<CostAuditPeriodRecord> addPeriodRecord(
            @ApiParam(name = "record", value = "监审期间记录", required = true)
            @RequestBody CostAuditPeriodRecord record) throws Exception {

        if (record == null) {
            return CommonResult.<CostAuditPeriodRecord>error().message("记录不能为空");
        }

        if (StringUtil.isEmpty(record.getTaskId())) {
            return CommonResult.<CostAuditPeriodRecord>error().message("taskId不能为空");
        }

        if (StringUtil.isEmpty(record.getAuditPeriod())) {
            return CommonResult.<CostAuditPeriodRecord>error().message("auditPeriod不能为空");
        }

        // 校验项目监审期间
        CostProjectTask task = costProjectTaskManager.getById(record.getTaskId());
        CostProjectApproval approval = costProjectApprovalManager.getById(task.getProjectId());
        if (approval == null) {
            return CommonResult.<CostAuditPeriodRecord>error().message("项目不存在");
        }

        if (!Arrays.asList(approval.getAuditPeriod().split(",")).contains(record.getAuditPeriod())) {
            return CommonResult.<CostAuditPeriodRecord>error().message("项目所属监审期间不存在");
        }

        // 检查是否已存在相同的期间记录
        CostAuditPeriodRecord existRecord = costAuditPeriodRecordManager.getOne(
                new QueryWrapper<CostAuditPeriodRecord>()
                        .eq("type", record.getType())
                        .eq("task_id", record.getTaskId())
                        .eq("audit_period", record.getAuditPeriod())
        );
        if (existRecord != null) {
            return CommonResult.<CostAuditPeriodRecord>error().message("该监审期间记录已存在");
        }

        // 设置填报时间为当前时间
        if (record.getReportingTime() == null) {
            record.setReportingTime(java.time.LocalDateTime.now());
        }
        record.setIsDeleted("0");
        record.setType(record.getType());
        costAuditPeriodRecordManager.save(record);

        return CommonResult.<CostAuditPeriodRecord>ok().value(record).message("新增监审期间记录成功");
    }

    /**
     * 企业报送-调查表-动态表-查询监审期间记录列表
     *
     * @param queryData 查询条件（包含uploadId和auditedUnitId）
     * @return 记录列表
     */
    @PostMapping(value = "/listPeriodRecord")
    @ApiOperation(value = "查询监审期间记录列表（动态表使用）", httpMethod = "POST")
    public CommonResult<List<CostAuditPeriodRecord>> listPeriodRecord(
            @ApiParam(name = "queryData", value = "查询条件", required = true)
            @RequestBody CostAuditPeriodRecord queryData) throws Exception {

        if (queryData == null || StringUtil.isEmpty(queryData.getUploadId()) || StringUtil.isEmpty(queryData.getAuditedUnitId())) {
            return CommonResult.<List<CostAuditPeriodRecord>>ok().value(new ArrayList<>());
        }

        List<CostAuditPeriodRecord> recordList = costAuditPeriodRecordManager.listByUploadIdAndAuditedUnitId(queryData.getUploadId(), queryData.getAuditedUnitId(), queryData.getType());
        return CommonResult.<List<CostAuditPeriodRecord>>ok().value(recordList);
    }

    /**
     * 企业报送-调查表-动态表-删除监审期间记录
     *
     * @param id   记录ID
     * @param type 类型：1-成本调查表 2-财务数据表
     * @return 删除结果
     */
    @PostMapping(value = "/deletePeriodRecord")
    @ApiOperation(value = "删除监审期间记录（动态表使用）", httpMethod = "POST")
    public CommonResult<String> deletePeriodRecord(
            @ApiParam(name = "id", value = "记录ID", required = true)
            @RequestParam String id,
            @ApiParam(name = "type", value = "类型：1-成本调查表 2-财务数据表", required = true)
            @RequestParam(required = true) String type) throws Exception {

        if (StringUtil.isEmpty(id)) {
            return CommonResult.<String>error().message("id不能为空");
        }

        CostAuditPeriodRecord record = costAuditPeriodRecordManager.getById(id);
        if (record != null) {
            costAuditPeriodRecordManager.removeById(record);

            // 同时删除该期间下的所有上传数据
            QueryWrapper<CostSurveyTemplateUploadData> wrapper = new QueryWrapper<>();
            wrapper.eq("period_record_id", id)
                    .eq("type", type);

            costSurveyTemplateUploadDataManager.remove(wrapper);
        }

        return CommonResult.<String>ok().message("删除监审期间记录成功");
    }

    /**
     * 企业报送-调查表-审核
     *
     * @return 审核结果
     */
    @PostMapping(value = "/review")
    @ApiOperation(value = "调查表审核", httpMethod = "POST")
    public CommonResult<String> review(@RequestBody CostSurveyTemplateUpload costProjectTaskMaterial) {
        String id = costProjectTaskMaterial.getId();
        String type = costProjectTaskMaterial.getType();
        String auditedStatus = costProjectTaskMaterial.getAuditedStatus();
        switch (type) {
            case "1": {
                // 成本调查表审核逻辑
                CostSurveyTemplateUpload upload = costSurveyTemplateUploadManager.getById(id);
                if (upload != null) {
                    upload.setAuditedStatus(auditedStatus);
                    costSurveyTemplateUploadManager.updateById(upload);
                }
                break;
            }
            case "2": {
                // 财务数据表审核逻辑
                CostProjectTaskMaterial material = costProjectTaskMaterialManager.getById(id);
                if (material != null) {
                    material.setAuditedStatus(auditedStatus);
                    costProjectTaskMaterialManager.updateById(material);
                }
                break;
            }
            default:
                return CommonResult.<String>error().message("不支持的类型");
        }
        return CommonResult.<String>ok().message("审核成功");
    }

    /**
     * 导出调查表Excel
     *
     * @param surveyTemplateId 模板ID
     * @param versionId        版本ID（可选）
     * @param type             类型：1-成本调查表 2-财务数据表
     * @param taskId           任务ID（用于获取立项年限）
     * @param response         HTTP响应对象
     */
    @GetMapping(value = "/exportExcel")
    @ApiOperation(value = "导出调查表Excel", httpMethod = "GET")
    public void exportExcel(
            @ApiParam(name = "surveyTemplateId", value = "模板ID", required = true)
            @RequestParam(required = true) String surveyTemplateId,
            @ApiParam(name = "versionId", value = "版本ID")
            @RequestParam(required = false) String versionId,
            @ApiParam(name = "type", value = "类型：1-成本调查表 2-财务数据表", required = true)
            @RequestParam(required = true) String type,
            @ApiParam(name = "taskId", value = "任务ID")
            @RequestParam(required = false) String taskId,
            HttpServletResponse response) throws Exception {

        String templateType = null;
        String templateName = null;

        // 获取立项年限（仅type=1和2需要）
        String[] auditPeriods = null;
        if (("1".equals(type) || "2".equals(type)) && StringUtil.isNotEmpty(taskId)) {
            CostProjectTask task = costProjectTaskManager.getById(taskId);
            if (task != null) {
                CostProjectApproval approval = costProjectApprovalManager.getById(task.getProjectId());
                if (approval != null && StringUtil.isNotEmpty(approval.getAuditPeriod())) {
                    auditPeriods = approval.getAuditPeriod().split(",");
                }
            }
        }

        switch (type) {
            // 成本调查表逻辑
            case ("1"): {
                CostSurveyTemplate template = costSurveyTemplateManager.get(surveyTemplateId);
                if (template == null) {
                    response.sendError(HttpServletResponse.SC_BAD_REQUEST, "未找到指定的调查模板");
                    return;
                }
                templateType = template.getTemplateType();
                templateName = template.getSurveyTemplateName();

                CostSurveyTemplateVersion currentVersion = StringUtil.isNotEmpty(versionId) ?
                        costSurveyTemplateVersionManager.get(versionId) : costSurveyTemplateVersionManager.selectCurrentVersion(surveyTemplateId);

                if (currentVersion == null) {
                    response.sendError(HttpServletResponse.SC_BAD_REQUEST, "未找到指定的模板版本");
                    return;
                }

                List<CostSurveyTemplateHeaders> headersList =
                        costSurveyTemplateHeadersManager
                                .listVisibleBySurveyTemplateIdAndVersion(surveyTemplateId, currentVersion.getId())
                                .stream()
                                .filter(header -> StringUtil.isEmpty(header.getShowVisible()) || "1".equals(header.getShowVisible()))
                                .sorted(Comparator.comparing(header -> {
                                    String orderNum = header.getOrderNum();
                                    if (StringUtil.isEmpty(orderNum)) {
                                        return Integer.MAX_VALUE; // 空值排到最后
                                    }
                                    try {
                                        return Integer.parseInt(orderNum.trim());
                                    } catch (NumberFormatException e) {
                                        return Integer.MAX_VALUE; // 无效数字排到最后
                                    }
                                }))
                                .collect(Collectors.toList());


                if (headersList.isEmpty()) {
                    response.sendError(HttpServletResponse.SC_BAD_REQUEST, "未找到表头信息");
                    return;
                }

                List<CostSurveyTemplateItems> itemsList = costSurveyTemplateItemsDao.selectBySurveyTemplateIdAndVersion(
                        surveyTemplateId, currentVersion.getId());

                Workbook workbook = new XSSFWorkbook();
                String sheetName = System.currentTimeMillis() + "_成本调查表";
                Sheet sheet = workbook.createSheet(sheetName);

                // 创建样式
                CellStyle titleStyle = createTitleStyle(workbook);
                CellStyle headerStyle = createHeaderStyle(workbook);
                CellStyle dataStyle = createDataStyle(workbook);

                // 第一行：大标题
                Row titleRow = sheet.createRow(0);
                Cell titleCell = titleRow.createCell(0);
                titleCell.setCellValue(templateName);
                titleCell.setCellStyle(titleStyle);

                // 计算总列数（包括隐藏列）
                int totalColumns = headersList.size();
                boolean needExtraColumns = "2".equals(templateType) || "3".equals(templateType);
                if (needExtraColumns) {
                    totalColumns += 2; // rowId + parentId
                }
                if ("2".equals(templateType) && auditPeriods != null && auditPeriods.length > 0) {
                    totalColumns += auditPeriods.length + 1; // 年限列 + 备注列
                }

                // 合并标题行单元格
                if (totalColumns > 1) {
                    sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, totalColumns - 1));
                }

                // 第二行：列表头
                Row headerRow = sheet.createRow(1);
                int colIndex = 0;

                // 添加原有表头
                for (int i = 0; i < headersList.size(); i++) {
                    Cell headerCell = headerRow.createCell(colIndex++);
                    headerCell.setCellValue(headersList.get(i).getFieldName());
                    headerCell.setCellStyle(headerStyle);
                }

                // 如果是固定表或动态表，在最后添加特殊列
                int rowIdColIndex = -1;
                int parentIdColIndex = -1;
                if (needExtraColumns) {
                    // 添加 rowId 列（用于标识行）
                    rowIdColIndex = colIndex;
                    Cell rowIdCell = headerRow.createCell(colIndex++);
                    rowIdCell.setCellValue("行ID");
                    rowIdCell.setCellStyle(headerStyle);
                    // 添加 parentId 列（用于标识父子关系）
                    parentIdColIndex = colIndex;
                    Cell parentIdCell = headerRow.createCell(colIndex++);
                    parentIdCell.setCellValue("父行ID");
                    parentIdCell.setCellStyle(headerStyle);
                }

                // 只有固定表需要添加年限列和备注列
                if ("2".equals(templateType) && auditPeriods != null && auditPeriods.length > 0) {
                    for (String period : auditPeriods) {
                        Cell periodCell = headerRow.createCell(colIndex++);
                        periodCell.setCellValue(period.trim());
                        periodCell.setCellStyle(headerStyle);
                    }
                    Cell remarkCell = headerRow.createCell(colIndex++);
                    remarkCell.setCellValue("备注");
                    remarkCell.setCellStyle(headerStyle);
                }

                if (itemsList != null && !itemsList.isEmpty()) {
                    Map<String, Integer> headerIndexMap = new HashMap<>();
                    for (int i = 0; i < headersList.size(); i++) {
                        headerIndexMap.put(headersList.get(i).getId(), i);
                    }
                    fillExcelData(sheet, itemsList, headerIndexMap, templateType, type, rowIdColIndex, parentIdColIndex, dataStyle);
                }

                for (int i = 0; i < headersList.size(); i++) {
                    sheet.autoSizeColumn(i);
                    sheet.setColumnWidth(i, Math.max(sheet.getColumnWidth(i), 3000));
                }

                // 隐藏行ID和父行ID列
                if (rowIdColIndex >= 0) {
                    sheet.setColumnHidden(rowIdColIndex, true);
                }
                if (parentIdColIndex >= 0) {
                    sheet.setColumnHidden(parentIdColIndex, true);
                }

                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setCharacterEncoding("utf-8");
                String fileName = "成本调查表_" + System.currentTimeMillis() + ".xlsx";
                response.setHeader("Content-Disposition", "attachment; filename=" +
                        URLEncoder.encode(fileName, "UTF-8"));

                try {
                    workbook.write(response.getOutputStream());
                } catch (Exception e) {
                    System.err.println("成本调查表导出失败: " + e.getMessage());
                    e.printStackTrace();
                    throw e;
                } finally {
                    workbook.close();
                }
                break;
            }
            // 财务数据表逻辑
            case ("2"): {
                // 1.获取模板信息
                CostSurveyFdTemplate template = costSurveyFdTemplateManager.get(surveyTemplateId);
                if (template == null) {
                    response.sendError(HttpServletResponse.SC_BAD_REQUEST, "未找到指定的财务数据表模板");
                    return;
                }
                templateType = template.getTemplateType();
                templateName = template.getSurveyTemplateName();

                // 2.获取模板版本
                CostSurveyFdTemplateVersion currentVersion = StringUtil.isNotEmpty(versionId) ?
                        costSurveyFdTemplateVersionManager.get(versionId) : costSurveyFdTemplateVersionManager.selectCurrentVersion(surveyTemplateId);

                if (currentVersion == null) {
                    response.sendError(HttpServletResponse.SC_BAD_REQUEST, "未找到指定的模板版本");
                    return;
                }

                // 3.获取模板表头
                List<CostSurveyFdTemplateHeaders> headersList =
                        costSurveyFdTemplateHeadersManager
                                .listVisibleBySurveyTemplateIdAndVersion(surveyTemplateId, currentVersion.getId())
                                .stream()
                                .filter(header -> StringUtil.isEmpty(header.getShowVisible()) || "1".equals(header.getShowVisible()))
                                .sorted(Comparator.comparing(header -> {
                                    String orderNum = header.getOrderNum();
                                    if (StringUtil.isEmpty(orderNum)) {
                                        return Integer.MAX_VALUE; // 空值排到最后
                                    }
                                    try {
                                        return Integer.parseInt(orderNum.trim());
                                    } catch (NumberFormatException e) {
                                        return Integer.MAX_VALUE; // 无效数字排到最后
                                    }
                                }))
                                .collect(Collectors.toList());


                if (headersList.isEmpty()) {
                    response.sendError(HttpServletResponse.SC_BAD_REQUEST, "未找到表头信息");
                    return;
                }

                // 4.获取模板表项
                List<CostSurveyFdTemplateItems> itemsList = costSurveyFdTemplateItemsDao.selectBySurveyTemplateIdAndVersion(
                        surveyTemplateId, currentVersion.getId());

                // 5.返回excel
                Workbook workbook = new XSSFWorkbook();
                String sheetName = System.currentTimeMillis() + "_财务数据表";
                Sheet sheet = workbook.createSheet(sheetName);

                // 创建样式
                CellStyle titleStyle = createTitleStyle(workbook);
                CellStyle headerStyle = createHeaderStyle(workbook);
                CellStyle dataStyle = createDataStyle(workbook);

                // 第一行：大标题
                Row titleRow = sheet.createRow(0);
                Cell titleCell = titleRow.createCell(0);
                titleCell.setCellValue(templateName);
                titleCell.setCellStyle(titleStyle);

                // 计算总列数（包括隐藏列）
                int totalColumns = headersList.size();
                boolean needExtraColumns = "2".equals(templateType) || "3".equals(templateType);
                if (needExtraColumns) {
                    totalColumns += 2; // rowId + parentId
                }
                if ("2".equals(templateType) && auditPeriods != null && auditPeriods.length > 0) {
                    totalColumns += auditPeriods.length + 1; // 年限列 + 备注列
                }

                // 合并标题行单元格
                if (totalColumns > 1) {
                    sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, totalColumns - 1));
                }

                // 第二行：列表头
                Row headerRow = sheet.createRow(1);
                int colIndex = 0;

                // 添加原有表头
                for (int i = 0; i < headersList.size(); i++) {
                    Cell headerCell = headerRow.createCell(colIndex++);
                    headerCell.setCellValue(headersList.get(i).getFieldName());
                    headerCell.setCellStyle(headerStyle);
                }

                // 如果是固定表或动态表，在最后添加特殊列
                int rowIdColIndex = -1;
                int parentIdColIndex = -1;
                if (needExtraColumns) {
                    // 添加 rowId 列（用于标识行）
                    rowIdColIndex = colIndex;
                    Cell rowIdCell = headerRow.createCell(colIndex++);
                    rowIdCell.setCellValue("行ID");
                    rowIdCell.setCellStyle(headerStyle);
                    // 添加 parentId 列（用于标识父子关系）
                    parentIdColIndex = colIndex;
                    Cell parentIdCell = headerRow.createCell(colIndex++);
                    parentIdCell.setCellValue("父行ID");
                    parentIdCell.setCellStyle(headerStyle);
                }

                // 只有固定表需要添加年限列和备注列
                if ("2".equals(templateType) && auditPeriods != null && auditPeriods.length > 0) {
                    for (String period : auditPeriods) {
                        Cell periodCell = headerRow.createCell(colIndex++);
                        periodCell.setCellValue(period.trim());
                        periodCell.setCellStyle(headerStyle);
                    }
                    Cell remarkCell = headerRow.createCell(colIndex++);
                    remarkCell.setCellValue("备注");
                    remarkCell.setCellStyle(headerStyle);
                }

                if (itemsList != null && !itemsList.isEmpty()) {
                    Map<String, Integer> headerIndexMap = new HashMap<>();
                    for (int i = 0; i < headersList.size(); i++) {
                        headerIndexMap.put(headersList.get(i).getId(), i);
                    }
                    fillExcelDataFd(sheet, itemsList, headerIndexMap, templateType, rowIdColIndex, parentIdColIndex, dataStyle);
                }

                for (int i = 0; i < headersList.size(); i++) {
                    sheet.autoSizeColumn(i);
                    sheet.setColumnWidth(i, Math.max(sheet.getColumnWidth(i), 3000));
                }

                // 隐藏行ID和父行ID列
                if (rowIdColIndex >= 0) {
                    sheet.setColumnHidden(rowIdColIndex, true);
                }
                if (parentIdColIndex >= 0) {
                    sheet.setColumnHidden(parentIdColIndex, true);
                }

                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setCharacterEncoding("utf-8");
                String fileName = "财务数据表_" + System.currentTimeMillis() + ".xlsx";
                response.setHeader("Content-Disposition", "attachment; filename=" +
                        URLEncoder.encode(fileName, "UTF-8"));

                try {
                    workbook.write(response.getOutputStream());
                } catch (Exception e) {
                    System.err.println("财务数据表导出失败: " + e.getMessage());
                    e.printStackTrace();
                    throw e;
                } finally {
                    workbook.close();
                }
                break;
            }
            // 核定表逻辑
            case ("3"): {
                CostVerifyTemplate template = costVerifyTemplateManager.get(surveyTemplateId);
                if (template == null) {
                    response.sendError(HttpServletResponse.SC_BAD_REQUEST, "未找到指定的核定表模板");
                    return;
                }

                List<CostVerifyTemplateHeaders> headersList =
                        costVerifyTemplateHeadersDao.selectList(
                                        new QueryWrapper<CostVerifyTemplateHeaders>()
                                                .eq("survey_template_id", surveyTemplateId)
                                )
                                .stream()
                                .filter(h -> StringUtil.isEmpty(h.getShowVisible()) || "1".equals(h.getShowVisible()))
                                .sorted(Comparator.comparing(h -> {
                                    String orderNum = h.getOrderNum();
                                    if (StringUtil.isEmpty(orderNum)) {
                                        return Integer.MAX_VALUE;
                                    }
                                    try {
                                        return Integer.parseInt(orderNum.trim());
                                    } catch (NumberFormatException e) {
                                        return Integer.MAX_VALUE;
                                    }
                                }))
                                .collect(Collectors.toList());

                if (headersList.isEmpty()) {
                    response.sendError(HttpServletResponse.SC_BAD_REQUEST, "未找到表头信息");
                    return;
                }

                List<CostVerifyTemplateItems> itemsList =
                        costVerifyTemplateItemsDao.selectByVerifyTemplateId(surveyTemplateId, null);

                Workbook workbook = new XSSFWorkbook();
                String sheetName = System.currentTimeMillis() + "_核定表";
                Sheet sheet = workbook.createSheet(sheetName);

                // 创建样式
                CellStyle titleStyle = createTitleStyle(workbook);
                CellStyle headerStyle = createHeaderStyle(workbook);
                CellStyle dataStyle = createDataStyle(workbook);

                // 第一行：大标题
                Row titleRow = sheet.createRow(0);
                Cell titleCell = titleRow.createCell(0);
                titleCell.setCellValue(template.getSurveyTemplateName());
                titleCell.setCellStyle(titleStyle);

                // 计算总列数（包括隐藏列）
                int totalColumns = headersList.size() + 2; // 包括行ID和父行ID

                // 合并标题行单元格
                if (totalColumns > 1) {
                    sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, totalColumns - 1));
                }

                // 第二行：列表头
                Row headerRow = sheet.createRow(1);
                int colIndex = 0;

                for (int i = 0; i < headersList.size(); i++) {
                    Cell headerCell = headerRow.createCell(colIndex++);
                    headerCell.setCellValue(headersList.get(i).getFieldName());
                    headerCell.setCellStyle(headerStyle);
                }

                int rowIdColIndex = colIndex;
                Cell rowIdCell = headerRow.createCell(colIndex++);
                rowIdCell.setCellValue("行ID");
                rowIdCell.setCellStyle(headerStyle);
                int parentIdColIndex = colIndex;
                Cell parentIdCell = headerRow.createCell(colIndex++);
                parentIdCell.setCellValue("父行ID");
                parentIdCell.setCellStyle(headerStyle);

                if (itemsList != null && !itemsList.isEmpty()) {
                    Map<String, Integer> headerIndexMap = new HashMap<>();
                    for (int i = 0; i < headersList.size(); i++) {
                        headerIndexMap.put(headersList.get(i).getId(), i);
                    }
                    fillExcelDataVerify(sheet, itemsList, headerIndexMap, rowIdColIndex, parentIdColIndex, dataStyle);
                }

                for (int i = 0; i < headersList.size(); i++) {
                    sheet.autoSizeColumn(i);
                    sheet.setColumnWidth(i, Math.max(sheet.getColumnWidth(i), 3000));
                }

                sheet.setColumnHidden(rowIdColIndex, true);
                sheet.setColumnHidden(parentIdColIndex, true);

                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setCharacterEncoding("utf-8");
                String fileName = "核定表_" + System.currentTimeMillis() + ".xlsx";
                response.setHeader("Content-Disposition", "attachment; filename=" +
                        URLEncoder.encode(fileName, "UTF-8"));

                try {
                    workbook.write(response.getOutputStream());
                } catch (Exception e) {
                    System.err.println("核定表导出失败: " + e.getMessage());
                    e.printStackTrace();
                    throw e;
                } finally {
                    workbook.close();
                }
                break;
            }
            // 其它不支持
            default: {
                response.sendError(HttpServletResponse.SC_BAD_REQUEST, "不支持的类型");
                break;
            }
        }

    }

    /**
     * 导入调查表Excel数据
     */
    @PostMapping(value = "/importExcel")
    @ApiOperation(value = "导入调查表Excel数据", httpMethod = "POST")
    public CommonResult<String> importExcel(
            @ApiParam(name = "file", value = "Excel文件", required = true)
            @RequestParam("file") MultipartFile file,
            @ApiParam(name = "surveyTemplateId", value = "模板ID", required = true)
            @RequestParam(required = true) String surveyTemplateId,
            @ApiParam(name = "taskId", value = "任务ID", required = true)
            @RequestParam(required = true) String taskId,
            @ApiParam(name = "refId", value = "上传记录ID", required = false)
            @RequestParam(required = false) String refId,
            @ApiParam(name = "materialId", value = "上传记录ID", required = false)
            @RequestParam(required = false) String materialId,
            @ApiParam(name = "uploadId", value = "上传记录ID", required = false)
            @RequestParam(required = false) String uploadId,
            @ApiParam(name = "periodRecordId", value = "监审期间记录ID")
            @RequestParam(required = false) String periodRecordId,
            @ApiParam(name = "type", value = "类型：1-成本调查表 2-财务数据表", required = true)
            @RequestParam(required = true) String type) throws Exception {

        if (file == null || file.isEmpty()) {
            return CommonResult.<String>error().message("上传文件不能为空");
        }

        String fileName = file.getOriginalFilename();
        if (fileName == null || (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls"))) {
            return CommonResult.<String>error().message("文件格式不正确，请上传Excel文件");
        }
        String templateType = null;
        if (refId == null) {
            refId = uploadId;
        }
        if (refId == null) {
            refId = materialId;
        }
        if (periodRecordId !=null){
            CostAuditPeriodRecord periodRecord = costAuditPeriodRecordManager.get(periodRecordId);
            if (periodRecord != null){
                templateType = periodRecord.getType();
            }
        }
        switch (type) {
            // 成本调查表逻辑
            case "1": {
                CostProjectTask task = costProjectTaskManager.getById(taskId);
                String auditedUnitId = task != null ? task.getAuditedUnitId() : null;

                CostSurveyTemplate template = costSurveyTemplateManager.get(surveyTemplateId);
                if (template == null) {
                    return CommonResult.<String>error().message("未找到指定的调查模板");
                }
                templateType = template.getTemplateType();

                CostSurveyTemplateVersion currentVersion = costSurveyTemplateVersionManager.selectCurrentVersion(surveyTemplateId);
                if (currentVersion == null) {
                    return CommonResult.<String>error().message("未找到启用的模板版本");
                }

                List<CostSurveyTemplateHeaders> headersList = costSurveyTemplateHeadersManager.listVisibleBySurveyTemplateIdAndVersion(
                                surveyTemplateId, currentVersion.getId()).stream()
                        .filter(header -> StringUtil.isEmpty(header.getShowVisible()) || "1".equals(header.getShowVisible()))
                        .collect(Collectors.toList());

                if (headersList.isEmpty()) {
                    return CommonResult.<String>error().message("未找到表头信息");
                }

                Map<String, CostSurveyTemplateHeaders> headerNameMap = headersList.stream()
                        .filter(header -> StringUtil.isNotEmpty(header.getFieldName()))
                        .collect(Collectors.toMap(
                                header -> header.getFieldName().trim(),
                                header -> header
                        ));

                Workbook workbook = null;
                try (InputStream inputStream = file.getInputStream()) {
                    workbook = fileName.endsWith(".xlsx") ?
                            new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);

                    Sheet sheet = workbook.getSheetAt(0);
                    if (sheet == null || sheet.getPhysicalNumberOfRows() < 2) {
                        return CommonResult.<String>error().message("Excel文件没有数据");
                    }

                    // 第0行是标题，第1行是表头
                    Row headerRow = sheet.getRow(1);
                    if (headerRow == null) {
                        return CommonResult.<String>error().message("Excel文件第一行为空");
                    }

                    // 检查是否有特殊列：行ID、父行ID、年限列、备注列
                    Integer rowIdColumnIndex = null; // 行ID列索引
                    Integer parentIdColumnIndex = null; // 父行ID列索引
                    Map<String, Integer> auditPeriodColumnMap = new HashMap<>(); // 年限->列索引
                    Integer remarkColumnIndex = null; // 备注列索引
                    Map<Integer, CostSurveyTemplateHeaders> columnIndexMap = new HashMap<>();
                    for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                        Cell cell = headerRow.getCell(i);
                        if (cell != null) {
                            String cellValue = getCellStringValue(cell);
                            if (StringUtil.isNotEmpty(cellValue)) {
                                String trimmedValue = cellValue.trim();
                                if ("行ID".equals(trimmedValue)) {
                                    rowIdColumnIndex = i;
                                } else if ("父行ID".equals(trimmedValue)) {
                                    parentIdColumnIndex = i;
                                } else if ("备注".equals(trimmedValue)) {
                                    remarkColumnIndex = i;
                                } else if (trimmedValue.matches("\\d{4}")) {
                                    // 如果是4位数字，认为是年限列
                                    auditPeriodColumnMap.put(trimmedValue, i);
                                } else {
                                    CostSurveyTemplateHeaders header = headerNameMap.get(trimmedValue);
                                    if (header != null) {
                                        columnIndexMap.put(i, header);
                                    }
                                }
                            }
                        }
                    }

                    if (columnIndexMap.isEmpty()) {
                        return CommonResult.<String>error().message("Excel表头与模板表头不匹配");
                    }

                    // 固定表和动态表必须包含行ID和父行ID列
                    if ("2".equals(templateType) || "3".equals(templateType)) {
                        if (rowIdColumnIndex == null) {
                            return CommonResult.<String>error().message("固定表/动态表导入失败：Excel文件缺少【行ID】列。请使用系统导出的模板，不要删除隐藏列。");
                        }
                        if (parentIdColumnIndex == null) {
                            return CommonResult.<String>error().message("固定表/动态表导入失败：Excel文件缺少【父行ID】列。请使用系统导出的模板，不要删除隐藏列。");
                        }
                    }

//                    // 校验表头是否与导出时一致
//                    List<String> headerValidationErrors = validateExcelHeaders(
//                        headerRow, headersList, taskId, templateType, type,
//                        rowIdColumnIndex, parentIdColumnIndex, auditPeriodColumnMap, remarkColumnIndex);
//                    if (!headerValidationErrors.isEmpty()) {
//                        return CommonResult.<String>error().message(
//                                "导入失败，表头校验不通过：<br>" + String.join("<br>", headerValidationErrors));
//                    }

                    List<CostSurveyTemplateUploadData> dataList = new ArrayList<>();
                    Map<String, Integer> rowIdToExcelRowMap = new HashMap<>();
                    int dataRowCount = 0;

                    // 从第2行开始读取数据（第0行是标题，第1行是表头）
                    for (int rowIndex = 2; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                        Row dataRow = sheet.getRow(rowIndex);
                        if (dataRow == null || isEmptyRow(dataRow)) continue;

                        // 确定当前行的rowId
                        String currentRowId;
                        if (rowIdColumnIndex != null) {
                            // 如果有行ID列，必须使用Excel中的行ID
                            Cell rowIdCell = dataRow.getCell(rowIdColumnIndex);
                            String excelRowId = getCellStringValue(rowIdCell);
                            if (StringUtil.isNotEmpty(excelRowId)) {
                                currentRowId = excelRowId.trim();
                            } else {
                                // 固定表和动态表都不支持新增行，行ID不能为空
                                CommonResult<String> result = CommonResult.<String>error().message(String.format("第%d行 行ID不能为空，请使用系统导出的模板文件", rowIndex + 1));
                                result.setCode(250);
                                return result;
                            }
                        } else {
                            return CommonResult.<String>error().message("固定表/动态表导入失败：Excel文件缺少【行ID】列。请使用系统导出的模板，不要删除隐藏列。");
                        }

                        rowIdToExcelRowMap.put(currentRowId, rowIndex + 1);

                        // 读取父行ID
                        String parentRowId = null;
                        if (parentIdColumnIndex != null) {
                            Cell parentIdCell = dataRow.getCell(parentIdColumnIndex);
                            parentRowId = getCellStringValue(parentIdCell);
                            if (StringUtil.isNotEmpty(parentRowId)) {
                                parentRowId = parentRowId.trim();
                            }
                        }

                        // 读取原有字段数据（只保存有值的数据）
                        for (Map.Entry<Integer, CostSurveyTemplateHeaders> entry : columnIndexMap.entrySet()) {
                            Cell cell = dataRow.getCell(entry.getKey());
                            String cellValue = getCellStringValue(cell);

                            // 只保存有值的数据
                            if (StringUtil.isNotEmpty(cellValue)) {
                                CostSurveyTemplateUploadData uploadData = createUploadData(
                                        surveyTemplateId, taskId, uploadId, currentRowId,
                                        entry.getValue(), cellValue, periodRecordId, type);
                                uploadData.setUploadId(uploadId);
                                uploadData.setRefId(refId);
                                uploadData.setAuditedUnitId(auditedUnitId);
                                uploadData.setParentId(parentRowId);
                                dataList.add(uploadData);
                            }
                        }

                        // 读取年限列数据（只保存有值的数据）
                        for (Map.Entry<String, Integer> periodEntry : auditPeriodColumnMap.entrySet()) {
                            String period = periodEntry.getKey();
                            Integer colIndex = periodEntry.getValue();
                            Cell cell = dataRow.getCell(colIndex);
                            String cellValue = getCellStringValue(cell);

                            // 只保存有值的数据
                            if (StringUtil.isNotEmpty(cellValue)) {
                                CostSurveyTemplateUploadData periodData = new CostSurveyTemplateUploadData();
                                periodData.setSurveyTemplateId(surveyTemplateId);
                                periodData.setTaskId(taskId);
                                periodData.setUploadId(uploadId);
                                periodData.setRefId(refId);
                                periodData.setType(type);
                                periodData.setRowid(currentRowId);
                                periodData.setRkey(period); // 年限，如：2024
                                periodData.setRvalue(cellValue.trim());
                                periodData.setAuditedUnitId(auditedUnitId);
                                periodData.setParentId(parentRowId);
                                if (StringUtil.isNotEmpty(periodRecordId)) {
                                    periodData.setPeriodRecordId(periodRecordId);
                                }
                                periodData.setIsDeleted("0");
                                dataList.add(periodData);
                            }
                        }

                        // 读取备注列数据（只保存有值的数据）
                        if (remarkColumnIndex != null) {
                            Cell remarkCell = dataRow.getCell(remarkColumnIndex);
                            String remarkValue = getCellStringValue(remarkCell);

                            // 只保存有值的数据
                            if (StringUtil.isNotEmpty(remarkValue)) {
                                CostSurveyTemplateUploadData remarkData = new CostSurveyTemplateUploadData();
                                remarkData.setSurveyTemplateId(surveyTemplateId);
                                remarkData.setTaskId(taskId);
                                remarkData.setRefId(refId);
                                remarkData.setUploadId(uploadId);
                                remarkData.setType(type);
                                remarkData.setRowid(currentRowId);
                                remarkData.setRkey("remark");
                                remarkData.setRvalue(remarkValue.trim());
                                remarkData.setAuditedUnitId(auditedUnitId);
                                remarkData.setParentId(parentRowId);
                                if (StringUtil.isNotEmpty(periodRecordId)) {
                                    remarkData.setPeriodRecordId(periodRecordId);
                                }
                                remarkData.setIsDeleted("0");
                                dataList.add(remarkData);
                            }
                        }

                        dataRowCount++;
                    }

                    if (dataList.isEmpty()) {
                        return CommonResult.<String>error().message("Excel文件中没有有效数据");
                    }

                    List<String> errors = verifyImportData(dataList, type, surveyTemplateId, rowIdToExcelRowMap);
                    if (!errors.isEmpty()) {
                        CommonResult<String> result = CommonResult.<String>error().message("导入失败，发现以下问题：<br>" + String.join("<br>", errors));
                        result.setCode(250);
                        return result;
                    }
                    costSurveyTemplateUploadDataManager.saveData(dataList);

                    // 更新上传状态
                    CostSurveyTemplateUpload upload = costSurveyTemplateUploadManager.getById(materialId);
                    if (upload != null) {
                        upload.setIsUpload("1");
                        costSurveyTemplateUploadManager.updateById(upload);
                    }

                    return CommonResult.<String>ok().message("导入成功，共导入 " + dataRowCount + " 行数据");
                } catch (Exception e) {
                    return CommonResult.<String>error().message("导入失败：" + e.getMessage());
                } finally {
                    if (workbook != null) {
                        workbook.close();
                    }
                }
            }
            // 财务数据表逻辑
            case "2": {
                CostProjectTask task = costProjectTaskManager.getById(taskId);
                String auditedUnitId = task != null ? task.getAuditedUnitId() : null;

                CostSurveyFdTemplate template = costSurveyFdTemplateManager.get(surveyTemplateId);
                if (template == null) {
                    return CommonResult.<String>error().message("未找到指定的财务数据表模板");
                }
                templateType = template.getTemplateType();

                CostSurveyFdTemplateVersion currentVersion = costSurveyFdTemplateVersionManager.selectCurrentVersion(surveyTemplateId);
                if (currentVersion == null) {
                    return CommonResult.<String>error().message("未找到启用的模板版本");
                }


                List<CostSurveyFdTemplateHeaders> headersList = costSurveyFdTemplateHeadersManager.listVisibleBySurveyTemplateIdAndVersion(
                                surveyTemplateId, currentVersion.getId()).stream()
                        .filter(header -> StringUtil.isEmpty(header.getShowVisible()) || "1".equals(header.getShowVisible()))
                        .collect(Collectors.toList());

                if (headersList.isEmpty()) {
                    return CommonResult.<String>error().message("未找到表头信息");
                }

                Map<String, CostSurveyFdTemplateHeaders> headerNameMap = headersList.stream()
                        .filter(header -> StringUtil.isNotEmpty(header.getFieldName()))
                        .collect(Collectors.toMap(
                                header -> header.getFieldName().trim(),
                                header -> header
                        ));

                Workbook workbook = null;
                try (InputStream inputStream = file.getInputStream()) {
                    workbook = fileName.endsWith(".xlsx") ?
                            new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);

                    Sheet sheet = workbook.getSheetAt(0);
                    if (sheet == null || sheet.getPhysicalNumberOfRows() < 2) {
                        return CommonResult.<String>error().message("Excel文件没有数据");
                    }

                    // 第0行是标题，第1行是表头
                    Row headerRow = sheet.getRow(1);
                    if (headerRow == null) {
                        return CommonResult.<String>error().message("Excel文件第一行为空");
                    }

                    // 检查是否有立项年限列、备注列、行ID列和父行ID列
                    Map<String, Integer> auditPeriodColumnMap = new HashMap<>(); // 年限->列索引
                    Integer remarkColumnIndex = null; // 备注列索引
                    Integer rowIdColumnIndex = null; // 行ID列索引
                    Integer parentIdColumnIndex = null; // 父行ID列索引
                    Map<Integer, CostSurveyFdTemplateHeaders> columnIndexMap = new HashMap<>();
                    for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                        Cell cell = headerRow.getCell(i);
                        if (cell != null) {
                            String cellValue = getCellStringValue(cell);
                            if (StringUtil.isNotEmpty(cellValue)) {
                                String trimmedValue = cellValue.trim();
                                if ("备注".equals(trimmedValue)) {
                                    remarkColumnIndex = i;
                                } else if ("行ID".equals(trimmedValue)) {
                                    rowIdColumnIndex = i;
                                } else if ("父行ID".equals(trimmedValue)) {
                                    parentIdColumnIndex = i;
                                } else if (trimmedValue.matches("\\d{4}")) {
                                    // 如果是4位数字，认为是年限列
                                    auditPeriodColumnMap.put(trimmedValue, i);
                                } else {
                                    CostSurveyFdTemplateHeaders header = headerNameMap.get(trimmedValue);
                                    if (header != null) {
                                        columnIndexMap.put(i, header);
                                    }
                                }
                            }
                        }
                    }

                    if (columnIndexMap.isEmpty()) {
                        return CommonResult.<String>error().message("Excel表头与模板表头不匹配");
                    }

                    // 校验表头是否与导出时一致
//                    List<String> headerValidationErrors = validateExcelHeadersFd(
//                        headerRow, headersList, taskId, templateType, type,
//                        rowIdColumnIndex, parentIdColumnIndex, auditPeriodColumnMap, remarkColumnIndex);
//                    if (!headerValidationErrors.isEmpty()) {
//                        return CommonResult.<String>error().message(
//                                "导入失败，表头校验不通过：<br>" + String.join("<br>", headerValidationErrors));
//                    }

                    List<CostSurveyTemplateUploadData> dataList = new ArrayList<>();
                    Map<String, Integer> rowIdToExcelRowMap = new HashMap<>();
                    int dataRowCount = 0;

                    // 从第2行开始读取数据（第0行是标题，第1行是表头）
                    for (int rowIndex = 2; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                        Row dataRow = sheet.getRow(rowIndex);
                        if (dataRow == null || isEmptyRow(dataRow)) continue;

                        // 确定当前行的rowId
                        String currentRowId;
                        if (rowIdColumnIndex != null) {
                            // 如果有行ID列，必须使用Excel中的行ID
                            Cell rowIdCell = dataRow.getCell(rowIdColumnIndex);
                            String excelRowId = getCellStringValue(rowIdCell);
                            if (StringUtil.isNotEmpty(excelRowId)) {
                                currentRowId = excelRowId.trim();
                            } else {
                                // 固定表和动态表都不支持新增行，行ID不能为空
                                CommonResult<String> result = CommonResult.<String>error().message(String.format("第%d行 行ID不能为空，请使用系统导出的模板文件", rowIndex + 1));
                                result.setCode(250);
                                return result;
                            }
                        } else {
                            return CommonResult.<String>error().message("固定表/动态表导入失败：Excel文件缺少【行ID】列。请使用系统导出的模板，不要删除隐藏列。");
                        }

                        // 记录 rowId 到 Excel 行号的映射（Excel行号从1开始，加上表头行，所以是 rowIndex + 1）
                        rowIdToExcelRowMap.put(currentRowId, rowIndex + 1);

                        // 读取父行ID
                        String parentRowId = null;
                        if (parentIdColumnIndex != null) {
                            Cell parentIdCell = dataRow.getCell(parentIdColumnIndex);
                            parentRowId = getCellStringValue(parentIdCell);
                            if (StringUtil.isNotEmpty(parentRowId)) {
                                parentRowId = parentRowId.trim();
                            }
                        }

                        // 读取原有字段数据（只保存有值的数据）
                        for (Map.Entry<Integer, CostSurveyFdTemplateHeaders> entry : columnIndexMap.entrySet()) {
                            Cell cell = dataRow.getCell(entry.getKey());
                            String cellValue = getCellStringValue(cell);

                            // 只保存有值的数据
                            if (StringUtil.isNotEmpty(cellValue)) {
                                CostSurveyTemplateUploadData uploadData = createUploadData(
                                        surveyTemplateId, taskId, uploadId, currentRowId,
                                        entry.getValue(), cellValue, periodRecordId, type);
                                uploadData.setUploadId(uploadId);
                                uploadData.setRefId(refId);
                                uploadData.setAuditedUnitId(auditedUnitId);
                                uploadData.setParentId(parentRowId);
                                dataList.add(uploadData);
                            }
                        }

                        // 读取年限列数据（只保存有值的数据）
                        for (Map.Entry<String, Integer> periodEntry : auditPeriodColumnMap.entrySet()) {
                            String period = periodEntry.getKey();
                            Integer colIndex = periodEntry.getValue();
                            Cell cell = dataRow.getCell(colIndex);
                            String cellValue = getCellStringValue(cell);

                            // 只保存有值的数据
                            if (StringUtil.isNotEmpty(cellValue)) {
                                CostSurveyTemplateUploadData periodData = new CostSurveyTemplateUploadData();
                                periodData.setSurveyTemplateId(surveyTemplateId);
                                periodData.setTaskId(taskId);
                                periodData.setRefId(refId);
                                periodData.setUploadId(uploadId);
                                periodData.setType(type);
                                periodData.setRowid(currentRowId);
                                periodData.setRkey(period);
                                periodData.setRvalue(cellValue.trim());
                                periodData.setAuditedUnitId(auditedUnitId);
                                periodData.setParentId(parentRowId);
                                if (StringUtil.isNotEmpty(periodRecordId)) {
                                    periodData.setPeriodRecordId(periodRecordId);
                                }
                                periodData.setIsDeleted("0");
                                dataList.add(periodData);
                            }
                        }

                        // 读取备注列数据（只保存有值的数据）
                        if (remarkColumnIndex != null) {
                            Cell remarkCell = dataRow.getCell(remarkColumnIndex);
                            String remarkValue = getCellStringValue(remarkCell);

                            // 只保存有值的数据
                            if (StringUtil.isNotEmpty(remarkValue)) {
                                CostSurveyTemplateUploadData remarkData = new CostSurveyTemplateUploadData();
                                remarkData.setSurveyTemplateId(surveyTemplateId);
                                remarkData.setTaskId(taskId);
                                remarkData.setRefId(refId);
                                remarkData.setUploadId(uploadId);
                                remarkData.setType(type);
                                remarkData.setRowid(currentRowId);
                                remarkData.setRkey("remark");
                                remarkData.setRvalue(remarkValue.trim());
                                remarkData.setAuditedUnitId(auditedUnitId);
                                remarkData.setParentId(parentRowId);
                                if (StringUtil.isNotEmpty(periodRecordId)) {
                                    remarkData.setPeriodRecordId(periodRecordId);
                                }
                                remarkData.setIsDeleted("0");
                                dataList.add(remarkData);
                            }
                        }
                    }

                    dataRowCount++;


                    if (dataList.isEmpty()) {
                        return CommonResult.<String>error().message("Excel文件中没有有效数据");
                    }
                    List<String> errors = verifyImportData(dataList, type, surveyTemplateId, rowIdToExcelRowMap);
                    if (!errors.isEmpty()) {
                        CommonResult<String> result = CommonResult.<String>error().message("导入失败，发现以下问题：<br>" + String.join("<br>", errors));
                        result.setCode(250);
                        return result;
                    }
                    costSurveyTemplateUploadDataManager.saveData(dataList);
                    if (materialId != null) {
                        CostProjectTaskMaterial material = costProjectTaskMaterialManager.getById(materialId);
                        material.setIsUpload("1");
                        costProjectTaskMaterialManager.updateById(material);
                    }

                    return CommonResult.<String>ok().message("导入成功，共导入 " + dataRowCount + " 行数据");
                } catch (Exception e) {
                    return CommonResult.<String>error().message("导入失败：" + e.getMessage());
                } finally {
                    if (workbook != null) {
                        workbook.close();
                    }
                }
            }
            // 核定表
            case "3": {
                CostProjectTask task = costProjectTaskManager.getById(taskId);
                String auditedUnitId = task != null ? task.getAuditedUnitId() : null;

                CostVerifyTemplate template = costVerifyTemplateManager.get(surveyTemplateId);
                if (template == null) {
                    return CommonResult.<String>error().message("未找到核定表模板");
                }
                List<CostVerifyTemplateHeaders> headersList =
                        costVerifyTemplateHeadersDao.selectList(
                                new LambdaQueryWrapper<CostVerifyTemplateHeaders>()
                                        .eq(CostVerifyTemplateHeaders::getSurveyTemplateId, surveyTemplateId)
                        );

                if (headersList.isEmpty()) {
                    return CommonResult.<String>error().message("未找到表头信息");
                }

                Map<String, CostVerifyTemplateHeaders> headerNameMap = headersList.stream()
                        .filter(h -> StringUtil.isNotEmpty(h.getFieldName()))
                        .collect(Collectors.toMap(
                                h -> h.getFieldName().trim(),
                                h -> h
                        ));

                Workbook workbook = null;
                try (InputStream inputStream = file.getInputStream()) {

                    workbook = fileName.endsWith(".xlsx") ?
                            new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);

                    Sheet sheet = workbook.getSheetAt(0);
                    if (sheet == null || sheet.getPhysicalNumberOfRows() < 2) {
                        return CommonResult.<String>error().message("Excel文件没有数据");
                    }

                    // 第0行是标题，第1行是表头
                    Row headerRow = sheet.getRow(1);
                    if (headerRow == null) {
                        return CommonResult.<String>error().message("Excel表头行为空");
                    }

                    // 识别行ID列和父行ID列
                    Integer rowIdColumnIndex = null;
                    Integer parentIdColumnIndex = null;
                    Map<Integer, CostVerifyTemplateHeaders> columnIndexMap = new HashMap<>();
                    for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                        Cell cell = headerRow.getCell(i);
                        if (cell != null) {
                            String cellValue = getCellStringValue(cell);
                            if (StringUtil.isNotEmpty(cellValue)) {
                                String trimmedValue = cellValue.trim();
                                if ("行ID".equals(trimmedValue)) {
                                    rowIdColumnIndex = i;
                                } else if ("父行ID".equals(trimmedValue)) {
                                    parentIdColumnIndex = i;
                                } else {
                                    CostVerifyTemplateHeaders header = headerNameMap.get(trimmedValue);
                                    if (header != null) {
                                        columnIndexMap.put(i, header);
                                    }
                                }
                            }
                        }
                    }

                    if (columnIndexMap.isEmpty()) {
                        return CommonResult.<String>error().message("Excel表头与模板不匹配");
                    }

                    // 读取数据行
                    List<CostSurveyTemplateUploadData> dataList = new ArrayList<>();
                    Map<String, Integer> rowIdToExcelRowMap = new HashMap<>();
                    Map<String, String> rowIdToParentIdMap = new HashMap<>();
                    int importRowCount = 0;

                    // 从第2行开始读取数据（第0行是标题，第1行是表头）
                    for (int rowIndex = 2; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                        Row dataRow = sheet.getRow(rowIndex);
                        if (dataRow == null || isEmptyRow(dataRow)) continue;

                        // 确定当前行的rowId
                        String currentRowId;
                        if (rowIdColumnIndex != null) {
                            // 如果有行ID列，必须使用Excel中的行ID
                            Cell rowIdCell = dataRow.getCell(rowIdColumnIndex);
                            String excelRowId = getCellStringValue(rowIdCell);
                            if (StringUtil.isNotEmpty(excelRowId)) {
                                currentRowId = excelRowId.trim();
                            } else {
                                // 核定表不支持新增行，行ID不能为空
                                CommonResult<String> result = CommonResult.<String>error().message(String.format("第%d行 行ID不能为空，请使用系统导出的模板文件", rowIndex + 1));
                                result.setCode(250);
                                return result;
                            }
                        } else {
                            // 核定表必须有行ID列
                            return CommonResult.<String>error().message("核定表导入失败：Excel文件缺少【行ID】列。请使用系统导出的模板文件。");
                        }

                        // 记录 rowId 到 Excel 行号的映射（Excel行号从1开始，加上表头行，所以是 rowIndex + 1）
                        rowIdToExcelRowMap.put(currentRowId, rowIndex + 1);

                        // 读取父行ID
                        String parentRowId = null;
                        if (parentIdColumnIndex != null) {
                            Cell parentIdCell = dataRow.getCell(parentIdColumnIndex);
                            parentRowId = getCellStringValue(parentIdCell);
                            if (StringUtil.isNotEmpty(parentRowId)) {
                                parentRowId = parentRowId.trim();
                            }
                        }
                        rowIdToParentIdMap.put(currentRowId, parentRowId);

                        for (Map.Entry<Integer, CostVerifyTemplateHeaders> entry : columnIndexMap.entrySet()) {
                            Cell cell = dataRow.getCell(entry.getKey());
                            String cellValue = getCellStringValue(cell);

                            if (StringUtil.isNotEmpty(cellValue)) {
                                CostSurveyTemplateUploadData uploadData = createUploadData(
                                        surveyTemplateId, taskId, uploadId, currentRowId,
                                        entry.getValue(), cellValue, periodRecordId, type
                                );
                                uploadData.setAuditedUnitId(auditedUnitId);
                                uploadData.setParentId(parentRowId);
                                dataList.add(uploadData);
                            }
                        }
                        importRowCount++;
                    }

                    if (dataList.isEmpty()) {
                        return CommonResult.<String>error().message("Excel文件没有有效数据");
                    }

                    // 计算 orderNum：根据模板的排序规则计算每个 rowId 的序号
                    Map<String, Integer> rowIdToOrderNum = calculateOrderNumForVerify(surveyTemplateId, rowIdToParentIdMap);

                    // 为每条数据设置 orderNum
                    for (CostSurveyTemplateUploadData data : dataList) {
                        Integer orderNum = rowIdToOrderNum.get(data.getRowid());
                        if (orderNum != null) {
                            data.setOrderNum(orderNum);
                        }
                    }

                    // 保存数据（核定表不需要复杂的字段校验）
                    costSurveyTemplateUploadDataManager.saveData(dataList);

                    return CommonResult.<String>ok().message("导入成功，共导入 " + importRowCount + " 行数据");

                } catch (Exception e) {
                    CommonResult<String> result = CommonResult.<String>error().message("导入失败：" + e.getMessage());
                    result.setCode(250);
                    return result;
                } finally {
                    if (workbook != null) workbook.close();
                }
            }
            default:
                return CommonResult.<String>error().message("不支持的类型");
        }
    }


    /**
     * 计算核定表的 orderNum（根据模板的排序规则）
     */
    private Map<String, Integer> calculateOrderNumForVerify(String surveyTemplateId, Map<String, String> rowIdToParentIdMap) {
        Map<String, Integer> result = new HashMap<>();

        // 获取模板项
        List<CostVerifyTemplateItems> itemsList = costVerifyTemplateItemsDao.selectByVerifyTemplateId(surveyTemplateId, null);
        if (itemsList == null || itemsList.isEmpty()) {
            return result;
        }

        // 按 rowid 分组
        Map<String, List<CostVerifyTemplateItems>> itemsByRowId = itemsList.stream()
                .filter(item -> StringUtil.isNotEmpty(item.getRowid()))
                .collect(Collectors.groupingBy(CostVerifyTemplateItems::getRowid));

        // 按父子关系排序（使用与导出相同的排序逻辑）
        List<String> sortedRowIds = sortRowIdsByParentChildVerify(itemsByRowId);

        // 构建 rowId -> orderNum 的映射（orderNum 从 1 开始）
        for (int i = 0; i < sortedRowIds.size(); i++) {
            result.put(sortedRowIds.get(i), i + 1);
        }

        return result;
    }

    /**
     * 获取友好的行号显示
     */
    private String getRowDisplay(String rowid, Map<String, Integer> rowIdToExcelRowMap) {
        if (rowIdToExcelRowMap == null) {
            return "行[" + rowid + "]";
        }
        Integer excelRow = rowIdToExcelRowMap.get(rowid);
        return excelRow != null ? "第" + excelRow + "行" : "行[" + rowid + "]";
    }

    // 校验逻辑
    private List<String> verifyImportData(List<CostSurveyTemplateUploadData> dataList, String type, String surveyTemplateId, Map<String, Integer> rowIdToExcelRowMap) {
        List<String> errors = new ArrayList<>();
        if (dataList == null || dataList.isEmpty()) {
            return errors;
        }

        // 获取模板类型（templateType）
        String templateType = getTemplateType(type, surveyTemplateId);

        // 按 rowid 分组数据
        Map<String, Map<String, String>> rowDataMap = new HashMap<>();
        for (CostSurveyTemplateUploadData data : dataList) {
            String rowid = data.getRowid();
            if (StringUtil.isEmpty(rowid)) {
                rowid = "default_row";
            }
            rowDataMap.computeIfAbsent(rowid, k -> new HashMap<>()).put(data.getRkey(), data.getRvalue());
        }

        // 获取表头信息（用于字段校验）
        List<? extends Object> headersList = getTemplateHeaders(type, surveyTemplateId);
        if (headersList == null || headersList.isEmpty()) {
            return errors;
        }

        // 1. 字段级别校验（必填、类型、长度等）
        // 固定表和动态表都校验所有行的所有字段（排除年限列和备注列）
        for (Map.Entry<String, Map<String, String>> rowEntry : rowDataMap.entrySet()) {
            String rowid = rowEntry.getKey();
            Map<String, String> rowData = rowEntry.getValue();
            List<String> fieldErrors = validateFieldRulesExcludingPeriods(rowid, rowData, headersList, rowIdToExcelRowMap);
            errors.addAll(fieldErrors);
        }

        // 2. 计算公式校验/结构校验（仅针对固定表，动态表不需要）
        if ("2".equals(templateType))   {
            System.out.println("========================================");
            System.out.println("固定表校验：开始结构校验和公式校验");
            List<String> structureErrors = validateFixedTableStructure(rowDataMap, headersList, surveyTemplateId, type);
            errors.addAll(structureErrors);

            // 根据类型获取模板项
            List<? extends Object> itemsList = getTemplateItems(type, surveyTemplateId);
            if (itemsList != null && !itemsList.isEmpty()) {
                // 按 rowid 分组模板项
                Map<String, List<? extends Object>> itemsByRowId = groupItemsByRowId(itemsList);

                // 构建全局的 cellCode -> value 映射（用于公式计算，因为公式可能引用其他行的单元格）
                // 注意：cellCode是行的标识（如A1、A2、Q1），不是列的标识
                // 对于年限列，需要构建 cellCode_年限 -> 值 的映射（如 A1_2024 -> "1"）
                Map<String, String> globalCellCodeMap = new HashMap<>();

                // 先构建 rowid -> cellCode 的映射
                Map<String, String> rowidToCellCodeMap = new HashMap<>();
                for (Map.Entry<String, List<? extends Object>> entry : itemsByRowId.entrySet()) {
                    String rowid = entry.getKey();
                    List<? extends Object> items = entry.getValue();
                    if (items != null && !items.isEmpty()) {
                        String cellCode = getItemCellCode(items.get(0));
                        if (StringUtil.isNotEmpty(cellCode)) {
                            rowidToCellCodeMap.put(rowid, cellCode);
                        }
                    }
                }

                // 遍历所有行数据，构建 cellCode_年限 -> 值 的映射
                for (Map.Entry<String, Map<String, String>> rowEntry : rowDataMap.entrySet()) {
                    String rowid = rowEntry.getKey();
                    Map<String, String> rowData = rowEntry.getValue();
                    String cellCode = rowidToCellCodeMap.get(rowid);

                    if (StringUtil.isEmpty(cellCode)) {
                        // rowid 不匹配说明导入的Excel不是从当前模板导出的，添加到错误列表
                        Integer excelRowNum = rowIdToExcelRowMap.get(rowid);
                        if (excelRowNum != null) {
                            errors.add(String.format("第%d行的数据在当前模板中不存在，请使用系统导出的最新模板文件", excelRowNum));
                        } else {
                            errors.add("存在无法识别的数据行，请使用系统导出的最新模板文件");
                        }
                        continue;
                    }

                    // 处理年限列的数据（key是4位数字，如"2024"）
                    for (Map.Entry<String, String> dataEntry : rowData.entrySet()) {
                        String key = dataEntry.getKey();
                        String value = dataEntry.getValue();
                        if (key.matches("\\d{4}") && StringUtil.isNotEmpty(value)) {
                            // key就是年限（如 2024）
                            String period = key;
                            // 构建 cellCode_年限 的key（如 A1_2024）
                            String mapKey = cellCode + "_" + period;
                            globalCellCodeMap.put(mapKey, value);
                        }
                    }
                }

                // 收集所有的年限（年限的key是4位数字，如"2024"）
                Set<String> periods = new HashSet<>();
                for (Map<String, String> rowData : rowDataMap.values()) {
                    for (String key : rowData.keySet()) {
                        if (key.matches("\\d{4}")) {
                            periods.add(key);
                        }
                    }
                }

                // 校验每一行的计算公式（针对每个年限分别校验）
                for (Map.Entry<String, Map<String, String>> rowEntry : rowDataMap.entrySet()) {
                    String rowid = rowEntry.getKey();
                    Map<String, String> rowData = rowEntry.getValue();

                    // 获取该行对应的模板项
                    List<? extends Object> rowItems = itemsByRowId.get(rowid);
                    if (rowItems == null || rowItems.isEmpty()) {
                        continue;
                    }

                    // 针对每个年限分别校验（收集错误）
                    for (String period : periods) {
                        List<String> formulaErrors = validateRowFormulasForPeriod(rowid, rowData, rowItems, globalCellCodeMap, period, type, rowIdToExcelRowMap);
                        errors.addAll(formulaErrors);
                    }
                }
            }
        }
        return errors;
    }

    /**
     * 固定表结构校验：行列数量必须匹配
     */
    private List<String> validateFixedTableStructure(Map<String, Map<String, String>> rowDataMap,
                                             List<? extends Object> headersList,
                                             String surveyTemplateId, String type) {
        List<String> errors = new ArrayList<>();
        // 1. 获取模板定义的行数
        List<? extends Object> templateItems = getTemplateItems(type, surveyTemplateId);
        if (templateItems == null || templateItems.isEmpty()) {
            errors.add("固定表模板未定义任何数据行，无法导入");
            return errors;
        }

        // 按 rowid 分组模板项，得到模板定义的行数
        Set<String> templateRowIds = templateItems.stream()
                .map(item -> getItemRowId(item))
                .filter(StringUtil::isNotEmpty)
                .collect(Collectors.toSet());

        int expectedRowCount = templateRowIds.size();
        int actualRowCount = rowDataMap.size();

        // 2. 校验行数是否匹配
        if (actualRowCount != expectedRowCount) {
            errors.add(String.format("固定表行数不匹配！模板定义：%d 行，导入数据：%d 行。固定表不允许增加或减少行。",
                    expectedRowCount, actualRowCount));
        }

        // 3. 校验每一行的字段是否完整
        // 注意：rowData的key是fieldName（中文），所以这里也要用fieldName来匹配
        Set<String> expectedFields = headersList.stream()
                .map(header -> getHeaderFieldName(header))
                .filter(StringUtil::isNotEmpty)
                .collect(Collectors.toSet());

        for (Map.Entry<String, Map<String, String>> rowEntry : rowDataMap.entrySet()) {
            String rowid = rowEntry.getKey();
            Map<String, String> rowData = rowEntry.getValue();
            Set<String> actualFields = rowData.keySet();

            // 检查是否有额外字段（排除模板定义的字段、年限列、备注列）
            Set<String> extraFields = new HashSet<>(actualFields);
            // 先移除年限列（4位数字的字段，如"2024"）和备注列（remark）
            extraFields.removeIf(field -> field.matches("\\d{4}") || "remark".equals(field));
            // 再移除模板定义的字段
            extraFields.removeAll(expectedFields);
            if (!extraFields.isEmpty()) {
                errors.add(String.format("行[%s] 包含模板中未定义的字段：%s。固定表不允许添加额外字段。",
                        rowid, String.join(", ", extraFields)));
            }

            // 检查是否缺少必需的字段（只检查必填字段）
            for (Object headerObj : headersList) {
                String fieldEname = getHeaderFieldEname(headerObj);
                String fieldName = getHeaderFieldName(headerObj);
                String isRequired = getHeaderIsRequired(headerObj);

                // 如果是必填字段但导入数据中没有这个字段
                if ("1".equals(isRequired) && !actualFields.contains(fieldEname)) {
                    errors.add(String.format("行[%s] 缺少必填字段：%s(%s)。固定表必须包含所有必填字段。",
                            rowid, fieldEname, fieldName));
                }
            }
        }
        return errors;
    }

    /**
     * 获取模板类型
     */
    private String getTemplateType(String type, String surveyTemplateId) {
        switch (type) {
            case "1": {
                // 成本调查表
                CostSurveyTemplate template = costSurveyTemplateManager.get(surveyTemplateId);
                return template != null ? template.getTemplateType() : null;
            }
            case "2": {
                // 财务数据表
                CostSurveyFdTemplate template = costSurveyFdTemplateManager.get(surveyTemplateId);
                return template != null ? template.getTemplateType() : null;
            }
            case "3":
                // 核定表（核定表没有 templateType 字段，默认返回 null）
                return null;
            default:
                return null;
        }
    }

    /**
     * 获取表头信息
     */
    private List<? extends Object> getTemplateHeaders(String type, String surveyTemplateId) {
        switch (type) {
            case "1": {
                // 成本调查表
                CostSurveyTemplateVersion version = costSurveyTemplateVersionManager.selectCurrentVersion(surveyTemplateId);
                if (version != null) {
                    return costSurveyTemplateHeadersManager.listVisibleBySurveyTemplateIdAndVersion(surveyTemplateId, version.getId());
                }
                break;
            }
            case "2": {
                // 财务数据表
                CostSurveyFdTemplateVersion version = costSurveyFdTemplateVersionManager.selectCurrentVersion(surveyTemplateId);
                if (version != null) {
                    return costSurveyFdTemplateHeadersManager.listVisibleBySurveyTemplateIdAndVersion(surveyTemplateId, version.getId());
                }
                break;
            }
            case "3":
                // 核定表
                return costVerifyTemplateHeadersDao.selectList(
                        new QueryWrapper<CostVerifyTemplateHeaders>()
                                .eq("survey_template_id", surveyTemplateId)
                );
            default:
                break;
        }
        return new ArrayList<>();
    }

    /**
     * 字段级别校验
     */
    private List<String> validateFieldRulesExcludingPeriods(String rowid, Map<String, String> rowData,
                                                    List<? extends Object> headersList, Map<String, Integer> rowIdToExcelRowMap) {
        List<String> errors = new ArrayList<>();
        String rowDisplay = getRowDisplay(rowid, rowIdToExcelRowMap);
        System.out.println("========================================");
        System.out.println("开始校验" + rowDisplay + "的字段（排除年限列）");
        System.out.println("表头数量: " + headersList.size());
        System.out.println("行数据: " + rowData);

        for (Object headerObj : headersList) {
            String fieldEname = getHeaderFieldEname(headerObj);
            String fieldName = getHeaderFieldName(headerObj);
            String fieldType = getHeaderFieldType(headerObj);
            Integer fieldTypelen = getHeaderFieldTypelen(headerObj);
            Integer fieldTypenointlen = getHeaderFieldTypenointlen(headerObj);
            String format = getHeaderFormat(headerObj);
            String isRequired = getHeaderIsRequired(headerObj);
            String isDict = getHeaderIsDict(headerObj);
            String dictCode = getHeaderDictCode(headerObj);

            // 获取用户输入的值（注意：rowData的key是fieldName中文字段名）
            String value = rowData.get(fieldName);

            System.out.println("  校验字段: " + fieldEname + "(" + fieldName + "), 值: " + value +
                             ", 必填: " + isRequired + ", 类型: " + fieldType + ", 字典: " + isDict);

            // 1. 必填校验（支持 "1" 和 "true" 两种格式）
            if (("1".equals(isRequired) || "true".equalsIgnoreCase(isRequired)) && StringUtil.isEmpty(value)) {
                errors.add(String.format("%s [%s]为必填项，不能为空", rowDisplay, fieldName));
                continue;
            }

            // 如果值为空且非必填，跳过后续校验
            if (StringUtil.isEmpty(value)) {
                continue;
            }

            // 2. 字典校验（支持 "1" 和 "true" 两种格式）
            if (("1".equals(isDict) || "true".equalsIgnoreCase(isDict)) && StringUtil.isNotEmpty(dictCode)) {
                try {
                    validateDictValue(rowDisplay, fieldEname, fieldName, value, dictCode);
                } catch (IllegalArgumentException e) {
                    errors.add(e.getMessage());
                }
            }

            // 3. 字段类型和长度校验（已合并）
            if (StringUtil.isNotEmpty(fieldType)) {
                try {
                    validateFieldType(rowDisplay, fieldEname, fieldName, value, fieldType, fieldTypelen, fieldTypenointlen, format);
                } catch (IllegalArgumentException e) {
                    errors.add(e.getMessage());
                }
            }
        }

        return errors;
    }

    /**
     * 字典校验
     */
    private void validateDictValue(String rowDisplay, String fieldEname, String fieldName,
                                   String value, String dictCode) {
        if (StringUtil.isEmpty(value) || StringUtil.isEmpty(dictCode)) {
            return;
        }

        try {
            // 查询字典数据
            SysType TYPE = sysTypeManager.getOne(
                    new QueryWrapper<SysType>()
                            .eq("TYPE_KEY_", dictCode)
            );
            QueryWrapper<DataDict> wrapper = new QueryWrapper<>();
            wrapper.eq("TYPE_ID_", TYPE.getId());
            List<DataDict> dictDataList = dataDictManager.list(wrapper);

            if (dictDataList == null || dictDataList.isEmpty()) {
                throw new IllegalArgumentException(String.format("%s [%s]的选项配置异常",
                        rowDisplay, fieldName));
            }

            // 检查值是否在字典允许的范围内（支持多选，用逗号分隔）
            String[] values = value.split(",");
            Set<String> validValues = dictDataList.stream()
                    .map(DataDict::getName)
                    .filter(StringUtil::isNotEmpty)
                    .collect(Collectors.toSet());

            List<String> invalidValues = new ArrayList<>();
            for (String val : values) {
                String trimmedVal = val.trim();
                if (StringUtil.isNotEmpty(trimmedVal) && !validValues.contains(trimmedVal)) {
                    invalidValues.add(trimmedVal);
                }
            }

            if (!invalidValues.isEmpty()) {
                // 构建详细的错误信息，包含允许的选项范围
                String validOptionsStr = String.join("、", validValues);
                throw new IllegalArgumentException(String.format(
                        "%s [%s]的值[%s]不在允许的选项范围内。允许的选项有：%s",
                        rowDisplay, fieldName, String.join("、", invalidValues), validOptionsStr));
            }
        } catch (IllegalArgumentException e) {
            throw e;
        } catch (Exception e) {
            throw new IllegalArgumentException(String.format("%s [%s]选项校验异常：%s",
                    rowDisplay, fieldName, e.getMessage()));
        }
    }

    /**
     * 根据日期格式字符串生成正则表达式
     */
    private String getDateRegexByFormat(String format) {
        if (StringUtil.isEmpty(format)) {
            return null;
        }

        switch (format) {
            case "yyyy-MM-dd":
                return "\\d{4}-\\d{2}-\\d{2}";
            case "yyyy-MM-dd HH:mm:ss":
                return "\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}";
            case "yyyy/MM/dd":
                return "\\d{4}/\\d{2}/\\d{2}";
            case "yyyy/MM/dd HH:mm:ss":
                return "\\d{4}/\\d{2}/\\d{2} \\d{2}:\\d{2}:\\d{2}";
            case "yyyy年MM月dd日":
                return "\\d{4}年\\d{2}月\\d{2}日";
            case "yyyyMMdd":
                return "\\d{8}";
            default:
                // 对于其他格式，尝试通用转换
                return format
                    .replace("yyyy", "\\\\d{4}")
                    .replace("MM", "\\\\d{2}")
                    .replace("dd", "\\\\d{2}")
                    .replace("HH", "\\\\d{2}")
                    .replace("mm", "\\\\d{2}")
                    .replace("ss", "\\\\d{2}");
        }
    }

    /**
     * 字段类型和长度校验（合并）
     */
    private void validateFieldType(String rowDisplay, String fieldEname, String fieldName,
                                   String value, String fieldType, Integer fieldTypelen, Integer fieldTypenointlen, String format) {
        try {
            switch (fieldType.toLowerCase()) {
                case "int":
                case "integer":
                    try {
                        Long.parseLong(value);

                        // 校验整数位数（必须精确匹配）
                        if (fieldTypelen != null && fieldTypelen > 0) {
                            String absValue = value.replace("-", "").replace("+", ""); // 去除正负号
                            if (absValue.length() != fieldTypelen) {
                                throw new IllegalArgumentException(
                                    String.format("%s [%s]必须是%d位整数，实际值：%s",
                                        rowDisplay, fieldName, fieldTypelen, value));
                            }
                        }
                    } catch (NumberFormatException e) {
                        throw new IllegalArgumentException(String.format("%s [%s]应为整数，实际值：%s", rowDisplay, fieldName, value));
                    }
                    break;

                case "double":
                    try {
                        Double.parseDouble(value);

                        // 数字精度校验
                        String[] parts = value.split("\\.");

                        // 整数部分长度
                        int intPartLen = parts[0].replace("-", "").replace("+", "").length();
                        if (fieldTypenointlen != null && fieldTypelen != null && intPartLen > (fieldTypelen - fieldTypenointlen)) {
                            throw new IllegalArgumentException(String.format("%s [%s]整数部分长度超限，最大：%d，实际：%d",
                                    rowDisplay, fieldName, (fieldTypelen - fieldTypenointlen), intPartLen));
                        }

                        // 小数部分长度（必须精确匹配）
                        if (fieldTypenointlen != null && fieldTypenointlen > 0) {
                            if (parts.length == 1) {
                                // 没有小数部分，但要求有小数位
                                throw new IllegalArgumentException(
                                    String.format("%s [%s]必须包含%d位小数，实际值：%s",
                                        rowDisplay, fieldName, fieldTypenointlen, value));
                            } else {
                                int decimalPartLen = parts[1].length();
                                // 改为精确匹配，而不是"不超过"
                                if (decimalPartLen != fieldTypenointlen) {
                                    throw new IllegalArgumentException(
                                        String.format("%s [%s]必须是%d位小数，实际：%d位",
                                            rowDisplay, fieldName, fieldTypenointlen, decimalPartLen));
                                }
                            }
                        }
                    } catch (NumberFormatException e) {
                        throw new IllegalArgumentException(String.format("%s [%s]应为数字，实际值：%s", rowDisplay, fieldName, value));
                    }
                    break;

                case "varchar":
                case "string":
                case "text":
                    // 字符串长度校验（使用 format 字段）
                    if (StringUtil.isNotEmpty(format)) {
                        try {
                            int maxLength = Integer.parseInt(format);
                            if (value.length() > maxLength) {
                                throw new IllegalArgumentException(String.format("%s [%s]长度超限，最大长度：%d，实际长度：%d",
                                        rowDisplay, fieldName, maxLength, value.length()));
                            }
                        } catch (NumberFormatException e) {
                            // format 不是数字，跳过长度校验
                        }
                    }
                    break;

                case "date":
                case "datetime":
                    // 日期格式校验（使用 format 字段）
                    if (StringUtil.isNotEmpty(format)) {
                        String regex = getDateRegexByFormat(format);
                        if (StringUtil.isNotEmpty(regex) && !value.matches(regex)) {
                            throw new IllegalArgumentException(String.format("%s [%s]日期格式应为%s，实际值：%s",
                                rowDisplay, fieldName, format, value));
                        }
                    } else {
                        // 如果没有 format，使用默认校验
                        if ("date".equals(fieldType.toLowerCase())) {
                            if (!value.matches("\\d{4}-\\d{2}-\\d{2}")) {
                                throw new IllegalArgumentException(String.format("%s [%s]日期格式应为yyyy-MM-dd，实际值：%s",
                                    rowDisplay, fieldName, value));
                            }
                        } else {
                            if (!value.matches("\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}") && !value.matches("\\d{4}-\\d{2}-\\d{2}")) {
                                throw new IllegalArgumentException(String.format("%s [%s]日期格式应为yyyy-MM-dd或yyyy-MM-dd HH:mm:ss，实际值：%s",
                                    rowDisplay, fieldName, value));
                            }
                        }
                    }
                    break;

                case "boolean":
                case "bool":
                    // 布尔类型校验（支持 true/false, 1/0, 是/否）
                    String lowerValue = value.toLowerCase().trim();
                    if (!lowerValue.equals("true") && !lowerValue.equals("false") &&
                        !lowerValue.equals("1") && !lowerValue.equals("0") &&
                        !lowerValue.equals("是") && !lowerValue.equals("否")) {
                        throw new IllegalArgumentException(String.format("%s [%s]应为布尔值(true/false, 1/0, 是/否)，实际值：%s",
                                rowDisplay, fieldName, value));
                    }
                    break;

                default:
                    // 未知类型，不校验
                    break;
            }
        } catch (IllegalArgumentException e) {
            throw e;
        } catch (Exception e) {
            throw new IllegalArgumentException(String.format("%s [%s]格式校验异常：%s", rowDisplay, fieldName, e.getMessage()));
        }
    }


    /**
     * 获取表头的字段英文名
     */
    private String getHeaderFieldEname(Object header) {
        if (header instanceof CostSurveyTemplateHeaders) {
            return ((CostSurveyTemplateHeaders) header).getFieldEname();
        } else if (header instanceof CostSurveyFdTemplateHeaders) {
            return ((CostSurveyFdTemplateHeaders) header).getFieldEname();
        } else if (header instanceof CostVerifyTemplateHeaders) {
            return ((CostVerifyTemplateHeaders) header).getFieldEname();
        }
        return null;
    }

    /**
     * 获取表头的字段名
     */
    private String getHeaderFieldName(Object header) {
        if (header instanceof CostSurveyTemplateHeaders) {
            return ((CostSurveyTemplateHeaders) header).getFieldName();
        } else if (header instanceof CostSurveyFdTemplateHeaders) {
            return ((CostSurveyFdTemplateHeaders) header).getFieldName();
        } else if (header instanceof CostVerifyTemplateHeaders) {
            return ((CostVerifyTemplateHeaders) header).getFieldName();
        }
        return null;
    }

    /**
     * 获取表头的字段类型
     */
    private String getHeaderFieldType(Object header) {
        if (header instanceof CostSurveyTemplateHeaders) {
            return ((CostSurveyTemplateHeaders) header).getFieldType();
        } else if (header instanceof CostSurveyFdTemplateHeaders) {
            return ((CostSurveyFdTemplateHeaders) header).getFieldType();
        } else if (header instanceof CostVerifyTemplateHeaders) {
            return ((CostVerifyTemplateHeaders) header).getFieldType();
        }
        return null;
    }

    /**
     * 获取表头的字段长度
     */
    private Integer getHeaderFieldTypelen(Object header) {
        if (header instanceof CostSurveyTemplateHeaders) {
            return ((CostSurveyTemplateHeaders) header).getFieldTypelen();
        } else if (header instanceof CostSurveyFdTemplateHeaders) {
            return ((CostSurveyFdTemplateHeaders) header).getFieldTypelen();
        } else if (header instanceof CostVerifyTemplateHeaders) {
            return ((CostVerifyTemplateHeaders) header).getFieldTypelen();
        }
        return null;
    }

    /**
     * 获取表头的小数位长度
     */
    private Integer getHeaderFieldTypenointlen(Object header) {
        if (header instanceof CostSurveyTemplateHeaders) {
            return ((CostSurveyTemplateHeaders) header).getFieldTypenointlen();
        } else if (header instanceof CostSurveyFdTemplateHeaders) {
            return ((CostSurveyFdTemplateHeaders) header).getFieldTypenointlen();
        } else if (header instanceof CostVerifyTemplateHeaders) {
            return ((CostVerifyTemplateHeaders) header).getFieldTypenointlen();
        }
        return null;
    }

    /**
     * 获取表头的是否必填
     */
    private String getHeaderIsRequired(Object header) {
        if (header instanceof CostSurveyTemplateHeaders) {
            return ((CostSurveyTemplateHeaders) header).getIsRequired();
        } else if (header instanceof CostSurveyFdTemplateHeaders) {
            return ((CostSurveyFdTemplateHeaders) header).getIsRequired();
        } else if (header instanceof CostVerifyTemplateHeaders) {
            return ((CostVerifyTemplateHeaders) header).getIsRequired();
        }
        return null;
    }

    /**
     * 获取表头的是否绑定字典
     */
    private String getHeaderIsDict(Object header) {
        if (header instanceof CostSurveyTemplateHeaders) {
            return ((CostSurveyTemplateHeaders) header).getIsDict();
        } else if (header instanceof CostSurveyFdTemplateHeaders) {
            return ((CostSurveyFdTemplateHeaders) header).getIsDict();
        } else if (header instanceof CostVerifyTemplateHeaders) {
            return ((CostVerifyTemplateHeaders) header).getIsDict();
        }
        return null;
    }

    /**
     * 获取表头的字典编码
     */
    private String getHeaderDictCode(Object header) {
        if (header instanceof CostSurveyTemplateHeaders) {
            return ((CostSurveyTemplateHeaders) header).getDictCode();
        } else if (header instanceof CostSurveyFdTemplateHeaders) {
            return ((CostSurveyFdTemplateHeaders) header).getDictCode();
        } else if (header instanceof CostVerifyTemplateHeaders) {
            return ((CostVerifyTemplateHeaders) header).getDictCode();
        }
        return null;
    }

    /**
     * 获取表头的格式
     */
    private String getHeaderFormat(Object header) {
        if (header instanceof CostSurveyTemplateHeaders) {
            return ((CostSurveyTemplateHeaders) header).getFormat();
        } else if (header instanceof CostSurveyFdTemplateHeaders) {
            return ((CostSurveyFdTemplateHeaders) header).getFormat();
        } else if (header instanceof CostVerifyTemplateHeaders) {
            return ((CostVerifyTemplateHeaders) header).getFormat();
        }
        return null;
    }

    /**
     * 根据类型获取模板项
     */
    private List<? extends Object> getTemplateItems(String type, String surveyTemplateId) {
        switch (type) {
            case "1": {
                // 成本调查表
                CostSurveyTemplateVersion version = costSurveyTemplateVersionManager.selectCurrentVersion(surveyTemplateId);
                if (version != null) {
                    return costSurveyTemplateItemsDao.selectBySurveyTemplateIdAndVersion(surveyTemplateId, version.getId());
                }
                break;
            }
            case "2": {
                // 财务数据表
                CostSurveyFdTemplateVersion version = costSurveyFdTemplateVersionManager.selectCurrentVersion(surveyTemplateId);
                if (version != null) {
                    return costSurveyFdTemplateItemsDao.selectBySurveyTemplateIdAndVersion(surveyTemplateId, version.getId());
                }
                break;
            }
            case "3":
                // 核定表
                return costVerifyTemplateItemsDao.selectByVerifyTemplateId(surveyTemplateId, null);
            default:
                break;
        }
        return new ArrayList<>();
    }

    /**
     * 按 rowid 分组模板项
     */
    private Map<String, List<? extends Object>> groupItemsByRowId(List<? extends Object> itemsList) {
        Map<String, List<Object>> result = new HashMap<>();
        for (Object item : itemsList) {
            String rowid = getItemRowId(item);
            if (StringUtil.isEmpty(rowid)) {
                rowid = "default_row";
            }
            result.computeIfAbsent(rowid, k -> new ArrayList<>()).add(item);
        }
        return (Map) result;
    }

    /**
     * 获取 item 的 rowid
     */
    private String getItemRowId(Object item) {
        if (item instanceof CostSurveyTemplateItems) {
            return ((CostSurveyTemplateItems) item).getRowid();
        } else if (item instanceof CostSurveyFdTemplateItems) {
            return ((CostSurveyFdTemplateItems) item).getRowid();
        } else if (item instanceof CostVerifyTemplateItems) {
            return ((CostVerifyTemplateItems) item).getRowid();
        }
        return null;
    }

    /**
     * 校验一行数据的计算公式（针对特定年限）
     */
    private List<String> validateRowFormulasForPeriod(String rowid, Map<String, String> rowData,
                                              List<? extends Object> rowItems, Map<String, String> globalCellCodeMap,
                                              String period, String type, Map<String, Integer> rowIdToExcelRowMap) {
        List<String> errors = new ArrayList<>();
        // 找到该行的计算公式（同一行的所有模板项共享同一个公式）
        String calculationFormula = null;
        String cellCode = null;
        for (Object item : rowItems) {
            String formula = getItemCalculationFormula(item);
            if (StringUtil.isNotEmpty(formula)) {
                calculationFormula = formula;
                cellCode = getItemCellCode(item);
                break;
            }
        }

        // 如果该行没有公式，跳过
        if (StringUtil.isEmpty(calculationFormula)) {
            return errors;
        }

        // 检查公式引用的单元格在当前年限是否有任何一个有值
        // 提取公式中的所有单元格引用（如 A1, A2, A3）
        boolean hasAnyReferencedValue = false;
        java.util.regex.Pattern pattern = java.util.regex.Pattern.compile("[A-Z]+\\d+");
        java.util.regex.Matcher matcher = pattern.matcher(calculationFormula);
        List<String> referencedCellsDebug = new ArrayList<>();
        Map<String, String> cellCodeToItemNameMap = new HashMap<>(); // 存储cellCode到项目名称的映射

        while (matcher.find()) {
            String referencedCell = matcher.group();
            String mapKey = referencedCell + "_" + period;
            String value = globalCellCodeMap.get(mapKey);
            String displayValue = StringUtil.isNotEmpty(value) ? value : "0";

            // 获取该cellCode对应的项目名称
            String itemName = getCellCodeItemName(referencedCell, type);
            cellCodeToItemNameMap.put(referencedCell, itemName);

            referencedCellsDebug.add(referencedCell + "=" + displayValue);
            if (StringUtil.isNotEmpty(value)) {
                hasAnyReferencedValue = true;
            }
        }

        // 如果公式引用的单元格在当前年限都没有值，跳过校验
        if (!hasAnyReferencedValue) {
            return errors;
        }

        // 获取用户输入的汇总值（当前年限）
        String inputValueStr = rowData.get(period);

        try {
            // 将公式中的单元格引用替换为 cellCode_年限 的形式
            // 例如：(A1+A2+A3) -> (A1_2024+A2_2024+A3_2024)
            String formulaWithPeriod = calculationFormula;
            matcher = pattern.matcher(calculationFormula);
            StringBuffer sb = new StringBuffer();
            while (matcher.find()) {
                String referencedCell = matcher.group();
                matcher.appendReplacement(sb, referencedCell + "_" + period);
            }
            matcher.appendTail(sb);
            formulaWithPeriod = sb.toString();

            // 验证公式（使用全局cellCode映射，因为公式可能引用其他行的单元格）
            Double calculatedValue = calculateFormula(formulaWithPeriod, globalCellCodeMap);

            // 用户未填写时默认为0
            Double inputValue = StringUtil.isEmpty(inputValueStr) ? 0.0 : Double.parseDouble(inputValueStr);

            // 输出校验信息
            System.out.println("========================================");
            System.out.println("行[" + rowid + "] [" + period + "年]列公式校验");
            System.out.println("公式: " + calculationFormula);
            System.out.println("引用值: " + String.join(", ", referencedCellsDebug));
            System.out.println("计算结果: " + String.format("%.2f", calculatedValue));
            System.out.println("填写值: " + String.format("%.2f", inputValue));

            // 比较计算值和输入值（允许小数点后2位的误差）
            if (Math.abs(calculatedValue - inputValue) > 0.01) {
                System.out.println("校验结果: 不匹配");
                System.out.println("========================================");
                // 获取友好的行号显示
                Integer excelRow = rowIdToExcelRowMap.get(rowid);
                String rowDisplay = excelRow != null ? "第" + excelRow + "行" : "行[" + rowid + "]";

                // 构建详细的计算过程（包含项目名称）
                String calculationProcess = buildCalculationProcessWithNames(calculationFormula, referencedCellsDebug, cellCodeToItemNameMap);

                errors.add(String.format("%s %s年列数据错误：<br>计算公式：%s<br>计算过程：%s<br>计算结果：%.2f<br>实际填写：%.2f",
                        rowDisplay, period, calculationFormula, calculationProcess, calculatedValue, inputValue));
            } else {
                System.out.println("校验结果: ✓ 通过");
                System.out.println("========================================");
            }
        } catch (Exception e) {
            // 统一处理计算错误，提供更详细但不重复的错误信息
            System.out.println("========================================");
            System.out.println("行[" + rowid + "] [" + period + "年]列公式校验");
            System.out.println("公式: " + calculationFormula);
            System.out.println("引用值: " + String.join(", ", referencedCellsDebug));
            System.out.println("校验结果: 计算错误 - " + e.getMessage());
            System.out.println("========================================");

            // 获取友好的行号显示
            Integer excelRow = rowIdToExcelRowMap.get(rowid);
            String rowDisplay = excelRow != null ? "第" + excelRow + "行" : "行[" + rowid + "]";

            // 简化错误信息，只显示关键信息
            String simpleError;
            if (e.getMessage().contains("除以零") || e.getMessage().contains("无穷大")) {
                simpleError = "除数为0，无法计算";
            } else if (e.getMessage().contains("溢出")) {
                simpleError = "数值过大，计算溢出";
            } else {
                simpleError = "公式计算错误";
            }

            errors.add(String.format("%s %s年列计算错误：%s", rowDisplay, period, simpleError));
        }
        return errors;
    }

    /**
     * 计算公式
     *
     * @param formula     公式字符串，如 "(A1+A2+A3)"
     * @param cellCodeMap cellCode到值的映射，如 {"A1": "1", "A2": "2", "A3": "3"}
     */
    private Double calculateFormula(String formula, Map<String, String> cellCodeMap) {
        // 将中文括号转换为英文括号，避免解析错误
        formula = formula.replace("（", "(")
                         .replace("）", ")")
                         .replace("【", "[")
                         .replace("】", "]")
                         .replace("｛", "{")
                         .replace("｝", "}");

        JEP jep = new JEP();
        jep.setAllowUndeclared(true);
        jep.setImplicitMul(true);

        // 提取公式中所有的单元格引用（如 A1_2024, Q2_2024）
        java.util.regex.Pattern pattern = java.util.regex.Pattern.compile("[A-Z]+\\d+_\\d{4}");
        java.util.regex.Matcher matcher = pattern.matcher(formula);
        Set<String> referencedCells = new HashSet<>();
        while (matcher.find()) {
            referencedCells.add(matcher.group());
        }

        // 为所有引用的单元格设置变量值
        Map<String, Double> variableValues = new HashMap<>();
        for (String cellCode : referencedCells) {
            String value = cellCodeMap.get(cellCode);
            Double numValue;
            if (StringUtil.isNotEmpty(value)) {
                try {
                    numValue = Double.parseDouble(value);
                } catch (NumberFormatException e) {
                    // 如果值不是数字（如中文、文本等），设置为0
                    numValue = 0.0;
                }
            } else {
                // 如果单元格没有值（用户未填写），默认为0
                numValue = 0.0;
            }
            variableValues.put(cellCode, numValue);
            jep.addVariable(cellCode, numValue);
        }

        // 解析并计算公式
        jep.parseExpression(formula);
        if (jep.hasError()) {
            throw new IllegalArgumentException("公式语法错误，请检查模板配置。公式: " + formula + ", 错误: " + jep.getErrorInfo());
        }

        double result = jep.getValue();
        if (Double.isNaN(result)) {
            // 构建详细的错误信息
            StringBuilder debugInfo = new StringBuilder();
            debugInfo.append("计算结果为非数字(NaN)，可能存在除以零或无效运算。");
            debugInfo.append("\n公式: ").append(formula);
            debugInfo.append("\n变量值: ");
            for (Map.Entry<String, Double> entry : variableValues.entrySet()) {
                debugInfo.append(entry.getKey()).append("=").append(entry.getValue()).append(", ");
            }
            throw new IllegalArgumentException(debugInfo.toString());
        }
        if (Double.isInfinite(result)) {
            // 构建详细的错误信息
            StringBuilder debugInfo = new StringBuilder();
            debugInfo.append("计算结果溢出(无穷大)，可能是除以零或数值过大。");
            debugInfo.append("\n公式: ").append(formula);
            debugInfo.append("\n变量值: ");
            for (Map.Entry<String, Double> entry : variableValues.entrySet()) {
                debugInfo.append(entry.getKey()).append("=").append(entry.getValue()).append(", ");
            }
            throw new IllegalArgumentException(debugInfo.toString());
        }

        return result;
    }


    /**
     * 获取 item 的计算公式
     */
    private String getItemCalculationFormula(Object item) {
        if (item instanceof CostSurveyTemplateItems) {
            return ((CostSurveyTemplateItems) item).getCalculationFormula();
        } else if (item instanceof CostSurveyFdTemplateItems) {
            return ((CostSurveyFdTemplateItems) item).getCalculationFormula();
        } else if (item instanceof CostVerifyTemplateItems) {
            return ((CostVerifyTemplateItems) item).getCalculationFormula();
        }
        return null;
    }


    /**
     * 获取 item 的 cellCode
     */
    private String getItemCellCode(Object item) {
        if (item instanceof CostSurveyTemplateItems) {
            return ((CostSurveyTemplateItems) item).getCellCode();
        } else if (item instanceof CostSurveyFdTemplateItems) {
            return ((CostSurveyFdTemplateItems) item).getCellCode();
        } else if (item instanceof CostVerifyTemplateItems) {
            return ((CostVerifyTemplateItems) item).getCellCode();
        }
        return null;
    }

    // ==================== 私有辅助方法 ====================

    private CostItemData buildCostItemData(List<CostSurveyTemplateItems> items, List<CostSurveyTemplateHeaders> headList) {
        String filename = headList.stream().map(CostSurveyTemplateHeaders::getFieldName).collect(Collectors.joining(","));
        String filenids = headList.stream().map(CostSurveyTemplateHeaders::getId).collect(Collectors.joining(","));

        // 按 orderNum 分组
        Map<Integer, List<CostSurveyTemplateItems>> groupedByHeadersId = items.stream()
                .collect(Collectors.groupingBy(CostSurveyTemplateItems::getOrderNum));

        List<Map<String, String>> result = new ArrayList<>();

        for (Map.Entry<Integer, List<CostSurveyTemplateItems>> entry : groupedByHeadersId.entrySet()) {
            List<CostSurveyTemplateItems> group = entry.getValue();
            if (group.isEmpty()) continue;

            // 主项（可以取第一个）
            CostSurveyTemplateItems mainItem = group.get(0);

            String headersIds = "";
            String itemIds = "";
            // 构造 key-value 映射
            Map<String, String> keyValueMap = new HashMap<>();
            for (CostSurveyTemplateItems item : group) {
                if (item.getRkey() != null && item.getRvalue() != null) {
                    keyValueMap.put(item.getRkey(), item.getRvalue());
                    headersIds += item.getHeadersId() + ",";
                    itemIds += item.getId() + ",";
                }
            }
            if (!StringUtil.isEmpty(headersIds))
                headersIds = headersIds.substring(0, headersIds.length() - 1);

            if (!StringUtil.isEmpty(itemIds))
                itemIds = itemIds.substring(0, itemIds.length() - 1);

            //mainItem 放入到keyValueMap中 除了 key value 这两个字段
            keyValueMap.put("id", mainItem.getId());
            keyValueMap.put("surveyTemplateId", mainItem.getSurveyTemplateId());
            keyValueMap.put("versionId", mainItem.getVersionId());
            keyValueMap.put("headersId", mainItem.getHeadersId());
            keyValueMap.put("cellCode", mainItem.getCellCode());
            keyValueMap.put("calculationFormula", mainItem.getCalculationFormula());
            keyValueMap.put("unit", mainItem.getUnit());
            keyValueMap.put("orderNum", mainItem.getOrderNum().toString());
            keyValueMap.put("jsonStr", mainItem.getJsonStr());
            keyValueMap.put("headersIds", headersIds);
            keyValueMap.put("itemIds", itemIds);
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            keyValueMap.put("createTime", mainItem.getCreateTime() != null ? mainItem.getCreateTime().format(formatter) : null);
            keyValueMap.put("updateTime", mainItem.getUpdateTime() != null ? mainItem.getUpdateTime().format(formatter) : null);
            keyValueMap.put("createBy", mainItem.getCreateBy());
            keyValueMap.put("updateBy", mainItem.getUpdateBy());
            keyValueMap.put("projectId", mainItem.getProjectId());
            keyValueMap.put("orderText", mainItem.getOrderText());
            keyValueMap.put("calculationTemplateId", mainItem.getCalculationTemplateId());
            keyValueMap.put("rowid", mainItem.getRowid());
            keyValueMap.put("parentid", mainItem.getParentid());

            result.add(keyValueMap);
        }
        CostItemData data = new CostItemData();
        data.setFixedFields(filename);
        data.setItemlist(result);
        data.setFixedFieldids(filenids);
        return data;
    }

    private CostItemData buildCostItemDataFd(List<CostSurveyFdTemplateItems> items, List<CostSurveyFdTemplateHeaders> headList) {
        String filename = headList.stream().map(CostSurveyFdTemplateHeaders::getFieldName).collect(Collectors.joining(","));
        String filenids = headList.stream().map(CostSurveyFdTemplateHeaders::getId).collect(Collectors.joining(","));

        // 按 orderNum 分组
        Map<Integer, List<CostSurveyFdTemplateItems>> groupedByOrderNum = items.stream()
                .collect(Collectors.groupingBy(CostSurveyFdTemplateItems::getOrderNum));

        List<Map<String, String>> result = new ArrayList<>();

        for (Map.Entry<Integer, List<CostSurveyFdTemplateItems>> entry : groupedByOrderNum.entrySet()) {
            List<CostSurveyFdTemplateItems> group = entry.getValue();
            if (group.isEmpty()) continue;

            // 主项（取第一个）
            CostSurveyFdTemplateItems mainItem = group.get(0);

            String headersIds = "";
            String itemIds = "";
            // 构造 key-value 映射
            Map<String, String> keyValueMap = new HashMap<>();
            for (CostSurveyFdTemplateItems item : group) {
                if (item.getRkey() != null && item.getRvalue() != null) {
                    keyValueMap.put(item.getRkey(), item.getRvalue());
                    headersIds += item.getHeadersId() + ",";
                    itemIds += item.getId() + ",";
                }
            }
            if (!StringUtil.isEmpty(headersIds))
                headersIds = headersIds.substring(0, headersIds.length() - 1);

            if (!StringUtil.isEmpty(itemIds))
                itemIds = itemIds.substring(0, itemIds.length() - 1);

            // 将mainItem属性放入keyValueMap
            keyValueMap.put("id", mainItem.getId());
            keyValueMap.put("surveyTemplateId", mainItem.getSurveyTemplateId());
            keyValueMap.put("versionId", mainItem.getVersionId());
            keyValueMap.put("headersId", mainItem.getHeadersId());
            keyValueMap.put("cellCode", mainItem.getCellCode());
            keyValueMap.put("calculationFormula", mainItem.getCalculationFormula());
            keyValueMap.put("unit", mainItem.getUnit());
            keyValueMap.put("orderNum", mainItem.getOrderNum().toString());
            keyValueMap.put("jsonStr", mainItem.getJsonStr());
            keyValueMap.put("headersIds", headersIds);
            keyValueMap.put("itemIds", itemIds);
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            keyValueMap.put("createTime", mainItem.getCreateTime() != null ? mainItem.getCreateTime().format(formatter) : null);
            keyValueMap.put("updateTime", mainItem.getUpdateTime() != null ? mainItem.getUpdateTime().format(formatter) : null);
            keyValueMap.put("createBy", mainItem.getCreateBy());
            keyValueMap.put("updateBy", mainItem.getUpdateBy());
            keyValueMap.put("projectId", mainItem.getProjectId());
            keyValueMap.put("orderText", mainItem.getOrderText());
            keyValueMap.put("calculationTemplateId", mainItem.getCalculationTemplateId());
            keyValueMap.put("rowid", mainItem.getRowid());
            keyValueMap.put("parentid", mainItem.getParentid());
            result.add(keyValueMap);
        }

        CostItemData data = new CostItemData();
        data.setFixedFields(filename);
        data.setItemlist(result);
        data.setFixedFieldids(filenids);
        return data;
    }

    private void fillExcelData(Sheet sheet, List<CostSurveyTemplateItems> itemsList,
                               Map<String, Integer> headerIndexMap, String templateType, String dataType,
                               int rowIdColIndex, int parentIdColIndex, CellStyle dataStyle) {
        if ("1".equals(templateType)) {
            Row dataRow = sheet.createRow(2);
            for (CostSurveyTemplateItems item : itemsList) {
                Integer colIndex = headerIndexMap.get(item.getHeadersId());
                if (colIndex != null) {
                    Cell cell = dataRow.createCell(colIndex);
                    cell.setCellValue(item.getRvalue());
                    cell.setCellStyle(dataStyle);
                }
            }
        } else {
            // 固定表和动态表：按rowid分组
            Map<String, List<CostSurveyTemplateItems>> itemsByRowId = itemsList.stream()
                    .filter(item -> StringUtil.isNotEmpty(item.getRowid()))
                    .collect(Collectors.groupingBy(CostSurveyTemplateItems::getRowid));

            // 固定表和动态表都需要按父子关系排序
            List<String> sortedRowIds = sortRowIdsByParentChild(itemsByRowId);

            // 获取表头行，计算总列数
            Row headerRow = sheet.getRow(1);
            int totalColumns = headerRow != null ? headerRow.getLastCellNum() : 0;

            int rowNum = 2;
            for (String rowId : sortedRowIds) {
                List<CostSurveyTemplateItems> rowItems = itemsByRowId.get(rowId);
                if (rowItems != null && !rowItems.isEmpty()) {
                    Row dataRow = sheet.createRow(rowNum++);

                    // 填充数据列
                    for (CostSurveyTemplateItems item : rowItems) {
                        Integer colIndex = headerIndexMap.get(item.getHeadersId());
                        if (colIndex != null) {
                            Cell cell = dataRow.createCell(colIndex);
                            cell.setCellValue(item.getRvalue());
                            cell.setCellStyle(dataStyle);
                        }
                    }

                    // 填充 rowId 和 parentId 列
                    if (rowIdColIndex >= 0) {
                        Cell cell = dataRow.createCell(rowIdColIndex);
                        cell.setCellValue(rowId);
                        cell.setCellStyle(dataStyle);
                    }
                    if (parentIdColIndex >= 0) {
                        String parentId = rowItems.get(0).getParentid();
                        if (StringUtil.isNotEmpty(parentId)) {
                            Cell cell = dataRow.createCell(parentIdColIndex);
                            cell.setCellValue(parentId);
                            cell.setCellStyle(dataStyle);
                        } else {
                            // 即使没有值，也要创建单元格并应用样式
                            Cell cell = dataRow.createCell(parentIdColIndex);
                            cell.setCellValue("");
                            cell.setCellStyle(dataStyle);
                        }
                    }

                    // 为所有剩余列（年限列和备注列）创建空单元格并应用样式
                    for (int colIndex = 0; colIndex < totalColumns; colIndex++) {
                        if (dataRow.getCell(colIndex) == null) {
                            Cell cell = dataRow.createCell(colIndex);
                            cell.setCellValue("");
                            cell.setCellStyle(dataStyle);
                        }
                    }
                }
            }
        }
    }

    /**
     * 按父子关系排序rowId（参考核定表实现，按 orderNum 排序）
     */
    private List<String> sortRowIdsByParentChild(Map<String, List<CostSurveyTemplateItems>> itemsByRowId) {
        List<String> result = new ArrayList<>();

        // 构建rowId到parentid的映射
        Map<String, String> rowIdToParentId = new HashMap<>();
        for (Map.Entry<String, List<CostSurveyTemplateItems>> entry : itemsByRowId.entrySet()) {
            String rowId = entry.getKey();
            List<CostSurveyTemplateItems> items = entry.getValue();
            if (!items.isEmpty()) {
                String parentId = items.get(0).getParentid();
                rowIdToParentId.put(rowId, parentId);
            }
        }

        // 找出所有根节点（parentid为空或不存在的），按 orderNum 排序
        Set<String> allRowIds = new HashSet<>(itemsByRowId.keySet());
        List<String> rootRowIds = allRowIds.stream()
                .filter(rowId -> {
                    String parentId = rowIdToParentId.get(rowId);
                    return StringUtil.isEmpty(parentId) || !allRowIds.contains(parentId);
                })
                .sorted((id1, id2) -> {
                    List<CostSurveyTemplateItems> items1 = itemsByRowId.get(id1);
                    List<CostSurveyTemplateItems> items2 = itemsByRowId.get(id2);
                    if (items1 == null || items1.isEmpty()) return 1;
                    if (items2 == null || items2.isEmpty()) return -1;
                    Integer order1 = items1.get(0).getOrderNum();
                    Integer order2 = items2.get(0).getOrderNum();
                    if (order1 == null) return 1;
                    if (order2 == null) return -1;
                    return order1.compareTo(order2);
                })
                .collect(Collectors.toList());

        // 递归添加节点及其子节点
        for (String rootRowId : rootRowIds) {
            addRowIdWithChildren(rootRowId, rowIdToParentId, allRowIds, result, itemsByRowId);
        }

        return result;
    }

    /**
     * 递归添加rowId及其所有子节点（参考核定表实现，按 orderNum 排序）
     */
    private void addRowIdWithChildren(String rowId, Map<String, String> rowIdToParentId,
                                      Set<String> allRowIds, List<String> result, Map<String, List<CostSurveyTemplateItems>> itemsByRowId) {
        result.add(rowId);

        // 找出所有子节点，按 orderNum 排序
        List<String> children = allRowIds.stream()
                .filter(id -> rowId.equals(rowIdToParentId.get(id)))
                .sorted((id1, id2) -> {
                    List<CostSurveyTemplateItems> items1 = itemsByRowId.get(id1);
                    List<CostSurveyTemplateItems> items2 = itemsByRowId.get(id2);
                    if (items1 == null || items1.isEmpty()) return 1;
                    if (items2 == null || items2.isEmpty()) return -1;
                    Integer order1 = items1.get(0).getOrderNum();
                    Integer order2 = items2.get(0).getOrderNum();
                    if (order1 == null) return 1;
                    if (order2 == null) return -1;
                    return order1.compareTo(order2);
                })
                .collect(Collectors.toList());

        // 递归处理子节点
        for (String childRowId : children) {
            addRowIdWithChildren(childRowId, rowIdToParentId, allRowIds, result, itemsByRowId);
        }
    }

    private void fillExcelDataFd(Sheet sheet, List<CostSurveyFdTemplateItems> itemsList,
                                 Map<String, Integer> headerIndexMap, String templateType,
                                 int rowIdColIndex, int parentIdColIndex, CellStyle dataStyle) {
        if ("1".equals(templateType)) {
            // 单表：所有数据在一行
            Row dataRow = sheet.createRow(2);
            for (CostSurveyFdTemplateItems item : itemsList) {
                Integer colIndex = headerIndexMap.get(item.getHeadersId());
                if (colIndex != null) {
                    Cell cell = dataRow.createCell(colIndex);
                    cell.setCellValue(item.getRvalue());
                    cell.setCellStyle(dataStyle);
                }
            }
        } else {
            // 固定表和动态表：按rowid分组
            Map<String, List<CostSurveyFdTemplateItems>> itemsByRowId = itemsList.stream()
                    .filter(item -> StringUtil.isNotEmpty(item.getRowid()))
                    .collect(Collectors.groupingBy(CostSurveyFdTemplateItems::getRowid));

            // 固定表和动态表都需要按父子关系排序
            List<String> sortedRowIds = sortRowIdsByParentChildFd(itemsByRowId);

            // 获取表头行，计算总列数
            Row headerRow = sheet.getRow(1);
            int totalColumns = headerRow != null ? headerRow.getLastCellNum() : 0;

            int rowNum = 2;
            for (String rowId : sortedRowIds) {
                List<CostSurveyFdTemplateItems> rowItems = itemsByRowId.get(rowId);
                if (rowItems != null && !rowItems.isEmpty()) {
                    Row dataRow = sheet.createRow(rowNum++);

                    // 填充数据列
                    for (CostSurveyFdTemplateItems item : rowItems) {
                        Integer colIndex = headerIndexMap.get(item.getHeadersId());
                        if (colIndex != null) {
                            Cell cell = dataRow.createCell(colIndex);
                            cell.setCellValue(item.getRvalue());
                            cell.setCellStyle(dataStyle);
                        }
                    }

                    // 填充 rowId 和 parentId 列
                    if (rowIdColIndex >= 0) {
                        Cell cell = dataRow.createCell(rowIdColIndex);
                        cell.setCellValue(rowId);
                        cell.setCellStyle(dataStyle);
                    }
                    if (parentIdColIndex >= 0) {
                        String parentId = rowItems.get(0).getParentid();
                        if (StringUtil.isNotEmpty(parentId)) {
                            Cell cell = dataRow.createCell(parentIdColIndex);
                            cell.setCellValue(parentId);
                            cell.setCellStyle(dataStyle);
                        } else {
                            // 即使没有值，也要创建单元格并应用样式
                            Cell cell = dataRow.createCell(parentIdColIndex);
                            cell.setCellValue("");
                            cell.setCellStyle(dataStyle);
                        }
                    }

                    // 为所有剩余列（年限列和备注列）创建空单元格并应用样式
                    for (int colIndex = 0; colIndex < totalColumns; colIndex++) {
                        if (dataRow.getCell(colIndex) == null) {
                            Cell cell = dataRow.createCell(colIndex);
                            cell.setCellValue("");
                            cell.setCellStyle(dataStyle);
                        }
                    }
                }
            }
        }
    }

    /**
     * 按父子关系排序rowId（财务数据表）
     */
    private List<String> sortRowIdsByParentChildFd(Map<String, List<CostSurveyFdTemplateItems>> itemsByRowId) {
        List<String> result = new ArrayList<>();

        // 构建rowId到parentid的映射
        Map<String, String> rowIdToParentId = new HashMap<>();
        for (Map.Entry<String, List<CostSurveyFdTemplateItems>> entry : itemsByRowId.entrySet()) {
            String rowId = entry.getKey();
            List<CostSurveyFdTemplateItems> items = entry.getValue();
            if (!items.isEmpty()) {
                String parentId = items.get(0).getParentid();
                rowIdToParentId.put(rowId, parentId);
            }
        }

        // 找出所有根节点（parentid为空或不存在的）
        Set<String> allRowIds = new HashSet<>(itemsByRowId.keySet());
        List<String> rootRowIds = allRowIds.stream()
                .filter(rowId -> {
                    String parentId = rowIdToParentId.get(rowId);
                    return StringUtil.isEmpty(parentId) || !allRowIds.contains(parentId);
                })
                .sorted()
                .collect(Collectors.toList());

        // 递归添加节点及其子节点
        for (String rootRowId : rootRowIds) {
            addRowIdWithChildrenFd(rootRowId, rowIdToParentId, allRowIds, result);
        }

        return result;
    }

    /**
     * 递归添加rowId及其所有子节点（财务数据表）
     */
    private void addRowIdWithChildrenFd(String rowId, Map<String, String> rowIdToParentId,
                                        Set<String> allRowIds, List<String> result) {
        result.add(rowId);

        // 找出所有子节点
        List<String> children = allRowIds.stream()
                .filter(id -> rowId.equals(rowIdToParentId.get(id)))
                .sorted()
                .collect(Collectors.toList());

        // 递归处理子节点
        for (String childRowId : children) {
            addRowIdWithChildrenFd(childRowId, rowIdToParentId, allRowIds, result);
        }
    }

    /**
     * 按父子关系排序rowId（核定表）
     */
    private List<String> sortRowIdsByParentChildVerify(Map<String, List<CostVerifyTemplateItems>> itemsByRowId) {
        List<String> result = new ArrayList<>();

        // 构建rowId到parentid的映射
        Map<String, String> rowIdToParentId = new HashMap<>();
        for (Map.Entry<String, List<CostVerifyTemplateItems>> entry : itemsByRowId.entrySet()) {
            String rowId = entry.getKey();
            List<CostVerifyTemplateItems> items = entry.getValue();
            if (!items.isEmpty()) {
                String parentId = items.get(0).getParentid();
                rowIdToParentId.put(rowId, parentId);
            }
        }

        // 找出所有根节点（parentid为空或不存在的），按 orderNum 排序
        Set<String> allRowIds = new HashSet<>(itemsByRowId.keySet());
        List<String> rootRowIds = allRowIds.stream()
                .filter(rowId -> {
                    String parentId = rowIdToParentId.get(rowId);
                    return StringUtil.isEmpty(parentId) || !allRowIds.contains(parentId);
                })
                .sorted((id1, id2) -> {
                    List<CostVerifyTemplateItems> items1 = itemsByRowId.get(id1);
                    List<CostVerifyTemplateItems> items2 = itemsByRowId.get(id2);
                    if (items1 == null || items1.isEmpty()) return 1;
                    if (items2 == null || items2.isEmpty()) return -1;
                    Integer order1 = items1.get(0).getOrderNum();
                    Integer order2 = items2.get(0).getOrderNum();
                    if (order1 == null) return 1;
                    if (order2 == null) return -1;
                    return order1.compareTo(order2);
                })
                .collect(Collectors.toList());

        // 递归添加节点及其子节点
        for (String rootRowId : rootRowIds) {
            addRowIdWithChildrenVerify(rootRowId, rowIdToParentId, allRowIds, result, itemsByRowId);
        }

        return result;
    }

    /**
     * 递归添加rowId及其所有子节点（核定表）
     */
    private void addRowIdWithChildrenVerify(String rowId, Map<String, String> rowIdToParentId,
                                           Set<String> allRowIds, List<String> result, Map<String, List<CostVerifyTemplateItems>> itemsByRowId) {
        result.add(rowId);

        // 找出所有子节点，按 orderNum 排序
        List<String> children = allRowIds.stream()
                .filter(id -> rowId.equals(rowIdToParentId.get(id)))
                .sorted((id1, id2) -> {
                    List<CostVerifyTemplateItems> items1 = itemsByRowId.get(id1);
                    List<CostVerifyTemplateItems> items2 = itemsByRowId.get(id2);
                    if (items1 == null || items1.isEmpty()) return 1;
                    if (items2 == null || items2.isEmpty()) return -1;
                    Integer order1 = items1.get(0).getOrderNum();
                    Integer order2 = items2.get(0).getOrderNum();
                    if (order1 == null) return 1;
                    if (order2 == null) return -1;
                    return order1.compareTo(order2);
                })
                .collect(Collectors.toList());

        // 递归处理子节点
        for (String childRowId : children) {
            addRowIdWithChildrenVerify(childRowId, rowIdToParentId, allRowIds, result, itemsByRowId);
        }
    }

    private void fillExcelDataVerify(Sheet sheet, List<CostVerifyTemplateItems> itemsList,
                                     Map<String, Integer> headerIndexMap, int rowIdColIndex, int parentIdColIndex, CellStyle dataStyle) {

        if (itemsList == null || itemsList.isEmpty()) {
            return;
        }

        // 按 rowid 分组
        Map<String, List<CostVerifyTemplateItems>> itemsByRowId = itemsList.stream()
                .filter(item -> StringUtil.isNotEmpty(item.getRowid()))
                .collect(Collectors.groupingBy(CostVerifyTemplateItems::getRowid));

        // 按父子关系排序
        List<String> sortedRowIds = sortRowIdsByParentChildVerify(itemsByRowId);

        // 获取表头行，计算总列数
        Row headerRow = sheet.getRow(1);
        int totalColumns = headerRow != null ? headerRow.getLastCellNum() : 0;

        int rowNum = 2;
        int totalCellsWritten = 0;
        int cellsSkippedNoMapping = 0;

        // 构建 rowId -> orderNum 的映射（orderNum 从 1 开始）
        Map<String, Integer> rowIdToOrderNum = new HashMap<>();
        for (int i = 0; i < sortedRowIds.size(); i++) {
            rowIdToOrderNum.put(sortedRowIds.get(i), i + 1);
        }

        for (String rowId : sortedRowIds) {
            List<CostVerifyTemplateItems> rowItems = itemsByRowId.get(rowId);
            if (rowItems != null && !rowItems.isEmpty()) {
                Row dataRow = sheet.createRow(rowNum++);

                // 填充数据列
                for (CostVerifyTemplateItems item : rowItems) {
                    Integer colIndex = headerIndexMap.get(item.getHeadersId());
                    if (colIndex != null) {
                        String cellValue = item.getRvalue();
                        if (StringUtil.isNotEmpty(cellValue)) {
                            Cell cell = dataRow.createCell(colIndex);
                            cell.setCellValue(cellValue);
                            cell.setCellStyle(dataStyle);
                            totalCellsWritten++;
                        }
                    } else {
                        cellsSkippedNoMapping++;
                    }
                }

                // 填充 rowId 和 parentId 列
                if (rowIdColIndex >= 0) {
                    Cell cell = dataRow.createCell(rowIdColIndex);
                    cell.setCellValue(rowId);
                    cell.setCellStyle(dataStyle);
                }
                if (parentIdColIndex >= 0) {
                    String parentId = rowItems.get(0).getParentid();
                    if (StringUtil.isNotEmpty(parentId)) {
                        Cell cell = dataRow.createCell(parentIdColIndex);
                        cell.setCellValue(parentId);
                        cell.setCellStyle(dataStyle);
                    } else {
                        // 即使没有值，也要创建单元格并应用样式
                        Cell cell = dataRow.createCell(parentIdColIndex);
                        cell.setCellValue("");
                        cell.setCellStyle(dataStyle);
                    }
                }

                // 为所有剩余列创建空单元格并应用样式
                for (int colIndex = 0; colIndex < totalColumns; colIndex++) {
                    if (dataRow.getCell(colIndex) == null) {
                        Cell cell = dataRow.createCell(colIndex);
                        cell.setCellValue("");
                        cell.setCellStyle(dataStyle);
                    }
                }
            }
        }

        System.out.println("数据填充完成 - 行数: " + (rowNum - 2) + ", 单元格: " + totalCellsWritten + ", 跳过: " + cellsSkippedNoMapping);
    }


    private String getCellStringValue(Cell cell) {
        if (cell == null) return "";

        try {
            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 格式化日期，根据单元格的格式判断是日期还是日期时间
                    java.util.Date dateValue = cell.getDateCellValue();
                    java.text.SimpleDateFormat dateFormat;

                    // 检查是否包含时间信息（时分秒不全为0）
                    java.util.Calendar cal = java.util.Calendar.getInstance();
                    cal.setTime(dateValue);
                    boolean hasTime = cal.get(java.util.Calendar.HOUR_OF_DAY) != 0 ||
                                     cal.get(java.util.Calendar.MINUTE) != 0 ||
                                     cal.get(java.util.Calendar.SECOND) != 0;

                    if (hasTime) {
                        dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    } else {
                        dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
                    }
                    return dateFormat.format(dateValue);
                } else {
                    double numericValue = cell.getNumericCellValue();
                    return numericValue == (long) numericValue ?
                            String.valueOf((long) numericValue) : String.valueOf(numericValue);
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    // 先尝试获取公式计算后的值类型
                    switch (cell.getCachedFormulaResultType()) {
                        case STRING:
                            return cell.getStringCellValue();
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                // 处理公式结果为日期的情况
                                java.util.Date dateValue = cell.getDateCellValue();
                                java.text.SimpleDateFormat dateFormat;
                                java.util.Calendar cal = java.util.Calendar.getInstance();
                                cal.setTime(dateValue);
                                boolean hasTime = cal.get(java.util.Calendar.HOUR_OF_DAY) != 0 ||
                                                 cal.get(java.util.Calendar.MINUTE) != 0 ||
                                                 cal.get(java.util.Calendar.SECOND) != 0;
                                if (hasTime) {
                                    dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                } else {
                                    dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
                                }
                                return dateFormat.format(dateValue);
                            } else {
                                double numericValue = cell.getNumericCellValue();
                                return numericValue == (long) numericValue ?
                                        String.valueOf((long) numericValue) : String.valueOf(numericValue);
                            }
                        case BOOLEAN:
                            return String.valueOf(cell.getBooleanCellValue());
                        default:
                            return "";
                    }
                } catch (Exception e) {
                    // 如果无法获取公式计算结果，返回空字符串
                    return "";
                }
                default:
                    return "";
            }
        } catch (Exception e) {
            // 如果读取单元格时发生任何异常，返回空字符串并记录错误
            System.err.println("Error reading cell at row " + cell.getRowIndex() +
                              ", column " + cell.getColumnIndex() + ": " + e.getMessage());
            return "";
        }
    }

    private boolean isEmptyRow(Row row) {
        for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
            Cell cell = row.getCell(colIndex);
            if (cell != null && StringUtil.isNotEmpty(getCellStringValue(cell))) {
                return false;
            }
        }
        return true;
    }


    private CostSurveyTemplateUploadData createUploadData(String surveyTemplateId, String taskId,
                                                          String refId, String rowid,
                                                          Object header,
                                                          String value, String periodRecordId, String type) {
        CostSurveyTemplateUploadData uploadData = new CostSurveyTemplateUploadData();
        uploadData.setSurveyTemplateId(surveyTemplateId);
        uploadData.setTaskId(taskId);
        uploadData.setRefId(refId);
        uploadData.setType(type);
        uploadData.setRowid(rowid);

        String fieldEname = null;
        String fieldName = null;

        if (header instanceof CostSurveyTemplateHeaders) {
            CostSurveyTemplateHeaders h = (CostSurveyTemplateHeaders) header;
            fieldEname = h.getFieldEname();
            fieldName = h.getFieldName();
        } else if (header instanceof CostSurveyFdTemplateHeaders) {
            CostSurveyFdTemplateHeaders h = (CostSurveyFdTemplateHeaders) header;
            fieldEname = h.getFieldEname();
            fieldName = h.getFieldName();
        } else if (header instanceof CostVerifyTemplateHeaders) {
            CostVerifyTemplateHeaders h = (CostVerifyTemplateHeaders) header;
            fieldEname = h.getFieldEname();
            fieldName = h.getFieldName();
        }

        uploadData.setRkey(fieldName);
        uploadData.setRvalue(value);
        if (StringUtil.isNotEmpty(periodRecordId)) {
            uploadData.setPeriodRecordId(periodRecordId);
        }
        uploadData.setIsDeleted("0");
        return uploadData;
    }

    /**
     * 校验Excel表头是否与导出时一致（成本调查表）
     */
    private List<String> validateExcelHeaders(Row headerRow, List<CostSurveyTemplateHeaders> headersList,
                                               String taskId, String templateType, String type,
                                               Integer rowIdColumnIndex, Integer parentIdColumnIndex,
                                               Map<String, Integer> auditPeriodColumnMap, Integer remarkColumnIndex) {
        List<String> errors = new ArrayList<>();

        // 1. 构建期望的表头列表
        Set<String> expectedHeaders = new HashSet<>();
        for (CostSurveyTemplateHeaders header : headersList) {
            if (StringUtil.isEmpty(header.getShowVisible()) || "1".equals(header.getShowVisible())) {
                expectedHeaders.add(header.getFieldName().trim());
            }
        }

        // 2. 如果是固定表，添加年限列和备注列
        if ("2".equals(templateType)) {
            CostProjectTask task = costProjectTaskManager.getById(taskId);
            if (task != null) {
                CostProjectApproval approval = costProjectApprovalManager.getById(task.getProjectId());
                if (approval != null && StringUtil.isNotEmpty(approval.getAuditPeriod())) {
                    String[] periods = approval.getAuditPeriod().split(",");
                    for (String period : periods) {
                        expectedHeaders.add(period.trim());
                    }
                    expectedHeaders.add("备注");
                }
            }
        }

        // 3. 如果是固定表或动态表，添加行ID和父行ID
        if ("2".equals(templateType) || "3".equals(templateType)) {
            expectedHeaders.add("行ID");
            expectedHeaders.add("父行ID");
        }

        // 4. 读取Excel中的实际表头
        Set<String> actualHeaders = new HashSet<>();
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            Cell cell = headerRow.getCell(i);
            if (cell != null) {
                String cellValue = getCellStringValue(cell);
                if (StringUtil.isNotEmpty(cellValue)) {
                    actualHeaders.add(cellValue.trim());
                }
            }
        }

        // 5. 检查是否有多余的表头
        Set<String> extraHeaders = new HashSet<>(actualHeaders);
        extraHeaders.removeAll(expectedHeaders);
        if (!extraHeaders.isEmpty()) {
            errors.add(String.format("Excel文件包含多余的表头列：%s", String.join("、", extraHeaders)));
        }

        // 6. 检查是否缺少必需的表头
        Set<String> missingHeaders = new HashSet<>(expectedHeaders);
        missingHeaders.removeAll(actualHeaders);
        if (!missingHeaders.isEmpty()) {
            errors.add(String.format("Excel文件缺少必需的表头列：%s", String.join("、", missingHeaders)));
        }

        return errors;
    }

    /**
     * 校验Excel表头是否与导出时一致（财务数据表）
     */
    private List<String> validateExcelHeadersFd(Row headerRow, List<CostSurveyFdTemplateHeaders> headersList,
                                                 String taskId, String templateType, String type,
                                                 Integer rowIdColumnIndex, Integer parentIdColumnIndex,
                                                 Map<String, Integer> auditPeriodColumnMap, Integer remarkColumnIndex) {
        List<String> errors = new ArrayList<>();

        // 1. 构建期望的表头列表
        Set<String> expectedHeaders = new HashSet<>();
        for (CostSurveyFdTemplateHeaders header : headersList) {
            if (StringUtil.isEmpty(header.getShowVisible()) || "1".equals(header.getShowVisible())) {
                expectedHeaders.add(header.getFieldName().trim());
            }
        }

        // 2. 如果是固定表，添加年限列和备注列
        if ("2".equals(templateType)) {
            CostProjectTask task = costProjectTaskManager.getById(taskId);
            if (task != null) {
                CostProjectApproval approval = costProjectApprovalManager.getById(task.getProjectId());
                if (approval != null && StringUtil.isNotEmpty(approval.getAuditPeriod())) {
                    String[] periods = approval.getAuditPeriod().split(",");
                    for (String period : periods) {
                        expectedHeaders.add(period.trim());
                    }
                    expectedHeaders.add("备注");
                }
            }
        }

        // 3. 如果是固定表或动态表，添加行ID和父行ID
        if ("2".equals(templateType) || "3".equals(templateType)) {
            expectedHeaders.add("行ID");
            expectedHeaders.add("父行ID");
        }

        // 4. 读取Excel中的实际表头
        Set<String> actualHeaders = new HashSet<>();
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            Cell cell = headerRow.getCell(i);
            if (cell != null) {
                String cellValue = getCellStringValue(cell);
                if (StringUtil.isNotEmpty(cellValue)) {
                    actualHeaders.add(cellValue.trim());
                }
            }
        }

        // 5. 检查是否有多余的表头
        Set<String> extraHeaders = new HashSet<>(actualHeaders);
        extraHeaders.removeAll(expectedHeaders);
        if (!extraHeaders.isEmpty()) {
            errors.add(String.format("Excel文件包含多余的表头列：%s", String.join("、", extraHeaders)));
        }

        // 6. 检查是否缺少必需的表头
        Set<String> missingHeaders = new HashSet<>(expectedHeaders);
        missingHeaders.removeAll(actualHeaders);
        if (!missingHeaders.isEmpty()) {
            errors.add(String.format("Excel文件缺少必需的表头列：%s", String.join("、", missingHeaders)));
        }

        return errors;
    }

    /**
     * 根据cellCode获取对应的项目名称
     * @param cellCode 单元格编码，如 "A1"
     * @param type 类型：1-成本调查表 2-财务数据表 3-核定表
     * @return 项目名称，如 "基本工资"
     */
    private String getCellCodeItemName(String cellCode, String type) {
        try {
            List<? extends Object> allItems = null;

            // 根据type获取所有模板项
            switch (type) {
                case "1": {
                    // 成本调查表 - 需要获取所有模板的items
                    List<CostSurveyTemplate> templates = costSurveyTemplateManager.list();
                    for (CostSurveyTemplate template : templates) {
                        CostSurveyTemplateVersion version = costSurveyTemplateVersionManager.selectCurrentVersion(template.getSurveyTemplateId());
                        if (version != null) {
                            List<CostSurveyTemplateItems> items = costSurveyTemplateItemsDao.selectBySurveyTemplateIdAndVersion(
                                    template.getSurveyTemplateId(), version.getId());
                            for (CostSurveyTemplateItems item : items) {
                                if (cellCode.equals(item.getCellCode())) {
                                    return item.getRvalue(); // 返回项目名称
                                }
                            }
                        }
                    }
                    break;
                }
                case "2": {
                    // 财务数据表
                    List<CostSurveyFdTemplate> templates = costSurveyFdTemplateManager.list();
                    for (CostSurveyFdTemplate template : templates) {
                        CostSurveyFdTemplateVersion version = costSurveyFdTemplateVersionManager.selectCurrentVersion(template.getSurveyTemplateId());
                        if (version != null) {
                            List<CostSurveyFdTemplateItems> items = costSurveyFdTemplateItemsDao.selectBySurveyTemplateIdAndVersion(
                                    template.getSurveyTemplateId(), version.getId());
                            for (CostSurveyFdTemplateItems item : items) {
                                if (cellCode.equals(item.getCellCode())) {
                                    return item.getRvalue(); // 返回项目名称
                                }
                            }
                        }
                    }
                    break;
                }
                case "3": {
                    // 核定表
                    List<CostVerifyTemplate> templates = costVerifyTemplateManager.list();
                    for (CostVerifyTemplate template : templates) {
                        List<CostVerifyTemplateItems> items = costVerifyTemplateItemsDao.selectByVerifyTemplateId(
                                template.getSurveyTemplateId(), null);
                        for (CostVerifyTemplateItems item : items) {
                            if (cellCode.equals(item.getCellCode())) {
                                return item.getRvalue(); // 返回项目名称
                            }
                        }
                    }
                    break;
                }
            }
        } catch (Exception e) {
            System.err.println("获取cellCode[" + cellCode + "]对应的项目名称失败: " + e.getMessage());
        }

        return cellCode; // 如果找不到，返回cellCode本身
    }

    /**
     * 构建带项目名称的计算过程字符串
     * @param formula 原始公式，如 "(A1+A2+A3)"
     * @param referencedCellsDebug 引用单元格的值列表，如 ["A1=10", "A2=20", "A3=30"]
     * @param cellCodeToItemNameMap cellCode到项目名称的映射
     * @return 计算过程字符串，如 "基本工资(10) + 津贴(20) + 奖金(30)"
     */
    private String buildCalculationProcessWithNames(String formula, List<String> referencedCellsDebug,
                                                     Map<String, String> cellCodeToItemNameMap) {
        String process = formula;

        // 将引用单元格替换为 "项目名称(值)" 的格式
        for (String cellDebug : referencedCellsDebug) {
            String[] parts = cellDebug.split("=");
            if (parts.length == 2) {
                String cellCode = parts[0];
                String value = parts[1];
                String itemName = cellCodeToItemNameMap.getOrDefault(cellCode, cellCode);

                // 构建替换字符串：基本工资(10)
                String replacement = itemName + "(" + value + ")";

                // 使用正则表达式替换，确保只替换完整的单元格引用
                process = process.replaceAll("\\b" + cellCode + "\\b", replacement);
            }
        }

        return process;
    }

    /**
     * 创建标题样式
     */
    private CellStyle createTitleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 字体设置
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);
        font.setBold(true);
        style.setFont(font);

        // 对齐方式
        style.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);
        style.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);

        return style;
    }

    /**
     * 创建表头样式
     */
    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 字体设置
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        font.setBold(true);
        style.setFont(font);

        // 对齐方式
        style.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);
        style.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);

        // 边框
        style.setBorderTop(org.apache.poi.ss.usermodel.BorderStyle.THIN);
        style.setBorderBottom(org.apache.poi.ss.usermodel.BorderStyle.THIN);
        style.setBorderLeft(org.apache.poi.ss.usermodel.BorderStyle.THIN);
        style.setBorderRight(org.apache.poi.ss.usermodel.BorderStyle.THIN);

        // 背景色（浅灰色）
        style.setFillForegroundColor(org.apache.poi.ss.usermodel.IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(org.apache.poi.ss.usermodel.FillPatternType.SOLID_FOREGROUND);

        return style;
    }

    /**
     * 创建数据样式
     */
    private CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 字体设置
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        style.setFont(font);

        // 对齐方式
        style.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);
        style.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);

        // 边框
        style.setBorderTop(org.apache.poi.ss.usermodel.BorderStyle.THIN);
        style.setBorderBottom(org.apache.poi.ss.usermodel.BorderStyle.THIN);
        style.setBorderLeft(org.apache.poi.ss.usermodel.BorderStyle.THIN);
        style.setBorderRight(org.apache.poi.ss.usermodel.BorderStyle.THIN);

        return style;
    }
}