Read excel file with EasyExcel and transfer it to database

The project requires that the course data in excel file should be transferred to the database, and the data in excel should be classified.

0. Preface

Introduction to the whole process

Now in the controller, the name path is uploaded, and the excel file is uploaded by calling the method saveSubject(). In saveSubject() created in service, this method is implemented in serviceImpl, and read method in EasyExcel is invoked in saveSubject.

, this method must implement the listener SubjectExcelListener, which reads excel data line by line. According to the characteristics of the file, the names of the first category and the second category cannot be repeated. If the incoming name is empty, it will be added, and if it is not empty, it will be rejected. Therefore, set a method to solve this problem. For example, set the front end as the first category and vue as the second category. Each name has an id and a parent_id, the parent of the first category_ id is 0, P of the second category_ id is the id of the first category, through the passed in name and p_id=0 through subjectservice getOne(wrapper); This method determines whether the first classification is empty, and the same method is used to determine the second classification. When adding, call these two methods to judge whether the first classification and the second classification are added.

In addition, because SubjectExcelListener cannot be managed by spring, it needs its own new and cannot inject other objects, so it needs to pass in serviceimpl by itself. Specifically, it has been written in the implementation code


 1. java file generator and code required by the folder

Specifically in this article How to use code generator

The entity class corresponding to the database is also created with the code generator

Note: auto fill mechanism for time use

@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="Subject object", description="Course subjects")
public class Subject implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "Course category ID")
    @TableId(value = "id", type = IdType.ID_WORKER_STR)
    private String id;

    @ApiModelProperty(value = "Category name")
    private String title;

    @ApiModelProperty(value = "father 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 gmtModfied;


2. Create controller

@CrossOrigin//Cross domain problem
public class SubjectController {
    private SubjectService subjectService;

    //Add course classification
    //Get the uploaded file and read out the file content
    public UnResult addSubject(MultipartFile file){
        //Upload excel file
        return UnResult.ok();

 3. Create entity class corresponding to excel table

index=0 is the first column and index=1 is the second column

 * @author ZhangTao
 * @date 2021/4/22 15:31
 * @note:Create entity class corresponding to excel
 * It is the first level classification of the header in excel and the columns in the second level classification

public class SubjectData {
    @ExcelProperty(index = 0)
    private String oneSubjectName;
    @ExcelProperty(index = 1)
    private String twoSubjectName;

4. Write the interface in service and the implementation method in serviceimpl

public interface SubjectService extends IService<Subject> {

    //Add course classification
    void saveSubject(MultipartFile file,SubjectService subjectService);

Because the listener is used to read the file in EasyExcel, the fourth step is to write the listener

public class SubjectServiceImpl extends ServiceImpl<SubjectMapper, Subject> implements SubjectService {

    public void saveSubject(MultipartFile file,SubjectService subjectService) {
        try {
            //File input stream
            InputStream in=file.getInputStream();
            //Call method to read
            //Let's inject the service directly so that it can be used later

            //Because the service cannot be injected into the listener, in this serviceiimpl, the service is injected through the listener, so that the sending method save in the service can be used in the listener/
  , SubjectData.class,new SubjectExcelListener(subjectService)).sheet().doRead();
        }catch(Exception e){

5. Write listener

 * @author ZhangTao
 * @date 2021/4/22 15:42
 * @note:monitor
 * /Because SubjectExcelListener cannot be managed by spring, it needs its own new and cannot inject other objects
 * The operation of database cannot be realized
 *Previously, the service has been injected into the listener,
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {

    public SubjectService subjectService;
    //Create a parameterless constructor so that you can add it later,
    public SubjectExcelListener(SubjectService subjectService) {
        this.subjectService = subjectService;
    public SubjectExcelListener(){


    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
            throw new GuliException(20001,"File data is empty");
        //Read from the second line
        //Read line by line. Two values are read each time. The first value is the primary classification and the second is the secondary classification
        //Judge whether the first level classification is empty
        Subject existOneSubject = this.existOneSubject(subjectService,subjectData.getOneSubjectName());
        //If it is blank, there is no same level-1 classification, then add it
        if(existOneSubject==null){//There is no same primary classification
            existOneSubject=new Subject();
            existOneSubject.setTitle(subjectData.getOneSubjectName());//The first level classification name just passed in
  ;//A lot has been done before so that the save method can be called by subjectService,

        //Get the id value of a section classification
        //pareat of secondary classification - id is the id value of primary classification
        String pid=existOneSubject.getId();
        //Add secondary classification
        //Judge whether the secondary classification is repeated
        Subject existTwoSubject = this.existTwoSubject(subjectService,subjectData.getTwoSubjectName(),pid);
        if(existTwoSubject==null){//There is no same primary classification
            existTwoSubject=new Subject();
            existTwoSubject.setTitle(subjectData.getTwoSubjectName());//First level classification name
    //Judge that the primary classification cannot be added repeatedly
    //Make it possible to judge whether there are values in the table according to the passed name and 0 (representing the first level classification) / / Yes
    //null if there is no value
    private Subject existOneSubject(SubjectService subjectService,String name){
        QueryWrapper<Subject> wrapper=new QueryWrapper<>();
        Subject oneSubject=subjectService.getOne(wrapper);//getOne queries a record according to the Wrapper
        return oneSubject;
    //    //Judge that the secondary classification cannot be added repeatedly

    private Subject existTwoSubject(SubjectService subjectService,String name,String pid){
        QueryWrapper<Subject> wrapper=new QueryWrapper<>();
        Subject twoSubject=subjectService.getOne(wrapper);
        return twoSubject;

    public void doAfterAllAnalysed(AnalysisContext analysisContext) {



swagger is used to test



Added by eggradio on Sun, 20 Feb 2022 02:35:18 +0200