首先校验
/**
* 校验导入文件
* @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. 解析Excel
List<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"));
}
}