前言

在平时的业务系统开发中，少不了需要用到导出、导入excel功能。Apache POI不做过多介绍

主要学习下，EasyPoi和EasyExcel的使用和区别对比。 ​

官网 https://www.yuque.com/easyexcel (opens new window) ​

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添加依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

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

package com.wg.easyexcel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.holder.ReadSheetHolder;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;



public class XlxsTool {

    private static final Logger log = LoggerFactory.getLogger(XlxsTool.class);
    
    public static <T> List<T> loadExcel(String excelPath, Class<T> clazz) {
        try (FileInputStream fis = new FileInputStream(excelPath)) {
            return loadExcel(fis, clazz);
        } catch (Exception xe) {
            throw new RuntimeException(xe);
        }
    }

    public static <T> List<T> loadExcel(InputStream inputStream, Class<T> clazz) {
        ExcelDataListener<T> excelDataListener = new ExcelDataListener<>();
        EasyExcel.read(inputStream, clazz, excelDataListener).sheet().doRead();
        return excelDataListener.getResult();
    }

    public static <T> Map<String, List<T>> loadAllExcel(String excelPath, Class<T> clazz) {
        try (FileInputStream fis = new FileInputStream(excelPath)) {
            return loadAllExcel(fis, clazz);
        } catch (Exception xe) {
            throw new RuntimeException(xe);
        }
    }

    public static <T> Map<String, List<T>> loadAllExcel(InputStream inputStream, Class<T> clazz) {
        ExcelDataAllSheetListener<T> excelDataListener = new ExcelDataAllSheetListener<>();
        EasyExcel.read(inputStream, clazz, excelDataListener).doReadAll();
        return excelDataListener.getResult();
    }

    public static <T> void export(String filePath, Class<T> type, List<T> mainExcelData) {
        EasyExcel.write(filePath, type).sheet().doWrite(mainExcelData);
    }

    public static <T> void export(String filePath, Class<T> type, Map<String, List<T>> mainExcelData) {
        ExcelWriter excelWriter = EasyExcel.write(filePath, type).build();
        AtomicInteger i = new AtomicInteger();
        mainExcelData.forEach((key, value) -> {
            WriteSheet writeSheet = EasyExcel.writerSheet(i.getAndIncrement(), key).build();
            excelWriter.write(value, writeSheet);
        });
        excelWriter.finish();
    }

    public static class ExcelDataListener<T> extends AnalysisEventListener<T> {
        private final List<T> result = new ArrayList<>();

        @Override
        public void invoke(T data, AnalysisContext context) {
            result.add(data);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {

        }

        public List<T> getResult() {
            return result;
        }
    }

    public static class ExcelDataAllSheetListener<T> extends AnalysisEventListener<T> {
        private final Map<String, List<T>> result = new LinkedHashMap<>();

        @Override
        public void invoke(T data, AnalysisContext context) {
            ReadSheetHolder readSheetHolder = context.readSheetHolder();
            String sheetName = readSheetHolder.getSheetName();
            List<T> ts = result.computeIfAbsent(sheetName, k -> new ArrayList<>());
            ts.add(data);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {

        }

        public Map<String, List<T>> getResult() {
            return result;
        }
    }

}

创建实体类

package com.wg.easyexcel.entity;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.wg.easyexcel.util.DefineConverter;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.Date;

@Data
@EqualsAndHashCode(callSuper = false)
public class Member {
        @ExcelProperty(value = "ID", index = 0)
        @ColumnWidth(10)
        private Long id;

        @ExcelProperty(value = "用户名", index = 1)
        @ColumnWidth(20)
        private String username;

        @ExcelIgnore
        private String password;

        @ExcelProperty(value = "昵称", index = 2)
        @ColumnWidth(20)
        private String nickname;

        @ExcelProperty(value = "出生日期", index = 3)
        @ColumnWidth(20)
        @DateTimeFormat("yyyy-MM-dd")
        private Date birthday;

        @ExcelProperty(value = "手机号", index = 4)
        @ColumnWidth(20)
        private String phone;

        @ExcelIgnore
        private String icon;

