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 uploadList = costSurveyTemplateUploadManager.listByTaskId(taskId); //创建记录 if (uploadList.isEmpty()) { CostProjectTask task = costProjectTaskManager.getById(taskId); List 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 wrapper = new QueryWrapper<>(); wrapper.eq("task_id", taskId); List 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 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.ok().value(new CostItemData()); } List items = costSurveyTemplateItemsDao.selectBySurveyTemplateIdAndVersion(surveyTemplateId, versionTemplate.getId()); // 使用已有的manager方法替代新接口 List headList = costSurveyTemplateHeadersManager.listVisibleBySurveyTemplateIdAndVersion(surveyTemplateId, versionTemplate.getId()); CostItemData costItemData = buildCostItemData(items, headList); return CommonResult.ok().value(costItemData); } case "2": { // 财务数据表逻辑 CostSurveyFdTemplateVersion versionTemplate = costSurveyFdTemplateVersionManager.selectCurrentVersion(surveyTemplateId); if (versionTemplate == null) { return CommonResult.ok().value(new CostItemData()); } List items = costSurveyFdTemplateItemsDao.selectBySurveyTemplateIdAndVersion(surveyTemplateId, versionTemplate.getId()); // 使用已有的manager方法替代新接口 List headList = costSurveyFdTemplateHeadersManager.listVisibleBySurveyTemplateIdAndVersion(surveyTemplateId, versionTemplate.getId()); CostItemData costItemData = buildCostItemDataFd(items, headList); return CommonResult.ok().value(costItemData); } default: return CommonResult.error().message("不支持的类型"); } } /** * 企业报送-调查表-在线填报 * * @param dataList 数据列表 * @return 保存结果 */ @PostMapping(value = "/saveUploadData") @ApiOperation(value = "保存调查表模板上传数据", httpMethod = "POST") public CommonResult saveUploadData( @ApiParam(name = "dataList", value = "数据列表", required = true) @RequestBody List dataList) throws Exception { System.err.println(JSON.toJSON(dataList)); if (dataList == null || dataList.isEmpty()) { return CommonResult.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.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.ok().message("保存数据成功"); } /** * 企业报送-调查表-在线填报记录查看 * * @return 数据列表 */ @PostMapping(value = "/getUploadData") @ApiOperation(value = "查询调查表模板上传数据", httpMethod = "POST") public CommonResult> getUploadData( @ApiParam(name = "queryData", value = "查询条件", required = true) @RequestBody CostSurveyTemplateUploadData queryData) throws Exception { if (queryData == null) { return CommonResult.>error().message("uploadId不能为空"); } // 根据refId查询数据 QueryWrapper 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 dataList = costSurveyTemplateUploadDataManager.list(wrapper); for (CostSurveyTemplateUploadData costSurveyTemplateUploadData : dataList) { costSurveyTemplateUploadData.setUploadId(costSurveyTemplateUploadData.getRefId()); } return CommonResult.>ok().value(dataList); } /** * 企业报送-调查表-动态表-新增监审期间记录 * * @param record 监审期间记录 * @return 保存结果 */ @PostMapping(value = "/addPeriodRecord") @ApiOperation(value = "新增监审期间记录（动态表使用）", httpMethod = "POST") public CommonResult addPeriodRecord( @ApiParam(name = "record", value = "监审期间记录", required = true) @RequestBody CostAuditPeriodRecord record) throws Exception { if (record == null) { return CommonResult.error().message("记录不能为空"); } if (StringUtil.isEmpty(record.getTaskId())) { return CommonResult.error().message("taskId不能为空"); } if (StringUtil.isEmpty(record.getAuditPeriod())) { return CommonResult.error().message("auditPeriod不能为空"); } // 校验项目监审期间 CostProjectTask task = costProjectTaskManager.getById(record.getTaskId()); CostProjectApproval approval = costProjectApprovalManager.getById(task.getProjectId()); if (approval == null) { return CommonResult.error().message("项目不存在"); } if (!Arrays.asList(approval.getAuditPeriod().split(",")).contains(record.getAuditPeriod())) { return CommonResult.error().message("项目所属监审期间不存在"); } // 检查是否已存在相同的期间记录 CostAuditPeriodRecord existRecord = costAuditPeriodRecordManager.getOne( new QueryWrapper() .eq("type", record.getType()) .eq("task_id", record.getTaskId()) .eq("audit_period", record.getAuditPeriod()) ); if (existRecord != null) { return CommonResult.error().message("该监审期间记录已存在"); } // 设置填报时间为当前时间 if (record.getReportingTime() == null) { record.setReportingTime(java.time.LocalDateTime.now()); } record.setIsDeleted("0"); record.setType(record.getType()); costAuditPeriodRecordManager.save(record); return CommonResult.ok().value(record).message("新增监审期间记录成功"); } /** * 企业报送-调查表-动态表-查询监审期间记录列表 * * @param queryData 查询条件（包含uploadId和auditedUnitId） * @return 记录列表 */ @PostMapping(value = "/listPeriodRecord") @ApiOperation(value = "查询监审期间记录列表（动态表使用）", httpMethod = "POST") public CommonResult> 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.>ok().value(new ArrayList<>()); } List recordList = costAuditPeriodRecordManager.listByUploadIdAndAuditedUnitId(queryData.getUploadId(), queryData.getAuditedUnitId(), queryData.getType()); return CommonResult.>ok().value(recordList); } /** * 企业报送-调查表-动态表-删除监审期间记录 * * @param id 记录ID * @param type 类型：1-成本调查表 2-财务数据表 * @return 删除结果 */ @PostMapping(value = "/deletePeriodRecord") @ApiOperation(value = "删除监审期间记录（动态表使用）", httpMethod = "POST") public CommonResult 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.error().message("id不能为空"); } CostAuditPeriodRecord record = costAuditPeriodRecordManager.getById(id); if (record != null) { record.setIsDeleted("1"); costAuditPeriodRecordManager.updateById(record); // 同时删除该期间下的所有上传数据 QueryWrapper 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.ok().message("删除监审期间记录成功"); } /** * 企业报送-调查表-审核 * * @return 审核结果 */ @PostMapping(value = "/review") @ApiOperation(value = "调查表审核", httpMethod = "POST") public CommonResult 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.error().message("不支持的类型"); } return CommonResult.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 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 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 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 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 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 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 headersList = costVerifyTemplateHeadersDao.selectList( new QueryWrapper() .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 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 allHeadersIds = itemsList.stream() .map(CostVerifyTemplateItems::getHeadersId) .filter(StringUtil::isNotEmpty) .collect(Collectors.toSet()); System.out.println("数据项中的唯一 headersId 数量: " + allHeadersIds.size()); // 检查是否有 headersId 在表头中找不到 Set existingHeaderIds = headersList.stream() .map(CostVerifyTemplateHeaders::getId) .collect(Collectors.toSet()); Set 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 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 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 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.error().message("上传文件不能为空"); } String fileName = file.getOriginalFilename(); if (fileName == null || (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls"))) { return CommonResult.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.error().message("未找到指定的调查模板"); } templateType = template.getTemplateType(); CostSurveyTemplateVersion currentVersion = costSurveyTemplateVersionManager.selectCurrentVersion(surveyTemplateId); if (currentVersion == null) { return CommonResult.error().message("未找到启用的模板版本"); } List 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.error().message("未找到表头信息"); } Map 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.error().message("Excel文件没有数据"); } Row headerRow = sheet.getRow(0); if (headerRow == null) { return CommonResult.error().message("Excel文件第一行为空"); } // 检查是否有特殊列：行ID、父行ID、年限列、备注列 Integer rowIdColumnIndex = null; // 行ID列索引 Integer parentIdColumnIndex = null; // 父行ID列索引 Map auditPeriodColumnMap = new HashMap<>(); // 年限->列索引 Integer remarkColumnIndex = null; // 备注列索引 Map 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.error().message("Excel表头与模板表头不匹配"); } // 固定表和动态表必须包含行ID和父行ID列 if ("2".equals(templateType) || "3".equals(templateType)) { if (rowIdColumnIndex == null) { return CommonResult.error().message("固定表/动态表导入失败：Excel文件缺少【行ID】列。请使用系统导出的模板，不要删除隐藏列。"); } if (parentIdColumnIndex == null) { return CommonResult.error().message("固定表/动态表导入失败：Excel文件缺少【父行ID】列。请使用系统导出的模板，不要删除隐藏列。"); } } List dataList = new ArrayList<>(); Map 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 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 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.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.ok().message("导入成功，共导入 " + dataRowCount + " 行数据"); } catch (Exception e) { return CommonResult.error().message("导入失败：" + e.getMessage()); } finally { if (workbook != null) { workbook.close(); } } } // 财务数据表逻辑 case "2": { CostSurveyFdTemplate template = costSurveyFdTemplateManager.get(surveyTemplateId); if (template == null) { return CommonResult.error().message("未找到指定的财务数据表模板"); } templateType = template.getTemplateType(); CostSurveyFdTemplateVersion currentVersion = costSurveyFdTemplateVersionManager.selectCurrentVersion(surveyTemplateId); if (currentVersion == null) { return CommonResult.error().message("未找到启用的模板版本"); } // 使用已有的manager方法替代新接口 List 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.error().message("未找到表头信息"); } Map 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.error().message("Excel文件没有数据"); } Row headerRow = sheet.getRow(0); if (headerRow == null) { return CommonResult.error().message("Excel文件第一行为空"); } // 检查是否有立项年限列、备注列、行ID列和父行ID列 Map auditPeriodColumnMap = new HashMap<>(); // 年限->列索引 Integer remarkColumnIndex = null; // 备注列索引 Integer rowIdColumnIndex = null; // 行ID列索引 Integer parentIdColumnIndex = null; // 父行ID列索引 Map 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.error().message("Excel表头与模板表头不匹配"); } List dataList = new ArrayList<>(); Map 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 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 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.error().message("Excel文件中没有有效数据"); } verifyImportData(dataList, type, surveyTemplateId, rowIdToExcelRowMap); costSurveyTemplateUploadDataManager.saveData(dataList); CostProjectTaskMaterial material = costProjectTaskMaterialManager.getById(materialId); material.setIsUpload("1"); costProjectTaskMaterialManager.updateById(material); return CommonResult.ok().message("导入成功，共导入 " + dataRowCount + " 行数据"); } catch (Exception e) { return CommonResult.error().message("导入失败：" + e.getMessage()); } finally { if (workbook != null) { workbook.close(); } } } // 核定表 case "3": { CostVerifyTemplate template = costVerifyTemplateManager.get(surveyTemplateId); if (template == null) { return CommonResult.error().message("未找到核定表模板"); } List headersList = costVerifyTemplateHeadersDao.selectList( new LambdaQueryWrapper() .eq(CostVerifyTemplateHeaders::getSurveyTemplateId, surveyTemplateId) ); if (headersList.isEmpty()) { return CommonResult.error().message("未找到表头信息"); } Map 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.error().message("Excel文件没有数据"); } Row headerRow = sheet.getRow(0); if (headerRow == null) { return CommonResult.error().message("Excel表头行为空"); } // 识别行ID列和父行ID列 Integer rowIdColumnIndex = null; Integer parentIdColumnIndex = null; Map 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.error().message("Excel表头与模板不匹配"); } // 读取数据行 List dataList = new ArrayList<>(); Map 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 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.error().message("Excel文件没有有效数据"); } verifyImportData(dataList, type, surveyTemplateId, rowIdToExcelRowMap); // 保存 costSurveyTemplateUploadDataManager.saveData(dataList); return CommonResult.ok().message("导入成功，共导入 " + importRowCount + " 行数据"); } catch (Exception e) { return CommonResult.error().message("导入失败：" + e.getMessage()); } finally { if (workbook != null) workbook.close(); } } default: return CommonResult.error().message("不支持的类型"); } } /** * 获取友好的行号显示 */ private String getRowDisplay(String rowid, Map rowIdToExcelRowMap) { if (rowIdToExcelRowMap == null) { return "行[" + rowid + "]"; } Integer excelRow = rowIdToExcelRowMap.get(rowid); return excelRow != null ? "第" + excelRow + "行" : "行[" + rowid + "]"; } // 校验逻辑 private void verifyImportData(List dataList, String type, String surveyTemplateId, Map rowIdToExcelRowMap) throws Exception { if (dataList == null || dataList.isEmpty()) { return; } // 获取模板类型（templateType） String templateType = getTemplateType(type, surveyTemplateId); // 按 rowid 分组数据 Map> 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 headersList = getTemplateHeaders(type, surveyTemplateId); if (headersList == null || headersList.isEmpty()) { return; } // 1. 字段级别校验（必填、类型、长度等） // 固定表和动态表都校验所有行的所有字段（排除年限列和备注列） for (Map.Entry> rowEntry : rowDataMap.entrySet()) { String rowid = rowEntry.getKey(); Map 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 itemsList = getTemplateItems(type, surveyTemplateId); if (itemsList != null && !itemsList.isEmpty()) { // 按 rowid 分组模板项 Map> itemsByRowId = groupItemsByRowId(itemsList); // 构建全局的 cellCode -> value 映射（用于公式计算，因为公式可能引用其他行的单元格） // 注意：cellCode是行的标识（如A1、A2、Q1），不是列的标识 // 对于年限列，需要构建 cellCode_年限 -> 值 的映射（如 A1_2024 -> "1"） Map globalCellCodeMap = new HashMap<>(); // 先构建 rowid -> cellCode 的映射 Map rowidToCellCodeMap = new HashMap<>(); for (Map.Entry> entry : itemsByRowId.entrySet()) { String rowid = entry.getKey(); List 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> rowEntry : rowDataMap.entrySet()) { String rowid = rowEntry.getKey(); Map 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 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 periods = new HashSet<>(); for (Map rowData : rowDataMap.values()) { for (String key : rowData.keySet()) { if (key.matches("\\d{4}")) { periods.add(key); } } } // 校验每一行的计算公式（针对每个年限分别校验） for (Map.Entry> rowEntry : rowDataMap.entrySet()) { String rowid = rowEntry.getKey(); Map rowData = rowEntry.getValue(); // 获取该行对应的模板项 List 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> rowDataMap, List headersList, String surveyTemplateId, String type) { // 1. 获取模板定义的行数 List templateItems = getTemplateItems(type, surveyTemplateId); if (templateItems == null || templateItems.isEmpty()) { throw new IllegalArgumentException("固定表模板未定义任何数据行，无法导入"); } // 按 rowid 分组模板项，得到模板定义的行数 Set 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 expectedFields = headersList.stream() .map(header -> getHeaderFieldEname(header)) .filter(StringUtil::isNotEmpty) .collect(Collectors.toSet()); for (Map.Entry> rowEntry : rowDataMap.entrySet()) { String rowid = rowEntry.getKey(); Map rowData = rowEntry.getValue(); Set actualFields = rowData.keySet(); // 检查是否有多余的字段（排除年限列和备注列） Set 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 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() .eq("survey_template_id", surveyTemplateId) ); default: break; } return new ArrayList<>(); } /** * 字段级别校验 */ private void validateFieldRulesExcludingPeriods(String rowid, Map rowData, List headersList, Map 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 wrapper = new QueryWrapper<>(); wrapper.eq("dict_type", dictCode) .eq("status", "0"); // 只查询正常状态的字典项 List 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 validValues = dictDataList.stream() .map(CostDictData::getDictValue) .filter(StringUtil::isNotEmpty) .collect(Collectors.toSet()); List 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 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> groupItemsByRowId(List itemsList) { Map> 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 rowData, List rowItems, Map globalCellCodeMap, String period, String type,Map 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 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 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 referencedCells = new HashSet<>(); while (matcher.find()) { referencedCells.add(matcher.group()); } // 为所有引用的单元格设置变量值 Map 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 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 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 items, List headList) { String filename = headList.stream().map(CostSurveyTemplateHeaders::getFieldName).collect(Collectors.joining(",")); String filenids = headList.stream().map(CostSurveyTemplateHeaders::getId).collect(Collectors.joining(",")); // 按 orderNum 分组 Map> groupedByHeadersId = items.stream() .collect(Collectors.groupingBy(CostSurveyTemplateItems::getOrderNum)); List> result = new ArrayList<>(); for (Map.Entry> entry : groupedByHeadersId.entrySet()) { List group = entry.getValue(); if (group.isEmpty()) continue; // 主项（可以取第一个） CostSurveyTemplateItems mainItem = group.get(0); String headersIds = ""; String itemIds = ""; // 构造 key-value 映射 Map 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 items, List headList) { String filename = headList.stream().map(CostSurveyFdTemplateHeaders::getFieldName).collect(Collectors.joining(",")); String filenids = headList.stream().map(CostSurveyFdTemplateHeaders::getId).collect(Collectors.joining(",")); // 按 orderNum 分组 Map> groupedByOrderNum = items.stream() .collect(Collectors.groupingBy(CostSurveyFdTemplateItems::getOrderNum)); List> result = new ArrayList<>(); for (Map.Entry> entry : groupedByOrderNum.entrySet()) { List group = entry.getValue(); if (group.isEmpty()) continue; // 主项（取第一个） CostSurveyFdTemplateItems mainItem = group.get(0); String headersIds = ""; String itemIds = ""; // 构造 key-value 映射 Map 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 itemsList, Map 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> itemsByRowId = itemsList.stream() .filter(item -> StringUtil.isNotEmpty(item.getRowid())) .collect(Collectors.groupingBy(CostSurveyTemplateItems::getRowid)); // 固定表和动态表都需要按父子关系排序 List sortedRowIds = sortRowIdsByParentChild(itemsByRowId); int rowNum = 1; for (String rowId : sortedRowIds) { List 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 sortRowIdsByParentChild(Map> itemsByRowId) { List result = new ArrayList<>(); // 构建rowId到parentid的映射 Map rowIdToParentId = new HashMap<>(); for (Map.Entry> entry : itemsByRowId.entrySet()) { String rowId = entry.getKey(); List items = entry.getValue(); if (!items.isEmpty()) { String parentId = items.get(0).getParentid(); rowIdToParentId.put(rowId, parentId); } } // 找出所有根节点（parentid为空或不存在的） Set allRowIds = new HashSet<>(itemsByRowId.keySet()); List 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 rowIdToParentId, Set allRowIds, List result) { result.add(rowId); // 找出所有子节点 List 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 itemsList, Map 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> itemsByRowId = itemsList.stream() .filter(item -> StringUtil.isNotEmpty(item.getRowid())) .collect(Collectors.groupingBy(CostSurveyFdTemplateItems::getRowid)); // 固定表和动态表都需要按父子关系排序 List sortedRowIds = sortRowIdsByParentChildFd(itemsByRowId); int rowNum = 1; for (String rowId : sortedRowIds) { List 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 sortRowIdsByParentChildFd(Map> itemsByRowId) { List result = new ArrayList<>(); // 构建rowId到parentid的映射 Map rowIdToParentId = new HashMap<>(); for (Map.Entry> entry : itemsByRowId.entrySet()) { String rowId = entry.getKey(); List items = entry.getValue(); if (!items.isEmpty()) { String parentId = items.get(0).getParentid(); rowIdToParentId.put(rowId, parentId); } } // 找出所有根节点（parentid为空或不存在的） Set allRowIds = new HashSet<>(itemsByRowId.keySet()); List 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 rowIdToParentId, Set allRowIds, List result) { result.add(rowId); // 找出所有子节点 List 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 sortRowIdsByParentChildVerify(Map> itemsByRowId) { List result = new ArrayList<>(); // 构建rowId到parentid的映射 Map rowIdToParentId = new HashMap<>(); for (Map.Entry> entry : itemsByRowId.entrySet()) { String rowId = entry.getKey(); List items = entry.getValue(); if (!items.isEmpty()) { String parentId = items.get(0).getParentid(); rowIdToParentId.put(rowId, parentId); } } // 找出所有根节点（parentid为空或不存在的） Set allRowIds = new HashSet<>(itemsByRowId.keySet()); List 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 rowIdToParentId, Set allRowIds, List result) { result.add(rowId); // 找出所有子节点 List 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 itemsList, Map headerIndexMap, int rowIdColIndex, int parentIdColIndex) { if (itemsList == null || itemsList.isEmpty()) { return; } // 按 rowid 分组 Map> itemsByRowId = itemsList.stream() .filter(item -> StringUtil.isNotEmpty(item.getRowid())) .collect(Collectors.groupingBy(CostVerifyTemplateItems::getRowid)); // 按父子关系排序 List sortedRowIds = sortRowIdsByParentChildVerify(itemsByRowId); int rowNum = 1; int totalCellsWritten = 0; int cellsSkippedNoMapping = 0; for (String rowId : sortedRowIds) { List 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; } }