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

avatar 2020年1月2日19:01:32 评论 5,460 views

之前介绍了一次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;
  2. import com.baomidou.mybatisplus.annotations.TableId;
  3. import com.baomidou.mybatisplus.annotations.TableName;
  4. import com.baomidou.mybatisplus.enums.IdType;
  5. import lombok.AllArgsConstructor;
  6. import lombok.Builder;
  7. import lombok.Data;
  8. import lombok.NoArgsConstructor;
  9. import javax.validation.constraints.NotBlank;
  10. import java.io.Serializable;
  11. import java.util.Date;
  12. /**
  13.  * <pre>
  14.  *     用户信息(MyBatisPlus)
  15.  * </pre>
  16.  *
  17.  * @author : saysky
  18.  * @date : 2017/11/14
  19.  */
  20. @Data
  21. @TableName("user")
  22. @Builder
  23. @NoArgsConstructor
  24. @AllArgsConstructor
  25. public class User implements Serializable {
  26.     private static final long serialVersionUID = -5144055068797033748L;
  27.     /**
  28.      * 编号,自增
  29.      */
  30.     @TableId(type = IdType.AUTO)
  31.     private Long id;
  32.     /**
  33.      * 用户名
  34.      */
  35.     @NotBlank(message = "用户名不能为空")
  36.     private String username;
  37.     /**
  38.      * 显示名称
  39.      */
  40.     private String nickname;
  41.     /**
  42.      * 密码
  43.      */
  44.     private String password;
  45.     /**
  46.      * 邮箱
  47.      */
  48.     private String email;
  49.     /**
  50.      * 头像
  51.      */
  52.     private String avatar;
  53.     /**
  54.      * 0 正常
  55.      * 1 禁用
  56.      * 2 已删除
  57.      */
  58.     private Integer status = 0;
  59.     /**
  60.      * 创建时间
  61.      */
  62.     private Date createdTime;
  63.     /**
  64.      * 创建人用户名
  65.      */
  66.     private String createdBy;
  67.     /**
  68.      * 更新时间
  69.      */
  70.     private Date updatedTime;
  71.     /**
  72.      * 更新人用户名
  73.      */
  74.     private String updatedBy;
  75.     public User(String username, String nickname, String password, String email, String avatar, Integer status, Date createdTime, String createdBy, Date updatedTime, String updatedBy) {
  76.         this.username = username;
  77.         this.nickname = nickname;
  78.         this.password = password;
  79.         this.email = email;
  80.         this.avatar = avatar;
  81.         this.status = status;
  82.         this.createdTime = createdTime;
  83.         this.createdBy = createdBy;
  84.         this.updatedTime = updatedTime;
  85.         this.updatedBy = updatedBy;
  86.     }
  87. }

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

 

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

不建议直接在 User 实体类上加 excel 相关注解,所以我们创建了一个专门用于 excel 的类,属性类型这里全部用 String,防止类型不一致无法转换报异常

  1. package com.liuyanzhao.sens.excel;
  2. import com.alibaba.excel.annotation.ExcelProperty;
  3. import com.alibaba.excel.annotation.write.style.ColumnWidth;
  4. import com.alibaba.excel.annotation.write.style.HeadRowHeight;
  5. import lombok.AllArgsConstructor;
  6. import lombok.Builder;
  7. import lombok.Data;
  8. import lombok.NoArgsConstructor;
  9. /**
  10.  * @author 言曌
  11.  * @date 2020-01-02 11:39
  12.  */
  13. @Data
  14. @AllArgsConstructor
  15. @NoArgsConstructor
  16. @Builder
  17. @HeadRowHeight(value = 40)
  18. public class UserExcel {
  19.     /**
  20.      * 用户名
  21.      */
  22.     @ExcelProperty(value = "用户名", index = 0)
  23.     @ColumnWidth(value = 15)
  24.     private String username;
  25.     /**
  26.      * 显示名称
  27.      */
  28.     @ExcelProperty(value = "昵称", index = 1)
  29.     @ColumnWidth(value = 15)
  30.     private String nickname;
  31.     /**
  32.      * 密码
  33.      */
  34.     @ExcelProperty(value = "密码", index = 2)
  35.     @ColumnWidth(value = 20)
  36.     private String password;
  37.     /**
  38.      * 邮箱
  39.      */
  40.     @ExcelProperty(value = "邮箱", index = 3)
  41.     @ColumnWidth(value = 20)
  42.     private String email;
  43.     /**
  44.      * 头像
  45.      */
  46.     @ExcelProperty(value = "头像", index = 4)
  47.     @ColumnWidth(value = 20)
  48.     private String avatar;
  49.     /**
  50.      * 0 正常
  51.      * 1 禁用
  52.      */
  53.     @ExcelProperty(value = "状态\r0正常,1 禁用", index = 5)
  54.     @ColumnWidth(value = 20)
  55.     private String status;
  56.     /**
  57.      * 注册时间 yyyy-MM-dd HH:mm:ss格式
  58.      */
  59.     @ExcelProperty(value = "注册时间", index = 6)
  60.     @ColumnWidth(value = 20)
  61.     private String createdTime;
  62. }

 

