EasyExcel 的基本使用

EasyExcel

EasyExcel 是一个基于 Java 的简单、省内存的读写 Excel 的开源项目。在尽可能节约内存的情况下支持读写百 M 的 Excel。

• 官网：https://easyexcel.opensource.alibaba.com

• 示例版本：com.alibaba.easyexcel:3.1.1

• 示例 Demo 框架：SpringBoot+MybatisPlus

1 前期准备

1.1 pom 文件引入 EasyExcel 依赖

<!-- easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

使用 poi 5+ 版本时注意事项

<!-- easyexcel -->
<!-- 3+ 版本的 easyexcel，使用 poi 5+ 版本时，需要自己引入 poi 5+ 版本的包，且手动排除：poi-ooxml-schemas -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
    <exclusions>
        <exclusion>
            <artifactId>poi-ooxml-schemas</artifactId>
            <groupId>org.apache.poi</groupId>
        </exclusion>
    </exclusions>
</dependency>

<!-- Excel 97-2003 工作簿 -->
<!-- 这是遵循二进制文件格式的旧 Excel 文件。该格式的文件扩展名为 .xls -->
<!-- 为了兼容性，这个依赖项也是要加的 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
</dependency>
<!-- 用于操作 Excel 2007+ 工作簿 -->
<!-- 这是 Excel 2007 和更高版本的默认基于 XML 的文件格式。该格式的文件扩展名为 .xlsx -->
<!-- 它遵循 Office Open XML (OOXML) 格式，这是一种由 Microsoft 开发的基于 XML 的压缩文件格式，用于表示办公文档 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

1.2 建表加测试数据

CREATE TABLE `tb_user`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `age` int NULL DEFAULT NULL COMMENT '年龄',
  `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址',
  `create_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `salary` double NULL DEFAULT NULL COMMENT '工资',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用于测试easyexcel' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES (1, '小白', 18, '男', '广东广州', '2022-11-09 23:37:56', 10000.01);
INSERT INTO `tb_user` VALUES (2, '小黑', 19, '女', '广东汕头', '2022-11-09 23:38:08', 10000.02);
INSERT INTO `tb_user` VALUES (3, '小绿', 20, '男', '广东汕头', '2022-11-09 23:37:57', 10000.03);
INSERT INTO `tb_user` VALUES (4, '小青', 21, '女', '广东汕头', '2022-11-09 23:38:07', 10000.04);
INSERT INTO `tb_user` VALUES (5, '小红', 22, '女', '广东广州', '2022-11-09 23:38:06', 10000.05);
INSERT INTO `tb_user` VALUES (6, '小橙', 23, '男', '广东广州', '2022-11-09 23:37:57', 10000.06);
INSERT INTO `tb_user` VALUES (7, '小黄', 24, '女', '广东潮州', '2022-11-09 23:38:06', 10000.07);
INSERT INTO `tb_user` VALUES (8, '小蓝', 25, '男', '广东惠州', '2022-11-09 23:37:58', 10000.08);
INSERT INTO `tb_user` VALUES (9, '小紫', 26, '女', '广东汕头', '2022-11-09 23:38:05', 10000.09);
INSERT INTO `tb_user` VALUES (10, '小灰', 27, '男', '广东揭阳', '2022-11-09 23:37:59', 10000.1);
INSERT INTO `tb_user` VALUES (11, '小粉', 28, '女', '广东汕头', '2022-11-09 23:38:04', 10000.11);
INSERT INTO `tb_user` VALUES (12, '小棕', 29, '男', '广东揭阳', '2022-11-09 23:38:00', 10000);
INSERT INTO `tb_user` VALUES (13, '小金', 30, '男', '广东惠州', '2022-11-09 23:38:01', 10000.12);

1.3 实体类

@Getter
@Setter
@Accessors(chain = true)
@TableName("tb_user")
public class User extends Model<User> {

    /**
     * 用户id
     */
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    /**
     * 用户名
     */
    @TableField("name")
    private String name;

    /**
     * 年龄
     */
    @TableField("age")
    private Integer age;

    /**
     * 性别：0-男 1-女
     */
    @TableField("gender")
    private String gender;

    /**
     * 地址
     */
    @TableField("address")
    private String address;

    /**
     * 创建时间
     */
    @TableField("create_time")
    private LocalDateTime createTime;

    /**
     * 工资
     */
    @TableField("salary")
    private Double salary;

    @Override
    public Serializable pkVal() {
        return this.id;
    }
}

1.4 EasyExcel 导入导出数据模型

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Accessors(chain = true)
public class UserVO {

    /**
     * 用户id
     */
    @ExcelProperty(value = "用户id")
    private Integer id;

    /**
     * 用户名
     */
    @ExcelProperty(value = "用户名")
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty(value = "年龄")
    private Integer age;

    /**
     * 性别
     */
    @ExcelProperty(value = "性别")
    private String gender;

    /**
     * 地址
     */
    @ExcelProperty(value = "地址")
    private String address;

    /**
     * 创建时间
     */
    @ExcelProperty(value = "创建时间")
    private LocalDateTime createTime;

    /**
     * 工资
     */
    @ExcelProperty(value = "工资")
    private Double salary;
}

1.5 写 Excel 时简单获取库中数据的方法

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestWriteDemo {
    @Autowired
    private IUserService userService;

    private List<UserVO> getDataByDatabase() {
        // 获取数据列表
        List<User> userList = userService.list();
        // 复制对象到excel数据模型实体类
        List<UserVO> userVOList = new ArrayList<>();
        userList.forEach(user -> {
            UserVO userVO = new UserVO();
            BeanUtils.copyProperties(user, userVO);
            userVOList.add(userVO);
        });
        return userVOList;
    }
}

2 写 Excel

2.1 最简单的写

2.1.1 示例 Demo

@Test
public void write01() {
    // 获取数据列表
    List<UserVO> userVOList = this.getDataByDatabase();

    // 输出文件路径
    String filename = "D:/easyexcel-test/write01.xlsx";
    // 指定使用类 UserVO 去写到第一个sheet，sheet命名为 数据列表，写完文件流会自动关闭
    EasyExcel.write(filename, UserVO.class).sheet("数据列表").doWrite(userVOList);
}

2.1.2 Excel 效果图

2.2 写入/排除指定列

2.2.1 排除指定列

2.2.1.1 示例 Demo

@Test
public void write02() {
    // 获取数据列表
    List<UserVO> userVOList = this.getDataByDatabase();

    // 输出文件路径
    String fileName = "D:/easyexcel-test/write02.xlsx";

    // 要排除的字段集合
    Set<String> excludeColumnFieldNames = new HashSet<>();
    excludeColumnFieldNames.add("salary");
    // 指定使用类 UserVO 去写到第一个sheet，sheet命名为 数据列表，写完文件流会自动关闭
    EasyExcel.write(fileName, UserVO.class)
             .excludeColumnFieldNames(excludeColumnFieldNames)
             .sheet("数据列表")
             .doWrite(userVOList);
}

2.2.1.2 Excel 效果图

2.2.2 写入指定列

2.2.2.1 示例 Demo

@Test
public void write03() {
    // 获取数据列表
    List<UserVO> userVOList = this.getDataByDatabase();

    // 输出文件路径
    String fileName = "D:/easyexcel-test/write03.xlsx";

    // 要写入的字段集合
    Set<String> includeColumnFieldNames = new HashSet<>();
    includeColumnFieldNames.add("name");
    includeColumnFieldNames.add("salary");
    // 指定使用类 UserVO 去写到第一个sheet，sheet命名为 数据列表，写完文件流会自动关闭
    EasyExcel.write(fileName, UserVO.class)
             .includeColumnFieldNames(includeColumnFieldNames)
             .sheet("数据列表")
             .doWrite(userVOList);
}

2.2.2.2 Excel 效果图

2.3 通过注解指定写入的列

