EasyExcel的使用

1、简介

在日常项目开发中，经常会遇到解析excel文件中的数据、或者将某些数据导出成excel文件的需求，目前Java解析、生成Excel比较有名的框架有Apache POI、jxl，但是它们都存在一个严重的问题就是非常的耗内存。POI有一套Sax模式的API可以在一定程度上解决一些内存溢出的问题，但是POI还是有一些缺陷，比如07版Excel解压缩以及解压缩存储都是在内存中完成的，内存消耗依然很大。如果系统并发量不大的话可能还行，但是一旦并发上来以后一定会导致OOM（内存溢出）或者JVM频繁的full gc。

EasyExcel是阿里巴巴提供的一个基于Java的快速、简洁、解决大文件内存溢出的Excel开源处理工具，它重写了POI对07版Excel的解析，一个3M的Excel用POI sax解析依然需要100M左右内存，但是改用EasyExcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便。

2、使用介绍

EasyExcel能大大减少占用内存的主要原因是在解析excel文件时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。POI与EasyExcel的区别如下：

POI是通过WookBook来解析表格文件的，虽然内部有使用到Sax模式，能够大大提高效率，但是要解析大文件（10w、100w行）的话很容易出现OOM（内存溢出）。相比之下：

1. EasyExcel的解析是在磁盘上进行的，几乎可以将几mb级别的内存压缩到kb级别，几乎不用担心OOM；
2. 用Java模型进行关系映射，通过 @ExcelProperty注解就可以完成行与列的映射；
3. EasyExcel中有一个类AnalysisEventListener，里面有一个方法invoke实现了一行一行返回，另外还可以重写该类的doAfterAllAnalysed方法，用来做事后处理之类的操作，相当的灵活。

3、代码实现

在实际使用过程中，首先需要引入Easyexcel的maven依赖。

com.alibabaeasyexcel3.0.5

对象注解@ExcelProperty用于匹配excel和实体类的匹配，参数如下：

3.1 excel简单读

比如读取的excel表头如下：

则对应的数据模型为：

import com.alibaba.excel.annotation.ExcelProperty;import lombok.Data;@Datapublic class CaseInfoDto {/** * 案件名称 */@ExcelProperty(value = "案件名称", index = 0)private String caseName;/** * 案件类型 */@ExcelProperty(value = "案件类型", index = 1)private String caseType;/** * 嫌疑人数 */@ExcelProperty(value = "嫌疑人数", index = 2)private Integer suspectsNum;/** * 案件状态 */@ExcelProperty(value = "案件状态", index = 3)private String caseStatus;/** * 发案时间 */@ExcelProperty(value = "发案时间", index = 4)private String happenTime;/** * 发案地点 */@ExcelProperty(value = "发案地点", index = 5)private String location;}

在读的时候，推荐使用index进行映射，因为有时候我们拿到的excel文件其表头文字可能会非常长，此时用value显示不适合。如果Java类中的属性字段顺序和Excel中的表头字段顺序完全一致, 甚至可以不写@ExcelProperty注解也能顺利解析。

读取excel的关键是写一个监听器，实现ReadListener，如下所示是ReadListener的源码，每解析一行数据就会调用invoke()方法返回解析的数据，当全部解析完成后会调用doAfterAllAnalysed()方法，因为我们只需要重写这两个方法即可。

下面介绍几种监听器实现的方案：

第一种：手动实现ReadListener

如下所示是自定义的监听器，每解析到一条数据判断案例名称是否为空，如果不为空才加入到结果集中，最后全部解析完毕后，分批入库。