三、导出工具类

因为模板下载和导出功能,代码几乎一样,为了减少冗余,我们把导出这部分的代码放到一个新建的工具类里

  1. package com.liuyanzhao.sens.utils;
  2. import com.alibaba.excel.EasyExcel;
  3. import com.alibaba.excel.support.ExcelTypeEnum;
  4. import com.alibaba.excel.write.metadata.style.WriteCellStyle;
  5. import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
  6. import org.apache.poi.ss.usermodel.HorizontalAlignment;
  7. import javax.servlet.http.HttpServletResponse;
  8. import java.io.OutputStream;
  9. import java.net.URLEncoder;
  10. import java.util.List;
  11. /**
  12.  * @author 言曌
  13.  * @date 2020-01-02 11:21
  14.  */
  15. public class ExcelUtil {
  16.     /**
  17.      * 导出
  18.      * @param response
  19.      * @param data
  20.      * @param fileName
  21.      * @param sheetName
  22.      * @param clazz
  23.      * @throws Exception
  24.      */
  25.     public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
  26.         //表头样式
  27.         WriteCellStyle headWriteCellStyle = new WriteCellStyle();
  28.         //设置表头居中对齐
  29.         headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
  30.         //内容样式
  31.         WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
  32.         //设置内容靠左对齐
  33.         contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
  34.         HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
  35.         EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
  36.     }
  37.     private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
  38.         fileName = URLEncoder.encode(fileName, "UTF-8");
  39.         response.setContentType("application/vnd.ms-excel");
  40.         response.setCharacterEncoding("utf8");
  41.         response.setHeader("Content-Disposition""attachment;filename=" + fileName + ".xlsx");
  42.         return response.getOutputStream();
  43.     }
  44. }

 

四、模板下载

模板下载其实和导出的意思差不多,只不过数据使我们手写的模板数据,而非真实数据

  1. /**
  2.     * 下载Excel模板
  3.     */
  4.    @GetMapping("/excel/template")
  5.    public void downloadTemplate(HttpServletResponse response) {
  6.        String fileName = "导入用户模板";
  7.        String sheetName = "导入用户模板";
  8.        List<UserExcel> userList = new ArrayList<>();
  9.        userList.add(new UserExcel("saysky""言曌""123456""847064370@qq.com""http://xxx.com/xx.jpg""0""2017-12-31 12:13:14"));
  10.        userList.add(new UserExcel("qiqi""琪琪""123456""666666@qq.com""http://xxx.com/xx.jpg""0""2018-5-20 13:14:00"));
  11.        try {
  12.            ExcelUtil.writeExcel(response, userList, fileName, sheetName, UserExcel.class);
  13.        } catch (Exception e) {
  14.            e.printStackTrace();
  15.        }
  16.    }

 

五、导出数据

查询所有用户,然后写入 excel,通过输出流给浏览器

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

 

六、导入数据

