前言

在平时的业务系统开发中，少不了需要用到导出、导入excel功能。Apache POI不做过多介绍
主要学习下，EasyPoi和EasyExcel的使用和区别对比。

官网
https://www.yuque.com/easyexcel

EasyPoi和EasyExcel都是基于Apache POI进行二次开发的。
不同点在于：

  1. EasyPoi 在读写数据的时候，优先是先将数据写入内存，优点是读写性能非常高，但是当数据量很大的时候，会出现OOM，当然它也提供了 sax 模式的读写方式，需要调用特定的方法实现。它主要的特点就是将更多重复的工作，全部简单化，避免编写重复的代码！
  2. EasyExcel 基于sax模式一行一行解析进行读写数据，不会出现OOM情况，在并发量很大的情况下，程序在经过高并发场景的验证下，依然能稳定运行！相对于 EasyPoi 来说，读写性能稍慢！

SAX（simple API for XML） 是一种XML解析的替代方法。相比于DOM，SAX是一种速度更快，更有效的方法。它逐行扫描文档，一边扫描一边解析。而且相比于DOM，SAX可以在解析文档的任意时刻停止解析，但任何事物都有其相反的一面，对于SAX来说就是操作复杂。

EasyPoi 对定制化的导出支持非常的丰富，如果当前的项目需求，并发量不大、数据量也不大，但是需要导出 excel 的文件样式千差万别，那么我推荐你用 EasyPoi；反之，使用 EasyExcel ！

本篇学习EasyExcel的使用 👇

简单使用测试

pom.xml添加依赖

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

对于简单的导入导出封装了一个XlxsTool工具类

  1. package com.wg.easyexcel;
  2. import com.alibaba.excel.EasyExcel;
  3. import com.alibaba.excel.ExcelWriter;
  4. import com.alibaba.excel.context.AnalysisContext;
  5. import com.alibaba.excel.event.AnalysisEventListener;
  6. import com.alibaba.excel.read.metadata.holder.ReadSheetHolder;
  7. import com.alibaba.excel.write.metadata.WriteSheet;
  8. import org.slf4j.Logger;
  9. import org.slf4j.LoggerFactory;
  11. import java.io.FileInputStream;
  12. import java.io.InputStream;
  13. import java.util.*;
  14. import java.util.concurrent.atomic.AtomicInteger;
  18. public class XlxsTool {
  20.     private static final Logger log = LoggerFactory.getLogger(XlxsTool.class);
  22.     public static <T> List<T> loadExcel(String excelPath, Class<T> clazz) {
  23.         try (FileInputStream fis = new FileInputStream(excelPath)) {
  24.             return loadExcel(fis, clazz);
  25.         } catch (Exception xe) {
  26.             throw new RuntimeException(xe);
  27.         }
  28.     }
  30.     public static <T> List<T> loadExcel(InputStream inputStream, Class<T> clazz) {
  31.         ExcelDataListener<T> excelDataListener = new ExcelDataListener<>();
  32.         EasyExcel.read(inputStream, clazz, excelDataListener).sheet().doRead();
  33.         return excelDataListener.getResult();
  34.     }
  36.     public static <T> Map<String, List<T>> loadAllExcel(String excelPath, Class<T> clazz) {
  37.         try (FileInputStream fis = new FileInputStream(excelPath)) {
  38.             return loadAllExcel(fis, clazz);
  39.         } catch (Exception xe) {
  40.             throw new RuntimeException(xe);
  41.         }
  42.     }
  44.     public static <T> Map<String, List<T>> loadAllExcel(InputStream inputStream, Class<T> clazz) {
  45.         ExcelDataAllSheetListener<T> excelDataListener = new ExcelDataAllSheetListener<>();
  46.         EasyExcel.read(inputStream, clazz, excelDataListener).doReadAll();
  47.         return excelDataListener.getResult();
  48.     }
  50.     public static <T> void export(String filePath, Class<T> type, List<T> mainExcelData) {
  51.         EasyExcel.write(filePath, type).sheet().doWrite(mainExcelData);
  52.     }
  54.     public static <T> void export(String filePath, Class<T> type, Map<String, List<T>> mainExcelData) {
  55.         ExcelWriter excelWriter = EasyExcel.write(filePath, type).build();
  56.         AtomicInteger i = new AtomicInteger();
  57.         mainExcelData.forEach((key, value) -> {
  58.             WriteSheet writeSheet = EasyExcel.writerSheet(i.getAndIncrement(), key).build();
  59.             excelWriter.write(value, writeSheet);
  60.         });
  61.         excelWriter.finish();
  62.     }
  64.     public static class ExcelDataListener<T> extends AnalysisEventListener<T> {
  65.         private final List<T> result = new ArrayList<>();
  67.         @Override
  68.         public void invoke(T data, AnalysisContext context) {
  69.             result.add(data);
  70.         }
  72.         @Override
  73.         public void doAfterAllAnalysed(AnalysisContext context) {
  75.         }
  77.         public List<T> getResult() {
  78.             return result;
  79.         }
  80.     }
  82.     public static class ExcelDataAllSheetListener<T> extends AnalysisEventListener<T> {
  83.         private final Map<String, List<T>> result = new LinkedHashMap<>();
  85.         @Override
  86.         public void invoke(T data, AnalysisContext context) {
  87.             ReadSheetHolder readSheetHolder = context.readSheetHolder();
  88.             String sheetName = readSheetHolder.getSheetName();
  89.             List<T> ts = result.computeIfAbsent(sheetName, k -> new ArrayList<>());
  90.             ts.add(data);
  91.         }
  93.         @Override
  94.         public void doAfterAllAnalysed(AnalysisContext context) {
  96.         }
  98.         public Map<String, List<T>> getResult() {
  99.             return result;
  100.         }
  101.     }
  103. }