2.3.1 Excel 数据模型对象

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Accessors(chain = true)
public class UserVO {

    /**
     * 用户id
     */
    @ExcelProperty(value = "用户id", index = 0)
    private Integer id;

    /**
     * 用户名
     */
    @ExcelProperty(value = "用户名", index = 1)
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty(value = "年龄", index = 2)
    private Integer age;

    /**
     * 性别
     */
    @ExcelProperty(value = "性别", index = 3)
    private String gender;

    /**
     * 地址
     */
    @ExcelProperty(value = "地址", index = 4)
    private String address;

    /**
     * 创建时间
     */
//    @ExcelProperty(value = "创建时间", index = 5)
    @ExcelIgnore
    private LocalDateTime createTime;

    /**
     * 工资
     */
    @ExcelProperty(value = "工资", index = 6)
//    @ExcelProperty(value = "工资", order = 6)
    private Double salary;
    
}

2.3.2 示例 Demo

@Test
public void write04() {
    // 获取数据列表
    List<UserVO> userVOList = this.getDataByDatabase();

    // 输出文件路径
    String filename = "D:/easyexcel-test/write04.xlsx";
    // 指定使用类 UserVO 去写到第一个sheet，sheet命名为 数据列表，写完文件流会自动关闭
    EasyExcel.write(filename, UserVO.class).sheet("数据列表").doWrite(userVOList);
}

2.3.3 Excel 效果图

2.3.4 小结

1、@ExcelProperty的属性value代表列名，index代表列号（从0开始），order代表排序；

2、使用index某个列号没写就会出现上述例子有空列的情况，使用order则会自动按从小到大排序，不会出现空列；

3、如果index和order混用，则index优先级更高，最终效果是order会从空列开始按顺序填充；

4、@ExcelIgnore代表忽略该列，不进行写入。

2.4 复杂头写入

2.4.1 Excel 数据模型对象

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Accessors(chain = true)
public class UserVO {

    /**
     * 用户id
     */
    @ExcelProperty(value = {"个人信息", "用户id"}, index = 0)
    private Integer id;

    /**
     * 用户名
     */
    @ExcelProperty(value = {"个人信息", "用户名"}, index = 1)
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty(value = {"个人信息", "年龄"}, index = 2)
    private Integer age;

    /**
     * 性别
     */
    @ExcelProperty(value = {"个人信息", "性别"}, index = 3)
    private String gender;

    /**
     * 地址
     */
    @ExcelProperty(value = {"个人信息", "地址"}, index = 4)
    private String address;

    /**
     * 创建时间
     */
    @ExcelProperty(value = {"个人信息", "创建时间"}, index = 5)
//    @ExcelIgnore
    private LocalDateTime createTime;

    /**
     * 工资
     */
    @ExcelProperty(value = {"个人信息", "工资"}, index = 6)
//    @ExcelProperty(value = "工资", order = 6)
    private Double salary;

}

2.4.2 示例 Demo

@Test
public void write05() {
    // 获取数据列表
    List<UserVO> userVOList = this.getDataByDatabase();

    // 输出文件路径
    String filename = "D:/easyexcel-test/write05.xlsx";
    // 指定使用类 UserVO 去写到第一个sheet，sheet命名为 数据列表，写完文件流会自动关闭
    EasyExcel.write(filename, UserVO.class).sheet("数据列表").doWrite(userVOList);
}

2.4.3 Excel 效果图

2.5 重复多次写入（写到单个或者多个Sheet）

2.5.1 模拟分页获取数据的方法

/**
 * 模拟分页获取数据
 *
 * @param pageNum 页码
 * @param pageSize 每页记录数
 * @return
 */
private List<UserVO> getDataByPage(Integer pageNum, Integer pageSize) {
    // 获取数据列表
    List<User> userList = userService.list();
    // 分页处理对象到excel数据模型实体类
    List<UserVO> userVOList = userList.stream().skip((long) (pageNum - 1) * pageSize).limit(pageSize).map(user -> {
        UserVO userVO = new UserVO();
        BeanUtils.copyProperties(user, userVO);
        return userVO;
    }).collect(Collectors.toList());

    return userVOList;
}

2.5.2 重复多次写入单个Sheet

2.5.2.1 示例 Demo

@Test
public void write06() {
    // 输出文件路径
    String filename = "D:/easyexcel-test/write06.xlsx";
    // 创建ExcelWriter对象
    ExcelWriter excelWriter = EasyExcel.write(filename, UserVO.class).build();
    // 创建Sheet对象
    WriteSheet writeSheet = EasyExcel.writerSheet("数据列表").build();
    // 模拟分页，向Excel的同一个Sheet重复写入数据
    // 每页5条记录，从第一页开始写入
    int pageSize = 5;
    for (int pageNum = 1; ; pageNum++) {
        List<UserVO> userVOList = getDataByPage(pageNum, pageSize);
        if (userVOList == null || userVOList.isEmpty()) {
            break;
        }
        excelWriter.write(userVOList, writeSheet);
    }
    // 关闭文件流
    excelWriter.finish();
}

2.5.2.2 Excel 效果图

2.5.3 重复多次写入多个Sheet，且数据模型对象相同

2.5.3.1 示例 Demo

@Test
public void write07() {
    // 输出文件路径
    String filename = "D:/easyexcel-test/write07.xlsx";
    // 创建ExcelWriter对象，指定使用类 UserVO 去写入数据
    ExcelWriter excelWriter = EasyExcel.write(filename, UserVO.class).build();
    // 模拟分页，向Excel的同一个Sheet重复写入数据
    // 每页5条记录，从第一页开始写入
    int pageSize = 5;
    for (int pageNum = 1; ; pageNum++) {
        List<UserVO> userVOList = getDataByPage(pageNum, pageSize);
        if (userVOList == null || userVOList.isEmpty()) {
            break;
        }
        // 创建Sheet对象，这里可以不指定sheetNo，但sheetName必须不一样
        WriteSheet writeSheet = EasyExcel.writerSheet(pageNum, "数据列表" + pageNum).build();
        excelWriter.write(userVOList, writeSheet);
    }
    // 关闭文件流
    excelWriter.finish();
}

2.5.3.2 Excel 效果图

2.5.4 重复多次写入多个Sheet，且数据模型对象不同

2.5.4.1 示例 Demo

@Test
public void write08() {
    // 输出文件路径
    String filename = "D:/easyexcel-test/write08.xlsx";
    // 创建ExcelWriter对象，此处不指定使用的类
    ExcelWriter excelWriter = EasyExcel.write(filename).build();
    // 模拟分页，向Excel的同一个Sheet重复写入数据
    // 每页5条记录，从第一页开始写入
    int pageSize = 5;
    for (int pageNum = 1; ; pageNum++) {
        List<UserVO> userVOList = getDataByPage(pageNum, pageSize);
        if (userVOList == null || userVOList.isEmpty()) {
            break;
        }
        // 创建Sheet对象，这里可以不指定sheetNo，但sheetName必须不一样
        // 在创建Sheet对象时通过head方法指定使用的类，数据类型不同时可以直接更换，此处不再赘述
        WriteSheet writeSheet = EasyExcel.writerSheet(pageNum, "数据列表" + pageNum).head(UserVO.class).build();
        excelWriter.write(userVOList, writeSheet);
    }
    // 关闭文件流
    excelWriter.finish();
}

2.5.4.2 小结

重复多次写入多个Sheet，数据模型对象相同与不同的区别在于一个是在创建ExcelWriter对象时指定写入的类，一个是在创建Sheet对象时指定写入的类，此处就不再举例赘述了。

2.6 日期、数字或者自定义格式转换

2.6.1 日期、数字格式转换

2.6.1.1 Excel 数据模型对象

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Accessors(chain = true)
public class UserVO {

