欢迎大家关注我的公众号【老周聊架构】,Java后端主流技术栈的原理、源码分析、架构以及各种互联网高并发、高性能、高可用的解决方案。
一、背景
最近项目中遇到了这样的需求,要实现从前端导入Excel
文件至MySQL
数据库。开始我的第一想法就是用 POI
,毕竟我之前有用过 POI
来实现这样类似的功能。不同的是 POI
是导入前端传来的 JSON
数据,而这次的需求直接传个 Excel
文件,还想着怎么把 Excel
里的数据怎么解析出来呢?这时无意间发现了 alibaba
开源的 EasyExcel
。哈哈哈,来的早不如来得巧。想着之前用 POI
解析的感觉好麻烦,所以就开始整 EasyExcel
了。
对 POI 感兴趣的可以看我这一篇博文:post请求实现excel导出下载功能
阿里是这样对 EasyExcel
定义的:
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。easyexcel重写了poi对07版Excel的解析,能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M,并且再大的excel不会出现内存溢出,03版依赖POI的sax模式。在上层做了模型转换的封装,让使用者更加简单方便。
好了,了解了 EasyExcel
相对于 POI
有这么多好处了,那我们也废话不多说,下面直接开搞了。
二、集成 EasyExcel
1、pom.xml 加上 easyexcel 的依赖
<!--alibaba easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.3</version>
</dependency>
2、Controller 类
package com.riemann.springbootdemo.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.riemann.springbootdemo.listener.UploadEasyExcelListener;
import com.riemann.springbootdemo.model.ReturnT;
import com.riemann.springbootdemo.model.UploadEasyExcelData;
import com.riemann.springbootdemo.service.UploadEasyExcelService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiOperation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.Closeable;
import java.io.IOException;
import java.io.InputStream;
/**
* @author riemann
* @date 2019/12/19 22:10
*/
@RestController
public class UploadEasyExcelController {
private static final Logger LOGGER = LoggerFactory.getLogger(UploadEasyExcelController.class);
@Autowired
private UploadEasyExcelService ueeService;
@PostMapping(value = "/uploadEasyExcel",produces = "application/json;charset=UTF-8")
public ResponseEntity<ReturnT<String>> UploadEasyExcel(@RequestParam(value = "serviceFile") MultipartFile serviceFile) {
if (serviceFile == null) {
return new ResponseEntity<>(new ReturnT<>(ReturnT.BAD_REQUEST, "Params can not be null"), HttpStatus.BAD_REQUEST);
}
ExcelReader excelReader = null;
InputStream in = null;
try {
in = serviceFile.getInputStream();
excelReader = EasyExcel.read(in, UploadEasyExcelData.class,
new UploadEasyExcelListener(ueeService)).build();
ReadSheet readSheet = EasyExcel.readSheet(0).build();
excelReader.read(readSheet);
} catch (IOException ex) {
LOGGER.error("import excel to db fail", ex);
} finally {
close(in);
// 这里一定别忘记关闭,读的时候会创建临时文件,到时磁盘会崩
if (excelReader != null) {
excelReader.finish();
}
}
return new ResponseEntity<>(new ReturnT<>("upload easyexcel success"), HttpStatus.OK);
}
private void close(Closeable closeable) {
if (closeable != null) {
try {
closeable.close();
} catch (IOException e) {
LOGGER.error("Close io stream error", e);
}
}
}
}
(@RequestParam(value = "serviceFile") MultipartFile serviceFile)
这里上传 Excel
文件用的是 MultipartFile
3、实体类 UploadEasyExcelData
package com.riemann.springbootdemo.model;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
/**
* @author riemann
* @date 2019/12/19 23:08
*/
@NoArgsConstructor
@Data
@Getter
@Setter
public class UploadEasyExcelData {
@ExcelProperty(value = "name", index = 0)
private String name;
@ExcelProperty(value = "sex", index = 1)
private String sex;
@ExcelProperty(value = "age", index = 2)
private Integer age;
@ExcelProperty(value = "address", index = 3)
private String address;
@ExcelProperty(value = "phone", index = 4)
private String phone;
}
这里的实体类是是与上传的 Excel
的信息相对应,这里也用了 lombok
,lombok
能通过注解的方式,在编译时自动为属性生成构造器、getter/setter、equals、hashcode、toString
方法。出现的神奇就是在源码中没有getter
和setter
方法,但是在编译生成的字节码文件中有getter
和setter
方法。这样就省去了手动重建这些代码的麻烦,使代码看起来更简洁些。
这是它的 maven
依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
4、监听类 UploadEasyExcelListener
EasyExcel.read(in, UploadEasyExcelData.class,
new UploadEasyExcelListener(ueeService)).build();
注意,EasyExcel
这里的 read
函数,除了传流、实体类还有监听类。
package com.riemann.springbootdemo.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.riemann.springbootdemo.model.UploadEasyExcelData;
import com.riemann.springbootdemo.service.UploadEasyExcelService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.ArrayList;
import java.util.List;
/**
* 上传Excel的监听类
* @author riemann
* @date 2019/12/19 23:26
*/
public class UploadEasyExcelListener extends AnalysisEventListener<UploadEasyExcelData> {
private static final Logger LOGGER = LoggerFactory.getLogger(UploadEasyExcelListener.class);
// 每隔1000条存储数据库,然后清理list,方便内存回收
private static final int BATCH_COUNT = 1000;
// 存储解析excel条数
private int count = 0;
@Autowired
private UploadEasyExcelService ueeService;
List<UploadEasyExcelData> ueeDatas = new ArrayList<>();
public UploadEasyExcelListener(UploadEasyExcelService ueeService) {
super();
this.ueeService = ueeService;
}
@Override
public void invoke(UploadEasyExcelData ueeData, AnalysisContext analysisContext) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("One record was Parsed, uploadEasyExcelData:{}", ueeData);
}
count += 1;
ueeDatas.add(ueeData);
if (ueeDatas.size() >= BATCH_COUNT) {
ueeService.saveEasyExcelMappingData(ueeDatas);
ueeDatas.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
ueeService.saveEasyExcelMappingData(ueeDatas);
LOGGER.info("all excel data parsed, count:{}", count);
}
}
5、接口、实现类、Dao层
比较简单我就直接放了
UploadEasyExcelService.java
package com.riemann.springbootdemo.service;
import com.riemann.springbootdemo.model.UploadEasyExcelData;
import java.util.List;
/**
* @author riemann
* @date 2019/12/19 23:31
*/
public interface UploadEasyExcelService {
/**
* 通过easy excel上传excel数据至mysql
* @param ueeDatas
*/
void saveEasyExcelMappingData(List<UploadEasyExcelData> ueeDatas);
}
UploadEasyExcelServiceImpl.java
package com.riemann.springbootdemo.service.impl;
import com.riemann.springbootdemo.dao.EasyExcelDao;
import com.riemann.springbootdemo.model.UploadEasyExcelData;
import com.riemann.springbootdemo.service.UploadEasyExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author riemann
* @date 2019/12/19 23:40
*/
@Service
public class UploadEasyExcelServiceImpl implements UploadEasyExcelService {
@Autowired
private EasyExcelDao easyExcelDao;
@Override
public void saveEasyExcelMappingData(List<UploadEasyExcelData> ueeDatas) {
easyExcelDao.saveEasyExcelMappingData(ueeDatas);
}
}
EasyExcelDao.java
package com.riemann.springbootdemo.dao;
import com.riemann.springbootdemo.model.UploadEasyExcelData;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @author riemann
* @date 2019/12/19 23:42
*/
public interface EasyExcelDao {
void saveEasyExcelMappingData(@Param("ueeDatas") List<UploadEasyExcelData> ueeDatas);
}
6、EasyExcelMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.riemann.springbootdemo.dao.EasyExcelDao" >
<sql id="BASE_TABLE">
easy_excel_data
</sql>
<sql id="BASE_COLUMN">
name,sex,age,address,phone
</sql>
<insert id="saveEasyExcelMappingData" parameterType="com.riemann.springbootdemo.model.UploadEasyExcelData">
<selectKey keyProperty="pid" resultType="long">
select LAST_INSERT_ID()
</selectKey>
INSERT INTO
<include refid="BASE_TABLE"/>
(
name,sex,age,address,phone
)
VALUES
<foreach collection="ueeDatas" item="ueeDatas" separator=",">
(
#{ueeDatas.name, jdbcType=VARCHAR},
#{ueeDatas.sex, jdbcType=INTEGER},
#{ueeDatas.age, jdbcType=VARCHAR},
#{ueeDatas.address, jdbcType=INTEGER},
#{ueeDatas.phone, jdbcType=VARCHAR}
)
</foreach>
</insert>
</mapper>
这里的数据是我为了演示这个功能测试的,做的需求比这个复杂很多。
7、数据库与 Excel
8、postman 请求测试
选好了 Excel 文件后,直接请求。
postman 这里请求成功了
我们再去 MySQL 数据库看看
数据库也成功导入了,这样我们就成功集成EasyExcel实现了Excel文件导入至MySQL。