cbjs-mvue-master/assistMg/src/main/java/com/hotent/enterpriseDeclare/controller/material/CostProjectTaskSurveyGenericController.java

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.model.CostCatalogSurvey;
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.baseInfo.manager.CostDictDataManager;
import com.hotent.baseInfo.model.CostDictData;
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.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.net.URLEncoder;
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;


    // 财务数据表相关
    @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;

    @Autowired
    private CostDictDataManager costDictDataManager;

    /**
     * 企业报送-调查表-列表
     *
     * @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);
                     List<CostSurveyTemplate> costSurveyTemplates = costSurveyTemplateManager.taskListByCatalogId(task.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);
                    }
                }
                return CommonResult.ok().value(costSurveyTemplateUploadManager.listByTaskId(taskId));
            }
            case "2": {
                // 财务数据表逻辑
                QueryWrapper<CostProjectTaskMaterial> wrapper = new QueryWrapper<>();
                wrapper.eq("task_id", taskId);
                List<CostProjectTaskMaterial> materialList = costProjectTaskMaterialManager.list(wrapper);
                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());
                // 使用已有的manager方法替代新接口
                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());
                // 使用已有的manager方法替代新接口
                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不能为空");
            }
        }

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

        costSurveyTemplateUploadDataManager.saveData(dataList);

        // 只有成本调查表需要更新upload状态dataList = {ArrayList@29238}  size = 27
        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");
                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());
        }

        wrapper.orderByAsc("rowid", "rkey");
        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) {
            record.setIsDeleted("1");
            costAuditPeriodRecordManager.updateById(record);

            // 同时删除该期间下的所有上传数据
            QueryWrapper<CostSurveyTemplateUploadData> wrapper = new QueryWrapper<>();
            wrapper.eq("period_record_id", id)
                    .eq("type", type);
            CostSurveyTemplateUploadData updateData = new CostSurveyTemplateUploadData();
            updateData.setIsDeleted("1");
            costSurveyTemplateUploadDataManager.update(updateData, 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 = currentVersion.getSurveyTemplateName() != null ?
                        currentVersion.getSurveyTemplateName() : "成本调查表";
                Sheet sheet = workbook.createSheet(sheetName);

                Row headerRow = sheet.createRow(0);
                int colIndex = 0;

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

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

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

                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);
                }

                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 = sheetName + "_" + System.currentTimeMillis() + ".xlsx";
                response.setHeader("Content-Disposition", "attachment; filename=" +
                        URLEncoder.encode(fileName, "UTF-8"));

                workbook.write(response.getOutputStream());
                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 = currentVersion.getSurveyTemplateName() != null ?
                        currentVersion.getSurveyTemplateName() : "财务数据表";
                Sheet sheet = workbook.createSheet(sheetName);

                Row headerRow = sheet.createRow(0);
                int colIndex = 0;

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

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

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

                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);
                }

                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 = sheetName + "_" + System.currentTimeMillis() + ".xlsx";
                response.setHeader("Content-Disposition", "attachment; filename=" +
                        URLEncoder.encode(fileName, "UTF-8"));

                workbook.write(response.getOutputStream());
                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);

                // 添加数据验证日志
                System.out.println("核定表导出 - 模板ID: " + surveyTemplateId + ", 表头数量: " + headersList.size() + ", 数据项数量: " + (itemsList != null ? itemsList.size() : 0));

                // 在创建 Excel 之前，先补充缺失的表头
                if (itemsList != null && !itemsList.isEmpty()) {
                    // 收集所有数据项中的 headersId
                    Set<String> allHeadersIds = itemsList.stream()
                            .map(CostVerifyTemplateItems::getHeadersId)
                            .filter(StringUtil::isNotEmpty)
                            .collect(Collectors.toSet());

                    System.out.println("数据项中的唯一 headersId 数量: " + allHeadersIds.size());

                    // 检查是否有 headersId 在表头中找不到
                    Set<String> existingHeaderIds = headersList.stream()
                            .map(CostVerifyTemplateHeaders::getId)
                            .collect(Collectors.toSet());

                    Set<String> missingHeaderIds = new HashSet<>(allHeadersIds);
                    missingHeaderIds.removeAll(existingHeaderIds);

                    if (!missingHeaderIds.isEmpty()) {
                        System.err.println("警告：发现 " + missingHeaderIds.size() + " 个数据项的 headersId 在表头中不存在:");
                        for (String missingId : missingHeaderIds) {
                            System.err.println("  - " + missingId);
                            // 查询这个缺失的表头信息
                            CostVerifyTemplateHeaders missingHeader = costVerifyTemplateHeadersDao.selectById(missingId);
                            if (missingHeader != null) {
                                System.err.println("    找到表头: " + missingHeader.getFieldName() + ", showVisible: " + missingHeader.getShowVisible());
                                // 将缺失的表头添加到列表中
                                headersList.add(missingHeader);
                            } else {
                                System.err.println("    表头不存在于数据库中");
                            }
                        }
                        System.out.println("已补充缺失的表头，新的表头总数: " + headersList.size());
                    }
                }

                // 打印表头统计
                System.out.println("最终表头列表: 共 " + headersList.size() + " 列");

                Workbook workbook = new XSSFWorkbook();
                String sheetName = template.getSurveyTemplateName() != null ? template.getSurveyTemplateName() : "核定表";

                // Excel sheet 名称不能包含以下字符: : \ / ? * [ ]
                // 并且长度不能超过 31 个字符
                sheetName = sheetName.replaceAll("[:\\\\/*?\\[\\]]", "_");
                if (sheetName.length() > 31) {
                    sheetName = sheetName.substring(0, 31);
                }
                System.out.println("Sheet 名称: " + sheetName);

                Sheet sheet = workbook.createSheet(sheetName);

                // 创建表头行
                Row headerRow = sheet.createRow(0);
                int colIndex = 0;

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

                // 添加行ID列和父行ID列（固定表需要）
                int rowIdColIndex = colIndex;
                headerRow.createCell(colIndex++).setCellValue("行ID");
                int parentIdColIndex = colIndex;
                headerRow.createCell(colIndex++).setCellValue("父行ID");

                // 填充数据
                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);
                        System.out.println("表头映射 - ID: " + headersList.get(i).getId() + ", 列索引: " + i + ", 字段名: " + headersList.get(i).getFieldName());
                    }

                    // 打印 items 的 headersId 信息
                    System.out.println("数据项的 headersId 分布:");
                    Map<String, Long> headersIdCount = itemsList.stream()
                            .collect(Collectors.groupingBy(
                                    item -> item.getHeadersId() != null ? item.getHeadersId() : "null",
                                    Collectors.counting()
                            ));
                    headersIdCount.forEach((headersId, count) ->
                            System.out.println("  headersId: " + headersId + ", 数量: " + count)
                    );

                    fillExcelDataVerify(sheet, itemsList, headerIndexMap, rowIdColIndex, parentIdColIndex);
                }

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

                // 隐藏行ID和父行ID列
                sheet.setColumnHidden(rowIdColIndex, true);
                sheet.setColumnHidden(parentIdColIndex, true);

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

                System.out.println("开始写入 Excel 文件到输出流...");
                workbook.write(response.getOutputStream());
                workbook.close();
                System.out.println("Excel 文件写入完成");
                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;
        }
        switch (type) {
            // 成本调查表逻辑
            case "1": {
                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文件没有数据");
                    }

                    Row headerRow = sheet.getRow(0);
                    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<CostSurveyTemplateUploadData> dataList = new ArrayList<>();
                    Map<String, Integer> rowIdToExcelRowMap = new HashMap<>(); // rowId -> Excel行号映射
                    int dataRowCount = 0;

                    for (int rowIndex = 1; 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不能为空
                                // 如果为空则使用行号作为兜底
                                currentRowId = "row_" + rowIndex;
                            }
                        } else {
                            // 没有行ID列，使用原有逻辑
                            currentRowId = "1".equals(templateType) ?
                                    "row_" + System.currentTimeMillis() : "row_" + rowIndex;
                        }

                        // 记录 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, CostSurveyTemplateHeaders> entry : columnIndexMap.entrySet()) {
                            Cell cell = dataRow.getCell(entry.getKey());
                            String cellValue = getCellStringValue(cell);

                            // 即使是空值也要添加，保证数据完整性
                            CostSurveyTemplateUploadData uploadData = createUploadData(
                                    surveyTemplateId, taskId, uploadId, currentRowId,
                                    entry.getValue(), cellValue != null ? cellValue : "", periodRecordId, type);
                            uploadData.setUploadId(uploadId);
                            uploadData.setRefId(refId);
                            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);

                            // 即使是空值也要添加，保证数据完整性
                            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 != null ? cellValue.trim() : "");
                            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);

                            // 即使是空值也要添加，保证数据完整性
                            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 != null ? remarkValue.trim() : "");
                            if (StringUtil.isNotEmpty(periodRecordId)) {
                                remarkData.setPeriodRecordId(periodRecordId);
                            }
                            remarkData.setIsDeleted("0");
                            dataList.add(remarkData);
                        }

                        dataRowCount++;
                    }

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

                    verifyImportData(dataList, type, surveyTemplateId, rowIdToExcelRowMap);
                    costSurveyTemplateUploadDataManager.saveData(dataList);

                    // 更新上传状态
                    CostSurveyTemplateUpload upload = costSurveyTemplateUploadManager.getById(uploadId);
                    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": {
                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("未找到启用的模板版本");
                }

                // 使用已有的manager方法替代新接口
                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文件没有数据");
                    }

                    Row headerRow = sheet.getRow(0);
                    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<CostSurveyTemplateUploadData> dataList = new ArrayList<>();
                    Map<String, Integer> rowIdToExcelRowMap = new HashMap<>(); // rowId -> Excel行号映射
                    int dataRowCount = 0;

                    for (int rowIndex = 1; 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不能为空
                                // 如果为空则使用行号作为兜底
                                currentRowId = "row_" + rowIndex;
                            }
                        } else {
                            // 没有行ID列，使用原有逻辑
                            currentRowId = "1".equals(templateType) ?
                                    "row_" + System.currentTimeMillis() : "row_" + rowIndex;
                        }

                        // 记录 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);

                            // 即使是空值也要添加，保证数据完整性
                            CostSurveyTemplateUploadData uploadData = createUploadData(
                                    surveyTemplateId, taskId, uploadId, currentRowId,
                                    entry.getValue(), cellValue != null ? cellValue : "", periodRecordId, type);
                            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);

                            // 即使是空值也要添加，保证数据完整性
                            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); // 年限，如：2024
                            periodData.setRvalue(cellValue != null ? cellValue.trim() : "");
                            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);

                            // 即使是空值也要添加，保证数据完整性
                            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 != null ? remarkValue.trim() : "");
                            if (StringUtil.isNotEmpty(periodRecordId)) {
                                remarkData.setPeriodRecordId(periodRecordId);
                            }
                            remarkData.setIsDeleted("0");
                            dataList.add(remarkData);
                        }
                    }

                    dataRowCount++;


                    if (dataList.isEmpty()) {
                        return CommonResult.<String>error().message("Excel文件中没有有效数据");
                    }
                    verifyImportData(dataList, type, surveyTemplateId, rowIdToExcelRowMap);
                    costSurveyTemplateUploadDataManager.saveData(dataList);
                    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": {
                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文件没有数据");
                    }

                    Row headerRow = sheet.getRow(0);
                    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<>(); // rowId -> Excel行号映射
                    int importRowCount = 0;

                    for (int rowIndex = 1; 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不能为空
                                // 如果为空则使用行号作为兜底
                                currentRowId = "row_" + rowIndex;
                            }
                        } else {
                            // 没有行ID列，使用行号
                            currentRowId = "row_" + rowIndex;
                        }

                        // 记录 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, 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
                                );
                                dataList.add(uploadData);
                            }
                        }
                        importRowCount++;
                    }

                    if (dataList.isEmpty()) {
                        return CommonResult.<String>error().message("Excel文件没有有效数据");
                    }
                    verifyImportData(dataList, type, surveyTemplateId, rowIdToExcelRowMap);
                    // 保存
                    costSurveyTemplateUploadDataManager.saveData(dataList);


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

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


    /**
     * 获取友好的行号显示
     */
    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 void verifyImportData(List<CostSurveyTemplateUploadData> dataList, String type, String surveyTemplateId, Map<String, Integer> rowIdToExcelRowMap) throws Exception {
        if (dataList == null || dataList.isEmpty()) {
            return;
        }

        // 获取模板类型（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;
        }

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

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

            // 根据类型获取模板项
            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不是从当前模板导出的，直接报错
                        throw new IllegalArgumentException(String.format("行[%s] 的行ID在模板中不存在，请使用系统导出的最新模板文件", rowid));
                    }

                    // 处理年限列的数据（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;
                    }

                    // 针对每个年限分别校验（直接抛异常，不需要检查errors）
                    for (String period : periods) {
                        validateRowFormulasForPeriod(rowid, rowData, rowItems, globalCellCodeMap, period, type,rowIdToExcelRowMap);
                    }
                }
            }
        }
    }

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

        // 按 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) {
            throw new IllegalArgumentException(String.format("固定表行数不匹配！模板定义：%d 行，导入数据：%d 行。固定表不允许增加或减少行。",
                    expectedRowCount, actualRowCount));
        }

        // 3. 校验每一行的字段是否完整
        Set<String> expectedFields = headersList.stream()
                .map(header -> getHeaderFieldEname(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);
            extraFields.removeAll(expectedFields);
            // 移除年限列（4位数字的字段，如"2024"）和备注列（remark）
            extraFields.removeIf(field -> field.matches("\\d{4}") || "remark".equals(field));
            if (!extraFields.isEmpty()) {
                throw new IllegalArgumentException(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)) {
                    throw new IllegalArgumentException(String.format("行[%s] 缺少必填字段：%s(%s)。固定表必须包含所有必填字段。",
                            rowid, fieldEname, fieldName));
                }
            }
        }
    }

    /**
     * 获取模板类型
     */
    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 void validateFieldRulesExcludingPeriods(String rowid, Map<String, String> rowData,
                                                    List<? extends Object> headersList, Map<String, Integer> rowIdToExcelRowMap) {
        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 isRequired = getHeaderIsRequired(headerObj);
            String isDict = getHeaderIsDict(headerObj);
            String dictCode = getHeaderDictCode(headerObj);

            // 获取用户输入的值
            String value = rowData.get(fieldEname);

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

            // 1. 必填校验（支持 "1" 和 "true" 两种格式）
            if (("1".equals(isRequired) || "true".equalsIgnoreCase(isRequired)) && StringUtil.isEmpty(value)) {
                System.out.println("  ❌ 必填校验失败！");
                throw new IllegalArgumentException(String.format("%s 字段[%s(%s)]为必填项，不能为空", rowDisplay, fieldEname, fieldName));
            }

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

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

            // 3. 字段类型校验
            if (StringUtil.isNotEmpty(fieldType)) {
                validateFieldType(rowid, fieldEname, fieldName, value, fieldType);
            }

            // 4. 字段长度校验
            if (fieldTypelen != null && fieldTypelen > 0) {
                validateFieldLength(rowid, fieldEname, fieldName, value, fieldType, fieldTypelen, fieldTypenointlen);
            }
        }

        // 注意：年限列（如2024、2025）和备注列（remark）不在headersList中，
        // 它们存储在rowData中但不会被上面的循环处理，因此自动被排除
    }

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

        try {
            // 查询字典数据
            QueryWrapper<CostDictData> wrapper = new QueryWrapper<>();
            wrapper.eq("dict_type", dictCode)
                   .eq("status", "0"); // 只查询正常状态的字典项
            List<CostDictData> dictDataList = costDictDataManager.list(wrapper);

            if (dictDataList == null || dictDataList.isEmpty()) {
                throw new IllegalArgumentException(String.format("行[%s] 字段[%s(%s)]的字典配置[%s]不存在或未配置字典项",
                        rowid, fieldEname, fieldName, dictCode));
            }

            // 检查值是否在字典允许的范围内（支持多选，用逗号分隔）
            String[] values = value.split(",");
            Set<String> validValues = dictDataList.stream()
                    .map(CostDictData::getDictValue)
                    .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()) {
                throw new IllegalArgumentException(String.format("行[%s] 字段[%s(%s)]的值[%s]不在字典[%s]允许的范围内，有效值：%s",
                        rowid, fieldEname, fieldName,
                        String.join(", ", invalidValues),
                        dictCode,
                        String.join(", ", validValues)));
            }
        } catch (IllegalArgumentException e) {
            throw e;
        } catch (Exception e) {
            throw new IllegalArgumentException(String.format("行[%s] 字段[%s(%s)]字典校验异常：%s",
                    rowid, fieldEname, fieldName, e.getMessage()));
        }
    }

    /**
     * 字段类型校验
     */
    private void validateFieldType(String rowDisplay, String fieldEname, String fieldName,
                                   String value, String fieldType) {
        try {
            switch (fieldType.toLowerCase()) {
                case "int":
                case "integer":
                case "bigint":
                    try {
                        Long.parseLong(value);
                    } catch (NumberFormatException e) {
                        throw new IllegalArgumentException(String.format("%s 字段[%s(%s)]类型错误，应为整数，实际值：%s", rowDisplay, fieldEname, fieldName, value));
                    }
                    break;

                case "decimal":
                case "double":
                case "float":
                case "number":
                    try {
                        Double.parseDouble(value);
                    } catch (NumberFormatException e) {
                        throw new IllegalArgumentException(String.format("%s 字段[%s(%s)]类型错误，应为数字，实际值：%s", rowDisplay, fieldEname, fieldName, value));
                    }
                    break;

                case "date":
                    // 日期格式校验（可以根据实际需求调整）
                    if (!value.matches("\\d{4}-\\d{2}-\\d{2}")) {
                        throw new IllegalArgumentException(String.format("%s 字段[%s(%s)]日期格式错误，应为yyyy-MM-dd，实际值：%s", rowDisplay, fieldEname, fieldName, value));
                    }
                    break;

                case "datetime":
                    // 日期时间格式校验
                    if (!value.matches("\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}")) {
                        throw new IllegalArgumentException(String.format("%s 字段[%s(%s)]日期时间格式错误，应为yyyy-MM-dd HH:mm:ss，实际值：%s", rowDisplay, fieldEname, 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(%s)]类型错误，应为布尔值(true/false, 1/0, 是/否)，实际值：%s",
                                rowDisplay, fieldEname, fieldName, value));
                    }
                    break;

                case "varchar":
                case "string":
                case "text":
                    // 字符串类型，无需特殊校验
                    break;

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

    /**
     * 字段长度校验
     */
    private void validateFieldLength(String rowid, String fieldEname, String fieldName, String value,
                                     String fieldType, Integer fieldTypelen, Integer fieldTypenointlen) {
        try {
            switch (fieldType.toLowerCase()) {
                case "varchar":
                case "string":
                case "text":
                    // 字符串长度校验
                    if (value.length() > fieldTypelen) {
                        throw new IllegalArgumentException(String.format("行[%s] 字段[%s(%s)]长度超限，最大长度：%d，实际长度：%d",
                                rowid, fieldEname, fieldName, fieldTypelen, value.length()));
                    }
                    break;

                case "decimal":
                case "double":
                case "float":
                case "number":
                    // 数字精度校验
                    try {
                        double numValue = Double.parseDouble(value);
                        String[] parts = value.split("\\.");

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

                        // 小数部分长度
                        if (parts.length > 1 && fieldTypenointlen != null) {
                            int decimalPartLen = parts[1].length();
                            if (decimalPartLen > fieldTypenointlen) {
                                throw new IllegalArgumentException(String.format("行[%s] 字段[%s(%s)]小数位数超限，最大：%d，实际：%d",
                                        rowid, fieldEname, fieldName, fieldTypenointlen, decimalPartLen));
                            }
                        }
                    } catch (NumberFormatException e) {
                        // 类型校验已经处理过了，这里不重复报错
                    }
                    break;

                default:
                    break;
            }
        } catch (IllegalArgumentException e) {
            throw e;
        } catch (Exception e) {
            throw new IllegalArgumentException(String.format("行[%s] 字段[%s(%s)]长度校验异常：%s", rowid, fieldEname, 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 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 void validateRowFormulasForPeriod(String rowid, Map<String, String> rowData,
                                              List<? extends Object> rowItems, Map<String, String> globalCellCodeMap,
                                              String period, String type,Map<String, Integer> rowIdToExcelRowMap) {
        // 找到该行的计算公式（同一行的所有模板项共享同一个公式）
        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;
        }

        // 检查公式引用的单元格在当前年限是否有任何一个有值
        // 提取公式中的所有单元格引用（如 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<>();
        while (matcher.find()) {
            String referencedCell = matcher.group();
            // 构建 cellCode_年限 的key（如 A1_2024）
            String mapKey = referencedCell + "_" + period;
            String value = globalCellCodeMap.get(mapKey);
            String displayValue = StringUtil.isNotEmpty(value) ? value : "0";
            referencedCellsDebug.add(referencedCell + "=" + displayValue);
            if (StringUtil.isNotEmpty(value)) {
                hasAnyReferencedValue = true;
            }
        }

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

        // 获取用户输入的汇总值（当前年限）
        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 + "]";
                throw new IllegalArgumentException(String.format("%s [%s]列汇总值错误：计算值[%.2f]，填写值[%.2f]",
                        rowDisplay, period + "年", calculatedValue, inputValue));
            } else {
                System.out.println("校验结果: ✓ 通过");
                System.out.println("========================================");
            }
        } catch (IllegalArgumentException e) {
            // 重新抛出校验错误
            throw e;
        } 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("========================================");
            throw new IllegalArgumentException(String.format("行[%s] [%s]列计算错误：%s",
                    rowid, period + "年", e.getMessage()));
        }
    }

    /**
     * 计算公式
     *
     * @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) {
        if ("1".equals(templateType)) {
            Row dataRow = sheet.createRow(1);
            for (CostSurveyTemplateItems item : itemsList) {
                Integer colIndex = headerIndexMap.get(item.getHeadersId());
                if (colIndex != null) {
                    dataRow.createCell(colIndex).setCellValue(item.getRvalue());
                }
            }
        } 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);

            int rowNum = 1;
            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) {
                            dataRow.createCell(colIndex).setCellValue(item.getRvalue());
                        }
                    }

                    // 填充 rowId 和 parentId 列
                    if (rowIdColIndex >= 0) {
                        dataRow.createCell(rowIdColIndex).setCellValue(rowId);
                    }
                    if (parentIdColIndex >= 0) {
                        String parentId = rowItems.get(0).getParentid();
                        if (StringUtil.isNotEmpty(parentId)) {
                            dataRow.createCell(parentIdColIndex).setCellValue(parentId);
                        }
                    }
                }
            }
        }
    }

    /**
     * 按父子关系排序rowId
     */
    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为空或不存在的）
        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) {
            addRowIdWithChildren(rootRowId, rowIdToParentId, allRowIds, result);
        }

        return result;
    }

    /**
     * 递归添加rowId及其所有子节点
     */
    private void addRowIdWithChildren(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) {
            addRowIdWithChildren(childRowId, rowIdToParentId, allRowIds, result);
        }
    }

    private void fillExcelDataFd(Sheet sheet, List<CostSurveyFdTemplateItems> itemsList,
                                 Map<String, Integer> headerIndexMap, String templateType,
                                 int rowIdColIndex, int parentIdColIndex) {
        if ("1".equals(templateType)) {
            // 单表：所有数据在一行
            Row dataRow = sheet.createRow(1);
            for (CostSurveyFdTemplateItems item : itemsList) {
                Integer colIndex = headerIndexMap.get(item.getHeadersId());
                if (colIndex != null) {
                    dataRow.createCell(colIndex).setCellValue(item.getRvalue());
                }
            }
        } 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);

            int rowNum = 1;
            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) {
                            dataRow.createCell(colIndex).setCellValue(item.getRvalue());
                        }
                    }

                    // 填充 rowId 和 parentId 列
                    if (rowIdColIndex >= 0) {
                        dataRow.createCell(rowIdColIndex).setCellValue(rowId);
                    }
                    if (parentIdColIndex >= 0) {
                        String parentId = rowItems.get(0).getParentid();
                        if (StringUtil.isNotEmpty(parentId)) {
                            dataRow.createCell(parentIdColIndex).setCellValue(parentId);
                        }
                    }
                }
            }
        }
    }

    /**
     * 按父子关系排序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为空或不存在的）
        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) {
            addRowIdWithChildrenVerify(rootRowId, rowIdToParentId, allRowIds, result);
        }

        return result;
    }

    /**
     * 递归添加rowId及其所有子节点（核定表）
     */
    private void addRowIdWithChildrenVerify(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) {
            addRowIdWithChildrenVerify(childRowId, rowIdToParentId, allRowIds, result);
        }
    }

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

        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);

        int rowNum = 1;
        int totalCellsWritten = 0;
        int cellsSkippedNoMapping = 0;

        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)) {
                            dataRow.createCell(colIndex).setCellValue(cellValue);
                            totalCellsWritten++;
                        }
                    } else {
                        cellsSkippedNoMapping++;
                    }
                }

                // 填充 rowId 和 parentId 列
                if (rowIdColIndex >= 0) {
                    dataRow.createCell(rowIdColIndex).setCellValue(rowId);
                }
                if (parentIdColIndex >= 0) {
                    String parentId = rowItems.get(0).getParentid();
                    if (StringUtil.isNotEmpty(parentId)) {
                        dataRow.createCell(parentIdColIndex).setCellValue(parentId);
                    }
                }
            }
        }

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


    private String getCellStringValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } 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 {
                    return cell.getStringCellValue();
                } catch (Exception e) {
                    try {
                        return String.valueOf(cell.getNumericCellValue());
                    } catch (Exception ex) {
                        return "";
                    }
                }
            default:
                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(StringUtil.isNotEmpty(fieldEname) ? fieldEname : fieldName);
        uploadData.setRvalue(value);
        if (StringUtil.isNotEmpty(periodRecordId)) {
            uploadData.setPeriodRecordId(periodRecordId);
        }
        uploadData.setIsDeleted("0");
        return uploadData;
    }
}