    /**
     * 用户id
     */
    @ExcelProperty(value = {"个人信息01", "用户id"}, index = 0)
    private Integer id;

    /**
     * 用户名
     */
    @ExcelProperty(value = {"个人信息01", "用户名"}, index = 1)
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty(value = {"个人信息01", "年龄"}, index = 2)
    private Integer age;

    /**
     * 性别
     */
    @ExcelProperty(value = {"个人信息01", "性别"}, index = 3)
    private String gender;

    /**
     * 地址
     */
    @ExcelProperty(value = {"个人信息01", "地址"}, index = 4)
    private String address;

    /**
     * 创建时间
     */
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty(value = {"个人信息02", "创建时间"}, index = 5)
//    @ExcelIgnore
    private LocalDateTime createTime;

    /**
     * 工资
     */
    @NumberFormat("0.00")
    @ExcelProperty(value = {"个人信息03", "工资"}, index = 6)
//    @ExcelProperty(value = "工资", order = 6)
    private Double salary;

    /**
     * 测试数字格式转换
     */
    @NumberFormat("#.##")
    @ExcelProperty(value = {"测试数字格式转换", "数字01"}, index = 7)
    private Double testNum01 = 0.90;
    
    @NumberFormat("0.00")
    @ExcelProperty(value = {"测试数字格式转换", "数字02"}, index = 8)
    private Double testNum02 = 0.90;

    @NumberFormat("#.##%")
    @ExcelProperty(value = {"测试数字格式转换", "数字03"}, index = 9)
    private Double testNum03 = 0.90;

    @NumberFormat("0.00%")
    @ExcelProperty(value = {"测试数字格式转换", "数字04"}, index = 10)
    private Double testNum04 = 0.90;

}

2.6.1.2 示例 Demo

@Test
public void write09() {
    // 获取数据列表
    List<UserVO> userVOList = this.getDataByDatabase();

    // 输出文件路径
    String filename = "D:/easyexcel-test/write09.xlsx";
    // 指定使用类 UserVO 去写到第一个sheet，sheet命名为 数据列表，写完文件流会自动关闭
    EasyExcel.write(filename, UserVO.class).sheet("数据列表").doWrite(userVOList);
}

2.6.1.3 Excel 效果图

2.6.1.4 小结

1、日期格式化使用@DateTimeFormat，数字格式化使用@NumberFormat；

2、@NumberFormat("#.##")不保留精度，会将小数点前首位 0 以及小数末尾的 0 都去掉；@NumberFormat("0.00")则会保留精度；

3、@NumberFormat("0.00%")格式化时加上%会将小数转为百分数。

2.6.2 自定义转换器实现格式转换

2.6.2.1 自定义转换器

public class IdConverter implements Converter<Integer> {

    /**
     * 给字段加个前缀
     */
    private static final String ID_PREFIX = "特工-";

    /**
     * Java 字段的数据类型-Integer
     */
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

    /**
     * Excel文件中单元格的数据类型-String
     */
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 读取 Excel 文件时调用，将 String 类型单元格的值转为 Integer 类型的 Java 字段
     */
    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 获取字符串类型单元格的值
        String value = cellData.getStringValue();
        String[] split = value.split("-");
        return Integer.valueOf(split[1]);
    }

    /**
     * 写入 Excel 文件时调用，将 Integer 类型的 Java 字段转为 String 类型单元格的值
     */
    @Override
    public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new WriteCellData<>(ID_PREFIX + value);
    }

}

2.6.2.2 Excel 数据模型对象

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Accessors(chain = true)
public class UserVO {

    /**
     * 用户id
     */
    @ExcelProperty(value = {"个人信息01", "用户id"}, index = 0, converter = IdConverter.class)
    private Integer id;

    /**
     * 用户名
     */
    @ExcelProperty(value = {"个人信息01", "用户名"}, index = 1)
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty(value = {"个人信息01", "年龄"}, index = 2)
    private Integer age;

    /**
     * 性别
     */
    @ExcelProperty(value = {"个人信息01", "性别"}, index = 3)
    private String gender;

    /**
     * 地址
     */
    @ExcelProperty(value = {"个人信息01", "地址"}, index = 4)
    private String address;

    /**
     * 创建时间
     */
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty(value = {"个人信息02", "创建时间"}, index = 5)
//    @ExcelIgnore
    private LocalDateTime createTime;

    /**
     * 工资
     */
    @NumberFormat("0.00")
    @ExcelProperty(value = {"个人信息03", "工资"}, index = 6)
//    @ExcelProperty(value = "工资", order = 6)
    private Double salary;

//    /**
//     * 测试数字格式转换
//     */
//    @NumberFormat("#.##")
//    @ExcelProperty(value = {"测试数字格式转换", "数字01"}, index = 7)
//    private Double testNum01 = 0.90;
//
//    @NumberFormat("0.00")
//    @ExcelProperty(value = {"测试数字格式转换", "数字02"}, index = 8)
//    private Double testNum02 = 0.90;
//
//    @NumberFormat("#.##%")
//    @ExcelProperty(value = {"测试数字格式转换", "数字03"}, index = 9)
//    private Double testNum03 = 0.90;
//
//    @NumberFormat("0.00%")
//    @ExcelProperty(value = {"测试数字格式转换", "数字04"}, index = 10)
//    private Double testNum04 = 0.90;

}

2.6.2.3 示例 Demo

@Test
public void write10() {
    // 获取数据列表
    List<UserVO> userVOList = this.getDataByDatabase();

    // 输出文件路径
    String filename = "D:/easyexcel-test/write10.xlsx";
    // 指定使用类 UserVO 去写到第一个sheet，sheet命名为 数据列表，写完文件流会自动关闭
    EasyExcel.write(filename, UserVO.class).sheet("数据列表").doWrite(userVOList);
}

2.6.2.4 Excel 效果图

2.7 列宽、行高

2.7.1 Excel 数据模型对象

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Accessors(chain = true)
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(15)
public class UserVO {

    /**
     * 用户id
     */
    @ExcelProperty(value = {"个人信息01", "用户id"}, index = 0, converter = IdConverter.class)
    private Integer id;

    /**
     * 用户名
     */
    @ExcelProperty(value = {"个人信息01", "用户名"}, index = 1)
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty(value = {"个人信息01", "年龄"}, index = 2)
    private Integer age;

    /**
     * 性别
     */
    @ExcelProperty(value = {"个人信息01", "性别"}, index = 3)
    private String gender;

    /**
     * 地址
     */
    @ExcelProperty(value = {"个人信息01", "地址"}, index = 4)
    private String address;

    /**
     * 创建时间
     */
    @ColumnWidth(30)
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty(value = {"个人信息02", "创建时间"}, index = 5)
//    @ExcelIgnore
    private LocalDateTime createTime;

    /**
     * 工资
     */
    @NumberFormat("0.00")
    @ExcelProperty(value = {"个人信息03", "工资"}, index = 6)
//    @ExcelProperty(value = "工资", order = 6)
    private Double salary;

//    /**
//     * 测试数字格式转换
//     */
//    @NumberFormat("#.##")
//    @ExcelProperty(value = {"测试数字格式转换", "数字01"}, index = 7)
//    private Double testNum01 = 0.90;
//
//    @NumberFormat("0.00")
//    @ExcelProperty(value = {"测试数字格式转换", "数字02"}, index = 8)
//    private Double testNum02 = 0.90;
//
//    @NumberFormat("#.##%")
//    @ExcelProperty(value = {"测试数字格式转换", "数字03"}, index = 9)
//    private Double testNum03 = 0.90;
//
//    @NumberFormat("0.00%")
//    @ExcelProperty(value = {"测试数字格式转换", "数字04"}, index = 10)
//    private Double testNum04 = 0.90;

}

2.7.2 示例 Demo