        @ExcelProperty(value = "性别", index = 5, converter = DefineConverter.GenderConverter.class)
        @ColumnWidth(10)
        private Integer gender;
}

类注解

@Data与@EqualsAndHashCode的使用 (opens new window)

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

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

其他注解参考 https://www.cnblogs.com/bluekang/p/13438666.html (opens new window)

提示

@ExcelProperty 是最重要的一个注解，有三个参数value,index,converter。分别代表列名，列序号，数据转换方式 value和index只能二选一，通常不用设置converter

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

创建自定义转换器

package com.wg.easyexcel.util;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.StringUtils;

/**
 * 自定义转换器
 */
public class DefineConverter {

     // excel性别转换器
     // String与Integer转换
    public static class GenderConverter implements Converter<Integer> {
        @Override
        public Class<?> supportJavaTypeKey() {
            // 对象类型属性
            return Integer.class;
        }

        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
            // CellData属性类型
            return CellDataTypeEnum.STRING;
        }

        @Override
        public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
            // CellData转对象属性
            String cellStr = context.getReadCellData().getStringValue();
            if (StringUtils.isEmpty(cellStr)){
                return null;
            }
            if ("男".equals(cellStr)){
                return 0;
            }else if ("女".equals(cellStr)){
                return 1;
            }else {
                return null;
            }
        }

        @Override
        public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
            // 对象属性转CellData
            Integer cellValue = context.getValue();
            if (cellValue == null){
                return new WriteCellData<>("");
            }
            if (cellValue == 0){
                return new WriteCellData<>("男");
            }else if (cellValue == 1) {
                return new WriteCellData<>("女");
            } else {
                return new WriteCellData<>("");
            }
        }
    }
}

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

package com.wg.easyexcel;
import com.wg.easyexcel.entity.Member;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;

// 简单导入导出
public class simpleDemo {

    private static final Logger log = LoggerFactory.getLogger(simpleDemo.class);
    private static final String readExcelPath = "/Users/weigang/IdeaProjects/study/easyexcel/src/main/resources/file/simple/simple-excel.xlsx";
    private static final String outputExcelPath = "/Users/weigang/IdeaProjects/study/easyexcel/src/main/resources/file/simple/simple-excel-output.xlsx";

    @Test
    public void test(){
        // 单 sheet 读取
        List<Member> dataList = XlxsTool.loadExcel(readExcelPath, Member.class);
        
        // 多sheet 读取
        // key为sheet页的名字，value为对应sheet页的数据
        // Map<String ,List<Member>> dataMap = XlxsTool.loadAllExcel(readExcelPath, Member.class);
        
        // 导出
        XlxsTool.export(outputExcelPath, Member.class, dataList);
    }
}

效果

实例：mybatis+mysql+easyexcel

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

Mysql数据库

member建表语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for member
-- ----------------------------
DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
  `id` bigint NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `nickname` varchar(255) DEFAULT NULL,
  `birthday` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `gender` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of member
-- ----------------------------
BEGIN;
INSERT INTO `member` VALUES (1, 'weig', '1234', 'gangzi', '1995-05-27', '15900330611', 0);
INSERT INTO `member` VALUES (2, 'lijr', '1234', 'ruru', '1995-07-01', '13920803165', 1);
INSERT INTO `member` VALUES (3, 'weip', '1234', 'erdan', '1997-04-25', '15900330611', 0);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

定义对应实体类 Member

@Data
@EqualsAndHashCode(callSuper = false)
public class Member {
        @ExcelProperty(value = "ID", index = 0)
        @ColumnWidth(10)
        private Long id;

        @ExcelProperty(value = "用户名", index = 1)
        @ColumnWidth(20)
        private String username;

        @ExcelIgnore
        private String password;

        @ExcelProperty(value = "昵称", index = 2)
        @ColumnWidth(20)
        private String nickname;

        @ExcelProperty(value = "出生日期", index = 3)
        @ColumnWidth(20)
        @DateTimeFormat("yyyy-MM-dd")
        private Date birthday;

        @ExcelProperty(value = "手机号", index = 4)
        @ColumnWidth(20)
        private String phone;

        @ExcelIgnore
        private String icon;

