From a0469a082320c33fc19d4e7239b7ddde67945227 Mon Sep 17 00:00:00 2001 From: “djh” <“3298565835@qq.com”> Date: 星期四, 03 七月 2025 14:07:53 +0800 Subject: [PATCH] 修改 --- multi-system/src/main/java/com/gkhy/exam/system/service/impl/CompanyIndustryTemplateServiceImpl.java | 175 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++- 1 files changed, 172 insertions(+), 3 deletions(-) diff --git a/multi-system/src/main/java/com/gkhy/exam/system/service/impl/CompanyIndustryTemplateServiceImpl.java b/multi-system/src/main/java/com/gkhy/exam/system/service/impl/CompanyIndustryTemplateServiceImpl.java index c500fe4..856bce3 100644 --- a/multi-system/src/main/java/com/gkhy/exam/system/service/impl/CompanyIndustryTemplateServiceImpl.java +++ b/multi-system/src/main/java/com/gkhy/exam/system/service/impl/CompanyIndustryTemplateServiceImpl.java @@ -1,5 +1,6 @@ package com.gkhy.exam.system.service.impl; +import com.alibaba.fastjson2.JSONObject; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.gkhy.exam.common.api.CommonPage; import com.gkhy.exam.common.api.CommonResult; @@ -7,18 +8,27 @@ import com.gkhy.exam.common.exception.ApiException; import com.gkhy.exam.common.utils.PageUtils; import com.gkhy.exam.common.utils.SecurityUtils; -import com.gkhy.exam.system.domain.CompanyIndustryTemplate; -import com.gkhy.exam.system.domain.CompanyRoster; -import com.gkhy.exam.system.domain.SysCompany; +import com.gkhy.exam.common.utils.StringUtils; +import com.gkhy.exam.system.domain.*; import com.gkhy.exam.system.mapper.CompanyIndustryTemplateMapper; import com.gkhy.exam.system.mapper.CompanyRosterMapper; +import com.gkhy.exam.system.mapper.SysIndustryTypeMapper; import com.gkhy.exam.system.service.CompanyIndustryTemplateService; import com.gkhy.exam.system.service.SysCompanyService; +import com.gkhy.exam.system.service.SysIndustryTypeService; +import org.apache.poi.ss.usermodel.*; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; +import org.springframework.web.multipart.MultipartFile; +import java.io.IOException; import java.time.LocalDateTime; +import java.util.ArrayList; +import java.util.HashMap; import java.util.List; +import java.util.Map; + +import static cn.hutool.poi.excel.cell.CellUtil.getCellValue; @Service public class CompanyIndustryTemplateServiceImpl extends ServiceImpl<CompanyIndustryTemplateMapper, CompanyIndustryTemplate> implements CompanyIndustryTemplateService { @@ -27,6 +37,9 @@ private CompanyIndustryTemplateMapper companyIndustryTemplateMapper; @Autowired private SysCompanyService sysCompanyService; + + @Autowired + private SysIndustryTypeMapper sysIndustryTypeMapper; @@ -86,4 +99,160 @@ } return CommonResult.failed(); } + + @Override + public CommonResult uploadQuestion(MultipartFile file) throws IOException { + List<CompanyIndustryType> companyIndustryTypes = sysIndustryTypeMapper.selectIndustryTypeList(); + List<SysCompany> list = sysCompanyService.selectCompanyLists(); + Workbook workbook = WorkbookFactory.create(file.getInputStream()); + Sheet sheet = workbook.getSheetAt(0); + +// 1. 创建列名-索引映射表 + Map<String, Integer> columnIndexMap = new HashMap<>(); + Row headerRow = sheet.getRow(0); + for (Cell cell : headerRow) { + String headerName = cell.getStringCellValue().trim(); + columnIndexMap.put(headerName, cell.getColumnIndex()); + } + +// 验证必要列是否存在 + String[] requiredHeaders = {"章节", "文件记录", "分类", "行业", "企业名称"}; + List<String> missingHeaders = new ArrayList<>(); + for (String header : requiredHeaders) { + if (!columnIndexMap.containsKey(header)) { + missingHeaders.add(header); + } + } + if (!missingHeaders.isEmpty()) { + workbook.close(); + return CommonResult.failed("缺少必要表头: " + String.join(", ", missingHeaders)); + } + List<CompanyIndustryTemplate> companyIndustryTemplates = new ArrayList<>(); + LoginUserDetails loginUser = SecurityUtils.getLoginUser(); + DataFormatter dataFormatter = new DataFormatter(); + StringBuilder errorBuilder = new StringBuilder(); + // 2. 获取各列的索引 + int chapterIndex = columnIndexMap.get("章节"); + int templateNameIndex = columnIndexMap.get("文件记录"); + int typeIndex = columnIndexMap.get("分类"); + int industryTypeIndex = columnIndexMap.get("行业"); + int companyNameIndex = columnIndexMap.get("企业名称"); + // 3. 从第二行开始处理数据 + for (int i = 1; i <= sheet.getLastRowNum(); i++) { + Row row = sheet.getRow(i); + if (row == null) continue; + + CompanyIndustryTemplate template = new CompanyIndustryTemplate(); + template.setCreateBy(loginUser.getUsername()); + template.setCreateTime(LocalDateTime.now()); + + // 4. 使用动态索引获取单元格 + template.setChapter(getCellValue(row, chapterIndex, dataFormatter)); + template.setTemplateName(getCellValue(row, templateNameIndex, dataFormatter)); + template.setType(getCellValue(row, typeIndex, dataFormatter)); + + // 5. 处理行业类型映射 + String industryName = getCellValue(row, industryTypeIndex, dataFormatter); + boolean industryFound = false; + for (CompanyIndustryType type : companyIndustryTypes) { + if (industryName.equals(type.getName())) { + template.setIndustryType(type.getId()); + industryFound = true; + break; + } + } + if (!industryFound) { + errorBuilder.append("第").append(i + 1).append("行: 未找到行业[").append(industryName).append("]; "); + } + + // 6. 处理企业映射 + String companyName = getCellValue(row, companyNameIndex, dataFormatter); + boolean companyFound = false; + for (SysCompany company : list) { + if (companyName.equals(company.getName())) { + template.setCompanyId(company.getId()); + companyFound = true; + break; + } + } + if (!companyFound) { + errorBuilder.append("第").append(i + 1).append("行: 未找到企业[").append(companyName).append("]; "); + } + if (SecurityUtils.getCompanyId()!=null){ + template.setCompanyId(SecurityUtils.getCompanyId()); + } + + companyIndustryTemplates.add(template); + } + + // 7. 错误处理 + if (errorBuilder.length() > 0) { + workbook.close(); + return CommonResult.failed(errorBuilder.toString()); + } + + // 8. 批量插入 + int affectedRows = companyIndustryTemplateMapper.insertIndustrys(companyIndustryTemplates); + workbook.close(); + + if (affectedRows < 1) { + throw new ApiException("导入行业模版失败"); + } + return CommonResult.success(); + } + // 安全获取单元格值 + private String getCellValue(Row row, int columnIndex, DataFormatter formatter) { + if (columnIndex < 0) return ""; + Cell cell = row.getCell(columnIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); + return formatter.formatCellValue(cell).trim(); + } + +// List<CompanyIndustryType> companyIndustryTypes = sysIndustryTypeMapper.selectIndustryTypeList(); +// List<SysCompany> list = sysCompanyService.selectCompanyLists(); +// Workbook workbook = WorkbookFactory.create(file.getInputStream()); +// Sheet sheetAt = workbook.getSheetAt(0); +// List<CompanyIndustryTemplate> companyIndustryTemplates = new ArrayList<>(); +// LoginUserDetails loginUser = SecurityUtils.getLoginUser(); +// DataFormatter dataFormatter = new DataFormatter(); +// StringBuilder stringBuilder = new StringBuilder(); +// for (int i = 0; i <sheetAt.getLastRowNum(); i++) { +// Row row = sheetAt.getRow(i + 1); +// CompanyIndustryTemplate companyIndustryTemplate = new CompanyIndustryTemplate(); +// if (row!=null){ +// companyIndustryTemplate.setChapter(dataFormatter.formatCellValue(row.getCell(0))); +// companyIndustryTemplate.setTemplateName(dataFormatter.formatCellValue(row.getCell(1))); +// companyIndustryTemplate.setType(dataFormatter.formatCellValue(row.getCell(2))); +// for (CompanyIndustryType companyIndustryType : companyIndustryTypes) { +// if (dataFormatter.formatCellValue(row.getCell(3)).equals(companyIndustryType.getName())){ +// companyIndustryTemplate.setIndustryType(companyIndustryType.getId()); +// } +// } +// if (companyIndustryTemplate.getIndustryType()==null){ +// stringBuilder.append("未找到对应行业类型:["+dataFormatter.formatCellValue(row.getCell(3))+"] ,"); +// } +// companyIndustryTemplate.setCreateBy(loginUser.getUsername()); +// companyIndustryTemplate.setCreateTime(LocalDateTime.now()); +// for (SysCompany sysCompany : list) { +// if (dataFormatter.formatCellValue(row.getCell(4)).equals(sysCompany.getName())){ +// companyIndustryTemplate.setCompanyId(sysCompany.getId()); +// } +// } +// if (companyIndustryTemplate.getCompanyId()==null){ +// stringBuilder.append("未找到对应企业:["+dataFormatter.formatCellValue(row.getCell(4))+"]"); +// } +// companyIndustryTemplates.add(companyIndustryTemplate); +// } +// } +// if (StringUtils.isNotBlank(stringBuilder)){ +// workbook.close(); +// return CommonResult.failed(stringBuilder.toString()); +// } +// int i = companyIndustryTemplateMapper.insertIndustrys(companyIndustryTemplates); +// if (i<1){ +// throw new ApiException("导入行业模版失败"); +// } +// workbook.close(); +// return CommonResult.success(); + + } -- Gitblit v1.9.2