@Test
public void write11() {
    // 获取数据列表
    List<UserVO> userVOList = this.getDataByDatabase();

    // 输出文件路径
    String filename = "D:/easyexcel-test/write11.xlsx";
    // 指定使用类 UserVO 去写到第一个sheet，sheet命名为 数据列表，写完文件流会自动关闭
    EasyExcel.write(filename, UserVO.class).sheet("数据列表").doWrite(userVOList);
}

2.7.3 Excel 效果图

2.7.4 小结

1、@HeadRowHeight(20)：头部行高
2、@ContentRowHeight(15)：内容行高
3、@ColumnWidth(15)：列宽，可以作用在类或者字段上

2.8 图片导出

2.8.1 Excel 数据模型对象

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Accessors(chain = true)
@ContentRowHeight(120)
@ColumnWidth(25)
public class ImageVO {

    /**
     * File类型
     */
    @ExcelProperty(value = "File类型")
    private File imageFile;

    /**
     * InputStream类型
     */
    @ExcelProperty(value = "InputStream类型")
    private InputStream imageInputStream;

    /**
     * String类型，使用String类型表示一个图片时需要指定StringImageConverter转换器
     */
    @ExcelProperty(value = "String类型", converter = StringImageConverter.class)
    private String imageStr;

    /**
     * 二进制字节数组
     */
    @ExcelProperty(value = "二进制字节数组")
    private byte[] imageByteArr;

    /**
     * URL链接
     */
    @ColumnWidth(30)
    @ExcelProperty(value = "URL链接")
    private URL imageUrl;

}

2.8.2 示例 Demo

@Test
public void write12() throws IOException {
    // 输出文件路径
    String filename = "D:/easyexcel-test/write12.xlsx";

    // String类型图片路径
    String imageStr = "D:/easyexcel-test/test.jpg";
    // InputStream类型
    FileInputStream imageInputStream = new FileInputStream(imageStr);
    // File类型
    File imageFile = new File(imageStr);
    // 二进制字节数组
    byte[] imageByteArr = FileUtils.readFileToByteArray(imageFile);
    // 网络图片url链接
    URL imageUrl = new URL("https://www.ssfiction.com/wp-content/uploads/2020/08/20200805_5f2b1669e9a24.jpg");

    // 构造数据列表
    List<ImageVO> imageList = new ArrayList<>();
    ImageVO imageVO = new ImageVO();
    imageVO.setImageFile(imageFile)
        .setImageInputStream(imageInputStream)
        .setImageByteArr(imageByteArr)
        .setImageStr(imageStr)
        .setImageUrl(imageUrl);
    imageList.add(imageVO);

    // 指定使用类 UserVO 去写到第一个sheet，sheet命名为 数据列表，写完文件流会自动关闭
    EasyExcel.write(filename, ImageVO.class).sheet("图片数据列表").doWrite(imageList);
}

2.8.3 Excel 效果图

3 读 Excel

3.1 最简单的读

3.1.1 导入的 Excel

3.1.2 Excel 数据模型对象

• 注：一般来说，不建议index和name同时用，要么一个字段只用index，要么一个字段只用name去匹配；如果用名字去匹配且名字有重复，会导致只有一个字段读取到数据。（这里偷个懒还使用写 Excel 时的对象）

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
// 使用 @Accessors 会导致读取 excel 数据为空
//@Accessors(chain = true)
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(15)
public class UserVO {

    /**
     * 用户id
     */
    @ExcelProperty(value = {"个人信息01", "用户id"}, index = 0, converter = IdConverter.class)
    private Integer id;

    /**
     * 用户名
     */
    @ExcelProperty(value = {"个人信息01", "用户名"}, index = 1)
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty(value = {"个人信息01", "年龄"}, index = 2)
    private Integer age;

    /**
     * 性别
     */
    @ExcelProperty(value = {"个人信息01", "性别"}, index = 3)
    private String gender;

    /**
     * 地址
     */
    @ExcelProperty(value = {"个人信息01", "地址"}, index = 4)
    private String address;

    /**
     * 创建时间
     */
    @ColumnWidth(30)
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty(value = {"个人信息02", "创建时间"}, index = 5)
    private LocalDateTime createTime;

    /**
     * 工资
     */
    @NumberFormat("0.00")
    @ExcelProperty(value = {"个人信息03", "工资"}, index = 6)
    private Double salary;

}

3.1.3 方式一 - 使用 PageReadListener

3.1.3.1 示例 Demo

@Test
public void read01() {
    // 读取文件路径
    String filename = "D:/easyexcel-test/read01.xlsx";

    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    // 这里每次会读取100条数据 然后返回过来 直接调用使用数据就行
    EasyExcel.read(filename, UserVO.class, new PageReadListener<UserVO>(userList -> {
        for (UserVO user : userList) {
            log.info("读取到一条数据{}", user);
        }
    })).sheet().doRead();
}

3.1.3.2 运行结果

3.1.3.3 PageReadListener 源码部分

public class PageReadListener<T> implements ReadListener<T> {
    public static int BATCH_COUNT = 100;
    private List<T> cachedDataList;
    private final Consumer<List<T>> consumer;

    public PageReadListener(Consumer<List<T>> consumer) {
        this.cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        this.consumer = consumer;
    }

    public void invoke(T data, AnalysisContext context) {
        this.cachedDataList.add(data);
        if (this.cachedDataList.size() >= BATCH_COUNT) {
            this.consumer.accept(this.cachedDataList);
            this.cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }

    }

    public void doAfterAllAnalysed(AnalysisContext context) {
        if (CollectionUtils.isNotEmpty(this.cachedDataList)) {
            this.consumer.accept(this.cachedDataList);
        }

    }
}

3.1.4 方式二 - 使用匿名内部类

3.1.4.1 示例 Demo

@Test
public void read02() {
    // 读取文件路径
    String filename = "D:/easyexcel-test/read01.xlsx";

    // 这里需要指定用哪个 class 去读，然后读取第一个 sheet 文件流就会自动关闭
    EasyExcel.read(filename, UserVO.class, new AnalysisEventListener<UserVO>() {

        /**
          * 单次缓存的数据量，达到该值后存储一次数据库，然后清理 list，方便内存回收
          */
        private static final int BATCH_COUNT = 5;

        /**
          * 缓存的数据列表
          */
        private final List<UserVO> userList = new ArrayList<>();

        @Override
        public void invoke(UserVO data, AnalysisContext context) {
            log.info("解析一行数据:{}", data);
            userList.add(data);
            // 数据量达到设置的阈值，处理一次数据，然后清空列表，防止内存中数据量过大导致OOM
            if (userList.size() >= BATCH_COUNT) {
                // 存储数据
                userService.saveData(userList);
                // 清空数据列表
                userList.clear();
            }
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            // 处理剩余的没达到阈值的数据
            if (userList.size() > 0) {
                userService.saveData(userList);
            }

            log.info("解析完成");
        }
    }).sheet().doRead();
}

3.1.4.2 运行结果

3.1.5 方式三 - 定义一个监听器

3.1.5.1 示例 Demo

@Test
public void read03() {
    // 读取文件路径
    String filename = "D:/easyexcel-test/read01.xlsx";

    // 这里需要指定用哪个 class 去读，然后读取第一个 sheet 文件流就会自动关闭
    EasyExcel.read(filename, UserVO.class, new UserListener(userService)).sheet().doRead();
}

3.1.5.2 监听器

@Slf4j
// 继承 AnalysisEventListener 或者实现 ReadListener 接口，重写 invoke 和 doAfterAllAnalysed 方法
public class UserListener extends AnalysisEventListener<UserVO> {

    /**
     * 由于 listener 不能被 spring 管理，所以要使用构造器注入 service
     */
    private final IUserService userService;

    /**
     * 单次缓存的数据量，达到该值后存储一次数据库，然后清理 list，方便内存回收
     */
    private static final int BATCH_COUNT = 5;

