Springboot2.0.03 + EasyExcel2.2.3 导入导出
使用 EasyExcel2.2.3 导入
在项目中使用POI导入导出,发现有缺陷,然后就发现了阿里开源 https://www.yuque.com/easyexcel/doc/easyexcel
1.引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
小伙伴没有直接去maven官方库直接找最新的版本
然后直接在test里面测试,我是直接拿官网的demo测试的.
@Data
public class DemoData{
private String name;
private String age;
}
/*
* 监听器
*/
public class DemoDataListener extends AnalysisEventListener <Object> {
private static final Logger LOGGER = LoggerFactory.getLogger( DemoDataListener.class );
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 1000;
List <Object> list = new ArrayList <Object>();
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private RobotService tbjdvBaseService;
private JSONObject jsonObject;
public DemoDataListener() {
// 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
* 我使用的接口,直接存储mysql数据库,然后还用到了除excl以外的属性
*/
public DemoDataListener(RobotService robotService, JSONObject jsonObject1) {
this.tbjdvBaseService = robotService;
this.jsonObject = jsonObject1;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(Object data, AnalysisContext context) {
LOGGER.info( "解析到一条数据:{}", JSON.toJSONString( data ) );
list.add( JSON.toJSONString( data ) );
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info( "所有数据解析完成!" );
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info( "{}条数据,开始存储数据库!", list.size() );
JSONObject json = new JSONObject();
json.put( "list", list );
json.put( "esle", jsonObject );
tbjdvBaseService.dataProcessInsert( json );
LOGGER.info( "存储数据库成功!" );
}
}
service层
public interface testService{
int save(JSONObject json);
}
impl
@Override
public int save(JSONObject json) {
return testMapper.save( json );
}
dao层使用的mybatis
@Mapper
public interface testMapper {
@InsertProvider(type = saveSql.class, method = "save")
int save(JSONObject jsonObject);
@Slf4j
class saveSql{
public String save(JSONObject json) {
JSONArray jsonArray = JSONArray.parseArray( json.getString( "list" ) );
JSONObject esle = JSONObject.parseObject( json.getString( "esle" ) );
StringBuilder sql = new StringBuilder();
sql.append( "INSERT INTO user" );
sql.append( "(create_at,opt_by,status,email,name,age)" );
sql.append( " VALUES " );
for (int i = 0; i < jsonArray.size(); i++) {
JSONObject jsonObject = JSONObject.parseObject( jsonArray.get( i ).toString() );
sql.append( "('" );
sql.append( TimeUtil.getNowDate( LocalDateTime.now() ) ).append( "'," );
sql.append( esle.get( "opt_by") ).append( "," );
sql.append( " 1 " ).append( ",'" );
sql.append( esle.get( "email" ) ).append( "','" );
sql.append( jsonObject.get( "name" ) ).append( "'," );
sql.append( jsonObject.get( "age" ) ).append( ")" );
if (i < jsonArray.size() - 1) {
sql.append( "," );
}
}
System.out.println( sql.toString() );
}
}
}
开始接下来的正题了
@Test
public void simpleRead() {
//里面说明下,我司的后端项目没有对应数据库实体对象,用的都是json来接受,所以我这边创建一个json对象,如果有小伙伴跟我情况类似,欢迎来交流
JSONObject excelData = new JSONObject();
excelData.put( "email", "test@163.com" );
excelData.put( "opt_by", 5 );
//本地创建一个excl文件.
String fileName = "f:" + File.separator + "demo2.xlsx";
// 读取全部sheet
// 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
EasyExcel.read(fileName, DemoData.class, new DemoDataListener(testService, excelData)).doReadAll();
// 读取部分sheet
// fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
ExcelReader excelReader = EasyExcel.read(fileName).build();
// 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
// ReadSheet readSheet1 = EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
// ReadSheet readSheet2 = EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
// 这里注意 一定要把sheet1 sheet2 一起传进去,不然有个问题就是03版的excel 会读取多次,浪费性能
// excelReader.read(readSheet1, readSheet2);
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
}
excel内容如下
2020-06-09 11:14:36.867 INFO 9536 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"14","name":"张三"}
2020-06-09 11:14:36.869 INFO 9536 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"14","name":"李四"}
2020-06-09 11:14:36.870 INFO 9536 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"12","name":"王五"}
2020-06-09 11:14:36.870 INFO 9536 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"32","name":"赵六"}
2020-06-09 11:14:36.871 INFO 9536 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"12","name":"韩七"}
2020-06-09 11:14:36.871 INFO 9536 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"21","name":"张三"}
2020-06-09 11:14:36.871 INFO 9536 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 6条数据,开始存储数据库!
INSERT INTO user(create_at,opt_by,status,email,name,age) VALUES ('2020-06-09 11:14:36',5, 1 ,'test@163.com','张三',14),('2020-06-09 11:14:36',5, 1 ,'test@163.com','李四',14),('2020-06-09 11:14:36',5, 1 ,'test@163.com','王五',12),('2020-06-09 11:14:36',5, 1 ,'test@163.com','赵六',32),('2020-06-09 11:14:36',5, 1 ,'test@163.com','韩七',12),('2020-06-09 11:14:36',5, 1 ,'test@163.com','张三',21)
2020-06-09 11:14:36.922 INFO 9536 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2020-06-09 11:14:37.567 INFO 9536 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2020-06-09 11:14:37.599 INFO 9536 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 存储数据库成功!
2020-06-09 11:14:37.599 INFO 9536 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 所有数据解析完成!
2020-06-09 11:14:37.701 INFO 9536 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2020-06-09 11:14:37.719 INFO 9536 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
Process finished with exit code 0
这是读取一个sheet表
下面我们读取全部的sheet表,如果读取指定的,可以去看官网,上面有案例
然后复制了2个sheet表
@Test
public void simpleRead() {
JSONObject excelData = new JSONObject();
excelData.put( "email", "test@163.com" );
excelData.put( "opt_by", 5 );
String fileName = "f:" + File.separator + "demo3.xlsx";
// 读取全部sheet
// 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
// EasyExcel.read(fileName, DemoData.class, new DemoDataListener(robotService, excelData)).doReadAll();
// 读取部分sheet
// fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
ExcelReader excelReader = EasyExcel.read(fileName).build();
//excelReader.excelExecutor().sheetList()获取sheet集合,然后遍历
excelReader.excelExecutor().sheetList().forEach( i->{
ReadSheet readSheet1 = EasyExcel.readSheet( i.getSheetNo() ).head( DemoData.class ).registerReadListener( new DemoDataListener( robotService, excelData ) ).build();
excelReader.read( readSheet1 );
} );
// 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
// ReadSheet readSheet1 = EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
// ReadSheet readSheet2 = EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
// 这里注意 一定要把sheet1 sheet2 一起传进去,不然有个问题就是03版的excel 会读取多次,浪费性能
// excelReader.read(readSheet1, readSheet2);
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
}
===========0
2020-06-09 11:40:35.834 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"1","name":"sheet1"}
2020-06-09 11:40:35.836 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"1","name":"sheet1"}
2020-06-09 11:40:35.838 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"1","name":"sheet1"}
2020-06-09 11:40:35.839 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"1","name":"sheet1"}
2020-06-09 11:40:35.840 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"1","name":"sheet1"}
2020-06-09 11:40:35.841 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"1","name":"sheet1"}
2020-06-09 11:40:35.841 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"1","name":"sheet1"}
2020-06-09 11:40:35.842 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"1","name":"sheet1"}
2020-06-09 11:40:35.842 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"1","name":"sheet1"}
2020-06-09 11:40:35.842 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"1","name":"sheet1"}
2020-06-09 11:40:35.843 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 10条数据,开始存储数据库!
INSERT INTO user(create_at,opt_by,status,email,name,age) VALUES ('2020-06-09 11:40:35',5, 1 ,'test@163.com','sheet1','1'),('2020-06-09 11:40:35',5, 1 ,'test@163.com','sheet1','1'),('2020-06-09 11:40:35',5, 1 ,'test@163.com','sheet1','1'),('2020-06-09 11:40:35',5, 1 ,'test@163.com','sheet1','1'),('2020-06-09 11:40:35',5, 1 ,'test@163.com','sheet1','1'),('2020-06-09 11:40:35',5, 1 ,'test@163.com','sheet1','1'),('2020-06-09 11:40:35',5, 1 ,'test@163.com','sheet1','1'),('2020-06-09 11:40:35',5, 1 ,'test@163.com','sheet1','1'),('2020-06-09 11:40:35',5, 1 ,'test@163.com','sheet1','1'),('2020-06-09 11:40:35',5, 1 ,'test@163.com','sheet1','1')
2020-06-09 11:40:35.890 INFO 2348 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2020-06-09 11:40:36.473 INFO 2348 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2020-06-09 11:40:36.504 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 存储数据库成功!
2020-06-09 11:40:36.505 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 所有数据解析完成!
===========1
2020-06-09 11:40:36.507 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"2","name":"sheet2"}
2020-06-09 11:40:36.508 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"2","name":"sheet2"}
2020-06-09 11:40:36.508 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"2","name":"sheet2"}
2020-06-09 11:40:36.508 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"2","name":"sheet2"}
2020-06-09 11:40:36.508 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"2","name":"sheet2"}
2020-06-09 11:40:36.509 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"2","name":"sheet2"}
2020-06-09 11:40:36.510 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"2","name":"sheet2"}
2020-06-09 11:40:36.510 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"2","name":"sheet2"}
2020-06-09 11:40:36.511 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"2","name":"sheet2"}
2020-06-09 11:40:36.511 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"2","name":"sheet2"}
2020-06-09 11:40:36.511 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{}
2020-06-09 11:40:36.511 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{}
2020-06-09 11:40:36.511 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{}
2020-06-09 11:40:36.512 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{}
2020-06-09 11:40:36.512 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 14条数据,开始存储数据库!
INSERT INTO user(create_at,opt_by,status,email,name,age) VALUES ('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet2','2'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet2','2'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet2','2'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet2','2'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet2','2'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet2','2'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet2','2'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet2','2'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet2','2'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet2','2'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','null','null'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','null','null'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','null','null'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','null','null')
2020-06-09 11:40:36.521 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 存储数据库成功!
2020-06-09 11:40:36.521 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 所有数据解析完成!
===========2
2020-06-09 11:40:36.525 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"3","name":"sheet3"}
2020-06-09 11:40:36.526 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"3","name":"sheet3"}
2020-06-09 11:40:36.527 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"3","name":"sheet3"}
2020-06-09 11:40:36.527 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"3","name":"sheet3"}
2020-06-09 11:40:36.527 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"3","name":"sheet3"}
2020-06-09 11:40:36.527 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"3","name":"sheet3"}
2020-06-09 11:40:36.528 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"3","name":"sheet3"}
2020-06-09 11:40:36.529 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"3","name":"sheet3"}
2020-06-09 11:40:36.530 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"3","name":"sheet3"}
2020-06-09 11:40:36.530 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 解析到一条数据:{"age":"3","name":"sheet3"}
2020-06-09 11:40:36.530 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 10条数据,开始存储数据库!
INSERT INTO user(create_at,opt_by,status,email,name,age) VALUES ('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet3','3'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet3','3'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet3','3'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet3','3'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet3','3'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet3','3'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet3','3'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet3','3'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet3','3'),('2020-06-09 11:40:36',5, 1 ,'test@163.com','sheet3','3')
2020-06-09 11:40:36.538 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 存储数据库成功!
2020-06-09 11:40:36.538 INFO 2348 --- [ main] c.b.y.t.s.i.r.e.DemoDataListener : 所有数据解析完成!