EasyExcel官方文档:https://www.yuque.com/easyexcel/doc/easyexcel.
初识EasyExcel
传统POI的内存消耗较大
特点
- 功能强大
- 代码书写冗余繁杂
- 读写大文件耗费内存较大，容易OOM
EasyExcel
重写了POI对07版Excel的解析
- EasyExcel重写了POI对07版Excel的解析，可以把内存消耗从100M左右降低到10M以内，并且再大的Excel不会出现内存溢出，03版仍依赖POI的SAX模式。
下图为64M内存1分钟内读取75M(46W行25列)的Excel（当然还有急速模式能更快，但是内存占用会在100M多一点） - 在上层做了模型转换的封装，让使用者更加简单方便
特点 - 在数据模型层面进行了封装，使用简单
- 重写了07版本的Excel的解析代码，降低内存消耗，能有效避免OOM
- 只能操作Excel
- 不能读取图片
一、使用EasyExcel写入
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.jxsl</groupId>
<artifactId>exceldemo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>exceldemo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.78</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
application.yml
server:
port: 8887
#\u6570\u636E\u6E90
spring:
datasource:
druid:
url: jdbc:mysql://localhost:3306/excel?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: 123456
initial-size: 10
max-active: 50
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
# mysql \u6570\u636E\u5E93\u7684\u7279\u5F81 \u4F1A\u5173\u95ED\u5DF2\u7ECF\u8FDE\u63A5\u4E868\u4E2A\u5C0F\u65F6\u7684\u8FDE\u63A5
validation-query: SELECT 1 FROM DUAL
# mybatis 配置
mybatis:
# 扫描映射文件
mapper-locations: classpath:mapper/*.xml
configuration:
# 开启驼峰映射配置
map-underscore-to-camel-case: true
# log
# 整个工程只答应info 以及以上级别的日志
logging:
level:
root: info
com.jsxl.mapper: debug
实体类
package com.jsxl.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DemoData {
private Integer id;
private String string;
private Date date;
private Double doubleData;
}
TestWrite
@Test
public void simpleWrite() {
//jdk1.8
EasyExcel.write(path, DemoData.class)
.sheet("模板")
.doWrite(() -> {
// 分页查询数据
return demoDataMapper.selectByExample(null);
});
// // 写法2
// // 这里 需要指定写用哪个class去写
// ExcelWriter excelWriter = null;
// try {
// excelWriter = EasyExcel.write(path, DemoData.class).build();
// WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
// excelWriter.write(data(), writeSheet);
// } finally {
// // 千万别忘记finish 会帮忙关闭流
// if (excelWriter != null) {
// excelWriter.finish();
// }
// }
/**
* 根据参数只导出指定列
* 1. 创建excel对应的实体对象 参照{@link DemoData}
* 2. 根据自己或者排除自己需要的列
* 3. 直接写即可
*/
@Test
void excludeOrIncludeWrite() {
String fileName = path + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
// 这里需要注意 在使用ExcelProperty注解的使用，如果想不空列则需要加入order字段，而不是index,order会忽略空列，然后继续往后，而index，不会忽略空列，在第几列就是第几列。
// 根据用户传入字段 假设我们要忽略 date
Set<String> excludeColumnFiledNames = new HashSet<String>();
excludeColumnFiledNames.add("date");
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板")
.doWrite(demoDataMapper.selectByExample(null));
fileName = path + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
// 根据用户传入字段 假设我们只要导出 date
Set<String> includeColumnFiledNames = new HashSet<String>();
includeColumnFiledNames.add("date");
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, DemoData.class).includeColumnFiledNames(includeColumnFiledNames).sheet("模板")
.doWrite(demoDataMapper.selectByExample(null));
}
指定写入的列
@ExcelProperty(value = "数字标题", index = 3)
private Double doubleData;
复杂头写入
@Getter
@Setter
@EqualsAndHashCode
public class ComplexHeadData {
@ExcelProperty({"主标题", "字符串标题"})
private String string;
@ExcelProperty({"主标题", "日期标题"})
private Date date;
@ExcelProperty({"主标题", "数字标题"})
private Double doubleData;
}
注解ColumnWidth()
/**
* Set the width of the table
*
* @author Jiaju Zhuang
*/
@Target({ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited//@Inherited修饰的注解的@Retention是RetentionPolicy.RUNTIME，则增强了继承性，在反射中可以获取得到
public @interface ColumnWidth {
/**
* Column width
* 默认返回-1
* -1 means the default co
*/
// 返回列的宽度
int value() default -1;
}
注解ExcelProperty
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelProperty {
/**
* 工作表标题的名称。
* write: 当你有多个头时，它会自动合并
* read: 当你有多个头时，选最后一个
* @return The name of the sheet header
*/
String[] value() default {""};
/**
* 返回当前列的索引
*读取或写入列的索引，如果它等于-1，它是按Java类排序。
* priority: index > order > default sort
*
* @return Index of column
*/
int index() default -1;
/**
* 定义列的排序顺序。
* priority: index > order > default sort
* @return Order of column
*/
int order() default Integer.MAX_VALUE;
/**
* 强制当前字段使用这个转换器
* @return Converter
*/
Class<? extends Converter<?>> converter() default AutoConverter.class;
/**
* 如果默认格式不满足，可以设置格式
* @return Format string
* @deprecated please use {@link com.alibaba.excel.annotation.format.DateTimeFormat}
*/
@Deprecated
//若某类或某方法加上该注解之后，表示此方法或类不再建议使用，调用时也会出现删除线，但并不代表不能用，只是说，不推荐使用，因为还有更好的方法可以调用。
String format() default "";
}
注解ExcelIgnore
/**
* 忽略将该字段
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelIgnore {}
二、使用EasyExcel读出
新增一个接口
int save(@Param("list") List<DemoData> list);
Mapper.xml
<insert id="save" parameterType="java.util.List">
insert into excel.demo_data (string,date,double_data)
values
<foreach collection="list" item="item" separator=",">
(#{item.string}, #{item.date},#{item.doubleData})
</foreach>
</insert>
这里需要一个监听器，注解这个监听器不能被spring管理
package com.jsxl.read;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.jsxl.mapper.DemoDataMapper;
import com.jsxl.pojo.DemoData;
import lombok.extern.slf4j.Slf4j;
import java.util.List;
@Slf4j
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener implements ReadListener<DemoData> {
/**
* 引入接口
*/
DemoDataMapper demoDataMapper;
/**
* 我们通过构造函数从外部传入接口
* @param demoDataMapper
*/
public DemoDataListener(DemoDataMapper demoDataMapper){
this.demoDataMapper = demoDataMapper;
}
/**
* 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
/**
* 这个每一条数据解析都会来调用
*/
@Override
public void invoke(DemoData data, AnalysisContext context) {
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
cachedDataList.add(data);
System.out.println(cachedDataList.size());
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据，开始存储数据库！", cachedDataList.size());
demoDataMapper.save(cachedDataList);
log.info("存储数据库成功！");
}
}
三、填充
Excel表格中用{} 来表示包裹要填充的变量，如果单元格文本中本来就有{，}左右大括号，需要在括号前面使用斜杠转义{，}。
代码中被填充数据的实体对象的成员变量名或被填充map集合的key需要和Excel中被{}包裹的变量名称一致。
3.1 简单填充
实体类
@Getter
@Setter
@EqualsAndHashCode
public class FillData {
private String name;
private double number;
private Date date;
}
测试
// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
private static final String templateFileName1 = "D:\\github\\exceldemo\\模板1.xlsx";
private static final String fileName1="D:\\github\\exceldemo\\simpleFill1.xlsx";
/**
* 最简单的填充
*/
@Test
public void simpleFill() {
// 方案1 根据对象填充
// 这里 会填充到第一个sheet， 然后文件流会自动关闭
FillData fillData = new FillData();
fillData.setName("张三");
fillData.setNumber(5.2);
EasyExcel.write(fileName1).withTemplate(templateFileName1).sheet().doFill(fillData);
// 方案2 根据Map填充
// 这里 会填充到第一个sheet， 然后文件流会自动关闭
// Map<String, Object> map = new HashMap<String, Object>();
// map.put("name", "张三");
// map.put("number", 5.2);
// EasyExcel.write(fileName1).withTemplate(templateFileName1).sheet().doFill(map);
}
3.2 填充列表
测试
@Test
public void listFill() {
// 填充list 的时候还要注意 模板中{.} 多了个点 表示list
// 方案1 一下子全部放到内存里面 并填充
// 这里 会填充到第一个sheet， 然后文件流会自动关闭
EasyExcel.write(fileName2).withTemplate(templateFileName2).sheet().doFill(data());
// 方案2 分多次 填充 会使用文件缓存（省内存） jdk8
// since: 3.0.0-beta1
// EasyExcel.write(fileName2)
// .withTemplate(templateFileName2)
// .sheet()
// .doFill(() -> {
// // 分页查询数据
// return data();
// });
//
// // 方案3 分多次 填充 会使用文件缓存（省内存）
// ExcelWriter excelWriter = EasyExcel.write(fileName2).withTemplate(templateFileName2).build();
// WriteSheet writeSheet = EasyExcel.writerSheet().build();
// excelWriter.fill(data(), writeSheet);
// excelWriter.fill(data(), writeSheet);
// // 千万别忘记关闭流
// excelWriter.finish();
}
public List<FillData> data(){
List<FillData> list = Lists.newArrayList();
for (int i = 0; i < 10 ; i++){
FillData fillData = new FillData();
fillData.setName("name"+i);
fillData.setNumber(Math.random()*100);
fillData.setDate(new Date());
list.add(fillData);
}
return list;
}
3.3 数据量大的复杂填充
private static final String templateFileName3 = "D:\\github\\exceldemo\\模板3.xlsx";
private static final String fileName3="D:\\github\\exceldemo\\simpleFill3.xlsx";
/**
* 数据量大的复杂填充
* 这里的解决方案是 确保模板list为最后一行，然后再拼接table.还有03版没救，只能刚正面加内存。
* @since 2.1.1
*/
@Test
public void complexFillWithTable() {
// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
// {} 代表普通变量 {.} 代表是list的变量
// 这里模板 删除了list以后的数据，也就是统计的这一行
ExcelWriter excelWriter = EasyExcel.write(fileName3).withTemplate(templateFileName3).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
// 直接写入数据
excelWriter.fill(data(), writeSheet);
excelWriter.fill(data(), writeSheet);
// 写入list之前的数据
Map<String, Object> map = new HashMap<String, Object>();
map.put("date", new SimpleDateFormat("yyyy年MM月dd日HH:mm:ss").format(new Date()));
excelWriter.fill(map, writeSheet);
// list 后面还有个统计 想办法手动写入
// 这里偷懒直接用list 也可以用对象
List<List<String>> totalListList = new ArrayList<List<String>>();
List<String> totalList = new ArrayList<String>();
totalListList.add(totalList);
totalList.add(null);
totalList.add(null);
totalList.add(null);
// 第四列
totalList.add("统计:1000");
// 这里是write 别和fill 搞错了
excelWriter.write(totalListList, writeSheet);
excelWriter.finish();
// 总体上写法比较复杂 但是也没有想到好的版本 异步的去写入excel 不支持行的删除和移动，也不支持备注这种的写入，所以也排除了可以
// 新建一个 然后一点点复制过来的方案，最后导致list需要新增行的时候，后面的列的数据没法后移，后续会继续想想解决方案
}
3.4 横向的填充
private static final String templateFileName4 = "D:\\github\\exceldemo\\模板4.xlsx";
private static final String fileName4="D:\\github\\exceldemo\\simpleFill4.xlsx";
/**
* 横向的填充
*
* @since 2.1.1
*/
@Test
public void horizontalFill() {
// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
// {} 代表普通变量 {.} 代表是list的变量
ExcelWriter excelWriter = EasyExcel.write(fileName4).withTemplate(templateFileName4).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
excelWriter.fill(data(), fillConfig, writeSheet);
excelWriter.fill(data(), fillConfig, writeSheet);
Map<String, Object> map = new HashMap<String, Object>();
map.put("date", new SimpleDateFormat("yyyy年MM月dd日HH:mm:ss").format(new Date()));
excelWriter.fill(map, writeSheet);
// 别忘记关闭流
excelWriter.finish();
}
3.5 多列表组合填充填充
private static final String templateFileName5 = "D:\\github\\exceldemo\\模板5.xlsx";
private static final String fileName5="D:\\github\\exceldemo\\simpleFill5.xlsx";
/**
* 多列表组合填充填充
*
* @since 2.2.0-beta1
*/
@Test
public void compositeFill() {
// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
// {} 代表普通变量 {.} 代表是list的变量 {前缀.} 前缀可以区分不同的list
ExcelWriter excelWriter = EasyExcel.write(fileName5).withTemplate(templateFileName5).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
// 如果有多个list 模板上必须有{前缀.} 这里的前缀就是 data1，然后多个list必须用 FillWrapper包裹
excelWriter.fill(new FillWrapper("data1", data()), fillConfig, writeSheet);
excelWriter.fill(new FillWrapper("data2", data()), writeSheet);
excelWriter.fill(new FillWrapper("data3", data()), writeSheet);
Map<String, Object> map = new HashMap<String, Object>();
map.put("date", new SimpleDateFormat("yyyy年MM月dd日HH:mm:ss").format(new Date()));
excelWriter.fill(map, writeSheet);
// 别忘记关闭流
excelWriter.finish();
}
FillConfig类
@Getter
@Setter
@EqualsAndHashCode
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class FillConfig {
private WriteDirectionEnum direction;
/**
* 每次使用list参数时创建一个新行。如果需要，默认创建。
* <p>
* Warnning:If you use <code>forceNewRow</code> set true, will not be able to use asynchronous write file, simply
* say the whole file will be stored in memory.
*/
private Boolean forceNewRow;
/**
* Automatically inherit style
* default true.
*/
private Boolean autoStyle;
private boolean hasInit;
public void init() {
if (hasInit) {
return;
}
if (direction == null) {
direction = WriteDirectionEnum.VERTICAL;
}
if (forceNewRow == null) {
forceNewRow = Boolean.FALSE;
}
if (autoStyle == null) {
autoStyle = Boolean.TRUE;
}
hasInit = true;
}
}