EasyExcel2.0 实现模板下载、导入和导出功能

avatar 2020年01月02日19:01:32 6 42511 views
博主分享免费Java教学视频，B站账号：Java刘哥 ，长期提供技术问题解决、项目定制：本站商品点此
之前介绍了一次EasyExcel 1.x 版本的导入导出，网上大部分教程也是基于 1.x 版本。

2.x 版本和1.x版本相差挺大的，用起来也简单很多。

本文以对 User 的导出导出为例。

代码地址：https://github.com/saysky/sensboot

官方文档地址：https://alibaba-easyexcel.github.io/


一、依赖


pom.xml 中添加依赖

  1. <dependency>
  2.     <groupId>com.alibaba</groupId>
  3.     <artifactId>easyexcel</artifactId>
  4.     <version>2.1.4</version>
  5. </dependency>

注意：主要确保不要有其他版本的依赖或者其他版本的 POI，如果有的话升级 POI 版本


二、实体类和DTO


1.User实体类
  1. package com.liuyanzhao.sens.entity;
  3. import com.baomidou.mybatisplus.annotations.TableId;
  4. import com.baomidou.mybatisplus.annotations.TableName;
  5. import com.baomidou.mybatisplus.enums.IdType;
  6. import lombok.AllArgsConstructor;
  7. import lombok.Builder;
  8. import lombok.Data;
  9. import lombok.NoArgsConstructor;
  11. import javax.validation.constraints.NotBlank;
  12. import java.io.Serializable;
  13. import java.util.Date;
  15. /**
  16.  * <pre>
  17.  *     用户信息(MyBatisPlus)
  18.  * </pre>
  19.  *
  20.  * @author : saysky
  21.  * @date : 2017/11/14
  22.  */
  23. @Data
  24. @TableName("user")
  25. @Builder
  26. @NoArgsConstructor
  27. @AllArgsConstructor
  28. public class User implements Serializable {
  30.     private static final long serialVersionUID = -5144055068797033748L;
  32.     /**
  33.      * 编号，自增
  34.      */
  35.     @TableId(type = IdType.AUTO)
  36.     private Long id;
  38.     /**
  39.      * 用户名
  40.      */
  41.     @NotBlank(message = "用户名不能为空")
  42.     private String username;
  44.     /**
  45.      * 显示名称
  46.      */
  47.     private String nickname;
  49.     /**
  50.      * 密码
  51.      */
  52.     private String password;
  54.     /**
  55.      * 邮箱
  56.      */
  57.     private String email;
  59.     /**
  60.      * 头像
  61.      */
  62.     private String avatar;
  64.     /**
  65.      * 0 正常
  66.      * 1 禁用
  67.      * 2 已删除
  68.      */
  69.     private Integer status = 0;
  71.     /**
  72.      * 创建时间
  73.      */
  74.     private Date createdTime;
  76.     /**
  77.      * 创建人用户名
  78.      */
  79.     private String createdBy;
  81.     /**
  82.      * 更新时间
  83.      */
  84.     private Date updatedTime;
  86.     /**
  87.      * 更新人用户名
  88.      */
  89.     private String updatedBy;
  92.     public User(String username, String nickname, String password, String email, String avatar, Integer status, Date createdTime, String createdBy, Date updatedTime, String updatedBy) {
  93.         this.username = username;
  94.         this.nickname = nickname;
  95.         this.password = password;
  96.         this.email = email;
  97.         this.avatar = avatar;
  98.         this.status = status;
  99.         this.createdTime = createdTime;
  100.         this.createdBy = createdBy;
  101.         this.updatedTime = updatedTime;
  102.         this.updatedBy = updatedBy;
  103.     }
  104. }

注意：这里我用的是 lombok 生成 getter/setter 和构造器，用的是 mybatis-plus 作为 ORM 框架



2.UserExcel 用于导入导出的对象

