From affd7e9f1cc591be36102299cac45b35d9536a17 Mon Sep 17 00:00:00 2001 From: huliguo <2023611923@qq.com> Date: 星期六, 12 七月 2025 15:08:49 +0800 Subject: [PATCH] stream流改for --- src/main/java/com/linghu/controller/UserController.java | 201 ++++++++++++++++++++++++++++++++++++++++++++++--- 1 files changed, 186 insertions(+), 15 deletions(-) diff --git a/src/main/java/com/linghu/controller/UserController.java b/src/main/java/com/linghu/controller/UserController.java index 9165d8c..619e913 100644 --- a/src/main/java/com/linghu/controller/UserController.java +++ b/src/main/java/com/linghu/controller/UserController.java @@ -1,28 +1,45 @@ package com.linghu.controller; import com.alibaba.excel.EasyExcel; +import com.alibaba.excel.ExcelWriter; +import com.alibaba.excel.write.handler.SheetWriteHandler; +import com.alibaba.excel.write.metadata.WriteSheet; +import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; +import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; +import com.linghu.listener.PlatformExcelListener; +import com.linghu.listener.UserExcelListener; import com.linghu.model.common.ResponseResult; +import com.linghu.model.entity.Platform; import com.linghu.model.entity.Sectionalization; -import com.linghu.model.entity.Type; import com.linghu.model.entity.User; -import com.linghu.model.excel.KeywordExcel; +import com.linghu.model.excel.ExcelDataWithRow; +import com.linghu.model.excel.PlatformExcel; import com.linghu.model.excel.UserExcel; import com.linghu.model.vo.UserPageVO; +import com.linghu.service.SectionalizationService; import com.linghu.service.UserService; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; +import org.apache.poi.ss.usermodel.DataValidation; +import org.apache.poi.ss.usermodel.DataValidationConstraint; +import org.apache.poi.ss.usermodel.DataValidationHelper; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.util.CellRangeAddressList; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.HttpHeaders; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; +import org.springframework.util.StringUtils; import org.springframework.web.bind.annotation.*; +import org.springframework.web.multipart.MultipartFile; import java.io.ByteArrayOutputStream; import java.io.IOException; -import java.util.ArrayList; -import java.util.List; +import java.time.LocalDateTime; +import java.util.*; +import java.util.stream.Collectors; @RestController @RequestMapping("/user") @@ -40,8 +57,9 @@ public ResponseResult add(@RequestBody User user) { List<User> list = userService.list(new LambdaQueryWrapper<User>().eq(User::getUser_email, user.getUser_email())); if (list != null && list.size() > 0) { - return ResponseResult.error("该邮箱已存在"); + return ResponseResult.success("该邮箱已存在"); } + user.setStatus("正常"); userService.save(user); return ResponseResult.success(); @@ -52,13 +70,17 @@ * 修改用户 */ @PutMapping - @ApiOperation(value = "添加") + @ApiOperation(value = "修改") public ResponseResult edit(@RequestBody User user) { + User user1 = userService.getById(user.getUser_id()); + if (user1 == null) { + return ResponseResult.error("该账户不存在"); + } List<User> list = userService.list(new LambdaQueryWrapper<User>() .ne(User::getUser_id, user.getUser_id()) .eq(User::getUser_email, user.getUser_email())); if (list != null && list.size() > 0) { - return ResponseResult.error("该邮箱已存在"); + return ResponseResult.success("该邮箱已存在"); } userService.updateById(user); return ResponseResult.success(); @@ -80,37 +102,186 @@ * 分页查询 */ @GetMapping - @ApiOperation(value = "删除") + @ApiOperation(value = "分页") public ResponseResult<Page<UserPageVO>> page(@RequestParam(value = "pageSize", required = false, defaultValue = "10")Integer pageSize, @RequestParam(value = "pageNum", required = false,defaultValue = "1")Integer pageNum, @RequestParam(value = "sectionalization_id",required = false)Integer sectionalization_id, @RequestParam(value = "status" ,required = false)String status) { Page<UserPageVO> page = new Page<>(pageNum, pageSize); return ResponseResult.success( userService.getPage(page,sectionalization_id,status)); - - } /** * 下载模板 */ - @PostMapping("/downloadTemplate") + @GetMapping("/downloadTemplate") @ApiOperation("下载模板") public ResponseEntity<byte[]> downloadTemplate() throws IOException { - ByteArrayOutputStream out = new ByteArrayOutputStream(); - EasyExcel.write(out, UserExcel.class).sheet("账号模板").doWrite(new ArrayList<>()); + // 1. 查询所有分组名称 + List<Sectionalization> sections = sectionalizationService.list(null); + List<String> sectionNames = sections.stream() + .map(Sectionalization::getSectionalization_name) + .filter(Objects::nonNull) + .collect(Collectors.toList()); + // 2. 创建Excel写出器 + ByteArrayOutputStream out = new ByteArrayOutputStream(); + + // 3. 使用自定义的SheetWriteHandler来添加数据验证 + ExcelWriter excelWriter = EasyExcel.write(out, UserExcel.class) + .registerWriteHandler(new SheetWriteHandler() { + @Override + public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, + WriteSheetHolder writeSheetHolder) { + Sheet sheet = writeSheetHolder.getSheet(); + + // 添加数据验证(下拉框) + DataValidationHelper helper = sheet.getDataValidationHelper(); + CellRangeAddressList rangeList = new CellRangeAddressList( + 1, 65535, 3, 3); // D列(第4列) + + DataValidationConstraint constraint = helper.createExplicitListConstraint( + sectionNames.toArray(new String[0])); + DataValidation validation = helper.createValidation(constraint, rangeList); + sheet.addValidationData(validation); + } + }) + .build(); + + // 4. 写入空数据 + WriteSheet writeSheet = EasyExcel.writerSheet("账号模板").build(); + excelWriter.write(new ArrayList<>(), writeSheet); + excelWriter.finish(); + + // 5. 构建响应 return ResponseEntity.ok() - .header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=keyword_template.xlsx") + .header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=user_template.xlsx") .contentType(MediaType.APPLICATION_OCTET_STREAM) .body(out.toByteArray()); } - /** * 导入文件 */ + // 导入文件 + @Autowired + private SectionalizationService sectionalizationService; + @PostMapping("/importUserExcel") + @ApiOperation("导入用户数据") + public ResponseResult importUserExcel(@RequestParam("file") MultipartFile file) { + try { + // 1. 检查文件是否为空 + if (file.isEmpty()) { + return ResponseResult.error("上传文件不能为空"); + } + + // 2. 读取数据库中已有的用户和分组,用于重复校验 + // 2.1 已存在的用户(按用户名和邮箱分组) + List<User> userList = userService.list(); + Map<String, User> userNameMap = userList.stream() + .collect(Collectors.toMap( + User::getUser_name, + user -> user, // 变量名修正为user(原excel变量名不合理) + (oldValue, newValue) -> oldValue // 重复用户名保留旧值 + )); + Map<String, User> userEmailMap = userList.stream() + .collect(Collectors.toMap( + User::getUser_email, + user -> user, + (oldValue, newValue) -> oldValue // 重复邮箱保留旧值 + )); + + // 2.2 已存在的分组(按分组名称映射) + List<Sectionalization> sectionalizationList = sectionalizationService.list(); + Map<String, Sectionalization> sectionMap = sectionalizationList.stream() + .collect(Collectors.toMap( + Sectionalization::getSectionalization_name, + section -> section, + (oldValue, newValue) -> oldValue + )); + + // 3. 读取Excel数据(使用自定义监听器) + UserExcelListener listener = new UserExcelListener(); + EasyExcel.read(file.getInputStream(), UserExcel.class, listener) + .sheet() // 读取第一个sheet + .doRead(); // 执行读取 + + // 4. 校验Excel数据并转换为User对象 + List<ExcelDataWithRow<UserExcel>> excelDataList = listener.getDataList(); + List<User> insertUserList = new ArrayList<>(); + List<String> errorMessages = new ArrayList<>(); + Set<String> emailSet = new HashSet<>(); // 用于校验导入数据中的邮箱重复 + + for (ExcelDataWithRow<UserExcel> excelData : excelDataList) { + int rowNum = excelData.getRowNumber(); + UserExcel excelRowData = excelData.getData(); + List<String> rowErrors = new ArrayList<>(); + + // 4.1 校验必填字段 + if (!StringUtils.hasText(excelRowData.getUser_name())) { + rowErrors.add("账户名不能为空"); + } else if (userNameMap.containsKey(excelRowData.getUser_name())) { + rowErrors.add("账户名已存在(数据库中重复)"); + } + + if (!StringUtils.hasText(excelRowData.getUser_email())) { + rowErrors.add("邮箱不能为空"); + } else { + if (userEmailMap.containsKey(excelRowData.getUser_email())) { + rowErrors.add("邮箱已存在(数据库中重复)"); + } + if (emailSet.contains(excelRowData.getUser_email())) { + rowErrors.add("邮箱重复(导入数据中重复)"); + } + } + + if (!StringUtils.hasText(excelRowData.getPassword())) { + rowErrors.add("密码不能为空"); + } + + if (!StringUtils.hasText(excelRowData.getSectionalization_name())) { + rowErrors.add("账户分组不能为空"); + } else if (!sectionMap.containsKey(excelRowData.getSectionalization_name())) { + rowErrors.add("账户分组不存在(数据库中无此分组)"); + } + + // 4.2 处理校验结果 + if (!rowErrors.isEmpty()) { + // 记录错误信息 + errorMessages.add(String.format("第%d行:%s", rowNum, String.join(";", rowErrors))); + } else { + // 校验通过,转换为User对象 + User user = new User(); + user.setUser_name(excelRowData.getUser_name()); + user.setUser_email(excelRowData.getUser_email()); + user.setPassword(excelRowData.getPassword()); // 实际场景建议加密存储 + user.setSectionalization_id(sectionMap.get(excelRowData.getSectionalization_name()).getSectionalization_id()); + user.setStatus("正常"); + + insertUserList.add(user); + emailSet.add(excelRowData.getUser_email()); // 记录已添加的邮箱,用于去重 + } + } + + // 5. 处理错误或执行批量插入 + if (!errorMessages.isEmpty()) { + // 组装错误信息 + StringBuilder errorMsg = new StringBuilder(); + errorMsg.append("导入失败,共").append(errorMessages.size()).append("条数据有误:\n"); + errorMessages.forEach(msg -> errorMsg.append(msg).append("\n")); + return ResponseResult.error( errorMsg.toString()); + } else { + // 批量插入数据库 + userService.saveBatch(insertUserList); + return ResponseResult.success("导入成功,共新增" + insertUserList.size() + "条用户数据"); + } + + } catch (Exception e) { + // 捕获异常(如文件格式错误、IO异常等) + return ResponseResult.error("导入失败:" + e.getMessage()); + } + } -- Gitblit v1.7.1