        @ExcelProperty(value = "性别", index = 5, converter = DefineConverter.GenderConverter.class)
        @ColumnWidth(10)
        private Integer gender;
}

还是需要定义转换器，采用如上就可以

定义对应Mapper接口 MemberMapper

@Mapper
public interface MemberMapper {

    @Select("SELECT * FROM member")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password"),
            @Result(property = "nickname", column = "nickname"),
            @Result(property = "birthday", column = "birthday"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "gender", column = "gender"),
    })
    List<Member> getMember();
}

定义对应service层 MemberServiceImpl

@Service
public class MemberServiceImpl {
    @Autowired
    MemberMapper mapper;

    public List<Member> getMember(){
        return mapper.getMember();
    }
}

定义controller层 easyexcelController

@RestController
@RequestMapping("/easyexcel")
public class easyexcelController {
    @Autowired
    MemberMapper memberService;

    // 简单导入
    @SneakyThrows
    @RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
    @ResponseBody
    public CommonResult importMemberList(@RequestPart("file") MultipartFile file) {
        List<Member> memberList = EasyExcel.read(file.getInputStream())
                .head(Member.class)
                .sheet()
                .doReadSync();
        return CommonResult.success(memberList);
    }


    // 设置excel下载响应头属性
    private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+" ,"%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }

    // 简单导出
    @SneakyThrows(IOException.class)
    @RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
    public void exportMemberList(HttpServletResponse response) {
        setExcelRespProp(response, "会员列表");
        List<Member> memberList = memberService.getMember();
        EasyExcel.write(response.getOutputStream())
                .head(Member.class)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("会员列表")
                .doWrite(memberList);
    }
}

补充：

对于导入返回的结果获取，封装了一个通用对象CommonResult

首先创建一个封装API的错误码的接口IErrorCode

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

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

package com.wg.easyexcel.common.api;

public enum ResultCode implements IErrorCode{
    SUCCESS(200, "操作成功"),
    FAILED(500, "操作失败"),
    VALIDATE_FAILED(404, "参数检验失败"),
    UNAUTHORIZED(401, "暂未登录或token已经过期"),
    FORBIDDEN(403, "没有相关权限");
    private long code;
    private String message;

    private ResultCode(long code, String message) {
        this.code = code;
        this.message = message;
    }

    public long getCode() {
        return code;
    }

    public String getMessage() {
        return message;
    }
}

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

package com.wg.easyexcel.common.api;

import org.apache.poi.ss.formula.functions.T;

/**
 * 通用返回对象
 * Created by macro on 2019/4/19.
 */
public class CommonResult<T> {

    private long code;
    private String message;
    private T data;

    protected CommonResult() {
    }

    protected CommonResult(long code, String message, T data) {
        this.code = code;
        this.message = message;
        this.data = data;
    }

    /**
     * 成功返回结果
     *
     * @param data 获取的数据
     */
    public static <T> CommonResult<T> success(T data) {
        return new CommonResult<T>(ResultCode.SUCCESS.getCode(), ResultCode.SUCCESS.getMessage(), data);
    }

    /**
     * 成功返回结果
     *
     * @param data 获取的数据
     * @param  message 提示信息
     */
    public static <T> CommonResult<T> success(T data, String message) {
        return new CommonResult<T>(ResultCode.SUCCESS.getCode(), message, data);
    }

    /**
     * 失败返回结果
     * @param errorCode 错误码
     */
    public static <T> CommonResult<T> failed(IErrorCode errorCode) {
        return new CommonResult<T>(errorCode.getCode(), errorCode.getMessage(), null);
    }

    /**
     * 失败返回结果
     * @param message 提示信息
     */
    public static <T> CommonResult<T> failed(String message) {
        return new CommonResult<T>(ResultCode.FAILED.getCode(), message, null);
    }

    /**
     * 失败返回结果
     */
    public static <T> CommonResult<T> failed() {
        return failed(ResultCode.FAILED);
    }

    /**
     * 参数验证失败返回结果
     */
    public static <T> CommonResult<T> validateFailed() {
        return failed(ResultCode.VALIDATE_FAILED);
    }