创建实体类

  1. package com.wg.easyexcel.entity;
  3. import com.alibaba.excel.annotation.ExcelIgnore;
  4. import com.alibaba.excel.annotation.ExcelProperty;
  5. import com.alibaba.excel.annotation.format.DateTimeFormat;
  6. import com.alibaba.excel.annotation.write.style.ColumnWidth;
  7. import com.wg.easyexcel.util.DefineConverter;
  8. import lombok.Data;
  9. import lombok.EqualsAndHashCode;
  11. import java.util.Date;
  13. @Data
  14. @EqualsAndHashCode(callSuper = false)
  15. public class Member {
  16.         @ExcelProperty(value = "ID", index = 0)
  17.         @ColumnWidth(10)
  18.         private Long id;
  20.         @ExcelProperty(value = "用户名", index = 1)
  21.         @ColumnWidth(20)
  22.         private String username;
  24.         @ExcelIgnore
  25.         private String password;
  27.         @ExcelProperty(value = "昵称", index = 2)
  28.         @ColumnWidth(20)
  29.         private String nickname;
  31.         @ExcelProperty(value = "出生日期", index = 3)
  32.         @ColumnWidth(20)
  33.         @DateTimeFormat("yyyy-MM-dd")
  34.         private Date birthday;
  36.         @ExcelProperty(value = "手机号", index = 4)
  37.         @ColumnWidth(20)
  38.         private String phone;
  40.         @ExcelIgnore
  41.         private String icon;
  43.         @ExcelProperty(value = "性别", index = 5, converter = DefineConverter.GenderConverter.class)
  44.         @ColumnWidth(10)
  45.         private Integer gender;
  46. }

类注解
@Data与@EqualsAndHashCode的使用

用到的easyexcel中的注解，一般常用的也就下面这几个

  • @ExcelProperty(value = "ID", index = 0)：index为excel中列的索引顺序
  • @ColumnWidth(10)：列的宽度
  • @DateTimeFormat("yyyy-MM-dd")：时间格式
  • @ExcelIgnore：忽略项

    其他注解参考 https://www.cnblogs.com/bluekang/p/13438666.html

:::info @ExcelProperty 是最重要的一个注解，有三个参数value,index,converter。分别代表列名，列序号，数据转换方式

value和index只能二选一，通常不用设置converter

  1. value ：通过标题文本对应
  2. index ：通过文本行号对应
  3. converter ：通常入库和出库转换使用，如性别，入库0和1，出库男和女 :::

创建自定义转换器

  1. package com.wg.easyexcel.util;
  3. import com.alibaba.excel.converters.Converter;
  4. import com.alibaba.excel.converters.ReadConverterContext;
  5. import com.alibaba.excel.converters.WriteConverterContext;
  6. import com.alibaba.excel.enums.CellDataTypeEnum;
  7. import com.alibaba.excel.metadata.data.WriteCellData;
  8. import com.alibaba.excel.util.StringUtils;
  10. /**
  11.  * 自定义转换器
  12.  */
  13. public class DefineConverter {
  15.      // excel性别转换器
  16.      // String与Integer转换
  17.     public static class GenderConverter implements Converter<Integer> {
  18.         @Override
  19.         public Class<?> supportJavaTypeKey() {
  20.             // 对象类型属性
  21.             return Integer.class;
  22.         }
  24.         @Override
  25.         public CellDataTypeEnum supportExcelTypeKey() {
  26.             // CellData属性类型
  27.             return CellDataTypeEnum.STRING;
  28.         }
  30.         @Override
  31.         public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
  32.             // CellData转对象属性
  33.             String cellStr = context.getReadCellData().getStringValue();
  34.             if (StringUtils.isEmpty(cellStr)){
  35.                 return null;
  36.             }
  37.             if ("男".equals(cellStr)){
  38.                 return 0;
  39.             }else if ("女".equals(cellStr)){
  40.                 return 1;
  41.             }else {
  42.                 return null;
  43.             }
  44.         }
  46.         @Override
  47.         public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
  48.             // 对象属性转CellData
  49.             Integer cellValue = context.getValue();
  50.             if (cellValue == null){
  51.                 return new WriteCellData<>("");
  52.             }
  53.             if (cellValue == 0){
  54.                 return new WriteCellData<>("男");
  55.             }else if (cellValue == 1) {
  56.                 return new WriteCellData<>("女");
  57.             } else {
  58.                 return new WriteCellData<>("");
  59.             }
  60.         }
  61.     }
  62. }

在pom.xml中添加juint的依赖，使用junit测试

  1. package com.wg.easyexcel;
  2. import com.wg.easyexcel.entity.Member;
  3. import org.junit.Test;
  4. import org.slf4j.Logger;
  5. import org.slf4j.LoggerFactory;
  7. import java.util.*;
  9. // 简单导入导出
  10. public class simpleDemo {
  12.     private static final Logger log = LoggerFactory.getLogger(simpleDemo.class);
  13.     private static final String readExcelPath = "/Users/weigang/IdeaProjects/study/easyexcel/src/main/resources/file/simple/simple-excel.xlsx";
  14.     private static final String outputExcelPath = "/Users/weigang/IdeaProjects/study/easyexcel/src/main/resources/file/simple/simple-excel-output.xlsx";
  16.     @Test
  17.     public void test(){
  18.         // 单 sheet 读取
  19.         List<Member> dataList = XlxsTool.loadExcel(readExcelPath, Member.class);
  21.         // 多sheet 读取
  22.         // key为sheet页的名字，value为对应sheet页的数据
  23.         // Map<String ,List<Member>> dataMap = XlxsTool.loadAllExcel(readExcelPath, Member.class);
  25.         // 导出
  26.         XlxsTool.export(outputExcelPath, Member.class, dataList);
  27.     }
  28. }

效果
image.png�

实例：mybatis+mysql+easyexcel

案例一：简单导入导出Member表

Mysql数据库
member建表语句

  1. SET NAMES utf8mb4;
  2. SET FOREIGN_KEY_CHECKS = 0;
  4. -- ----------------------------
  5. -- Table structure for member
  6. -- ----------------------------
  7. DROP TABLE IF EXISTS `member`;
  8. CREATE TABLE `member` (
  9.   `id` bigint NOT NULL,
  10.   `username` varchar(255) DEFAULT NULL,
  11.   `password` varchar(255) DEFAULT NULL,
  12.   `nickname` varchar(255) DEFAULT NULL,
  13.   `birthday` varchar(255) DEFAULT NULL,
  14.   `phone` varchar(255) DEFAULT NULL,
  15.   `gender` int DEFAULT NULL,
  16.   PRIMARY KEY (`id`)
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  19. -- ----------------------------
  20. -- Records of member
  21. -- ----------------------------
  22. BEGIN;
  23. INSERT INTO `member` VALUES (1, 'weig', '1234', 'gangzi', '1995-05-27', '15900330611', 0);
  24. INSERT INTO `member` VALUES (2, 'lijr', '1234', 'ruru', '1995-07-01', '13920803165', 1);
  25. INSERT INTO `member` VALUES (3, 'weip', '1234', 'erdan', '1997-04-25', '15900330611', 0);
  26. COMMIT;
  28. SET FOREIGN_KEY_CHECKS = 1;

定义对应实体类 Member

  1. @Data
  2. @EqualsAndHashCode(callSuper = false)
  3. public class Member {
  4.         @ExcelProperty(value = "ID", index = 0)
  5.         @ColumnWidth(10)
  6.         private Long id;
  8.         @ExcelProperty(value = "用户名", index = 1)
  9.         @ColumnWidth(20)
  10.         private String username;
  12.         @ExcelIgnore
  13.         private String password;
  15.         @ExcelProperty(value = "昵称", index = 2)
  16.         @ColumnWidth(20)
  17.         private String nickname;
  19.         @ExcelProperty(value = "出生日期", index = 3)
  20.         @ColumnWidth(20)
  21.         @DateTimeFormat("yyyy-MM-dd")
  22.         private Date birthday;
  24.         @ExcelProperty(value = "手机号", index = 4)
  25.         @ColumnWidth(20)
  26.         private String phone;
  28.         @ExcelIgnore
  29.         private String icon;
  31.         @ExcelProperty(value = "性别", index = 5, converter = DefineConverter.GenderConverter.class)
  32.         @ColumnWidth(10)
  33.         private Integer gender;
  34. }

还是需要定义转换器，采用如上就可以
定义对应Mapper接口 MemberMapper

  1. @Mapper
  2. public interface MemberMapper {
  4.     @Select("SELECT * FROM member")
  5.     @Results({
  6.             @Result(property = "id", column = "id"),
  7.             @Result(property = "username", column = "username"),
  8.             @Result(property = "password", column = "password"),
  9.             @Result(property = "nickname", column = "nickname"),
  10.             @Result(property = "birthday", column = "birthday"),
  11.             @Result(property = "phone", column = "phone"),
  12.             @Result(property = "gender", column = "gender"),
  13.     })
  14.     List<Member> getMember();
  15. }

定义对应service层 MemberServiceImpl

  1. @Service
  2. public class MemberServiceImpl {
  3.     @Autowired
  4.     MemberMapper mapper;
  6.     public List<Member> getMember(){
  7.         return mapper.getMember();
  8.     }
  9. }

定义controller层 easyexcelController

  1. @RestController
  2. @RequestMapping("/easyexcel")
  3. public class easyexcelController {
  4.     @Autowired
  5.     MemberMapper memberService;
  7.     // 简单导入
  8.     @SneakyThrows
  9.     @RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
  10.     @ResponseBody
  11.     public CommonResult importMemberList(@RequestPart("file") MultipartFile file) {
  12.         List<Member> memberList = EasyExcel.read(file.getInputStream())
  13.                 .head(Member.class)
  14.                 .sheet()
  15.                 .doReadSync();
  16.         return CommonResult.success(memberList);
  17.     }
  20.     // 设置excel下载响应头属性
  21.     private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
  22.         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  23.         response.setCharacterEncoding("utf-8");
  24.         String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+" ,"%20");
  25.         response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
  26.     }
  28.     // 简单导出
  29.     @SneakyThrows(IOException.class)
  30.     @RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
  31.     public void exportMemberList(HttpServletResponse response) {
  32.         setExcelRespProp(response, "会员列表");
  33.         List<Member> memberList = memberService.getMember();
  34.         EasyExcel.write(response.getOutputStream())
  35.                 .head(Member.class)
  36.                 .excelType(ExcelTypeEnum.XLSX)
  37.                 .sheet("会员列表")
  38.                 .doWrite(memberList);
  39.     }
  40. }