    /**
     * 缓存的数据列表
     */
    private final List<UserVO> userList = new ArrayList<>();

    /**
     * 构造器注入 service
     */
    public UserListener(IUserService userService) {
        this.userService = userService;
    }

    /**
     * 数据解析，每解析一行调用一次
     *
     * @param data 一行数据
     * @param analysisContext
     */
    @Override
    public void invoke(UserVO data, AnalysisContext analysisContext) {
        log.info("解析一行数据:{}", data);
        userList.add(data);
        // 数据量达到设置的阈值，处理一次数据，然后清空列表，防止内存中数据量过大导致OOM
        if (userList.size() >= BATCH_COUNT) {
            // 存储数据
            userService.saveData(userList);
            // 清空数据列表
            userList.clear();
        }
    }

    /**
     * 所有数据解析完成后调用
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 处理剩余的没达到阈值的数据
        if (userList.size() > 0) {
            userService.saveData(userList);
            // 清空数据列表
            // 读取多个 Sheet 时，每个 Sheet 解析完成都会进到这个方法，所以清空数据列表避免重复读取数据
            userList.clear();
        }

        log.info("解析完成");
    }

}

3.1.5.3 运行结果

3.1.6 小结

1、Excel 数据模型对象不能使用链式注解@Accessors(chain = true)，否则会导致读取 excel 数据字段均为空；

@Accessors(chain =true)是 lombok 的注解，设置为 true 代表该实体对象支持链式编程；其实质作用就是把原来set方法的返回类型void改为当前对象：

不加 @Accessors(chain =true)

public class User {
    private Integer id;
	private String name;
    
    public Integer getId() {
		return this.id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
    public String getName() {
		return this.name;
	}
	public void setName(String name) {
		this.name = name;
	}
} 

加上 @Accessors(chain =true)

@Accessors(chain =true)
public class User {
    private Integer id;
	private String name;
    
    public Integer getId() {
		return this.id;
	}
	public User setId(Integer id) {
		this.id = id;
        return this;
	}
    public String getName() {
		return this.name;
	}
	public User setName(String name) {
		this.name = name;
        return this;
	}
}

所谓链式编程

@Test
public void chainTest() {
    User user = new User();
    // 这就是链式
    user.setId(14).setName("小小白");
}

2、方式一中，从PageReadListener的源码中可以看到BATCH_COUNT等于100，所以使用PageReadListener每次读取100条数据；

3、方式二和方式三其实是一样的，只是方式二用的是匿名内部类的方式；

4、自定义监听器要继承AnalysisEventListener或者实现ReadListener接口，然后重写invoke和doAfterAllAnalysed方法；

5、监听器不能被 spring 管理，所以无法用@Autowired直接注入业务类，需要使用构造器注入；

public class UserListener {
    private final IUserService userService;

    /**
     * 构造器注入 service
     */
    public UserListener(IUserService userService) {
        this.userService = userService;
    }
}

6、日期、数字或者自定义格式转换操作使用的注解和转换器均与写 Excel 时相同。

3.2 读取多个 Sheet

3.2.1 导入的 Excel

3.2.2 读取全部 Sheet

3.2.2.1 示例 Demo

@Test
public void read04() {
    // 读取文件路径
    String filename = "D:/easyexcel-test/read02.xlsx";

    // 这里指定用哪个 class 去读
    // 这里 UserListener 的 doAfterAllAnalysed 会在每个 Sheet 读取完毕后调用一次。然后所有 Sheet 都会往同一个 UserListener 里写数据
    EasyExcel.read(filename, UserVO.class, new UserListener(userService)).doReadAll();
}

3.2.2.2 运行结果

3.2.3 读取部分 Sheet（同个监听器，同个数据对象模型）

3.2.3.1 示例 Demo

@Test
public void read05() {
    // 读取文件路径
    String filename = "D:/easyexcel-test/read02.xlsx";

    // 这里指定用哪个 class 去读
    // 创建 ExcelReader 对象
    ExcelReader excelReader = EasyExcel.read(filename, UserVO.class, new UserListener(userService)).build();

    // 这里的 readSheet 方法可以传 SheetNo 或 SheetName，SheetNo 下标从 0 开始
    ReadSheet sheet1 = EasyExcel.readSheet(0).build();
    ReadSheet sheet2 = EasyExcel.readSheet(1).build();

    // 这里的 read 方法参数是可变长度的，读取多少个就传多少个
    excelReader.read(sheet1, sheet2);

    // 关闭流
    excelReader.finish();
}

3.2.3.2 运行结果

3.2.4 读取部分 Sheet（不同监听器，不同数据对象模型）

3.2.4.1 示例 Demo

@Test
public void read06() {
    // 读取文件路径
    String filename = "D:/easyexcel-test/read02.xlsx";

    // 创建 ExcelReader 对象
    ExcelReader excelReader = EasyExcel.read(filename).build();

    // 这里的 readSheet 方法可以传 SheetNo 或 SheetName，SheetNo 下标从 0 开始
    // 这里的 head 方法传的是数据对象模型的 class，registerReadListener 方法传的是监听器，可以随意更换
    // 这种读取方式需要指定读的起始行，默认从第二行也就是下标为 1 读起，这里由于我们使用的对象有两行行头，所以需要设置 headRowNumber 为 2
    ReadSheet sheet1 = EasyExcel.readSheet(0)
                                .headRowNumber(2)
                                .head(UserVO.class)
                                .registerReadListener(new UserListener(userService))
                                .build();
    ReadSheet sheet2 = EasyExcel.readSheet(1)
                                .headRowNumber(2)
                                .head(UserVO.class)
                                .registerReadListener(new UserListener(userService))
                                .build();

    // 这里的 read 方法参数是可变长度的，读取多少个就传多少个
    excelReader.read(sheet1, sheet2);

    // 关闭流
    excelReader.finish();
}

3.2.4.2 运行结果

3.2.5 小结

1、如果创建了ExcelReader对象，则需要手动关闭流excelReader.finish()，因为在读取文件时会创建临时文件，如果不关闭流会导致 OOM；或者使用try()包起来实现自动关闭；

try (ExcelReader excelReader = EasyExcel.read(fileName).build()) {
	...
}

2、读取的数据对象和监听器可以在创建ExcelReader对象时指定，也可以在创建ReadSheet对象时指定，一般来说，使用同一个数据对象和监听器就可以直接在创建ExcelReader对象时指定；不同对象和监听器则在创建ReadSheet对象时分别指定；

3、读取多个 Sheet 时，每个 Sheet 解析完成都会进到doAfterAllAnalysed方法，所以在该方法内也需要清空列表以避免重复处理数据；

4、多行头则需要指定从哪一行开始读取数据，指定读取行使用headRowNumber方法；ExcelReader对象指定则是全局生效，ReadSheet对象指定则只针对当前Sheet。

3.3 数据转换等异常处理

3.3.1 导入的 Excel

3.3.2 监听器

@Slf4j
// 继承 AnalysisEventListener 或者实现 ReadListener 接口，重写 invoke 和 doAfterAllAnalysed 方法
public class UserListener extends AnalysisEventListener<UserVO> {

    /**
     * 由于 listener 不能被 spring 管理，所以要使用构造器注入 service
     */
    private final IUserService userService;

    /**
     * 单次缓存的数据量，达到该值后存储一次数据库，然后清理 list，方便内存回收
     */
    private static final int BATCH_COUNT = 5;

    /**
     * 缓存的数据列表
     */
    private final List<UserVO> userList = new ArrayList<>();

    /**
     * 构造器注入 service
     */
    public UserListener(IUserService userService) {
        this.userService = userService;
    }

    /**
     * 数据解析，每解析一行调用一次
     *
     * @param data 一行数据
     * @param analysisContext
     */
    @Override
    public void invoke(UserVO data, AnalysisContext analysisContext) {
        log.info("解析一行数据:{}", data);
        userList.add(data);
        // 数据量达到设置的阈值，处理一次数据，然后清空列表，防止内存中数据量过大导致OOM
        if (userList.size() >= BATCH_COUNT) {
            // 存储数据
            userService.saveData(userList);
            // 清空数据列表
            userList.clear();
        }
    }

    /**
     * 所有数据解析完成后调用
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 处理剩余的没达到阈值的数据
        if (userList.size() > 0) {
            userService.saveData(userList);
            // 清空数据列表
            // 读取多个 Sheet 时，每个 Sheet 解析完成都会进到这个方法，所以清空数据列表避免重复读取数据
            userList.clear();
        }

        log.info("解析完成");
    }

    /**
     * 在转换异常时会调用，如果在此抛出异常则停止读取，不抛出异常则继续读取下一行
     *
     * @param exception
     * @param context
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        log.error("解析异常，异常原因::{}", exception.getMessage());

        // 如果是某一个单元格的转换异常，可以获取到具体行号列号
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(), excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
        }
    }
}

3.3.3 运行结果

4 填充 Excel

4.1 最简单的填充

4.1.1 填充模板

4.1.2 根据对象填充

4.1.2.1 填充对象

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class TestFillVO {

    /**
     * 用户名
     */
    private String name;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 地址
     */
    private String address;

    /**
     * 创建时间
     */
    private LocalDateTime createTime;

}

4.1.2.2 示例 Demo

@Test
public void fill01() {
    // 填充模板文件路径
    // 模板使用 {} 包住想要填充的变量，如果要输出字符 "{"、"}" ，则需要使用 "\{"、"\}" 代替
    String templateFilename = "D:/easyexcel-test/template01.xlsx";

    // 输出文件路径
    String filename = "D:/easyexcel-test/fill01.xlsx";

    // 填充对象
    TestFillVO testFillVO = new TestFillVO();
    testFillVO.setName("小白");
    testFillVO.setAge(18);
    testFillVO.setAddress("广东广州");
    testFillVO.setCreateTime(LocalDateTime.now());

    // 这里读取第一个 sheet 之后，文件流就会自动关闭
    EasyExcel.write(filename).withTemplate(templateFilename).sheet().doFill(testFillVO);
}

4.1.2.3 Excel 效果图

4.1.3 根据 Map 填充

4.1.3.1 示例 Demo

@Test
public void fill02() {
    // 填充模板文件路径
    // 模板使用 {} 包住想要填充的变量，如果要输出字符 "{"、"}" ，则需要使用 "\{"、"\}" 代替
    String templateFilename = "D:/easyexcel-test/template01.xlsx";

    // 输出文件路径
    String filename = "D:/easyexcel-test/fill02.xlsx";

    // 填充对象
    Map<String, Object> fillMap = new HashMap<>();
    fillMap.put("name", "小黑");
    fillMap.put("age", 19);
    fillMap.put("address", "广东汕头");
    fillMap.put("createTime", LocalDateTime.now());

    // 这里读取第一个 sheet 之后，文件流就会自动关闭
    EasyExcel.write(filename).withTemplate(templateFilename).sheet().doFill(fillMap);
}

4.1.3.2 Excel 效果图

4.2 填充列表

4.2.1 填充模板

4.2.2 一次填充

4.2.2.1 获取数据列表的方法

private List<TestFillVO> getFillDataByDatabase() {
    // 获取数据列表
    List<User> userList = userService.list();
    // 复制对象到excel数据模型实体类
    List<TestFillVO> testFillVOList = new ArrayList<>();
    userList.forEach(user -> {
        TestFillVO testFillVO = new TestFillVO();
        BeanUtils.copyProperties(user, testFillVO);
        testFillVOList.add(testFillVO);
    });
    return testFillVOList;
}

4.2.2.2 示例 Demo

@Test
public void fill03() {
    // 填充模板文件路径
    // 模板使用 {} 包住想要填充的变量，如果要输出字符 "{"、"}" ，则需要使用 "\{"、"\}" 代替
    // 填充列表时，模板中的填充变量前要多一个点，表示列表，例：{.name}
    String templateFilename = "D:/easyexcel-test/template02.xlsx";

    // 输出文件路径
    String filename = "D:/easyexcel-test/fill03.xlsx";

    // 这里读取第一个 sheet 之后，文件流就会自动关闭
    EasyExcel.write(filename).withTemplate(templateFilename).sheet().doFill(this.getFillDataByDatabase());
}

4.2.2.3 Excel 效果图

4.2.3 多次填充

4.2.3.1 模拟分页获取填充数据的方法

private List<TestFillVO> getFillDataByPage(Integer pageNum, Integer pageSize) {
    // 获取数据列表
    List<User> userList = userService.list();
    // 分页处理对象到excel数据模型实体类
    List<TestFillVO> testFillVOList = userList.stream().skip((long) (pageNum - 1) * pageSize).limit(pageSize).map(user -> {
        TestFillVO testFillVO = new TestFillVO();
        BeanUtils.copyProperties(user, testFillVO);
        return testFillVO;
    }).collect(Collectors.toList());

    return testFillVOList;
}

4.2.3.2 示例 Demo

@Test
public void fill04() {
    // 填充模板文件路径
    // 模板使用 {} 包住想要填充的变量，如果要输出字符 "{"、"}" ，则需要使用 "\{"、"\}" 代替
    // 填充列表时，模板中的填充变量前要多一个点，表示列表，例：{.name}
    String templateFilename = "D:/easyexcel-test/template02.xlsx";

    // 输出文件路径
    String filename = "D:/easyexcel-test/fill04.xlsx";

    // 创建ExcelWriter对象
    ExcelWriter excelWriter = EasyExcel.write(filename).withTemplate(templateFilename).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    // 模拟分页，向Excel的同一个Sheet重复写入数据
    // 每页5条记录，从第一页开始写入
    int pageSize = 5;
    for (int pageNum = 1; ; pageNum++) {
        List<TestFillVO> testFillVOList = getFillDataByPage(pageNum, pageSize);
        if (testFillVOList == null || testFillVOList.isEmpty()) {
            break;
        }
        excelWriter.fill(testFillVOList, writeSheet);
    }
    // 关闭文件流
    excelWriter.finish();
}

4.2.3.3 Excel 效果图

4.2.4 小结

1、Excel 模板使用{}包住想要填充的变量，如果要输出字符"{"、"}"，则需要使用"\{"、"\}"代替；

2、填充列表时，模板中的填充变量前要多一个点，表示列表，例：{.name}；

3、填充列表时，如果数据量较大，多次填充可以有效节省内存的使用。

4.3 横向填充

4.3.1 填充模板

4.3.2 示例 Demo

@Test
public void fill05() {
    // 填充模板文件路径
    // 模板使用 {} 包住想要填充的变量，如果要输出字符 "{"、"}" ，则需要使用 "\{"、"\}" 代替
    // 填充列表时，模板中的填充变量前要多一个点，表示列表，例：{.name}
    String templateFilename = "D:/easyexcel-test/template03.xlsx";

    // 输出文件路径
    String filename = "D:/easyexcel-test/fill05.xlsx";

    // 创建ExcelWriter对象
    ExcelWriter excelWriter = EasyExcel.write(filename).withTemplate(templateFilename).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    // 填充配置，设置水平填充
    FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
    // 填充数据
    excelWriter.fill(this.getFillDataByDatabase(), fillConfig, writeSheet);

    // 填充统计时间
    Map<String, Object> fillMap = new HashMap<>();
    fillMap.put("statisticsTime", LocalDateTime.now());
    excelWriter.fill(fillMap, writeSheet);

    // 关闭文件流
    excelWriter.finish();
}

4.3.3 Excel 效果图

5 Web 中的实际应用

5.1 Excel 导入导出数据模型类

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(15)
public class UserDataVO {