主要是读取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.     }
  14.     // 2.检查是否大于1000条
  15.     if (userExcelList.size() > MAX_USER_IMPORT) {
  16.         throw new GlobalException(CodeMsg.OVER_MAX_USER_IMPORT_LIMIT.fillArgs(MAX_USER_IMPORT));
  17.     }
  18.     // 3.导入校验所有行列格式
  19.     checkImportData(userExcelList);
  20.     // 4.将 userExcelList 转成 userList
  21.     List<User> userList = userExcelList2UserList(userExcelList);
  22.     // 5.入库操作
  23.     userService.batchInsertOrUpdate(userList);
  24. }
  25. private void checkImportData(List<UserExcel> userExcelList) {
  26.     // 行号从第2行开始
  27.     int rowNo = 2;
  28.     // 遍历校验所有行和列
  29.     for (UserExcel userExcel : userExcelList) {
  30.         // 1.校验用户名
  31.         String username = userExcel.getUsername();
  32.         if (StringUtils.isEmpty(username)) {
  33.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "用户名"));
  34.         }
  35.         if (username.length() > 20 || username.length() < 4) {
  36.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "用户名"));
  37.         }
  38.         // 2.校验密码
  39.         String password = userExcel.getPassword();
  40.         if (StringUtils.isEmpty(password)) {
  41.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "密码"));
  42.         }
  43.         if (password.length() > 100 || password.length() < 6) {
  44.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "密码"));
  45.         }
  46.         // 3.校验昵称
  47.         String nickname = userExcel.getNickname();
  48.         if (StringUtils.isEmpty(nickname)) {
  49.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "昵称"));
  50.         }
  51.         if (nickname.length() > 20 || nickname.length() < 2) {
  52.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "昵称"));
  53.         }
  54.         // 4.校验Email
  55.         String email = userExcel.getEmail();
  56.         if (StringUtils.isEmpty(email)) {
  57.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "邮箱"));
  58.         }
  59.         if (!EMAIL_PATTERN.matcher(email).matches()) {
  60.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "邮箱"));
  61.         }
  62.         // 5.校验状态
  63.         String status = userExcel.getStatus();
  64.         if (StringUtils.isEmpty(status)) {
  65.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "状态"));
  66.         }
  67.         if (!"0".equals(status) && !"1".equals(status)) {
  68.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "状态"));
  69.         }
  70.         // 6.校验注册时间
  71.         String createdTime = userExcel.getCreatedTime();
  72.         if (StringUtils.isEmpty(createdTime)) {
  73.             throw new GlobalException(CodeMsg.IMPORT_FIELD_IS_EAMPTY.fillArgs(rowNo, "注册时间"));
  74.         }
  75.         try {
  76.             DATE_TIME_FORMAT.parse(createdTime);
  77.         } catch (ParseException e) {
  78.             throw new GlobalException(CodeMsg.IMPORT_FIELD_FORMAT_ERROR.fillArgs(rowNo, "注册时间"));
  79.         }
  80.     }
  81. }
  82. /**
  83.  * userExcelList转成userList
  84.  *
  85.  * @param userExcelList
  86.  * @return
  87.  */
  88. private List<User> userExcelList2UserList(List<UserExcel> userExcelList) throws ParseException {
  89.     Date now = new Date();
  90.     List<User> userList = new ArrayList<>();
  91.     for (UserExcel userExcel : userExcelList) {
  92.         User user = User.builder()
  93.                 .username(userExcel.getUsername())
  94.                 .password(userExcel.getPassword())
  95.                 .nickname(userExcel.getNickname())
  96.                 .email(userExcel.getEmail())
  97.                 .avatar(userExcel.getAvatar())
  98.                 .status(Integer.valueOf(userExcel.getStatus()))
  99.                 .createdTime(DATE_TIME_FORMAT.parse(userExcel.getCreatedTime())).build();
  100.         user.setCreatedBy("import");
  101.         user.setUpdatedBy("import");
  102.         user.setUpdatedTime(now);
  103.         userList.add(user);
  104.     }
  105.     return userList;
  106. }

具体 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<>();
  4.        // 1.excel同步读取数据
  5.        try {
  6.            ExcelReader excelReader = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(UserExcel.class).build();
  7.            List<ReadSheet> sheetList = excelReader.excelExecutor().sheetList();
  8.            List<UserExcel> childUserExcelList = new ArrayList<>();
  9.            for (ReadSheet sheet : sheetList) {
  10.                childUserExcelList = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(UserExcel.class).sheet(sheet.getSheetNo()).doReadSync();
  11.            }
  12.            userExcelList.addAll(childUserExcelList);
  13.        } catch (Exception e) {
  14.            e.printStackTrace();
  15.        }
  16.        // 2.检查是否大于1000条
  17.        if (userExcelList.size() > MAX_USER_IMPORT) {
  18.            throw new GlobalException(CodeMsg.OVER_MAX_USER_IMPORT_LIMIT.fillArgs(MAX_USER_IMPORT));
  19.        }
  20.        // 3.导入校验所有行列格式
  21.        checkImportData(userExcelList);
  22.        // 4.将 userExcelList 转成 userList
  23.        List<User> userList = userExcelList2UserList(userExcelList);
  24.        // 5.入库操作
  25.        userService.batchInsertOrUpdate(userList);
  26.    }

 

  • 微信
  • 交流学习,有偿服务
  • weinxin
  • 博客/Java交流群
  • 资源分享,问题解决,技术交流。群号:590480292
  • weinxin
avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: