Shulou(Shulou.com)06/01 Report--

This article mainly explains "how SpringBoot integrates EasyExcel applications". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's ideas to study and learn "how SpringBoot integrates EasyExcel applications".

1. Introduction

Features:

1. Java domain parsing and generating Excel are well-known frameworks such as Apache poi, jxl and so on. But they all have a serious problem is that they consume a lot of memory. If your system concurrency is not large, it may be OK, but once the concurrency comes up, there will be frequent full gc of OOM or JVM.

2. EasyExcel is an open source excel processing framework of Alibaba, which is famous for its easy to use and memory saving. The main reason why EasyExcel can greatly reduce the memory footprint is that instead of loading all the file data into memory at once when parsing Excel, it reads the data from one row on disk and parses it one by one.

3. EasyExcel adopts the parsing mode of one line, and notifies the processing (AnalysisEventListener) of the parsing result of one line in the mode of observer.

2. Application scenarios

1. Data import: reduce the workload of input

2. Data export: statistical information archiving

3. Data transmission: data transmission between heterogeneous systems

3. The effect to be achieved

Sql

CREATE TABLE `char (19) NOT NULL COMMENT "course category ID", `title` varchar (10) NOT NULL COMMENT "category name", `parent_ id` char (19) NOT NULL DEFAULT "0" COMMENT "parent ID", `sort` int unsigned NOT NULL DEFAULT "0" COMMENT "sort field, `gmt_ create`datetime NOT NULL COMMENT" creation time ", `gmt_ modified` datetime NOT NULL COMMENT" update time ", PRIMARY KEY (`id`) KEY `parent_ id` (`parent_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT COMMENT= "course subjects"

Convert to->

3. Use 3.1and pom dependency import

Warm reminder: the following version can not be replaced, it may not work if you change it.