不建议直接在 User 实体类上加 excel 相关注解，所以我们创建了一个专门用于 excel 的类，属性类型这里全部用 String，防止类型不一致无法转换报异常
  1. package com.liuyanzhao.sens.excel;
  3. import com.alibaba.excel.annotation.ExcelProperty;
  4. import com.alibaba.excel.annotation.write.style.ColumnWidth;
  5. import com.alibaba.excel.annotation.write.style.HeadRowHeight;
  6. import lombok.AllArgsConstructor;
  7. import lombok.Builder;
  8. import lombok.Data;
  9. import lombok.NoArgsConstructor;
  11. /**
  12.  * @author 言曌
  13.  * @date 2020-01-02 11:39
  14.  */
  15. @Data
  16. @AllArgsConstructor
  17. @NoArgsConstructor
  18. @Builder
  19. @HeadRowHeight(value = 40)
  20. public class UserExcel {
  22.     /**
  23.      * 用户名
  24.      */
  25.     @ExcelProperty(value = "用户名", index = 0)
  26.     @ColumnWidth(value = 15)
  27.     private String username;
  29.     /**
  30.      * 显示名称
  31.      */
  32.     @ExcelProperty(value = "昵称", index = 1)
  33.     @ColumnWidth(value = 15)
  34.     private String nickname;
  36.     /**
  37.      * 密码
  38.      */
  39.     @ExcelProperty(value = "密码", index = 2)
  40.     @ColumnWidth(value = 20)
  41.     private String password;
  43.     /**
  44.      * 邮箱
  45.      */
  46.     @ExcelProperty(value = "邮箱", index = 3)
  47.     @ColumnWidth(value = 20)
  48.     private String email;
  50.     /**
  51.      * 头像
  52.      */
  53.     @ExcelProperty(value = "头像", index = 4)
  54.     @ColumnWidth(value = 20)
  55.     private String avatar;
  57.     /**
  58.      * 0 正常
  59.      * 1 禁用
  60.      */
  61.     @ExcelProperty(value = "状态\r0正常,1 禁用", index = 5)
  62.     @ColumnWidth(value = 20)
  63.     private String status;
  65.     /**
  66.      * 注册时间 yyyy-MM-dd HH:mm:ss格式
  67.      */
  68.     @ExcelProperty(value = "注册时间", index = 6)
  69.     @ColumnWidth(value = 20)
  70.     private String createdTime;
  71. }


三、导出工具类


因为模板下载和导出功能，代码几乎一样，为了减少冗余，我们把导出这部分的代码放到一个新建的工具类里
  1. package com.liuyanzhao.sens.utils;
  3. import com.alibaba.excel.EasyExcel;
  4. import com.alibaba.excel.support.ExcelTypeEnum;
  5. import com.alibaba.excel.write.metadata.style.WriteCellStyle;
  6. import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
  7. import org.apache.poi.ss.usermodel.HorizontalAlignment;
  9. import javax.servlet.http.HttpServletResponse;
  10. import java.io.OutputStream;
  11. import java.net.URLEncoder;
  12. import java.util.List;
  14. /**
  15.  * @author 言曌
  16.  * @date 2020-01-02 11:21
  17.  */
  19. public class ExcelUtil {
  21.     /**
  22.      * 导出
  23.      * @param response
  24.      * @param data
  25.      * @param fileName
  26.      * @param sheetName
  27.      * @param clazz
  28.      * @throws Exception
  29.      */
  30.     public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
  31.         //表头样式
  32.         WriteCellStyle headWriteCellStyle = new WriteCellStyle();
  33.         //设置表头居中对齐
  34.         headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
  35.         //内容样式
  36.         WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
  37.         //设置内容靠左对齐
  38.         contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
  39.         HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
  40.         EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
  41.     }
  44.     private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
  45.         fileName = URLEncoder.encode(fileName, "UTF-8");
  46.         response.setContentType("application/vnd.ms-excel");
  47.         response.setCharacterEncoding("utf8");
  48.         response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
  49.         return response.getOutputStream();
  50.     }
  51. }


四、模板下载


模板下载其实和导出的意思差不多，只不过数据使我们手写的模板数据，而非真实数据
  1. /**
  2.     * 下载Excel模板
  3.     */
  4.    @GetMapping("/excel/template")
  5.    public void downloadTemplate(HttpServletResponse response) {
  6.        String fileName = "导入用户模板";
  7.        String sheetName = "导入用户模板";
  9.        List<UserExcel> userList = new ArrayList<>();
  11.        userList.add(new UserExcel("saysky", "言曌", "123456", "847064370@qq.com", "http://xxx.com/xx.jpg", "0", "2017-12-31 12:13:14"));
  12.        userList.add(new UserExcel("qiqi", "琪琪", "123456", "666666@qq.com", "http://xxx.com/xx.jpg", "0", "2018-5-20 13:14:00"));
  14.        try {
  15.            ExcelUtil.writeExcel(response, userList, fileName, sheetName, UserExcel.class);
  16.        } catch (Exception e) {
  17.            e.printStackTrace();
  18.        }
  19.    }