补充：
对于导入返回的结果获取，封装了一个通用对象CommonResult
首先创建一个封装API的错误码的接口IErrorCode

  1. /**
  2.  * 封装API的错误码
  3.  * Created by macro on 2019/4/19.
  4.  */
  5. public interface IErrorCode {
  6.     long getCode();
  7.     String getMessage();
  8. }

定义一个枚举类型ResultCode用来返回一些错误码

  1. package com.wg.easyexcel.common.api;
  3. public enum ResultCode implements IErrorCode{
  4.     SUCCESS(200, "操作成功"),
  5.     FAILED(500, "操作失败"),
  6.     VALIDATE_FAILED(404, "参数检验失败"),
  7.     UNAUTHORIZED(401, "暂未登录或token已经过期"),
  8.     FORBIDDEN(403, "没有相关权限");
  9.     private long code;
  10.     private String message;
  12.     private ResultCode(long code, String message) {
  13.         this.code = code;
  14.         this.message = message;
  15.     }
  17.     public long getCode() {
  18.         return code;
  19.     }
  21.     public String getMessage() {
  22.         return message;
  23.     }
  24. }

通过泛型定义一个通用返回对象 CommonResult

  1. package com.wg.easyexcel.common.api;
  3. import org.apache.poi.ss.formula.functions.T;
  5. /**
  6.  * 通用返回对象
  7.  * Created by macro on 2019/4/19.
  8.  */
  9. public class CommonResult<T> {
  11.     private long code;
  12.     private String message;
  13.     private T data;
  15.     protected CommonResult() {
  16.     }
  18.     protected CommonResult(long code, String message, T data) {
  19.         this.code = code;
  20.         this.message = message;
  21.         this.data = data;
  22.     }
  24.     /**
  25.      * 成功返回结果
  26.      *
  27.      * @param data 获取的数据
  28.      */
  29.     public static <T> CommonResult<T> success(T data) {
  30.         return new CommonResult<T>(ResultCode.SUCCESS.getCode(), ResultCode.SUCCESS.getMessage(), data);
  31.     }
  33.     /**
  34.      * 成功返回结果
  35.      *
  36.      * @param data 获取的数据
  37.      * @param  message 提示信息
  38.      */
  39.     public static <T> CommonResult<T> success(T data, String message) {
  40.         return new CommonResult<T>(ResultCode.SUCCESS.getCode(), message, data);
  41.     }
  43.     /**
  44.      * 失败返回结果
  45.      * @param errorCode 错误码
  46.      */
  47.     public static <T> CommonResult<T> failed(IErrorCode errorCode) {
  48.         return new CommonResult<T>(errorCode.getCode(), errorCode.getMessage(), null);
  49.     }
  51.     /**
  52.      * 失败返回结果
  53.      * @param message 提示信息
  54.      */
  55.     public static <T> CommonResult<T> failed(String message) {
  56.         return new CommonResult<T>(ResultCode.FAILED.getCode(), message, null);
  57.     }
  59.     /**
  60.      * 失败返回结果
  61.      */
  62.     public static <T> CommonResult<T> failed() {
  63.         return failed(ResultCode.FAILED);
  64.     }
  66.     /**
  67.      * 参数验证失败返回结果
  68.      */
  69.     public static <T> CommonResult<T> validateFailed() {
  70.         return failed(ResultCode.VALIDATE_FAILED);
  71.     }
  73.     /**
  74.      * 参数验证失败返回结果
  75.      * @param message 提示信息
  76.      */
  77.     public static <T> CommonResult<T> validateFailed(String message) {
  78.         return new CommonResult<T>(ResultCode.VALIDATE_FAILED.getCode(), message, null);
  79.     }
  81.     /**
  82.      * 未登录返回结果
  83.      */
  84.     public static <T> CommonResult<T> unauthorized(T data) {
  85.         return new CommonResult<T>(ResultCode.UNAUTHORIZED.getCode(), ResultCode.UNAUTHORIZED.getMessage(), data);
  86.     }
  88.     /**
  89.      * 未授权返回结果
  90.      */
  91.     public static <T> CommonResult<T> forbidden(T data) {
  92.         return new CommonResult<T>(ResultCode.FORBIDDEN.getCode(), ResultCode.FORBIDDEN.getMessage(), data);
  93.     }
  95.     public long getCode() {
  96.         return code;
  97.     }
  99.     public void setCode(long code) {
  100.         this.code = code;
  101.     }
  103.     public String getMessage() {
  104.         return message;
  105.     }
  107.     public void setMessage(String message) {
  108.         this.message = message;
  109.     }
  111.     public T getData() {
  112.         return data;
  113.     }
  115.     public void setData(T data) {
  116.         this.data = data;
  117.     }
  118. }