    /**
     * 用户id
     */
    @ExcelProperty(value = "用户id", index = 0)
    private Integer id;

    /**
     * 用户名
     */
    @ExcelProperty(value = "用户名", index = 1)
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty(value = "年龄", index = 2)
    private Integer age;

    /**
     * 性别
     */
    @ExcelProperty(value = "性别", index = 3)
    private String gender;

    /**
     * 地址
     */
    @ExcelProperty(value = "地址", index = 4)
    private String address;

    /**
     * 创建时间
     */
    @ColumnWidth(30)
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty(value = "创建时间", index = 5)
    private LocalDateTime createTime;

    /**
     * 工资
     */
    @NumberFormat("0.00")
    @ExcelProperty(value = "工资", index = 6)
    private Double salary;

}

5.2 导出 Excel（下载）

5.2.1 示例 Demo - 简单的写

5.2.1.1 接口代码示例

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private IUserService userService;

    /**
     * 获取数据列表
     *
     * @return
     */
    private List<UserDataVO> getDataByDatabase() {
        // 获取数据列表
        List<User> userList = userService.list();
        // 复制对象到excel数据模型实体类
        List<UserDataVO> userDataList = new ArrayList<>();
        userList.forEach(user -> {
            UserDataVO userData = new UserDataVO();
            BeanUtils.copyProperties(user, userData);
            userDataList.add(userData);
        });
        return userDataList;
    }

    /**
     * 导出Excel-简单的写
     *
     * @param response response
     */
    @GetMapping("/export")
    public void userExport(HttpServletResponse response) throws IOException {
        // 获取数据列表
        List<UserDataVO> userDataList = this.getDataByDatabase();

        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("用户数据列表", "utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");

        // 指定使用类 UserDataVO 去写到第一个sheet，sheet命名为 用户数据列表，写完文件流会自动关闭
        EasyExcel.write(response.getOutputStream(), UserDataVO.class).sheet("用户数据列表").doWrite(userDataList);
    }

}

5.2.1.2 接口导出 Excel 效果图

5.2.2 示例 Demo - 失败时返回 json

5.2.2.1 接口代码示例

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private IUserService userService;

     /**
     * 获取数据列表
     *
     * @return
     */
    private List<UserDataVO> getDataByDatabase() {
        // 获取数据列表
        List<User> userList = userService.list();
        // 复制对象到excel数据模型实体类
        List<UserDataVO> userDataList = new ArrayList<>();
        userList.forEach(user -> {
            UserDataVO userData = new UserDataVO();
            BeanUtils.copyProperties(user, userData);
            userDataList.add(userData);
        });
        return userDataList;
    }
    
    /**
     * 导出Excel-简单的写，失败时返回json
     *
     * @param response response
     */
    @GetMapping("/export02")
    public void userExport02(HttpServletResponse response) throws IOException {
        try {
            // 获取数据列表
            List<UserDataVO> userDataList = this.getDataByDatabase();

            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 设置防止中文名乱码
            String filename = URLEncoder.encode("用户数据列表", "utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");

            // 模拟发生异常
            throw new IOException();

            // 指定使用类 UserDataVO 去写到第一个sheet，sheet命名为 用户数据列表
            // 需要设置不自动关闭流  .autoCloseStream(Boolean.FALSE)
//            EasyExcel.write(response.getOutputStream(), UserDataVO.class)
//                     .autoCloseStream(Boolean.FALSE)
//                     .sheet("用户数据列表")
//                     .doWrite(userDataList);
        } catch (IOException e) {
            e.printStackTrace();
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, Object> map = new HashMap<>(2);
            map.put("code", 500);
            map.put("msg", "导出文件异常");
            response.getWriter().println(JSON.toJSONString(map));
        }
    }
    
}

5.2.2.2 模拟发生异常

@GetMapping("/export02")
public void userExport02(HttpServletResponse response) throws IOException {
    try {
        // 获取数据列表
        List<UserDataVO> userDataList = this.getDataByDatabase();

        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("用户数据列表", "utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");

        // 模拟发生异常
        throw new IOException();

        // 指定使用类 UserDataVO 去写到第一个sheet，sheet命名为 用户数据列表
        // 需要设置不自动关闭流  .autoCloseStream(Boolean.FALSE)
        // EasyExcel.write(response.getOutputStream(), UserDataVO.class)
        //          .autoCloseStream(Boolean.FALSE)
        //          .sheet("用户数据列表")
        //          .doWrite(userDataList);
    } catch (IOException e) {
        e.printStackTrace();
        // 重置response
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        Map<String, Object> map = new HashMap<>(2);
        map.put("code", 500);
        map.put("msg", "导出文件异常");
        response.getWriter().println(JSON.toJSONString(map));
    }
}

5.2.2.3 接口异常返回信息

5.3 导入 Excel（上传）

5.3.1 导入 Excel 文件

5.3.2 数据入库方法

public void saveUserData(List<UserDataVO> userDataList) {
    List<User> collect = userDataList.stream().map(userData -> {
        User user = new User();
        BeanUtils.copyProperties(userData, user);
        return user;
    }).collect(Collectors.toList());
    // 数据一次性插入，尽量避免多次 insert，影响性能
    this.saveBatch(collect);
    log.info("数据成功入库");
}

5.3.3 监听器

@Slf4j
// 继承 AnalysisEventListener 或者实现 ReadListener 接口，重写 invoke 和 doAfterAllAnalysed 方法
public class UserDataListener extends AnalysisEventListener<UserDataVO> {

    /**
     * 由于 listener 不能被 spring 管理，所以要使用构造器注入 service
     */
    private final IUserService userService;

    /**
     * 单次缓存的数据量，达到该值后存储一次数据库，然后清理 list，方便内存回收
     */
    private static final int BATCH_COUNT = 100;

    /**
     * 缓存的数据列表
     */
    private final List<UserDataVO> userDataList = new ArrayList<>();

    /**
     * 构造器注入 service
     */
    public UserDataListener(IUserService userService) {
        this.userService = userService;
    }

    /**
     * 数据解析，每解析一行调用一次
     *
     * @param data 一行数据
     * @param analysisContext
     */
    @Override
    public void invoke(UserDataVO data, AnalysisContext analysisContext) {
        log.info("解析一行数据:{}", data);
        userDataList.add(data);
        // 数据量达到设置的阈值，处理一次数据，然后清空列表，防止内存中数据量过大导致OOM
        if (userDataList.size() >= BATCH_COUNT) {
            // 存储数据
            userService.saveUserData(userDataList);
            // 清空数据列表
            userDataList.clear();
        }
    }

    /**
     * 所有数据解析完成后调用
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 处理剩余的没达到阈值的数据
        if (userDataList.size() > 0) {
            userService.saveUserData(userDataList);
            // 清空数据列表
            // 读取多个 Sheet 时，每个 Sheet 解析完成都会进到这个方法，所以清空数据列表避免重复读取数据
            userDataList.clear();
        }

        log.info("解析完成");
    }

}

5.3.4 接口代码示例

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private IUserService userService;

    /**
     * 导入Excel
     *
     * @param file excel文件
     */
    @GetMapping("/import")
    public String userImport(@RequestPart("excelFile") MultipartFile file) {
        try {
            // 这里需要指定使用哪个 class 去读，然后读取第一个 sheet 文件流就会自动关闭
            EasyExcel.read(file.getInputStream(), UserDataVO.class, new UserDataListener(userService)).sheet().doRead();
            return "success";
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "fail";
    }

}

5.3.5 运行结果

控制台日志

6 通用监听器及使用

6.1 通用监听器

@Slf4j
public class ExcelListener<T> extends AnalysisEventListener<T> {

    /**
     * 默认行数
     */
    private static int BATCH_COUNT = 1000;

    private final List<T> list;

    private final Consumer<List<T>> consumer;
    private final Predicate<T> predicate;