Com.alibaba easyexcel 2.1.1 org.apache.poi poi 3.17 org.apache.poi poi-ooxml 3.17 3.2 、 controllerpackage com.zhz.serviceedu.controller;import com.zhz.common.utils.R Import com.zhz.serviceedu.service.EduSubjectService;import io.swagger.annotations.Api;import io.swagger.annotations.ApiOperation;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.CrossOrigin;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import org.springframework.web.multipart.MultipartFile;/** *

* course subject front-end controller *

* * @ author zhz * @ since 2021-07-03 * / @ RestController@RequestMapping ("/ eduservice/subject") @ CrossOrigin@Api (tags = "course subjects") public class EduSubjectController {@ Autowired private EduSubjectService eduSubjectService / * add course categories, get uploaded files, read out file contents * / @ PostMapping ("/ addSubject") @ ApiOperation (value = "add course categories, get uploaded files, read file contents") public R addSubject (MultipartFile file) {/ / uploaded excel file eduSubjectService.saveSubject (file,eduSubjectService) Return R.ok ();}} 3.3, interfacepackage com.zhz.serviceedu.service;import com.zhz.serviceedu.entity.EduSubject;import com.baomidou.mybatisplus.extension.service.IService;import org.springframework.web.multipart.MultipartFile;/** *

* courses, subjects, services *

* * @ author zhz * @ since 2021-07-03 * / public interface EduSubjectService extends IService {/ * add course information * * @ author zhz * @ date 02:18 on 2021-07-02 * @ param file file object, used to obtain excel file * @ param eduSubjectService convenient listener partial reference * / void saveSubject (MultipartFile file, EduSubjectService eduSubjectService) 3. 4, implpackage com.zhz.serviceedu.service.impl;import com.alibaba.excel.EasyExcel;import com.zhz.serviceedu.entity.EduSubject;import com.zhz.serviceedu.entity.excel.SubjectData;import com.zhz.serviceedu.listener.SubjectExcelListener;import com.zhz.serviceedu.mapper.EduSubjectMapper;import com.zhz.serviceedu.service.EduSubjectService;import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;import org.springframework.stereotype.Service;import org.springframework.web.multipart.MultipartFile;import java.io.InputStream;/** *

* course subject service implementation class *

* * @ author zhz * @ since 2021-07-03 * / @ Servicepublic class EduSubjectServiceImpl extends ServiceImpl implements EduSubjectService {/ * * add course information * * @ param file file object Used to get the excel file * @ param eduSubjectService * @ author zhz * @ date 02:18 on 2021-07-02 * / @ Override public void saveSubject (MultipartFile file, EduSubjectService eduSubjectService) {try {/ / File input stream InputStream in = file.getInputStream () / / call the method to read EasyExcel.read (in, SubjectData.class,new SubjectExcelListener (eduSubjectService)). Sheet (). DoRead ();} catch (Exception e) {e.printStackTrace ();} 3.5, listenerpackage com.zhz.serviceedu.listener;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper Import com.zhz.servicebase.execptionhandler.GuliException;import com.zhz.serviceedu.entity.EduSubject;import com.zhz.serviceedu.entity.excel.SubjectData;import com.zhz.serviceedu.service.EduSubjectService;import lombok.extern.slf4j.Slf4j;import org.springframework.util.StringUtils / * * @ author zhouhengzhe * @ Description: excel listener * @ date at 2:28 on 2021-7-3 * / @ Slf4jpublic class SubjectExcelListener extends AnalysisEventListener {/ * create a parameter construction and pass subjectService to operate the database * since SubjectExcelListener cannot be managed by spring, you need to new yourself and cannot inject objects * it must be public here Otherwise, you will never get the object * / public EduSubjectService eduSubjectService Public SubjectExcelListener () {} public SubjectExcelListener (EduSubjectService eduSubjectService) {this.eduSubjectService = eduSubjectService;} / * reads the excel content, line by line, and here it is all business processing * @ param subjectData * @ param analysisContext * / @ Override public void invoke (SubjectData subjectData, AnalysisContext analysisContext) {log.info ("enter method call") If (StringUtils.isEmpty (subjectData)) {throw new GuliException (20001, "File data is empty");} / / one line to read excel content, each reading has two values, the first value is a first-level category, the second value is a second-level category / / to determine whether the first-level classification is repeated EduSubject existOneSubject = this.existOneSubject (eduSubjectService, subjectData.getOneSubjectName ()) If (StringUtils.isEmpty (existOneSubject)) {existOneSubject=new EduSubject (); existOneSubject.setParentId ("0"); / / first-level classification name existOneSubject.setTitle (subjectData.getOneSubjectName ()); eduSubjectService.save (existOneSubject);} / / get the pid value of the first-level classification String pid=existOneSubject.getId () / / add a secondary classification / / determine whether the secondary classification repeats EduSubject existTwoSubject= this.existTwoSubject (eduSubjectService, subjectData.getTwoSubjectName (), pid); if (StringUtils.isEmpty (existTwoSubject)) {existTwoSubject=new EduSubject (); existTwoSubject.setParentId (pid); / / secondary classification name existTwoSubject.setTitle (subjectData.getTwoSubjectName ()) EduSubjectService.save (existTwoSubject) }} / * execute * / @ Override public void doAfterAllAnalysed (AnalysisContext analysisContext) {} / * to determine whether the first-level classification is repeated * because when the parent_id of the course is 0, it represents the first-level classification. And the first-level classification data does not repeat * @ param eduSubjectService * @ param name * @ return * / private EduSubject existOneSubject (EduSubjectService eduSubjectService,String name) {QueryWrapper wrapper=new QueryWrapper () Wrapper.eq ("title", name); wrapper.eq ("parent_id", "0"); EduSubject subject = eduSubjectService.getOne (wrapper); return subject } / * determine whether the secondary classification repeats * * @ param eduSubjectService * @ param name * @ return * / private EduSubject existTwoSubject (EduSubjectService eduSubjectService,String name,String pid) {QueryWrapper wrapper=new QueryWrapper (); wrapper.eq ("title", name); wrapper.eq ("parent_id", pid); EduSubject eduSubject = eduSubjectService.getOne (wrapper) Return eduSubject;}} 3.6.Little details, entity class pojo

Because the primary key generation strategy of the entity class generated by mybatisplus is IdType.ID_WORKER, it needs to be modified to IdType.ID_WORKER_STR, otherwise there will be conversion problems.

Package com.zhz.serviceedu.entity;import com.baomidou.mybatisplus.annotation.FieldFill;import com.baomidou.mybatisplus.annotation.IdType;import java.util.Date;import com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableId;import java.io.Serializable;import io.swagger.annotations.ApiModel;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import lombok.EqualsAndHashCode;import lombok.experimental.Accessors;/** *

* course subjects *

* * @ author zhz * @ since 2021-07-03 * / @ Data@EqualsAndHashCode (callSuper = false) @ Accessors (chain = true) @ ApiModel (value= "EduSubject object", description= "course subject") public class EduSubject implements Serializable {private static final long serialVersionUID = 1L; @ ApiModelProperty (value= "course ID") @ TableId (value= "id", type = IdType.ID_WORKER_STR) private String id; @ ApiModelProperty (value= "Category name") private String title @ ApiModelProperty (value = "parent ID") private String parentId; @ ApiModelProperty (value = "sort field") private Integer sort; @ ApiModelProperty (value = "creation time") @ TableField (fill = FieldFill.INSERT) private Date gmtCreate; @ ApiModelProperty (value = "update time") @ TableField (fill = FieldFill.INSERT_UPDATE) private Date gmtModified Thank you for your reading. The above is the content of "how SpringBoot integrates EasyExcel applications". After the study of this article, I believe you have a deeper understanding of how SpringBoot integrates EasyExcel applications, and the specific usage needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.