通过ApiPost接口测试
在导入时，设置为Post请求。测试时需注意，同时需要在如图位置，设置上传参数，选择上传的文件
实时响应为解析出的excel内容
image.png
在导出设置为Get请求，在测试时需注意，测试成功后，点击右下方的下载，查看输出excel内容
image.png
image.png

案例二：复杂导出order，实现自定义单元格合并

业务：
一个订单 order，一个顾客 member，一个商品 product
每个订单 order 有

  1. 订单本身信息
  2. 对应的顾客 member
  3. 对应的若干商品 List

order
—————————————————————————
订单ID，订单编号，创建时间，收货地址，member，List
—————————————————————————

效果
普通订单表
image.png
实现自定义合并单元格策略的订单表
image.png

sql文件

  1. SET NAMES utf8mb4;
  2. SET FOREIGN_KEY_CHECKS = 0;
  4. -- ----------------------------
  5. -- Table structure for member
  6. -- ----------------------------
  7. DROP TABLE IF EXISTS `member`;
  8. CREATE TABLE `member` (
  9.   `id` bigint NOT NULL,
  10.   `username` varchar(255) DEFAULT NULL,
  11.   `password` varchar(255) DEFAULT NULL,
  12.   `nickname` varchar(255) DEFAULT NULL,
  13.   `birthday` varchar(255) DEFAULT NULL,
  14.   `phone` varchar(255) DEFAULT NULL,
  15.   `gender` int DEFAULT NULL,
  16.   PRIMARY KEY (`id`)
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  19. -- ----------------------------
  20. -- Records of member
  21. -- ----------------------------
  22. BEGIN;
  23. INSERT INTO `member` VALUES (1, 'weig', '1234', 'gangzi', '1995-05-27', '15900330611', 0);
  24. INSERT INTO `member` VALUES (2, 'lijr', '1234', 'ruru', '1995-07-01', '13920803165', 1);
  25. INSERT INTO `member` VALUES (3, 'weip', '1234', 'erdan', '1997-04-25', '15900330611', 0);
  26. COMMIT;
  28. -- ----------------------------
  29. -- Table structure for order
  30. -- ----------------------------
  31. DROP TABLE IF EXISTS `order`;
  32. CREATE TABLE `order` (
  33.   `id` bigint NOT NULL,
  34.   `orderSn` varchar(255) DEFAULT NULL,
  35.   `createTime` date DEFAULT NULL,
  36.   `receiverAddress` varchar(255) DEFAULT NULL,
  37.   PRIMARY KEY (`id`)
  38. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  40. -- ----------------------------
  41. -- Records of order
  42. -- ----------------------------
  43. BEGIN;
  44. INSERT INTO `order` VALUES (1, '20220115010100001', '2022-01-15', '天津市北辰区');
  45. INSERT INTO `order` VALUES (2, '20220115010100002', '2022-01-16', '天津市和平区');
  46. INSERT INTO `order` VALUES (3, '20220115010100003', '2022-01-17', '天津市西青区');
  47. COMMIT;
  49. -- ----------------------------
  50. -- Table structure for product
  51. -- ----------------------------
  52. DROP TABLE IF EXISTS `product`;
  53. CREATE TABLE `product` (
  54.   `id` bigint NOT NULL,
  55.   `productSn` varchar(255) DEFAULT NULL,
  56.   `name` varchar(255) DEFAULT NULL,
  57.   `subTitle` varchar(255) DEFAULT NULL,
  58.   `brandName` varchar(255) DEFAULT NULL,
  59.   `price` decimal(10,2) DEFAULT NULL,
  60.   `count` int DEFAULT NULL,
  61.   PRIMARY KEY (`id`)
  62. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  64. -- ----------------------------
  65. -- Records of product
  66. -- ----------------------------
  67. BEGIN;
  68. INSERT INTO `product` VALUES (1, '7437788', '小米10s', '小米10s的标题', '小米', 1.00, 100);
  69. INSERT INTO `product` VALUES (2, '7437789', '红米', '红米的标题', '小米', 2.00, 200);
  70. INSERT INTO `product` VALUES (3, '7437799', 'Appale12', 'Appale12的标题', '苹果', 3.00, 300);
  71. COMMIT;
  73. SET FOREIGN_KEY_CHECKS = 1;

在如上项目进行改进（Member之前定义过了，后面不再介绍）
定义对应的实体类 Member，Order，Product

  1. @Data
  2. @EqualsAndHashCode(callSuper = false)
  3. public class Order {
  4.     private Long id;
  5.     private String orderSn;
  6.     private Date createTime;
  7.     private String receiverAddress;
  8.     private Member member;
  9.     private List<Product> productList;
  10. }
  12. @Data
  13. @EqualsAndHashCode(callSuper = false)
  14. public class Product {
  15.     private Long id;
  16.     private String productSn;
  17.     private String name;
  18.     private String subTitle;
  19.     private String brandName;
  20.     private BigDecimal price;
  21.     private Integer count;
  22. }

对于自定义单元格合并策略，需要将order表的数据先平铺，通过另一实体类OrderData接收

  1. @Data
  2. @EqualsAndHashCode(callSuper = false)
  3. public class OrderData {
  4.         @ExcelProperty(value = "订单ID")
  5.         @ColumnWidth(10)
  6.         @CustomMerge(needMerge = true, isPk = true)
  7.         private String id;
  9.         @ExcelProperty(value = "订单编码")
  10.         @ColumnWidth(20)
  11.         @CustomMerge(needMerge = true)
  12.         private String orderSn;
  14.         @ExcelProperty(value = "创建时间")
  15.         @ColumnWidth(20)
  16.         @DateTimeFormat("yyyy-MM-dd")
  17.         @CustomMerge(needMerge = true)
  18.         private Date createTime;
  20.         @ExcelProperty(value = "收货地址")
  21.         @CustomMerge(needMerge = true)
  22.         @ColumnWidth(20)
  23.         private String receiverAddress;
  26.         @ExcelProperty(value = "顾客用户名")
  27.         @ColumnWidth(20)
  28.         @CustomMerge(needMerge = true)
  29.         private String username;
  31.         @ExcelProperty(value = "顾客昵称")
  32.         @ColumnWidth(30)
  33.         @CustomMerge(needMerge = true)
  34.         private String nickname;
  36.            // 如果为二级表头，value设置为数组形式
  37.         @ExcelProperty(value = {"商品信息", "商品编码"})
  38.         @ColumnWidth(20)
  39.         private String productSn;
  41.         @ExcelProperty(value = {"商品信息", "商品名称"})
  42.         @ColumnWidth(20)
  43.         private String name;
  45.         @ExcelProperty(value = {"商品信息", "商品标题"})
  46.         @ColumnWidth(30)
  47.         private String subTitle;
  49.         @ExcelProperty(value = {"商品信息", "品牌名称"})
  50.         @ColumnWidth(20)
  51.         private String brandName;
  53.         @ExcelProperty(value = {"商品信息", "商品价格"})
  54.         @ColumnWidth(20)
  55.         private BigDecimal price;
  57.         @ExcelProperty(value = {"商品信息", "商品数量"})
  58.         @ColumnWidth(20)
  59.         private Integer count;
  60. }

其中对于部分需要合并的单元格，自定义了一个注解@CustomMerge来标识是否需要合并以及合并的主键

  1. /**
  2.  * 自定义注解，用于判断是否需要合并以及合并的主键
  3.  */
  4. @Target({ElementType.FIELD})
  5. @Retention(RetentionPolicy.RUNTIME)
  6. @Inherited
  7. public @interface CustomMerge {
  9.     /**
  10.      * 是否需要合并单元格
  11.      */
  12.     boolean needMerge() default false;
  14.     /**
  15.      * 是否是主键,即该字段相同的行合并
  16.      */
  17.     boolean isPk() default false;
  18. }

接下来的重头戏，就是自定义单元格的合并策略 CustomMergeStrategy，最后在导出接口上需要将其注册上去

  1. /**
  2.  * 自定义单元格合并策略
  3.  */
  4. public class CustomMergeStrategy implements RowWriteHandler {
  6.     /**
  7.      * 主键下标
  8.      */
  9.     private Integer pkIndex;
  11.     /**
  12.      * 需要合并的列的下标集合
  13.      */
  14.     private List<Integer> needMergeColumnIndex = new ArrayList<>();
  16.     /**
  17.      * DTO数据类型
  18.      */
  19.     private Class<?> elementType;
  21.     public CustomMergeStrategy(Class<?> elementType) {
  22.         this.elementType = elementType;
  23.     }
  25.     @Override
  26.     public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
  27.         // 如果是标题,则直接返回
  28.         if (isHead) {
  29.             return;
  30.         }
  32.         // 获取当前sheet
  33.         Sheet sheet = writeSheetHolder.getSheet();
  35.         // 获取标题行
  36.         Row titleRow = sheet.getRow(0);
  38.         if (null == pkIndex) {
  39.             this.lazyInit(writeSheetHolder);
  40.         }
  42.         // 判断是否需要和上一行进行合并
  43.         // 不能和标题合并，只能数据行之间合并
  44.         if (row.getRowNum() <= 1) {
  45.             return;
  46.         }
  47.         // 获取上一行数据
  48.         Row lastRow = sheet.getRow(row.getRowNum() - 1);
  49.         // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
  50.         if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
  51.             for (Integer needMerIndex : needMergeColumnIndex) {
  52.                 CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
  53.                         needMerIndex, needMerIndex);
  54.                 sheet.addMergedRegionUnsafe(cellRangeAddress);
  55.             }
  56.         }
  57.     }
  59.     /**
  60.      * 初始化主键下标和需要合并字段的下标
  61.      */
  62.     private void lazyInit(WriteSheetHolder writeSheetHolder) {
  64.         // 获取当前sheet
  65.         Sheet sheet = writeSheetHolder.getSheet();
  67.         // 获取标题行
  68.         Row titleRow = sheet.getRow(0);
  69.         // 获取DTO的类型
  70.         Class<?> eleType = this.elementType;
  72.         // 获取DTO所有的属性
  73.         Field[] fields = eleType.getDeclaredFields();
  75.         // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
  76.         for (Field theField : fields) {
  77.             // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
  78.             ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
  79.             // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
  80.             if (null == easyExcelAnno) {
  81.                 continue;
  82.             }
  83.             // 获取自定义的注解，用于合并单元格
  84.             CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);
  86.             // 没有@CustomMerge注解的默认不合并
  87.             if (null == customMerge) {
  88.                 continue;
  89.             }
  91.             for (int index = 0; index < fields.length; index++) {
  92.                 Cell theCell = titleRow.getCell(index);
  93.                 // 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPE
  94.                 if (null == theCell) {
  95.                     continue;
  96.                 }
  97.                 // 将字段和excel的表头匹配上
  98.                 if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
  99.                     if (customMerge.isPk()) {
  100.                         pkIndex = index;
  101.                     }
  103.                     if (customMerge.needMerge()) {
  104.                         needMergeColumnIndex.add(index);
  105.                     }
  106.                 }
  107.             }
  108.         }
  110.         // 没有指定主键，则异常
  111.         if (null == this.pkIndex) {
  112.             throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
  113.         }
  115.     }
  116. }

