首先校验
/*** 校验导入文件* @param file 上传的文件* @return 校验结果，成功返回包含成功状态的AjaxResult，失败返回包含错误信息的AjaxResult*/private AjaxResult validateImportFile(MultipartFile file) {if (file.isEmpty()) {return AjaxResult.error("文件不能为空");}String filename = file.getOriginalFilename();if (filename == null || !(filename.endsWith(".xlsx") || filename.endsWith(".xls"))) {return AjaxResult.error("仅支持Excel文件");}return AjaxResult.success();}通过进行解析
@Override@Transactional(rollbackFor = Exception.class)public AjaxResult importWnList(@RequestParam("file") MultipartFile file) {// 1. 校验文件AjaxResult validateResult = validateImportFile(file);if (!validateResult.isSuccess()) {return validateResult;}try {// 2. 解析ExcelList<Wn> dataList = parseExcel(file);// 3. 处理业务逻辑Map<String, Object> importResult = saveListData(dataList);// 4. 构建返回消息return buildImportResult(importResult);} catch (Exception e) {return AjaxResult.error("导入失败: " + e.getMessage());}}
public List<Wn> parseExcel(MultipartFile file) throws IOException {List<Wn> list = new ArrayList<>();DataFormatter formatter = new DataFormatter();Workbook workbook = WorkbookFactory.create(file.getInputStream());Sheet sheet = workbook.getSheet("微脑列表");// 如果找不到指定名称的工作表，则获取第一个工作表if (sheet == null) {log.info("找不到名为'微脑列表'的工作表，将使用第一个工作表");sheet = workbook.getSheetAt(0);// 检查第一个工作表是否存在if (sheet == null) {throw new IllegalArgumentException("Excel文件中不包含任何工作表");}}for (Row row : sheet) {if (row.getRowNum() == 0) continue; // 跳过表头Wn wn = new Wn();wn.setWnNum(formatter.formatCellValue(row.getCell(0)));wn.setWnSn(formatter.formatCellValue(row.getCell(1)));wn.setWnType(formatter.formatCellValue(row.getCell(2)));wn.setAreaId(formatter.formatCellValue(row.getCell(3)));list.add(wn);}workbook.close();return list;}最后保存逻辑
public Map<String, Object> saveListData(List<Wn> dataList) {// 参数校验if (CollectionUtils.isEmpty(dataList)) {return Collections.singletonMap("message", "数据列表不能为空");}HashSet<Long> TempidList = new HashSet<>();// 初始化统计结果int successCount = 0;int failCount = 0;List<String> failReasons = new ArrayList<>();dataList.forEach(wn -> {String wnType = wn.getWnType();WnTemplate wnTemplateA = new WnTemplate();wnTemplateA.setId(Long.valueOf(wnType));List<WnTemplate> wnTemplates = wnTemplateMapper.selectWnTemplateList(wnTemplateA);TempidList.add(wnTemplates.get(0).getId());});// 优化：先加载所有模板信息到Map，避免循环内重复查询Map<Long, WnTemplate> templateMap = loadTemplateMapByType(TempidList);// 遍历处理每条数据for (int i = 0; i < dataList.size(); i++) {Wn wn = dataList.get(i);try {// 1. 输入参数基础校验validateWnData(wn);// 2. 唯一性校验（示例校验name和code组合唯一）if (selectWnByWnNum(wn.getWnNum()) != null) {throw new RuntimeException("微脑编号已存在"+wn.getWnNum());}// 3. 校验：微脑模板是否存在（从预加载的Map中获取）String wnType = wn.getWnType();WnTemplate wnTemplate = new WnTemplate();wnTemplate.setId(Long.valueOf(wnType));List<WnTemplate> wnTemplates = wnTemplateMapper.selectWnTemplateList(wnTemplate);WnTemplate wnTemplateB = templateMap.get(wnTemplates.get(0).getId());if (wnTemplateB == null) {throw new RuntimeException("该模板不存在：" + wn.getWnType());}wn.setWnType(wnTemplateB.getName());wn.setStatus("0");// 4. 插入数据insertWn(wn);if (StringUtils.isEmpty(wn.getQrCode())){String url=qrcode(wn.getId());wn.setQrCode(url);wnMapper.updateWn(wn);}for (WnTemplateSensors wnTemplateSensors : wnTemplateB.getSensorsList()) {Sensors sensors = new Sensors();sensors.setSensorType(wnTemplateSensors.getSensorType());sensors.setWnId(wn.getId());sensors.setCreateTime(DateUtils.getNowDate());sensors.setSensorNid(wnTemplateSensors.getSensorNid());sensorsMapper.insertSensors(sensors);}successCount++;} catch (Exception e) {failCount++;failReasons.add(failCount + ") 第" + (i + 2) + "行数据错误：" + e.getMessage());}}// 返回结构化结果Map<String, Object> result = new HashMap<>();result.put("successCount", successCount);result.put("failCount", failCount);result.put("failReasons", failReasons);return result;}构建返回信息
/*** 构建导入结果返回对象* @param importResult 导入结果Map，包含successCount、failCount和failReasons* @return 导入结果对象*/private AjaxResult buildImportResult(Map<String, Object> importResult) {// 从Map中提取结果数据Integer successCount = (Integer) importResult.getOrDefault("successCount", 0);Integer failCount = (Integer) importResult.getOrDefault("failCount", 0);List<String> failReasons = (List<String>) importResult.get("failReasons");// 构建返回消息String message;if(failCount.equals(0)){message = String.format("导入完成！成功 %d 条，失败 %d 条。",successCount,failCount);return AjaxResult.success(message).put("successCount", importResult.get("successCount")).put("failCount", failCount);}else{message = String.format("导入完成！成功 %d 条，失败 %d 条。\n失败原因：\n%s",successCount,failCount,String.join("\n", failReasons));return AjaxResult.success(message).put("successCount", importResult.get("successCount")).put("failCount", importResult.get("failCount")).put("details", importResult.get("failReasons"));}}
)
)
