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 millions of data

Basic import and export

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);
    }
}

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.

Entity transformation

The exported code is [no change required]

Million data import and export

EasyExcel's biggest feature is to avoid memory overflow, so how to do it?

First of all, let's talk about why Poi has the risk of memory overflow

When reading Excel files, Poi will first read all the data in the memory and then process it. At this time, if you read a large amount of file data, a java.lang.OutOfMemoryError: Java heap space error will occur.

So what does EasyPoi do?

1. EasyPoi does not read the entire file into memory at one time, but uses over stream to read and process at the same time
2. In the process of processing, EasyPoi uses the method of reading one piece of data and processing one piece of data to ensure that it does not store too much data in memory. It can set a limit by itself, such as setting a database for every 500 pieces of storage.

Next, we test the import and export of millions of data by code

Million data export

Prepare a user table with millions of data

The user entity classes are as follows:

@Data
public class CmfzUser implements Serializable {
   @ExcelIgnore
    private Integer userId;
    @ExcelProperty("Cell-phone number")
    private String userTelphone;
    private String userPassword;
    @ExcelProperty("Head address")
    private String userImage;
    @ExcelProperty("Nickname?")
    private String userNickname;
    @ExcelProperty("Name")
    private String userName;
    @ExcelProperty("Gender")
    private String userSex;
    @ExcelProperty("Personalized signature")
    private String userAutograph;
    @ExcelProperty("Province")
    private String userProvince;
    @ExcelProperty("City")
    private String userCity;
    @ExcelIgnore
    private Integer guruId;
    @ExcelIgnore
    private Integer userStatus;
    @ExcelIgnore
    private Date userCreateDate;

}

Code:

    @Test
    public void test10() throws IOException {
        Date start = new Date();
//        One million pages of data query database
        Integer userCount = 1000000;
        Integer size = 20000;
        Integer pageCount = userCount / size + 1;


        List<CmfzUser> users = null;
        String fileName =  "/Users/lubingyang/Desktop/Big data.xlsx";

        // You need to specify which class to write in
        ExcelWriter excelWriter = EasyExcel.write(fileName, CmfzUser.class).build();

//        Note here that if the same sheet is created only once
        WriteSheet writeSheet = EasyExcel.writerSheet("Big data").build();

//        Query test pages, query 20w pieces of data each time
        for (int i = 1; i <= pageCount; i++) {
            System.out.println(i);
            users = userDao.selectPage(new Page<>(i, size), null).getRecords();
//            Data writing
            excelWriter.write(users, writeSheet);

            users.clear();
        }


        Date end = new Date();
        System.out.println(new Date().getTime() - start.getTime()+"ms");

        // Don't forget that finish will help close the flow
        excelWriter.finish();

    }

The total execution time is:

Million data read

Using EasyExcel to read a file is a two-step process:

1. Create listener
2. read

Listener code

public class UserDataListener extends AnalysisEventListener<CmfzUser> {

    List<CmfzUser> list = new ArrayList<>();

    /**
     * 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 CmfzUserDao userDao;

    public UserDataListener() {

    }

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

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

        list.add(user);
        // You can set more than 500 pieces of data. You need to store the database once to prevent tens of thousands of pieces of data in memory. It's easy to OOM
        if (list.size() >= 3000) {
            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
teacherDao.addList(list);
        System.out.println("Store data:"+list.size()+"strip");
    }
}

Read code

 @Test
    public void test4(){
        String fileName =  "/Users/lubingyang/Desktop/Big data.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, CmfzUser.class, new UserDataListener(userDao)).sheet().doRead();


    }

Code can run perfectly

summary

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

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

For attention, for approval, for forwarding