    /**
     * 参数验证失败返回结果
     * @param message 提示信息
     */
    public static <T> CommonResult<T> validateFailed(String message) {
        return new CommonResult<T>(ResultCode.VALIDATE_FAILED.getCode(), message, null);
    }

    /**
     * 未登录返回结果
     */
    public static <T> CommonResult<T> unauthorized(T data) {
        return new CommonResult<T>(ResultCode.UNAUTHORIZED.getCode(), ResultCode.UNAUTHORIZED.getMessage(), data);
    }

    /**
     * 未授权返回结果
     */
    public static <T> CommonResult<T> forbidden(T data) {
        return new CommonResult<T>(ResultCode.FORBIDDEN.getCode(), ResultCode.FORBIDDEN.getMessage(), data);
    }

    public long getCode() {
        return code;
    }

    public void setCode(long code) {
        this.code = code;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public T getData() {
        return data;
    }

    public void setData(T data) {
        this.data = data;
    }
}

通过ApiPost接口测试

在导入时，设置为Post请求。测试时需注意，同时需要在如图位置，设置上传参数，选择上传的文件

实时响应为解析出的excel内容

在导出设置为Get请求，在测试时需注意，测试成功后，点击右下方的下载，查看输出excel内容

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

业务：

一个订单 order，一个顾客 member，一个商品 product

每个订单 order 有

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

order

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

效果

普通订单表

实现自定义合并单元格策略的订单表

​

sql文件

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for member
-- ----------------------------
DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
  `id` bigint NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `nickname` varchar(255) DEFAULT NULL,
  `birthday` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `gender` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of member
-- ----------------------------
BEGIN;
INSERT INTO `member` VALUES (1, 'weig', '1234', 'gangzi', '1995-05-27', '15900330611', 0);
INSERT INTO `member` VALUES (2, 'lijr', '1234', 'ruru', '1995-07-01', '13920803165', 1);
INSERT INTO `member` VALUES (3, 'weip', '1234', 'erdan', '1997-04-25', '15900330611', 0);
COMMIT;

-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `id` bigint NOT NULL,
  `orderSn` varchar(255) DEFAULT NULL,
  `createTime` date DEFAULT NULL,
  `receiverAddress` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of order
-- ----------------------------
BEGIN;
INSERT INTO `order` VALUES (1, '20220115010100001', '2022-01-15', '天津市北辰区');
INSERT INTO `order` VALUES (2, '20220115010100002', '2022-01-16', '天津市和平区');
INSERT INTO `order` VALUES (3, '20220115010100003', '2022-01-17', '天津市西青区');
COMMIT;

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` bigint NOT NULL,
  `productSn` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `subTitle` varchar(255) DEFAULT NULL,
  `brandName` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `count` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of product
-- ----------------------------
BEGIN;
INSERT INTO `product` VALUES (1, '7437788', '小米10s', '小米10s的标题', '小米', 1.00, 100);
INSERT INTO `product` VALUES (2, '7437789', '红米', '红米的标题', '小米', 2.00, 200);
INSERT INTO `product` VALUES (3, '7437799', 'Appale12', 'Appale12的标题', '苹果', 3.00, 300);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

在如上项目进行改进（Member之前定义过了，后面不再介绍）

定义对应的实体类 Member，Order，Product

@Data
@EqualsAndHashCode(callSuper = false)
public class Order {
    private Long id;
    private String orderSn;
    private Date createTime;
    private String receiverAddress;
    private Member member;
    private List<Product> productList;
}

@Data
@EqualsAndHashCode(callSuper = false)
public class Product {
    private Long id;
    private String productSn;
    private String name;
    private String subTitle;
    private String brandName;
    private BigDecimal price;
    private Integer count;
}

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

@Data
@EqualsAndHashCode(callSuper = false)
public class OrderData {
        @ExcelProperty(value = "订单ID")
        @ColumnWidth(10)
        @CustomMerge(needMerge = true, isPk = true)
        private String id;

        @ExcelProperty(value = "订单编码")
        @ColumnWidth(20)
        @CustomMerge(needMerge = true)
        private String orderSn;

        @ExcelProperty(value = "创建时间")
        @ColumnWidth(20)
        @DateTimeFormat("yyyy-MM-dd")
        @CustomMerge(needMerge = true)
        private Date createTime;

        @ExcelProperty(value = "收货地址")
        @CustomMerge(needMerge = true)
        @ColumnWidth(20)
        private String receiverAddress;

        
        @ExcelProperty(value = "顾客用户名")
        @ColumnWidth(20)
        @CustomMerge(needMerge = true)
        private String username;

        @ExcelProperty(value = "顾客昵称")
        @ColumnWidth(30)
        @CustomMerge(needMerge = true)
        private String nickname;

   		// 如果为二级表头，value设置为数组形式
        @ExcelProperty(value = {"商品信息", "商品编码"})
        @ColumnWidth(20)
        private String productSn;

        @ExcelProperty(value = {"商品信息", "商品名称"})
        @ColumnWidth(20)
        private String name;

        @ExcelProperty(value = {"商品信息", "商品标题"})
        @ColumnWidth(30)
        private String subTitle;

        @ExcelProperty(value = {"商品信息", "品牌名称"})
        @ColumnWidth(20)
        private String brandName;

        @ExcelProperty(value = {"商品信息", "商品价格"})
        @ColumnWidth(20)
        private BigDecimal price;

        @ExcelProperty(value = {"商品信息", "商品数量"})
        @ColumnWidth(20)
        private Integer count;
}

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

/**
 * 自定义注解，用于判断是否需要合并以及合并的主键
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {

    /**
     * 是否需要合并单元格
     */
    boolean needMerge() default false;

    /**
     * 是否是主键,即该字段相同的行合并
     */
    boolean isPk() default false;
}

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

/**
 * 自定义单元格合并策略
 */
public class CustomMergeStrategy implements RowWriteHandler {

    /**
     * 主键下标
     */
    private Integer pkIndex;

    /**
     * 需要合并的列的下标集合
     */
    private List<Integer> needMergeColumnIndex = new ArrayList<>();

    /**
     * DTO数据类型
     */
    private Class<?> elementType;

    public CustomMergeStrategy(Class<?> elementType) {
        this.elementType = elementType;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 如果是标题,则直接返回
        if (isHead) {
            return;
        }

        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // 获取标题行
        Row titleRow = sheet.getRow(0);

        if (null == pkIndex) {
            this.lazyInit(writeSheetHolder);
        }

        // 判断是否需要和上一行进行合并
        // 不能和标题合并，只能数据行之间合并
        if (row.getRowNum() <= 1) {
            return;
        }
        // 获取上一行数据
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
        if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
            for (Integer needMerIndex : needMergeColumnIndex) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
                        needMerIndex, needMerIndex);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
        }
    }

    /**
     * 初始化主键下标和需要合并字段的下标
     */
    private void lazyInit(WriteSheetHolder writeSheetHolder) {

        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // 获取标题行
        Row titleRow = sheet.getRow(0);
        // 获取DTO的类型
        Class<?> eleType = this.elementType;

        // 获取DTO所有的属性
        Field[] fields = eleType.getDeclaredFields();

        // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
        for (Field theField : fields) {
            // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
            ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
            // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
            if (null == easyExcelAnno) {
                continue;
            }
            // 获取自定义的注解，用于合并单元格
            CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);

            // 没有@CustomMerge注解的默认不合并
            if (null == customMerge) {
                continue;
            }

            for (int index = 0; index < fields.length; index++) {
                Cell theCell = titleRow.getCell(index);
                // 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPE
                if (null == theCell) {
                    continue;
                }
                // 将字段和excel的表头匹配上
                if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
                    if (customMerge.isPk()) {
                        pkIndex = index;
                    }

                    if (customMerge.needMerge()) {
                        needMergeColumnIndex.add(index);
                    }
                }
            }
        }

        // 没有指定主键，则异常
        if (null == this.pkIndex) {
            throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
        }

    }
}

定义对应Mapper接口 OrderMapper，ProductMapper

@Mapper
public interface OrderMapper {
    @Select("SELECT * FROM `order`")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "orderSn", column = "orderSn"),
            @Result(property = "createTime", column = "createTime"),
            @Result(property = "receiverAddress", column = "receiverAddress"),
    })
    List<Order> getOrder();
}


@Mapper
public interface ProductMapper {
        @Select("SELECT * FROM product")
        @Results({
                @Result(property = "id", column = "id"),
                @Result(property = "productSn", column = "productSn"),
                @Result(property = "name", column = "name"),
                @Result(property = "subTitle", column = "subTitle"),
                @Result(property = "brandName", column = "brandName"),
                @Result(property = "price", column = "price"),
                @Result(property = "count", column = "count"),
        })
        List<Product> getProduct();
}

定义对应service层 OrderServiceImpl，ProductServiceImpl

@Service
public class OrderServiceImpl {
    @Autowired
    OrderMapper mapper;

    public List<Order> getOrder(){
        return mapper.getOrder();
    }
}


@Service
public class ProductServiceImpl {
    @Autowired
    ProductMapper mapper;

    public List<Product> getProduct(){
        return mapper.getProduct();
    }
}

�easyexcelController中

@RestController
@RequestMapping("/easyexcel")
public class easyexcelController {

    @Autowired
    OrderServiceImpl orderService;
    @Autowired
    ProductMapper productService;
    @Autowired
    MemberMapper memberService;

    // 简单导入
    @SneakyThrows
    @RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
    @ResponseBody
    public CommonResult importMemberList(@RequestPart("file") MultipartFile file) {
        List<Member> memberList = EasyExcel.read(file.getInputStream())
                .head(Member.class)
                .sheet()
                .doReadSync();
        return CommonResult.success(memberList);
    }


    // 设置excel下载响应头属性
    private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+" ,"%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }

    // 简单导出
    @SneakyThrows(IOException.class)
    @RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
    public void exportMemberList(HttpServletResponse response) {
        setExcelRespProp(response, "会员列表");
        List<Member> memberList = memberService.getMember();
        EasyExcel.write(response.getOutputStream())
                .head(Member.class)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("会员列表")
                .doWrite(memberList);
    }

    // 复杂导出
    @SneakyThrows
    @RequestMapping(value = "/exportOrderList", method = RequestMethod.GET)
    public void exportOrderList(HttpServletResponse response) {
        List<Order> orderList = getOrderList();
        List<OrderData> orderDataList = convert(orderList);
        setExcelRespProp(response, "订单列表");
        EasyExcel.write(response.getOutputStream())
                .head(OrderData.class)
            	// 注册自定义合并策略
                .registerWriteHandler(new CustomMergeStrategy(OrderData.class))
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("订单列表")
                .doWrite(orderDataList);
    }

    // 平铺到OrderData上
    private List<OrderData> convert(List<Order> orderList) {
        List<OrderData> result = new ArrayList<>();
        for (Order order : orderList) {
            List<Product> productList = order.getProductList();
            Member member = order.getMember();
            for (Product product : productList) {
                OrderData orderData = new OrderData();
                BeanUtil.copyProperties(product,orderData);
                BeanUtil.copyProperties(order,orderData);
                BeanUtil.copyProperties(member,orderData);
                result.add(orderData);
            }
        }
        return result;
    }

    private List<Order> getOrderList() {
        List<Order> orderList = orderService.getOrder();
        List<Product> productList = productService.getProduct();
        List<Member> memberList = memberService.getMember();
        for (int i = 0; i < orderList.size(); i++) {
            Order order = orderList.get(i);
            order.setMember(memberList.get(i));
            order.setProductList(productList);
        }
        return orderList;
    }

}

over搞定🤝 ​

以上参考借鉴 https://juejin.cn/post/7051751438715715620 (opens new window)

EasyPoi与EasyExcel性能对比

https://www.jianshu.com/p/4e56a1a1db3f (opens new window)

总结

EasyExcel以使用简单、节省内存，性能高效著称。EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加载到内存中， 而是从磁盘上一行行读取数据，逐个解析。

但是对于复杂点的导出，需要自己实现。这点EasyPoi做的较好，使用简单，功能强大，但是性能低于easyexcel

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