import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.read.listener.ReadListener;import com.hikvision.ga.common.BusinessException;import com.hikvision.js.ctm01visual.entity.dto.CaseInfoDto;import com.hikvision.js.ctm01visual.exception.ErrorCode;import com.hikvision.js.ctm01visual.mapper.CaseInfoMapper;import com.hikvision.js.ctm01visual.util.ApplicationContextUtils;import lombok.extern.slf4j.Slf4j;import org.apache.commons.lang3.StringUtils;import org.springframework.context.annotation.DependsOn;import org.springframework.stereotype.Component;import java.util.ArrayList;import java.util.List;import java.util.stream.Collectors;/** * @ClassName EasyExcelDataListener * @Description * @Author* @Date 2023/4/21 9:36 * @Version 1.0 **/@Slf4j@Component@DependsOn("applicationContextUtils")public class DemoDataListener1 implements ReadListener {/** * 结果集 */List resultList = new ArrayList();/** * 每执行一条数据操作一次 * @param * @param analysisContext */@Overridepublic void invoke(CaseInfoDto caseInfoDto, AnalysisContext analysisContext) {//只有名称不为空才保存if (!StringUtils.isEmpty(caseInfoDto.getCaseName())) {resultList.add(caseInfoDto);}}/** * 解析完成后进行的操作 * @param analysisContext */@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {log.info("本次共导入{}条点位信息", resultList.size());batchInsertCaseInfo(resultList);}private void batchInsertCaseInfo(List list) {//要使用该方式注入，不能使用@Resource注入CaseInfoMapper caseInfoMapper = (CaseInfoMapper) ApplicationContextUtils.getBean("caseInfoMapper");int size = list.size();int offset = 0;try {while (offset < size) {List tempList = list.stream().skip(offset).limit(500).collect(Collectors.toList());offset += 500;caseInfoMapper.insertBatch(tempList);}} catch (Exception e) {log.error(ErrorCode.ERR_DATA_INSERT.getCode(), ErrorCode.ERR_DATA_INSERT.getMsg(), e);throw new BusinessException(ErrorCode.ERR_DATA_INSERT.getCode(), ErrorCode.ERR_DATA_INSERT.getMsg(), e);}}}

读excel的代码：

 @Overridepublic void importCaseInfo(MultipartFile file) {try {//第一种：采用固定的监听器，全部解析完后再入库处理EasyExcel.read(file.getInputStream(), CaseInfoDto.class, new DemoDataListener1()).sheet().doRead();} catch (Exception e) {log.error("解析excel失败：", e);}}

但是该方法是错误使用easyexcel的典型例子，虽然可以正常解析，但是存在以下几点问题：

（1）将全部excel的数据解析完成保存在内存中，最后入库，那么当数据量非常大的时候，仍然会导致OOM的问题。

（2）由于依赖注入的前后关系，无法使用@Resource注入其他的实例，只能通过ApplicationContextUtils.getBean()的方式去获取。

（3）如果一个项目中需要导入多个不同的excel文件，就要对应写多个监听器，不通用。

基于以上考虑，采用固定方式的监听器不推荐使用。

第二种：使用较为通用的监听器，且解析到一定条数的数据后就入库

使用泛型，并定义数据校验predicate和数据消费的consumer，将校验方式和消费方式通过构造器传入，实现多变的数据校验和消费方式。

import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.read.listener.ReadListener;import com.alibaba.fastjson.JSON;import lombok.extern.slf4j.Slf4j;import java.util.ArrayList;import java.util.List;import java.util.function.Consumer;import java.util.function.Predicate;/** * @ClassName EasyExcelDataListener * @Description * @Author* @Date 2023/4/21 9:36 * @Version 1.0 **/@Slf4jpublic class DemoDataListener2 implements ReadListener {/** * 结果集 */private List resultList ;/** * 每满500条进行一次入库操作 */private int BATCH_COUNT = 500;/** * 校验数据 */private Predicate predicate;/** * 满足 BATCH_COUNT 条后数据应该进行的操作 */private Consumer<List> consumer;/** * 读取每一条数据进行predicate操作， * 读取到count行数进行consumer操作 *@param count 读取的行数 * @param predicate 读取一条数据执行的方法。例：校验数据规则 * @param consumer读取规定行数后执行的方法 */public DemoDataListener2(int count, Predicate predicate, Consumer<List> consumer) {BATCH_COUNT = count;this.consumer = consumer == null " />= BATCH_COUNT) {consumer.accept(resultList);resultList.clear();}}/** * 解析完成后进行的操作 * @param analysisContext */@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {//将最后一次的数据处理掉if (resultList.size()>0) {consumer.accept(resultList);}log.info("解析excel数据完成");}}

此时的invoke()方法中先进行数据校验，符合条件后加入结果集，当结果集数据条数到达规定的量后，直接消费入库存储，然后将结果集resultList清空，防止过多数据保留在内存中造成OOM。当所有数据解析完成后，再将最后存储在内存的一部分数据入库。

该方式通过边解析边消费的方式，解决大批量数据下可能存在的OOM问题，且采用泛型，如果多个excel解析时，只要生成不同的predicate和consumer传入即可实现不同excel的解析。

上述监听器可采用更简单的实现方式，编写一个工具类，根据传入的consumer生成一个AnalysisEventListener对象，从而让代码更简洁。

import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import java.util.LinkedList;import java.util.List;import java.util.function.Consumer;/** * className EasyExcelUtils * description easyexcel监听器优化 * * 
 * 原始监听器： *（1）每次解析不同数据模型都要新增一个监听器, 重复工作量大; * (2)即使用了匿名内部类,程序也显得臃肿; * (3)数据处理一般都会存在于项目的service中, 监听器难免会依赖dao层, 导致程序耦合度高. * 解决方案: * （1）通过泛型指定数据模型类型, 针对不同类型的数据模型只需要定义一个监听器即可; * （2）使用jdk8新特性中的函数式接口, 将数据处理从监听器中剥离出去, 进行解耦 * 
 * * @author* @version 1.0 * @date 2023/7/26 15:38 **/public class EasyExcelUtils {/** * 获取读取Excel的监听器对象 * 为了解耦及减少每个数据模型bean都要创建一个监听器的臃肿, 使用泛型指定数据模型类型 * 使用jdk8新特性中的函数式接口 Consumer * 可以实现任何数据模型bean的数据解析, 不用重复定义监听器 * @param consumer 处理解析数据的函数, 一般可以是数据入库逻辑的函数 * @param threshold 阈值,达到阈值就处理一次存储的数据 * @param  数据模型泛型 * @return 返回监听器 */public static  AnalysisEventListener getReadListener(Consumer<List> consumer, int threshold) {return new AnalysisEventListener() {/** * 存储解析的数据 T t */// ArrayList基于数组实现, 查询更快//List dataList = new ArrayList(threshold);// LinkedList基于双向链表实现, 插入和删除更快List dataList = new LinkedList();/** * 每解析一行数据事件调度中心都会通知到这个方法, 订阅者1 * @param data 解析的每行数据 * @param context */@Overridepublic void invoke(T data, AnalysisContext context) {dataList.add(data);// 达到阈值就处理一次存储的数据if (dataList.size() >= threshold) {consumer.accept(dataList);dataList.clear();}}/** * excel文件解析完成后,事件调度中心会通知到该方法, 订阅者2 * @param context */@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 最后阈值外的数据做处理if (dataList.size() > 0) {consumer.accept(dataList);}}};}/** * 获取读取Excel的监听器对象, 不指定阈值, 默认阈值为 2000 * @param consumer * @param  * @return */public static  AnalysisEventListener getReadListener(Consumer<List> consumer) {return getReadListener(consumer, 2000);}}

此时读excel的代码：

 @Overridepublic void importCaseInfo(MultipartFile file) {try {//第二种：采用较为通用的监听器，每解析count条数据后就入库EasyExcel.read(file.getInputStream(), CaseInfoDto.class, new DemoDataListener2(500, dataCheck(), dataProcess())).sheet().doRead();//采用工具类EasyExcel.read(file.getInputStream(), CaseInfoDto.class, EasyExcelUtils.getReadListener(dataProcess(), 500)).sheet().doRead();} catch (Exception e) {log.error("解析excel失败：", e);}}

第三种：使用Easyexcel提供的官方自定义的监听器器

Easyexcel提供了一个官方自定义的监听器PageReadListener，该监听器默认每解析到100条数据就消费一次，无法自定义修改BATCH_COUNT值。该方法的源码如下：

此时读excel的代码是：

@Overridepublic void importCaseInfo(MultipartFile file) {try {//第三种：采用官方自定义的监听器，每解析100条数据就入库 EasyExcel.read(file.getInputStream(), CaseInfoDto.class, new PageReadListener(dataProcess())).sheet(1).doRead();} catch (Exception e) {log.error("解析excel失败：", e);}}

如果一个excel有多个sheet，可以在sheet()中指定读取某个sheet即可。

3.2 excel简单写

相对于读需要自定义监听器，easyexcel的写就简单多了，只要建立好实体类的映射后，直接使用EasyExcel.write()方法即可。

@Overridepublic void exportExcel(HttpServletResponse response) throws IOException {SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");String fileName = "测试easyexcel_" + df.format(new Date());fileName = URLEncoder.encode(fileName, "UTF-8");response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf8");response.setHeader("Content-disposition", "attachment;filename=" + fileName+".xlsx");//全部结果List assetMatchResultList = queryAll();ServletOutputStream outputStream = response.getOutputStream();//写入excelEasyExcel.write(outputStream, DeviceInfoDto.class).sheet("测试结果").doWrite(assetMatchResultList);}

读的过程通常会遇到数据转换，比如常用的人员性别，1表示男，2表示女性，0表示未知，那么数据库中存储的是通常是0，1，2这样的数字，而导出成excel文件的时候需要切换成对应的“未知”“男”“女”。前面我们说到@ExcelProperty注解的converter属性，就是进行转换用的，写的时候只要实现Converter接口中的convertToExcelData()方法即可。

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.GlobalConfiguration;import com.alibaba.excel.metadata.data.ReadCellData;import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.metadata.property.ExcelContentProperty;import io.swagger.models.auth.In;/** * className SexConverter * description * * @author* @version 1.0 * @date 2023/10/20 11:17 **/public class SexConverter implements Converter {@Overridepublic WriteCellData("女");default:return new WriteCellData("未知");}}}

然后在实体类上添加这个转换器。即可轻松实现数据转换。

同样的，在读excel的时候也可以进行转化，可以看下Converter接口的源码，其中convertToJavaData()方法就是用于读的时候进行转化的。

4、总结

在实际项目使用过程中，postman测试导出接口成功且文件能正常打开，但是将组件部署到服务器上使用导出功能时，导出的报表打不开，提示如下错误：

查看组件的debug日志，发现报了一个FontConfiguration.getVersion()的错误。

且在多个服务器上都出现过该问题。这个因为服务器上没有安装字体包导致的，总结的完整解决办法如下：

Xshell连接组件安装的服务器，执行：

a. 安装字体包：yum -y install fontconfig

b. 刷新内存中的字体缓存：fc-cache

c. 查看字体是否安装成功(有一种即可)：fc-list

d. 重启代码运行（不必重启服务器）

通常即可解决该问题，但是如果：在执行a步骤后出现下述错误：

需要：

a.a进入目录：cd /etc/yum.repos.d/

a.b备份原始文件：

mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup

若是找不到yum.repos.d文件夹，用mkdir创建一一个：mkdir /etc/yum.repos.d即可

a.c下载对应版本的repo文件：访问网址CentOS镜像使用帮助，根据服务器CentOS版本下载对应版本即可。

a.d将新的repo文件放到 /etc/yum.repos.d 目录下，使用ll查看目录下的文件，可见多了一个备份文件和新的repo文件。

a.e继续执行a中的安装字体包的操作

如果：新报出下面这个错误

则：修改resolve.conf文件：cd /etc、vim resolv.conf，在后面加入nameserver 8.8.8.8，保存。

继续执行a中的操作，等待一段时间后，直到出现Complete!即可。

正常情况下安装成功后的界面如下：

注意：以上所有操作均在root用户下执行。操作完成后重启组件后，新导出一次excel，即可成功打开。

EasyExcel作为轻量级快速、简洁、解决大文件内存溢出的java处理Excel工具，还有很多其他的读写操作功能，比如复杂表头的读取、获取excel额外信息如批注、超链接、合并单元格等等。本文中只介绍了最为简单的读写方式，读者可根据实际项目需求实现其他复杂功能，可参考官方网址：EasyExcel官方文档 – 基于Java的Excel处理工具 | Easy Excel。