1, Let's first learn about EasyExcel:
1. Both POI and easyexcel frameworks can generate excel, but POI framework consumes a lot of memory, so it is recommended to use easyexcel to generate excel. Easyexcel is an excel processing framework open source by Alibaba. The main reason why easyexcel can reduce memory consumption is that it does not load all the file data into memory at one time when parsing excel, but reads the data line by line from the disk and parses it one by one.
2. Scenario for EasyExcel import: reduce input workload
2, vue front end code implementation
Call Vue element UI official website( https://element.eleme.io/#/zh-CN/component/upload):
Take batch import notification information as an example, in notice In Vue:
<template>
<div>
<el-upload
class="upload-demo"
drag
action=""
:http-request="upload"
:before-upload="beforeUpload"
multiple>
<i class="el-icon-upload"></i>
<div class="el-upload__text">Drag the file here, or<em>Click upload</em></div>
<div class="el-upload__tip" slot="tip">Upload only xls/xlsx Documents, and no more than 100 M</div>
</el-upload>
<br/>
</div>
<template>
Two methods of file upload and import are called:
<script>
export default {
methods: {
//Import
beforeUpload: function(file) {
let extension = file.name.substring(file.name.lastIndexOf('.')+1);
let size = file.size / 1024 / 1024;
if(extension !== 'xlsx') {
this.$message.warning('Only upload suffix is.xlsx File');
}
if(size > 10) {
this.$message.warning('The file size must not exceed 10 M');
}
},
upload: function(param) {
let formData = new FormData()
formData.append('file', param.file)
this.$api.notice.importExcel(formData).then(res=>{
this.$message({message:'File uploaded successfully,Please refresh the page! ',type: 'ok'})
},err =>{});
}
}
}
</script>
Notice under the moudules folder in the http directory JS, url is the interface path of batch import of Excel in the back-end controller layer
//File import
export const importExcel = (data) => {
return axios({
url: '/notice/noticeImport',
method: 'post',
headers:{
'Content-type': 'multipart/from-data'
},
data
})
}
Let's take a look at the front-end effect:
3, spring boot backend code implementation:
1. Import dependency:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
2. yml is configured. Since file upload uploads files with a maximum of 1M by default, it is allowed to request files with a maximum of 200M. Therefore, in order to avoid that large files cannot be uploaded, it needs to be configured in yml.
spring
servlet:
multipart:
max-file-size: 100MB #The maximum allowable length of file upload is 100M, and the default length is 1M
max-request-size: 200MB #The maximum allowable size of the request is 200M, and the default is 10M
3. File upload interface of controller layer:
/**
* @Description Excel File notification information import
* @author ztt
* @date 2021-07-21 15:18
* @param file File stream
*/
@PostMapping(value = "/noticeImport", produces = "multipart/from-data; charset=utf-8")
@ResponseBody
public ResultJson noticeImport(MultipartFile file) {
log.info("Input file:"+file);
if(!noticeService.noticeImport(file,noticeService,sysUserNewService,redisUtil)){
return ResultJson.error("File import failed!");
}
log.info("Out parameter-File import succeeded!");
return ResultJson.ok("File import succeeded!");
}
4. Notice class in entity layer: here, the upload only involves two titles, title and content, which are displayed with @ ExcelProperty annotation, and the titles not involved, such as id and account, are hidden with @ ExcelIgnore.
@Builder
@Data
@AllArgsConstructor//Add all parameter constructor
@NoArgsConstructor//Add a parameterless constructor
public class Notice extends BaseRowModel {
@ExcelIgnore
private Long id;//Notification id
@ExcelProperty(value = "title",index = 0)
private String title;//Notice title
@ExcelProperty(value = "content",index = 1)
private String content;//Notice content
@ExcelIgnore
public String account;//Author account
@ExcelProperty(value = "author",index = 2)
private String nickName;//Author name
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "Release time",index = 3,converter = LocalDateTimeConverter.class)
public LocalDateTime publishTime;//Release time
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelIgnore
public LocalDateTime createTime;//Creation time
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ExcelIgnore
public LocalDateTime updateTime;//Modification time
}
5. The serviceImple layer of the file upload interface calls the listener that reads and writes the notification information to the database.
/**
* @Description Excel File notification information import
* @author ztt
* @date 2021-07-21 15:18
* @param file File stream
*/
@Override
public Boolean noticeImport(MultipartFile file,NoticeService noticeService, SysUserNewService sysUserNewService,RedisUtil redisUtil) {
Long startTime = System.currentTimeMillis();
log.info("File upload parameters file: "+file);
if(!file.isEmpty()){
try {
InputStream inputStream = new BufferedInputStream(file.getInputStream());
//Instantiate a class that implements the AnalysisEventListener interface
ExcelNoticeListener excelNoticeListener = new ExcelNoticeListener(noticeService,sysUserNewService,redisUtil);
ExcelReader reader = new ExcelReader(inputStream,null,excelNoticeListener);
//Read information
reader.read(new Sheet(1,1,Notice.class));
} catch (IOException e) {
log.info("Error message:{}",e.getMessage());
return false;
}
}
Long endTime = System.currentTimeMillis();
log.info("Elapsed time:{}ms",endTime-startTime);
log.info("Output parameter: the file is uploaded successfully");
return true;
}
6. Implementation listener: the listener reads data one by one and inserts it into the database.
But one thing to note: because the ExcelNoticeListener cannot be managed by spring, it needs its own new, cannot inject other objects, and cannot implement database operations (pass in the service layer, sysUserNewService, and redisUtil of the notice by calling noticeservice, sysUserNewService, and redisUtil at the controller layer, otherwise the Le file upload will fail!)
/**
* @author ztt
* @ClassName: ExcelListener
* @Description: monitor
* @date 2021 16:05, July 21
*/
@Slf4j
public class ExcelNoticeListener extends AnalysisEventListener<Notice> {
@Autowired
private SysUserNewService sysUserNewService;
@Autowired
private NoticeService noticeService;
@Autowired
RedisUtil redisUtil;
private List<Notice> datas = new ArrayList<>();
private static final int BATCH_COUNT = 1;
//Because the ExcelNoticeListener cannot be managed by spring, it needs its own new and cannot inject other objects
//Unable to implement database operation (by passing in the service layer of notice when calling service in the controller layer)
public ExcelNoticeListener(NoticeService noticeService,SysUserNewService sysUserNewService,RedisUtil redisUtil){
this.noticeService = noticeService;
this.sysUserNewService = sysUserNewService;
this.redisUtil = redisUtil;
}
public ExcelNoticeListener(){ }
@Override
public void invoke(Notice notice, AnalysisContext analysisContext) {
//Data is stored in data for batch processing or subsequent business logic processing.
datas.add(notice);
//Reach BATCH_COUNT, you need to store the database once to prevent tens of thousands of data from being in memory, which is easy to OOM
if(datas.size() >= BATCH_COUNT){
data();
// Storage completion cleanup data
datas.clear();
}
}
//A single piece of data is stored in the database
private void data() {
for(Notice notice : datas){
//Insert data into database
String account = String.valueOf(redisUtil.get("sysUserAccount"));
SysUserNewVo sysUserNewVo = sysUserNewService.getSysUserInfoByAccount(account);
String nickName = sysUserNewVo.getNickName();
notice.setAccount(account);
notice.setNickName(nickName);
notice.setPublishTime(LocalDateTime.now());
notice.setCreateTime(LocalDateTime.now());
Boolean x = noticeService.myNoticeAdd(notice);
if(!x){
log.info("Failed to add!");
}
log.info("Successfully added!");
}
}
/**
* When all data parsing is completed, it will be called
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
Let's take a look at the renderings:
Notice notice The content of xlsx is in the following format:
After batch upload of excel data, it will be displayed on the page as follows:
