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

  • The concept ten times is not as good as the code once, my friend, I hope you can type all the code cases in the article once

  • Like it before you look at it, make it a habit

SpringBoot illustrated tutorial series article directory

  1. SpringBoot图文教程1 “concept + case mind mapping” “basics”
  2. Use “logback” and “log4j” to log.
  3. SpringBoot graphic tutorial 3 – “‘ first love ‘complex” integrated Jsp
  4. 4 – SpringBoot implementation file upload and download
  5. Use Aop in SpringBoot
  6. SpringBoot Tutorial 6 – The use of filters in SpringBoot
  7. SpringBoot tutorial 7 – The use of SpringBoot interceptor posture is available here
  8. SpringBoot integration with MBG code Generator
  9. SpringBoot import/export Excel “Apache Poi”
  10. SpringBoot graphic tutorials 10 – template export Excel export | | millions data image export “easypoi”
  11. SpringBoot integration with MybatisPlus
  12. Basic use of SpringData Jpa
  13. SpringBoot+IDEA implementation code hot deployment

EasyExcel

EasyExcel is alibaba open source a Java Excel operation technology, and EasyPoi is the same tool class encapsulation Poi. But the difference is that in EasyExcel, Poi technology has solved the memory consumption problem of reading large quantities of data. Of course, it also encapsulates many common Excel operations

  • Basic import and export
  • Image import and export
  • Import and export of millions of data

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

Basic import and export

In the most basic import/export, the entity class of the data to be exported is as follows:

public class Teacher {

    / * *

* The teacher's primary key

* /

    private Integer teacherId;

    / * *

* name

* /

    private String teacherName;

    / * *

* Address of profile picture

* /

    private String teacherImage;

    / * *

* The teacher's status 0 means normal and 1 means deleted

* /

    private Integer teacherStatus;

}

Copy the code

Omit the get the set

1. Import dependencies

<dependency>

            <groupId>com.alibaba</groupId>

            <artifactId>easyexcel</artifactId>

            <version>At 2.0.5</version>

        </dependency>

Copy the code

2. Annotate the data entity class to be exported

EasyExcel is also annotated development, commonly annotated as follows

  • ExcelProperty specifies the column in Excel for which the current field corresponds
  • ExcelIgnore: All fields match excel by default
  • DateTimeFormat Date conversion, called when a String is used to receive data in Excel date format. In the value of reference to Java. Text. SimpleDateFormat
  • NumberFormat number conversion, which is called when String is used to receive excel NumberFormat data. See java.text.decimalFormat for the value inside

3. Import and export directly

Export code

   / * *

* Basic export

* /

    @Test

    public void test1(){

// Prepare 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";

        // You need to specify which class to write to, then write to the first sheet, name it template and the stream will automatically close

        // If you want to use 03, pass in the excelType argument

        EasyExcel.write(fileName, Teacher.class).sheet("Template").doWrite(teachers);





    }

Copy the code

Import the code

The data reading of EasyExcel will be slightly troublesome. The data read directly through the tool class cannot be processed directly, and an intermediate class listener class needs to be used. The general process is shown in the following figure

The code for the listener is shown below, 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 an important point that DemoDataListener cannot be managed by Spring. To read Excel, you need to use new method, and then use Spring constructor to pass it in

public class DemoDataListener extends AnalysisEventListener<Teacher> {

    / * *

* Store the database every 5 entries, 3000 entries in actual use, and then clean the list to facilitate memory reclamation

* /

    private static final int BATCH_COUNT = 5;



    / * *

* This collection is used to receive data from reading Excel files

* /

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



    / * *

* Assuming this is a DAO, there is of course business logic that this could also be a service. Of course this object doesn't work if you don't have to store it.

* /

    private TeacherDao teacherDao;



    public DemoDataListener(a) {



    }



    / * *

     *

* Do not use autowiring

* Pass the DAO as a parameter in the test class

* /

    public DemoDataListener(TeacherDao teacherDao) {

        this.teacherDao = teacherDao;

    }



    / * *

* This is called for every data parse

     *

* /

    @Override

    public void invoke(Teacher teacher, AnalysisContext context) {



        list.add(teacher);

        // Reach BATCH_COUNT, need to go to store a database, prevent data tens of thousands of data in memory, easy OOM

        if (list.size() >= BATCH_COUNT) {

            saveData();

            // Store the cleanup list

            list.clear();

        }

    }



    / * *

* This is called when all data parsing is complete

     *

* /

    @Override

    public void doAfterAllAnalysed(AnalysisContext context) {

        // Save the data here, too, to ensure that the last remaining data is also stored in the database

        saveData();



    }



    / * *

* Plus storage database

* /

    private void saveData(a) {

// This is where the DAO can be called and we can print the data directly

        System.out.println(list);

    }

}

Copy the code

Note:

  1. The listener class cannot be managed by Spring and comes out with a separate new each time

The imported code is as follows:

 / * *

* Add the DAO used by the database

* /

    @Autowired

    private TeacherDao teacherDao;

    / * *

* The easiest way to read

* /

    @Test

    public void simpleRead(a) {

        String fileName =  "/Users/lubingyang/Desktop/hhhh.xlsx";



        // We need to specify which class is used to read the sheet, and then the first read stream is automatically closed

        / * *

* Parameter 1 the file to read

* Parameter 2 specifies the entity class object corresponding to the data to be read

* Argument 3 Listener objects can be created with the DAO as an argument

* /

        EasyExcel.read(fileName, Teacher.class, new DemoDataListener(teacherDao)).sheet().doRead();





    }