定义对应Mapper接口 OrderMapper，ProductMapper

  1. @Mapper
  2. public interface OrderMapper {
  3.     @Select("SELECT * FROM `order`")
  4.     @Results({
  5.             @Result(property = "id", column = "id"),
  6.             @Result(property = "orderSn", column = "orderSn"),
  7.             @Result(property = "createTime", column = "createTime"),
  8.             @Result(property = "receiverAddress", column = "receiverAddress"),
  9.     })
  10.     List<Order> getOrder();
  11. }
  14. @Mapper
  15. public interface ProductMapper {
  16.         @Select("SELECT * FROM product")
  17.         @Results({
  18.                 @Result(property = "id", column = "id"),
  19.                 @Result(property = "productSn", column = "productSn"),
  20.                 @Result(property = "name", column = "name"),
  21.                 @Result(property = "subTitle", column = "subTitle"),
  22.                 @Result(property = "brandName", column = "brandName"),
  23.                 @Result(property = "price", column = "price"),
  24.                 @Result(property = "count", column = "count"),
  25.         })
  26.         List<Product> getProduct();
  27. }

定义对应service层 OrderServiceImpl，ProductServiceImpl

  1. @Service
  2. public class OrderServiceImpl {
  3.     @Autowired
  4.     OrderMapper mapper;
  6.     public List<Order> getOrder(){
  7.         return mapper.getOrder();
  8.     }
  9. }
  12. @Service
  13. public class ProductServiceImpl {
  14.     @Autowired
  15.     ProductMapper mapper;
  17.     public List<Product> getProduct(){
  18.         return mapper.getProduct();
  19.     }
  20. }

