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.User;
|
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.time.LocalDateTime;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@RestController
|
@RequestMapping("/user")
|
@Api(value = "账号相关接口", tags = "设置-账号管理")
|
public class UserController {
|
@Autowired
|
private UserService userService;
|
|
/**
|
* 新增用户
|
*/
|
|
@PostMapping
|
@ApiOperation(value = "添加")
|
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.success("该邮箱已存在");
|
}
|
user.setStatus("正常");
|
userService.save(user);
|
return ResponseResult.success();
|
|
}
|
|
|
/**
|
* 修改用户
|
*/
|
@PutMapping
|
@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.success("该邮箱已存在");
|
}
|
userService.updateById(user);
|
return ResponseResult.success();
|
|
}
|
|
/**
|
* 删除用户
|
*/
|
|
@DeleteMapping("/{user_id}")
|
@ApiOperation(value = "删除")
|
public ResponseResult delete(@PathVariable("user_id") Integer user_id) {
|
userService.removeById(user_id);
|
return ResponseResult.success();
|
|
}
|
/**
|
* 分页查询
|
*/
|
@GetMapping
|
@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));
|
}
|
|
/**
|
* 下载模板
|
*/
|
|
@GetMapping("/downloadTemplate")
|
@ApiOperation("下载模板")
|
public ResponseEntity<byte[]> downloadTemplate() throws IOException {
|
// 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=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());
|
}
|
}
|
|
|
|
|
}
|