五、导出数据


查询所有用户，然后写入 excel，通过输出流给浏览器
  1. /**
  2.  * 导出
  3.  */
  4. @GetMapping("/excel/export")
  5. public void exportData(HttpServletResponse response) {
  6.     String fileName = "用户列表";
  7.     String sheetName = "用户列表";
  9.     DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  10.     List<User> userList = userService.findAll();
  11.     List<UserExcel> userExcelList = new ArrayList<>();
  12.     for (User user : userList) {
  13.         UserExcel userExcel = UserExcel.builder()
  14.                 .username(user.getUsername())
  15.                 .password(user.getPassword())
  16.                 .nickname(user.getNickname())
  17.                 .email(user.getEmail())
  18.                 .avatar(user.getAvatar())
  19.                 .status(String.valueOf(user.getStatus()))
  20.                 .createdTime(dateFormat.format(user.getCreatedTime())).build();
  22.         userExcelList.add(userExcel);
  24.     }
  26.     try {
  27.         ExcelUtil.writeExcel(response, userExcelList, fileName, sheetName, UserExcel.class);
  28.     } catch (Exception e) {
  29.         e.printStackTrace();
  30.     }
  31. }


六、导入数据


主要是读取excel数据，然后进行自定义校验格式，然后入库。

这里导入都是用同步的，就是全部读取后再做写入操作。

如果需要异步那种，导入几条入库几条可以参考官网文档
  1. /**
  2.  * 导入：同步读，单sheet
  3.  * 注意：这里为了介绍 excel导入导出，代码都写在 controller，实际项目开发中，校验和处理代码建议放到 service
  4.  */
  5. @PostMapping("/excel/import")
  6. public void importData(MultipartFile file) throws ParseException {
  7.     List<UserExcel> userExcelList = null;
  8.     // 1.excel同步读取数据
  9.     try {
  10.         userExcelList = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(UserExcel.class).sheet().doReadSync();
  11.     } catch (Exception e) {
  12.         e.printStackTrace();
  13.     }
  15.     // 2.检查是否大于1000条
  16.     if (userExcelList.size() > MAX_USER_IMPORT) {
  17.         throw new GlobalException(CodeMsg.OVER_MAX_USER_IMPORT_LIMIT.fillArgs(MAX_USER_IMPORT));
  18.     }
  20.     // 3.导入校验所有行列格式
  21.     checkImportData(userExcelList);
  23.     // 4.将 userExcelList 转成 userList
  24.     List<User> userList = userExcelList2UserList(userExcelList);
  26.     // 5.入库操作
  27.     userService.batchInsertOrUpdate(userList);
  30. }
  32. private void checkImportData(List<UserExcel> userExcelList) {
  33.     // 行号从第2行开始
  34.     int rowNo = 2;
  35.     // 遍历校验所有行和列
  36.     for (UserExcel userExcel : userExcelList) {
  37.         // 1.校验用户名
  38.         String username = userExcel.getUsername();
  39.         if (StringUtils.isEmpty(username)) {
  40.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "用户名"));
  41.         }
  42.         if (username.length() > 20 || username.length() < 4) {
  43.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "用户名"));
  44.         }
  46.         // 2.校验密码
  47.         String password = userExcel.getPassword();
  48.         if (StringUtils.isEmpty(password)) {
  49.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "密码"));
  50.         }
  51.         if (password.length() > 100 || password.length() < 6) {
  52.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "密码"));
  53.         }
  55.         // 3.校验昵称
  56.         String nickname = userExcel.getNickname();
  57.         if (StringUtils.isEmpty(nickname)) {
  58.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "昵称"));
  59.         }
  60.         if (nickname.length() > 20 || nickname.length() < 2) {
  61.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "昵称"));
  62.         }
  64.         // 4.校验Email
  65.         String email = userExcel.getEmail();
  66.         if (StringUtils.isEmpty(email)) {
  67.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "邮箱"));
  68.         }
  69.         if (!EMAIL_PATTERN.matcher(email).matches()) {
  70.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "邮箱"));
  71.         }
  73.         // 5.校验状态
  74.         String status = userExcel.getStatus();
  75.         if (StringUtils.isEmpty(status)) {
  76.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "状态"));
  77.         }
  78.         if (!"0".equals(status) && !"1".equals(status)) {
  79.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "状态"));
  80.         }
  82.         // 6.校验注册时间
  83.         String createdTime = userExcel.getCreatedTime();
  84.         if (StringUtils.isEmpty(createdTime)) {
  85.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "注册时间"));
  86.         }
  87.         try {
  88.             DATE_TIME_FORMAT.parse(createdTime);
  89.         } catch (ParseException e) {
  90.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "注册时间"));
  91.         }
  92.     }
  93. }
  95. /**
  96.  * userExcelList转成userList
  97.  *
  98.  * @param userExcelList
  99.  * @return
  100.  */
  101. private List<User> userExcelList2UserList(List<UserExcel> userExcelList) throws ParseException {
  102.     Date now = new Date();
  103.     List<User> userList = new ArrayList<>();
  104.     for (UserExcel userExcel : userExcelList) {
  105.         User user = User.builder()
  106.                 .username(userExcel.getUsername())
  107.                 .password(userExcel.getPassword())
  108.                 .nickname(userExcel.getNickname())
  109.                 .email(userExcel.getEmail())
  110.                 .avatar(userExcel.getAvatar())
  111.                 .status(Integer.valueOf(userExcel.getStatus()))
  112.                 .createdTime(DATE_TIME_FORMAT.parse(userExcel.getCreatedTime())).build();
  114.         user.setCreatedBy("import");
  115.         user.setUpdatedBy("import");
  116.         user.setUpdatedTime(now);
  117.         userList.add(user);
  118.     }
  119.     return userList;
  120. }