�easyexcelController中

  1. @RestController
  2. @RequestMapping("/easyexcel")
  3. public class easyexcelController {
  5.     @Autowired
  6.     OrderServiceImpl orderService;
  7.     @Autowired
  8.     ProductMapper productService;
  9.     @Autowired
  10.     MemberMapper memberService;
  12.     // 简单导入
  13.     @SneakyThrows
  14.     @RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
  15.     @ResponseBody
  16.     public CommonResult importMemberList(@RequestPart("file") MultipartFile file) {
  17.         List<Member> memberList = EasyExcel.read(file.getInputStream())
  18.                 .head(Member.class)
  19.                 .sheet()
  20.                 .doReadSync();
  21.         return CommonResult.success(memberList);
  22.     }
  25.     // 设置excel下载响应头属性
  26.     private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
  27.         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  28.         response.setCharacterEncoding("utf-8");
  29.         String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+" ,"%20");
  30.         response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
  31.     }
  33.     // 简单导出
  34.     @SneakyThrows(IOException.class)
  35.     @RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
  36.     public void exportMemberList(HttpServletResponse response) {
  37.         setExcelRespProp(response, "会员列表");
  38.         List<Member> memberList = memberService.getMember();
  39.         EasyExcel.write(response.getOutputStream())
  40.                 .head(Member.class)
  41.                 .excelType(ExcelTypeEnum.XLSX)
  42.                 .sheet("会员列表")
  43.                 .doWrite(memberList);
  44.     }
  46.     // 复杂导出
  47.     @SneakyThrows
  48.     @RequestMapping(value = "/exportOrderList", method = RequestMethod.GET)
  49.     public void exportOrderList(HttpServletResponse response) {
  50.         List<Order> orderList = getOrderList();
  51.         List<OrderData> orderDataList = convert(orderList);
  52.         setExcelRespProp(response, "订单列表");
  53.         EasyExcel.write(response.getOutputStream())
  54.                 .head(OrderData.class)
  55.                 // 注册自定义合并策略
  56.                 .registerWriteHandler(new CustomMergeStrategy(OrderData.class))
  57.                 .excelType(ExcelTypeEnum.XLSX)
  58.                 .sheet("订单列表")
  59.                 .doWrite(orderDataList);
  60.     }
  62.     // 平铺到OrderData上
  63.     private List<OrderData> convert(List<Order> orderList) {
  64.         List<OrderData> result = new ArrayList<>();
  65.         for (Order order : orderList) {
  66.             List<Product> productList = order.getProductList();
  67.             Member member = order.getMember();
  68.             for (Product product : productList) {
  69.                 OrderData orderData = new OrderData();
  70.                 BeanUtil.copyProperties(product,orderData);
  71.                 BeanUtil.copyProperties(order,orderData);
  72.                 BeanUtil.copyProperties(member,orderData);
  73.                 result.add(orderData);
  74.             }
  75.         }
  76.         return result;
  77.     }
  79.     private List<Order> getOrderList() {
  80.         List<Order> orderList = orderService.getOrder();
  81.         List<Product> productList = productService.getProduct();
  82.         List<Member> memberList = memberService.getMember();
  83.         for (int i = 0; i < orderList.size(); i++) {
  84.             Order order = orderList.get(i);
  85.             order.setMember(memberList.get(i));
  86.             order.setProductList(productList);
  87.         }
  88.         return orderList;
  89.     }
  91. }

over搞定🤝

以上参考借鉴 https://juejin.cn/post/7051751438715715620

EasyPoi与EasyExcel性能对比

https://www.jianshu.com/p/4e56a1a1db3f

总结

EasyExcel以使用简单、节省内存，性能高效著称。EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中， 而是从磁盘上一行行读取数据，逐个解析。
但是对于复杂点的导出，需要自己实现。这点EasyPoi做的较好，使用简单，功能强大，但是性能低于easyexcel

  • 如果Excel导出数据量不大的话，可以使用EasyPoi
  • 如果数据量大，比较在意性能的话，使用EasyExcel。

公众号：程序员WeiG
个人博客：wggz.top