Excel轻松操控：掌握EasyPoi的妙用技巧（二）https://developer.aliyun.com/article/1480479
2.3.5 修改实体类
/** * (User)实体类 * * @author 莫提 * @since 2020-12-13 20:39:36 */ @Data @NoArgsConstructor @AllArgsConstructor @Builder @ExcelTarget("user") public class User implements Serializable { private static final long serialVersionUID = 587947964930607265L; /** * 用户ID */ @Excel(name = "ID",suffix = "号") private Integer userId; /** * 用户名 */ @Excel(name = "姓名") private String userName; /** * 工龄 */ @Excel(name = "工龄",suffix = "年") private Integer workAge; /** * 性别 */ @Excel(name = "性别",replace = {"男_1","女_0"}) private Integer sex; /** * 生日 */ @Excel(name = "生日",format = "yyyy年MM月dd日",width = 20) private Date birthday; /** * 部门ID */ @ExcelIgnore private Integer deptId; /** * 所属部门 */ @ExcelEntity private Department department; }
package com.moti.entity; import cn.afterturn.easypoi.excel.annotation.Excel; import cn.afterturn.easypoi.excel.annotation.ExcelTarget; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import java.io.Serializable; /** * (Department)实体类 * * @author 莫提 * @since 2020-12-13 20:39:38 */ @Data @NoArgsConstructor @AllArgsConstructor @Builder @ExcelTarget("department") public class Department implements Serializable { private static final long serialVersionUID = 164895400081727426L; /** * 部门ID */ private Integer deptId; /** * 部门名称 */ @Excel(name = "部门") private String deptName; /** * 地址 */ private String address; }
2.3.6 编写前端页面
<!DOCTYPE html> <html lang="en" xmlns:th="http://www.thymeleaf.org"> <head> <meta charset="UTF-8"> <title>主页</title> <!--引入核心css--> <link rel="stylesheet" href="boot/css/bootstrap.min.css"> <!--更好的更好的响应式支持--> <meta name="viewport" content="width=device-width, initial-scale=1"> <script src="js/jquery-3.4.1.js"></script> </head> <body> <hr> <div class="container"> <div class="row"> <div class="col-12"> <h3>选择Excle文件导入</h3> <form class="form-inline" method="post" action="importExcel" enctype="multipart/form-data"> <input type="file" class="form-control" name="file"> <input type="submit" class="btn btn-danger" value="导入"> </form> </div> <br> <div class="col-12"> <table class="table table-bordered"> <thead> <tr> <th>ID</th> <th>姓名</th> <th>部门</th> <th>性别</th> <th>工龄</th> <th>出生日期</th> </tr> </thead> <tbody> <tr th:each="user:${users}"> <td th:text="${user.userId}"></td> <td th:text="${user.userName}"></td> <td th:text="${user.department.deptName}"></td> <td th:if="${user.sex == 1}">男</td> <td th:if="${user.sex == 0}">女</td> <td th:text="${user.workAge}"></td> <td th:text="${#dates.format(user.birthday,'yyyy-MM-dd')}"></td> </tr> </tbody> </table> <br> <a href="exportExcel" class="btn btn-success btn-sm">导出</a> </div> </div> </div> </body> </html>
2.3.7 编写控制层
/** * @ClassName: HelloController * @Description: * @author: 莫提 * @date 2020/11/27 8:55 * @Version: 1.0 */ @Controller @Slf4j public class HelloController { @Autowired private UserService userService; @Autowired private DepartmentService departmentService; @GetMapping("/") public String hello(Map<String,Object> map){ // 获取全部用户 List<User> users = userService.listUsers(); users.forEach(user -> { Department department = departmentService.getById(user.getDeptId()); user.setDepartment(department); }); map.put("users",users); return "index"; } /** * 导入 */ @PostMapping("/importExcel") public String importExcel(MultipartFile file) throws Exception { if (ObjectUtils.isEmpty(file) || file.getSize() == 0){ return "redirect:/"; } log.info("接收到文件：{}",file.getOriginalFilename()); // 参数1：文件流 InputStream stream = file.getInputStream(); // 参数2：导入类型 ImportParams params = new ImportParams(); // 标题占用多少行 params.setTitleRows(1); // 头部属性占用多少行 params.setHeadRows(1); // 从指定的sheet的下标开始读取 // params.setStartSheetIndex(1); // 读取sheet的数量，需要和上面的配合 // params.setSheetNum(1); // 对Excle进行合法参数校验 params.setImportFields(new String[]{"姓名","部门"}); List<User> users = ExcelImportUtil.importExcel(stream, User.class,params); // 遍历结果，插入到数据库 users.forEach(user -> { Department build = Department.builder().deptName(user.getDepartment().getDeptName()).build(); List<Department> departments = departmentService.listDepartments(build); if (departments.size() > 0){ user.setDeptId(departments.get(0).getDeptId()); } userService.insert(user); }); log.info("导入用户：{}",users); return "redirect:/"; } /** * 导出Excel */ @GetMapping("/exportExcel") public void exportExcel(HttpServletResponse response) throws IOException { response.setHeader("content-disposition", "attachment;fileName="+ URLEncoder.encode("用户列表.xls","UTF-8")); ServletOutputStream outputStream = response.getOutputStream(); // 查询所有用户 List<User> users = userService.listUsers(); users.forEach(user -> { Department department = departmentService.getById(user.getDeptId()); user.setDepartment(department); }); // 生成文件的信息 ExportParams params = new ExportParams(); params.setTitle("导出的用户信息"); params.setSheetName("用户信息"); Workbook workbook = ExcelExportUtil.exportExcel(params, User.class, users); // 输出 workbook.write(outputStream); // 关闭资源 outputStream.close(); workbook.close(); } }
导入之后
