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; import com.gkhy.exam.common.domain.model.LoginUserDetails; 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.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 implements CompanyIndustryTemplateService { @Autowired private CompanyIndustryTemplateMapper companyIndustryTemplateMapper; @Autowired private SysCompanyService sysCompanyService; @Autowired private SysIndustryTypeMapper sysIndustryTypeMapper; @Override public CommonPage selectCompanyIndustryTemplateList(CompanyIndustryTemplate companyId) { boolean admin = SecurityUtils.adminUser(); if (!admin){ if (companyId==null){ throw new ApiException("非管理员操作，查询条件不可为空"); } } PageUtils.startPage(); List companyIndustryTemplates = companyIndustryTemplateMapper.selectCompanyIndustryTemplateList(companyId); return CommonPage.restPage(companyIndustryTemplates); } @Override public CommonResult insertCompanyIndustryTemplate(CompanyIndustryTemplate companyIndustryTemplate) { LoginUserDetails loginUser = SecurityUtils.getLoginUser(); SysCompany sysCompany = sysCompanyService.selectCompanyById(companyIndustryTemplate.getCompanyId()==null?SecurityUtils.getCompanyId():companyIndustryTemplate.getCompanyId()); companyIndustryTemplate.setCompanyName(sysCompany.getName()); companyIndustryTemplate.setCreateBy(loginUser.getUsername()); companyIndustryTemplate.setCreateTime(LocalDateTime.now()); int insert = companyIndustryTemplateMapper.insert(companyIndustryTemplate); if (insert>0){ return CommonResult.success(); } return CommonResult.failed(); } @Override public CommonResult updateCompanyIndustryTemplate(CompanyIndustryTemplate companyIndustryTemplate) { LoginUserDetails loginUser = SecurityUtils.getLoginUser(); SysCompany sysCompany = sysCompanyService.selectCompanyById(companyIndustryTemplate.getCompanyId()==null?SecurityUtils.getCompanyId():companyIndustryTemplate.getCompanyId()); companyIndustryTemplate.setCompanyName(sysCompany.getName()); companyIndustryTemplate.setUpdateBy(loginUser.getUsername()); companyIndustryTemplate.setUpdateTime(LocalDateTime.now()); int update = companyIndustryTemplateMapper.updateCompanyIndustryTemplateById(companyIndustryTemplate); if (update>0){ return CommonResult.success(); } return CommonResult.failed(); } @Override public CommonResult deletedCompanyIndustryTemplate(Integer companyIndustryTemplateId) { CompanyIndustryTemplate industryTemplate = new CompanyIndustryTemplate(); LoginUserDetails loginUser = SecurityUtils.getLoginUser(); industryTemplate.setUpdateBy(loginUser.getUsername()); industryTemplate.setUpdateTime(LocalDateTime.now()); industryTemplate.setDelFlag(1); industryTemplate.setId(companyIndustryTemplateId); int i = companyIndustryTemplateMapper.updateById(industryTemplate); if (i>0){ return CommonResult.success(); } return CommonResult.failed(); } @Override public CommonResult uploadQuestion(MultipartFile file) throws IOException { List companyIndustryTypes = sysIndustryTypeMapper.selectIndustryTypeList(); List list = sysCompanyService.selectCompanyLists(); Workbook workbook = WorkbookFactory.create(file.getInputStream()); Sheet sheet = workbook.getSheetAt(0); // 1. 创建列名-索引映射表 Map 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 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 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 companyIndustryTypes = sysIndustryTypeMapper.selectIndustryTypeList(); // List list = sysCompanyService.selectCompanyLists(); // Workbook workbook = WorkbookFactory.create(file.getInputStream()); // Sheet sheetAt = workbook.getSheetAt(0); // List companyIndustryTemplates = new ArrayList<>(); // LoginUserDetails loginUser = SecurityUtils.getLoginUser(); // DataFormatter dataFormatter = new DataFormatter(); // StringBuilder stringBuilder = new StringBuilder(); // for (int i = 0; i