    /**
     * 读取每一条数据进行 predicate 操作
     * 读取默认行数后进行 consumer 操作
     *
     * @param predicate 读取一条数据执行的方法。例：校验数据规则
     * @param consumer  读取规定行数后执行的方法
     */
    public ExcelListener(Predicate<T> predicate, Consumer<List<T>> consumer) {
        this(BATCH_COUNT, predicate, consumer);
    }

    /**
     * 读取每一条数据进行 predicate 操作
     * 读取 count 行就进行 consumer 操作
     *
     * @param count     读取的行数
     * @param predicate 读取一条数据执行的方法。例：校验数据规则
     * @param consumer  读取规定行数后执行的方法
     */
    public ExcelListener(int count, Predicate<T> predicate, Consumer<List<T>> consumer) {
        BATCH_COUNT = count;
        this.consumer = consumer == null ? ts -> {} : consumer;
        this.predicate = predicate == null ? t -> true : predicate;
        list = new ArrayList<>(BATCH_COUNT);
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        if (!predicate.test(data)){
            // 如果不符合校验规则，就不进行操作开始解析下一条
            log.info("该条数据不符合校验规则");
            return;
        }
        // 符合校验规则则添加
        list.add(data);
        // 达到 BATCH_COUNT 就去存储一次数据库，防止内存数据过多，导致 OOM
        if (list.size() >= BATCH_COUNT) {
            consumer.accept(list);
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (list.size() > 0) {
            consumer.accept(list);
        }
        log.info("所有数据解析完成！");
    }

}

6.2 接口代码示例

@GetMapping("/import02")
public String userImport02(@RequestPart("excelFile") MultipartFile file) {
    // 创建通用监听器
    ExcelListener<UserDataVO> excelListener = new ExcelListener<>(p -> StringUtils.isNotBlank(p.getName()) && p.getId() != null, userService::saveUserData);

    try {
        // 这里需要指定使用哪个 class 去读，然后读取第一个 sheet 文件流就会自动关闭
        EasyExcel.read(file.getInputStream(), UserDataVO.class, excelListener).sheet().doRead();
        return "success";
    } catch (IOException e) {
        e.printStackTrace();
    }
    return "fail";
}

6.3 增强通用监听器（返回导入异常的记录）

6.3.1 异常坐标信息对象

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExceptionCoordinate {

    /**
     * 行数
     */
    private Integer rowIndex;

    /**
     * 列数
     */
    private Integer columnIndex;

    /**
     * 异常信息
     */
    private String message;

}

6.3.2 Excel 导入结果对象

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ImportResult {

    /**
     * 成功导入行数
     */
    private Integer successCount = 0;

    /**
     * 导入失败行数
     */
    private Integer errorCount = 0;

    /**
     * 异常坐标信息列表
     */
    private List<ExceptionCoordinate> errorList;

    public ImportResult(Integer successCount, List<ExceptionCoordinate> errorList) {
        this.successCount = successCount;
        this.errorCount = errorList.size();
        this.errorList = errorList;
    }

}

6.3.3 通用监听器

@Slf4j
public class ExcelDataListener<T> extends AnalysisEventListener<T> {

    /**
     * 默认行数
     */
    private static int BATCH_COUNT = 1000;

    private final List<T> list;

    /**
     * 记录成功记录条数
     */
    private Integer successCount = 0;

    /**
     * 保存错误数据对应坐标
     */
    private final List<ExceptionCoordinate> errorList;

    private final Consumer<List<T>> consumer;
    private final Predicate<T> predicate;

    /**
     * 读取每一条数据进行 predicate 操作
     * 读取默认行数后进行 consumer 操作
     *
     * @param predicate 读取一条数据执行的方法。例：校验数据规则
     * @param consumer  读取规定行数后执行的方法
     */
    public ExcelDataListener(Predicate<T> predicate, Consumer<List<T>> consumer) {
        this(BATCH_COUNT, predicate, consumer);
    }

    /**
     * 读取每一条数据进行 predicate 操作
     * 读取 count 行就进行 consumer 操作
     *
     * @param count     读取的行数
     * @param predicate 读取一条数据执行的方法。例：校验数据规则
     * @param consumer  读取规定行数后执行的方法
     */
    public ExcelDataListener(int count, Predicate<T> predicate, Consumer<List<T>> consumer) {
        BATCH_COUNT = count;
        this.consumer = consumer == null ? ts -> {} : consumer;
        this.predicate = predicate == null ? t -> true : predicate;
        list = new ArrayList<>(BATCH_COUNT);
        errorList = new ArrayList<>();
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        if (!predicate.test(data)){
            // 如果不符合校验规则，就不进行操作开始解析下一条
            log.info("该条数据不符合校验规则");
            return;
        }
        // 符合校验规则则添加
        list.add(data);
        // 达到 BATCH_COUNT 就去存储一次数据库，防止内存数据过多，导致 OOM
        if (list.size() >= BATCH_COUNT) {
            consumer.accept(list);
            // 先累加成功记录条数，再清空列表
            successCount += list.size();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (list.size() > 0) {
            consumer.accept(list);
            // 累加成功记录条数
            successCount += list.size();
        }
        log.info("所有数据解析完成！");
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) {
        log.error("解析异常，异常原因::{}", exception.getMessage());

        // 如果是某一个单元格的转换异常，可以获取到具体行号列号
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            log.error("第{}行，第{}列解析异常", excelDataConvertException.getRowIndex() + 1, excelDataConvertException.getColumnIndex() + 1);
            // 转换异常数据坐标
            ExceptionCoordinate exceptionCoordinate = new ExceptionCoordinate();
            exceptionCoordinate.setRowIndex(excelDataConvertException.getRowIndex() + 1);
            exceptionCoordinate.setColumnIndex(excelDataConvertException.getColumnIndex() + 1);
            exceptionCoordinate.setMessage(excelDataConvertException.getMessage());
            errorList.add(exceptionCoordinate);
        }
    }

    /**
     * Excel导入结果
     *
     * @return
     */
    public ImportResult getImportResult() {
        return new ImportResult(successCount, errorList);
    }
}

6.3.4 接口代码示例

@GetMapping("/import03")
public ImportResult userImport03(@RequestPart("excelFile") MultipartFile file) throws IOException {
    // 创建通用监听器
    ExcelDataListener<UserDataVO> excelDataListener = new ExcelDataListener<>(p -> StringUtils.isNotBlank(p.getName()) && p.getId() != null, userService::saveUserData);

    // 这里需要指定使用哪个 class 去读，然后读取第一个 sheet 文件流就会自动关闭
    EasyExcel.read(file.getInputStream(), UserDataVO.class, excelDataListener).sheet().doRead();
    return excelDataListener.getImportResult();
}

6.3.5 运行结果

6.4 小结

上述写法使用了 java8 的函数式接口Predicate<T>和Consumer<T>

predicate.test(T t)：传入参数 t，能匹配上 predicate 返回 true，否则返回 false。用于测试对象。

@Test
public void testPredicate() {
    // Predicate<Integer> predicate = new Predicate<Integer>() {
    //     @Override
    //     public boolean test(Integer num) {
    //         return num > 10;
    //     }
    // };
    // 上述写法等同于
    Predicate<Integer> predicate = num -> num > 10;

    System.out.println(predicate.test(11)); // true
    System.out.println(predicate.test(9)); // false
}

consumer.accept(T t)：传入参数 t，进行 consumer 操作，无需返回值。

@Test
public void testConsumer() {
    // Consumer<Integer> consumer = new Consumer<Integer>() {
    //     @Override
    //     public void accept(Integer num) {
    //         System.out.println(num * num);
    //     }
    // };
    // 上述写法等同于
    Consumer<Integer> consumer = num -> System.out.println(num * num);

    consumer.accept(6); // 36
    consumer.accept(9); // 81
}