Spring boot graphic tutorial 14 - spring boot integration EasyExcel "I"

If there is the concept of flying in the sky, there must be the realization of landing

  • Ten times of concept is not as good as one time of code. Friend, I hope you can type all the code cases in this article

  • Praise before you see, form a habit

Spring boot text tutorial series article directory

  1. Spring boot picture and text tutorial 1 "concept + case mind map" and "basic chapter I"
  2. Spring boot graphic tutorial 2 - use of log "logback" and "log4j"
  3. Spring boot graphic tutorial 3 - "first love complex" integration
  4. Spring boot picture and text tutorial 4 - spring boot implementation file upload and download
  5. Spring boot graphic tutorial 5 - using Aop in spring boot
  6. Spring boot picture and text tutorial 6 - use of filters in spring boot
  7. Spring boot graphic tutorial 7 - the usage posture of spring boot interceptor
  8. SpringBoot graphic tutorial 8 - SpringBoot integrated MBG "code generator"
  9. Spring boot graphic Tutorial 9 - Import and export Excel "Apache Poi" from spring boot
  10. Spring boot picture and text tutorial 10 - template export | million data Excel export | picture export | easypoi "
  11. Spring boot graphic tutorial 11 - never write mapper file "SpringBoot integration MybatisPlus"
  12. SpringBoot graphic tutorial 12 - basic use of SpringData Jpa
  13. Spring boot graph and text tutorial 13 - hot deployment of code implemented by spring boot + idea

EasyExcel

EasyExcel is an open-source Java operation Excel technology of Alibaba. It is the same tool class that encapsulates the Poi as EasyPoi. But the difference is that in EasyExcel, the problem of memory consumption in reading large quantities of data by Poi technology is solved. Of course, it also encapsulates many common Excel operations

  • Basic import and export
  • Import and export of pictures
  • Import and export of mass data
  • Export of template

Official address: https://alibaba-easyexcel.github.io/quickstart/write.html

Basic import and export

For the most basic import and export, the entity classes of the data to be exported are as follows:

public class Teacher {
    /**
     *Teacher's primary key
     */
    private Integer teacherId;
    /**
     * name
     */
    private String teacherName;
    /**
     *Head image address
     */
    private String teacherImage;
    /**
     *Teacher's status 0 indicates normal 1 indicates deletion
     */
    private Integer teacherStatus;
}

Omit get set

1. Import dependency

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.0.5</version>
        </dependency>

2. Annotate the data entity class to be exported

EasyExcel is also an annotated development. The common annotations are as follows

  • ExcelProperty specifies which column in excel the current field corresponds to
  • ExcelIgnore by default, all fields will be matched with excel. If this annotation is added, the field will be ignored
  • For DateTimeFormat date conversion, String will be used to receive data in excel date format and this annotation will be called. The value in it refers to java.text.SimpleDateFormat
  • For NumberFormat number conversion, String will be used to receive data in excel number format, and this annotation will be called. The value in it refers to java.text.DecimalFormat

3. Import and export directly

Export code

   /**
     * Basic export
     */
    @Test
    public void test1(){
//        Preparation data
        List<Teacher> teachers = new ArrayList<>();
        teachers.add(new Teacher(1,"hhh","hhh.jpg",1));
        teachers.add(new Teacher(1,"hhh","hhh.jpg",1));
        teachers.add(new Teacher(1,"hhh","hhh.jpg",1));
        teachers.add(new Teacher(1,"hhh","hhh.jpg",1));

        String fileName =  "/Users/lubingyang/Desktop/hhhh.xlsx";
        // Here, you need to specify which class to write to, and then write to the first sheet, whose name is template, and then the file stream will be automatically closed
        // If you want to use 03 here, just pass in the excelType parameter
        EasyExcel.write(fileName, Teacher.class).sheet("Template").doWrite(teachers);


    }

Import code