Copy the code

The result is as follows:

Image export

EasyExcel supports import and export of various images, what does it mean? Under normal circumstances, the picture field in the database table stores the picture path, reading the picture file export is supported, in addition, also supports the network path, stream, byte array and so on. My case is based on reading local image files.

The official documentation 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 changes required]

Millions of data import and export

The biggest feature of EasyExcel is its ability to avoid memory overflow, so how do you do it?

First of all, why do POIS run the risk of running out of memory

When the Poi reads an Excel file, it first reads all the data into the memory and then processes it. This time if read from file data quantity is large, Java occurs. Lang. OutOfMemoryError: Java heap space error.

So what does EasyPoi do?

  1. Instead of reading the entire file into memory at once, EasyPoi uses the stream and processes it as it goes along
  2. When processing, EasyPoi adopts the method of processing one data as it reads one data to ensure that too much data will not be stored in memory. You can set a limit by yourself, for example, to store database every 500 data.


Next, let’s test the import and export of millions of data in code

Performance tests are available on the website, if you need them, at https://alibaba-easyexcel.github.io

Million data export

Train of thought

  1. Paging data reading
  2. Write each read data to Excel
  3. Discard non-essential data like styles, fonts, etc

Prepare a user table with millions of data

The user entity class is as follows:

@Data

public class CmfzUser implements Serializable {

   @ExcelIgnore

    private Integer userId;

    @ExcelProperty("Mobile phone Number")

    private String userTelphone;

    private String userPassword;

    @ExcelProperty("Avatar address")

    private String userImage;

    @ExcelProperty("Nickname")

    private String userNickname;

    @ExcelProperty("Name")

    private String userName;

    @ExcelProperty("Gender")

    private String userSex;

    @ExcelProperty("Personal 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;



}

Copy the code

Code:

    @Test

    public void test10() throws IOException {

        Date start = new Date(a);

// 1 million data paging 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." ";



        // We need to specify which class to write to

        ExcelWriter excelWriter = EasyExcel.write(fileName, CmfzUser.class).build();



// Create the same sheet only once

        WriteSheet writeSheet = EasyExcel.writerSheet("Big Data").build();



// Query the number of test pages. 20W data is queried each time

        for (int i = 1; i <= pageCount; i++) {

            System.out.println(i);

            users = userDao.selectPage(new Page<>(i, size), null).getRecords();

// Write out the data

            excelWriter.write(users, writeSheet);



            users.clear();

        }





        Date end = new Date(a);

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



        // Don't forget that Finish helps close the stream

        excelWriter.finish();



    }

Copy the code

The total execution time is:

Mega data read

The Excel file with millions of data cannot be read directly through Poi. I have tested it and an exception occurred


So if we’re going to use Poi we’re going to have to do a little bit of processing and then we’re going to look at that with EasyExcel

Reading files using EasyExcel is divided into two steps:

  1. Create listeners
  2. read

Listener code

public class UserDataListener extends AnalysisEventListener<CmfzUser> {



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



    / * *

* Assuming this is a DAO, there is of course business logic that this could also be a service. Of course this object doesn't work if you don't have to store it.

* /

    private CmfzUserDao userDao;



    public UserDataListener(a) {



    }



    / * *

     *

* Do not use autowiring

* Pass the DAO as a parameter in the test class

* /

    public UserDataListener(CmfzUserDao userDao) {

        this.userDao = userDao;

    }



    / * *

* This is called for every data parse

     *

* /

    @Override

    public void invoke(CmfzUser user, AnalysisContext context) {



        list.add(user);

        // Can set more than 500 pieces of data up, need to go to store a database, prevent data tens of thousands of pieces of data in memory, easy OOM

        if (list.size() >= 3000) {

            saveData();

            // Store the cleanup list

            list.clear();

        }

    }



    / * *

* This is called when all data parsing is complete

     *

* /

    @Override

    public void doAfterAllAnalysed(AnalysisContext context) {

        // Save the data here, too, to ensure that the last remaining data is also stored in the database

        saveData();



    }



    / * *

* Plus storage database

* /

    private void saveData(a) {

// This is where the DAO can be called and we can print the data directly

teacherDao.addList(list);

        System.out.println("Store data:"+list.size()+"Article");

    }

}

Copy the code

Read the code

 @Test

    public void test4(a){

        String fileName =  "/ Users/lubingyang/Desktop/big data. XLSX." ";



        // We need to specify which class is used to read the sheet, and then the first read stream is automatically closed

        / * *

* Parameter 1 the file to read

* Parameter 2 specifies the entity class object corresponding to the data to be read

* Argument 3 Listener objects can be created with the DAO as an argument

* /

        EasyExcel.read(fileName, CmfzUser.class, new UserDataListener(userDao)).sheet().doRead();





    }

Copy the code

The code will work perfectly

conclusion

Congratulations on completing this chapter. A round of applause! If this article is helpful to you, please help to like, comment, retweet, this is very important to 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.

Ask for attention, ask for likes, ask for retweets

Welcome to pay attention to my public account: Teacher Lu’s Java notes, will update Java technology graphic tutorials and video tutorials in the long term, Java learning experience, Java interview experience and Java actual combat development experience.