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