The data reading of EasyExcel will be a little bit troublesome. The data read directly through the tool class cannot be processed directly. It needs to use an intermediate class listener class. The general flow is as follows

The code of the listener is as follows, with detailed comments

package com.lu.booteasyexcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.lu.booteasyexcel.dao.TeacherDao;
import com.lu.booteasyexcel.entity.Teacher;

import java.util.ArrayList;
import java.util.List;

// There is a very important point that DemoDataListener cannot be managed by spring. You need new every time you read excel, and then you can use spring to construct methods and pass them in
public class DemoDataListener extends AnalysisEventListener<Teacher> {
    /**
     * Every five databases can be stored. In practice, 3000 databases can be used, and then the list can be cleaned to facilitate memory recycling
     */
    private static final int BATCH_COUNT = 5;

    /**
     * This collection is used to receive data from Excel files
     */
    List<Teacher> list = new ArrayList<Teacher>();

    /**
     * Suppose this is a DAO. Of course, if there is business logic, this can also be a service. Of course, it's no use not storing this object.
     */
    private TeacherDao teacherDao;

    public DemoDataListener() {

    }

    /**
     *
     * Do not use automatic assembly
     * Pass dao as a parameter in the test class
     */
    public DemoDataListener(TeacherDao teacherDao) {
        this.teacherDao = teacherDao;
    }

    /**
     * Every data parsing will call
     *
     */
    @Override
    public void invoke(Teacher teacher, AnalysisContext context) {

        list.add(teacher);
        // To achieve batch count, you need to store the database once to prevent tens of thousands of data in memory, which is easy to OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // Storage complete cleanup list
            list.clear();
        }
    }

    /**
     * When all data analysis is completed, it will be called
     *
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // Data should also be saved here to ensure that the last legacy data is also stored in the database
        saveData();

    }

    /**
     * Plus storage database
     */
    private void saveData() {
//        In this place, you can call dao and we will print the data directly
        System.out.println(list);
    }
}

Be careful:

  1. The listener class can't be managed by Spring. Each time, it uses a separate new

The imported code is as follows:

 /**
     * Add dao for database
     */
    @Autowired
    private TeacherDao teacherDao;
    /**
     * The simplest reading
     */
    @Test
    public void simpleRead() {
        String fileName =  "/Users/lubingyang/Desktop/hhhh.xlsx";

        // Here, you need to specify which class to use for reading, and then the first sheet file stream will be automatically closed when reading
        /**
         * Parameter 1 file to read
         * The entity class object corresponding to the data to be read in parameter 2
         * Parameter 3 listener object can pass dao as a parameter when creating
         */
        EasyExcel.read(fileName, Teacher.class, new DemoDataListener(teacherDao)).sheet().doRead();


    }

The results read are as follows:

Export of pictures

In EasyExcel, you can import and export many kinds of pictures. What do you mean? In general, the image field in the database table stores the image path, which is supported by reading the image file and exporting. In addition, it also supports network path, stream, byte array and other ways. My case focuses on reading local image files.

Official document address: https://alibaba-easyexcel.github.io/quickstart/write.html#%E5%9B%BE%E7%89%87%E5%AF%BC%E5%87%BA

Entity transformation

The exported code is [no change required]

summary

Tips: I've been back to work recently. I'm quite busy. This article is just the basic use of EasyExcel. The next article is about template export and million data export

Congratulations on the completion of this chapter, applaud for you! If this article is helpful to you, please like it, comment and forward it. It's very important for the author. Thank you.

Let's review the learning objectives of this article again

  • Master the basic use of EasyExcel in SpringBoot

To learn more about SpringBoot, stay tuned for this series of tutorials.

For attention, for approval, for forwarding

Welcome to my official account: Mr. Lu's Java notes will be updated in Java technology tutorials and video tutorials, Java learning experience, Java interview experience and Java practical development experience.

Tags: Programming Spring Excel Java SpringBoot

Posted on Wed, 11 Mar 2020 02:35:58 -0400 by altumdesign