具体 service 层入库代码和其他常量等其他代码这里就不贴了，需要完整代码请访问上面提供的 git 地址


七、多 sheet 导入


有时候导入要求支持多个工作表导入

只需要通过  excelReader.excelExecutor().sheetList() 获得 sheet 列表就行，然后分别进行上面的导入操作
  1. @PostMapping("/excel/import")
  2.    public void importDataByMoreSheet(MultipartFile file) throws ParseException, IOException {
  3.        List<UserExcel> userExcelList = new ArrayList<>();
  5.        // 1.excel同步读取数据
  6.        try {
  7.            ExcelReader excelReader = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(UserExcel.class).build();
  8.            List<ReadSheet> sheetList = excelReader.excelExecutor().sheetList();
  9.            List<UserExcel> childUserExcelList = new ArrayList<>();
  10.            for (ReadSheet sheet : sheetList) {
  11.                childUserExcelList = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(UserExcel.class).sheet(sheet.getSheetNo()).doReadSync();
  12.            }
  13.            userExcelList.addAll(childUserExcelList);
  14.        } catch (Exception e) {
  15.            e.printStackTrace();
  16.        }
  18.        // 2.检查是否大于1000条
  19.        if (userExcelList.size() > MAX_USER_IMPORT) {
  20.            throw new GlobalException(CodeMsg.OVER_MAX_USER_IMPORT_LIMIT.fillArgs(MAX_USER_IMPORT));
  21.        }
  23.        // 3.导入校验所有行列格式
  24.        checkImportData(userExcelList);
  26.        // 4.将 userExcelList 转成 userList
  27.        List<User> userList = userExcelList2UserList(userExcelList);
  29.        // 5.入库操作
  30.        userService.batchInsertOrUpdate(userList);
  32.    }

历史上的今天
一月
02日
  • 微信
  • 交流学习，资料分享
  • weinxin
  • 个人淘宝
  • 店铺名：言曌博客咨询部

  • (部分商品未及时上架淘宝)
avatar

发表评论

avatar 登录者：匿名
匿名评论，评论回复后会有邮件通知

  

已通过评论：0   待审核评论数：0