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”

preface

The last article briefly introduced the use of Poi, but using Poi to write code is a bit tedious, if you want to achieve complex requirements, such as: image export, multi-table data export, template export, large amount of data export, etc., using the most native Poi is not a good choice.

Should we wrap our own utility classes?

As good as the wheel is, it better be written by someone else. So from this article to introduce two excellent Poi tools Easypoi and Ali open source EasyExcel.

EasyPoi

EasyPoi is a library of tools that encapsulates POI and encapsulates some common Excel operations

  • Basic import and export
  • Image import and export
  • Import and export of multi-table data
  • Import and export of mass data
  • Exporting templates

Next we will work together to achieve the above functions

IO /#category_5… easyPoi.mydoc. IO /#category_5…

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>cn.afterturn</groupId>

            <artifactId>easypoi-base</artifactId>

            <version>3.2.0</version>

        </dependency>

        <dependency>

            <groupId>cn.afterturn</groupId>

            <artifactId>easypoi-web</artifactId>

            <version>3.2.0</version>

        </dependency>

        <dependency>

            <groupId>cn.afterturn</groupId>

            <artifactId>easypoi-annotation</artifactId>

            <version>3.2.0</version>

        </dependency>

Copy the code

2. Annotate the data entity class to be exported

The entity class of the data we want to export is Teacher, so we need to add Easypoi annotations to Teacher. Easypoi is annotate development, all the details of annotations are in the official document, but if the requirement to complete is the simplest import and export, @excel a annotation is enough.

@excel this is a mandatory annotation, if the need is simple, only use this one annotation is also ok, covers the common Excel requirements, you need to be familiar with this function, mainly divided into basic, image processing, time processing, merge processing several blocks

@Excel official API easypoi.mydoc. IO /#text_18690…

Well annotated entity classes are as follows:

3. Import and export directly using the utility classes in EasyPoi

EasyPoi is annotated development, and all Excel definitions, from styles to date formatting, are defined in the annotations of the entity class

Export code
/ * *

* easypoi export

* /

    @Test

    public void test4(a) throws IOException {

// Simulate data

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

        list.add(new Teacher(1."Miss Li"."hhh.jpg".1));

        list.add(new Teacher(2."Miss Li"."hhh.jpg".1));

        list.add(new Teacher(3."Miss Li"."hhh.jpg".1));

        list.add(new Teacher(4."Miss Li"."hhh.jpg".1));

        list.add(new Teacher(5."Miss Li"."hhh.jpg".1));

        list.add(new Teacher(6."Miss Li"."hhh.jpg".1));

        / * *

* Export the parameter object

* Parameter 1 title

* Parameter 2 Specifies the name of the table

* /

        ExportParams exportParams = new ExportParams("All teacher data"."teacher");

        / * *

* exportExcel Exports Excel files

* Parameter 1 Exports the parameter object

Parameter 2 Specifies the class object of the entity class to export

* Parameter 3 The data to be exported requires a collection of teacher objects queried from the collection database

         *

* The return value is the encapsulated file object

* /

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Teacher.class, list);



        workbook.write(new FileOutputStream("/Users/lubingyang/Desktop/teachers.xls"));



    }

Copy the code

The exported Excel is as follows:

Import the code

There is no configuration that needs to be defined separately for imports. As long as you can export, you can write the import code directly

 / * *

* easypoi import

* /

    @Test

    public void test5() throws Exception {

        FileInputStream inputStream = new FileInputStream("/Users/lubingyang/Desktop/teachers.xls");

        / * *

* ImportParams imports parameter objects

* Define title bar and header data

* /

        ImportParams importParams = new ImportParams();

        importParams.setTitleRows(1);

        importParams.setHeadRows(1);

        / * *

* importExcel import method

* Parameter 1 stream reads the file to import

Parameter 2: class object of the entity class to be imported Class object of the teacher object

* Parameter 3 Imports the parameter object

         *

* Return value import data is directly encapsulated as a collection object

* /

        List<Teacher> teachers = ExcelImportUtil.importExcel(inputStream, Teacher.class, importParams);



        for (Teacher teacher : teachers) {

            System.out.println(teacher);

        }

    }

Copy the code

Value to replace picture import and export

The value of the replacement

It is easy to find the following through the official documentation

Modify the entity class according to the document

Executing the export code gives the following effect

Image export

Modifying the Entity Class

After modification, you can export it directly

According to tests, if the image address field stores a relative path, it is best to treat it as a network absolute path or a local absolute path

Image import

The annotation of the entity class needs to set the path to save the image after import

api

Collection data import and export

Student = Teacher; Student = Teacher; Student = Teacher; Student = Teacher;

Entity class annotated

… class easyPoi.mydoc. IO /#text_19784…


The export code is as follows

@Test

    public void test4(a) throws IOException {

        List<Student> students = new ArrayList<>();

        students.add(new Student("hh"."Male"));

        students.add(new Student("hh"."Male"));

// Simulate data

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

        list.add(new Teacher(1."Miss Li"."/Users/lubingyang/Desktop/hhh.jpg".1,students));

        list.add(new Teacher(6."Miss Li"."/Users/lubingyang/Desktop/hhh.jpg".1,students));





        / * *

* Export the parameter object

* Parameter 1 title

* Parameter 2 Specifies the name of the table

* /

        ExportParams exportParams = new ExportParams("All teacher data"."teacher");

        / * *

* exportExcel Exports Excel files

* Parameter 1 Exports the parameter object

Parameter 2 Specifies the class object of the entity class to export

* Parameter 3 The data to be exported requires a collection of teacher objects queried from the collection database

         *

* The return value is the encapsulated file object

* /

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Teacher.class, list);



        workbook.write(new FileOutputStream("/Users/lubingyang/Desktop/teachers.xls"));



    }

Copy the code

Results the following

Bulk data export (millions of data)

About millions of data export recommended using Ali open source EasyExcel official introduction can control the memory in KB

Big data export means that when we export tens of thousands or millions of data, we query so much data from the database and load it into the memory at a time, and then write it into the memory, which will cause pressure on our memory and CPU. At this time, we need to handle the export like paging, and write into Excel in sections to relieve the pressure

EasyPoi provides two methods to force the use of the XSSF version of Excel

/ * *

     * @param entity

* Table title properties

     * @param pojoClass

* Excel object Class

     * @param dataSet

* Excel object data List

* /

    public static Workbook exportBigExcel(ExportParams entity, Class<? > pojoClass,

Collection<? > dataSet) {

        ExcelBatchExportServer batachServer = ExcelBatchExportServer

            .getExcelBatchExportServer(entity, pojoClass);

        return batachServer.appendData(dataSet);

    }



    public static void closeExportBigExcel(a) {

        ExcelBatchExportServer batachServer = ExcelBatchExportServer.getExcelBatchExportServer(null.

            null);

        batachServer.closeExportBigExcel();

    }

Copy the code

Train of thought

  1. Paging data reading
  2. Write each read data to Excel

The implementation code

Prepare a user table with millions of data

 @Test

    public void test10() throws IOException {

        Date start = new Date();

// Query the number of user tables in the database

        Integer userCount = userDao.selectCount(null);

// Count the total pages

        Integer pageCount = userCount / 200000 + 1;



        List<CmfzUser> users = null;

        Workbook workbook = null;

        ExportParams params = new ExportParams("Big Data Testing"."Test");



// 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, 200000), null).getRecords();

// Through the EasyPoi big data export method export

            workbook = ExcelExportUtil.exportBigExcel(params, CmfzUser.class, users);

            users.clear();

        }

        Date end = new Date();

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

        workbook.write(new FileOutputStream("/Users/lubingyang/Desktop/hhhh.xlsx"));



    }

Copy the code

The total execution time is:

Tips:

  1. Easypoi.mydoc. IO /#text_20298… easyPoi.mydoc. IO /#text_20298…
  2. Database queries using MybatisPlus if you are interested in my related article SpringBoot integration with MybatisPlus
  3. CSV and SXSSF are not as good as XSSF for millions of data. (POI provides a class for exporting large data.)

The template export

Template is a simple way to deal with complex Excel. Complex Excel styles can be directly edited with Excel, perfectly avoiding the minefield of code writing styles. At the same time, the support of instructions also improves the effectiveness of templates. EasyPoi support instructions and functions

A whitespace-delimited

Ternary operation {{test ? obj:obj2}}

N: indicates that the cell is a numeric type {{n:}}

Le: represents the length {{le ()}} inif/elseUse {{le () > 8? obj1 : obj2}}

Fd: format time {{fd: obj; yyyy-MM-dd)}}

Fn: format number {{fn:(obj;# # #. 00)}}

Fe: Iterates data to create a row

! Fe: Traverses data without creating a row

$feSize () line, and then insert

#fe: Horizontal traversal

V_fe: horizontal traversal value

!if: Deletes the current column {{!if: (test)}}

Single quotes indicate constant values' 'Such as'1'So the output is 1

& NULL & space

[] newline character multi-line traversal export

Sum: indicates statistics

Copy the code

{{}} represents an expression and values it according to the data in the expression

The following code

The template file address used for the sample code:

    @Test

    public void fe_map() throws Exception {

// Read the template file

        TemplateExportParams params = new TemplateExportParams(

                "/Users/ K /Desktop/ Special expenditure application _map.xls");



// Simulate the data to be written to the template

        Map<String.Object> map = new HashMap<String.Object> ();

        map.put("date"."2014-12-25");

        map.put("money".2000000.00);

        map.put("upperMoney"."Two million");

        map.put("company"."Pen Stealth Technology, Inc.");

        map.put("bureau"."Finance Bureau");

        map.put("person"."JueYue");

        map.put("phone"."1879740 * * * *");

        List<Map<String.String>> listMap = new ArrayList<Map<String.String> > ();

        for (int i = 0; i < 4; i++) {

            Map<String.String> lm = new HashMap<String.String> ();

            lm.put("id", i + 1 + "");

            lm.put("zijin", i * 10000 + "");

            lm.put("bianma"."A001");

            lm.put("mingcheng"."Design");

            lm.put("xiangmumingcheng"."EasyPoi " + i + "Period");

            lm.put("quancheng"."Open Source project");

            lm.put("sqje", i * 10000 + "");

            lm.put("hdje", i * 10000 + "");



            listMap.add(lm);

        }

        map.put("maplist", listMap);



// Export the template

        Workbook workbook = ExcelExportUtil.exportExcel(params, map);



        FileOutputStream fos = new FileOutputStream("/Users/ K /Desktop/ Special expenditure application 111_map.xls");

        workbook.write(fos);

        fos.close();

    }

Copy the code

The results are as follows

conclusion

Tips: Almost all Excel related work can be done with EasyPoi

You can follow the related articles POI and EasyExcel as well as the POI Chinese API document “40 postures for operating Excel Files”.

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 use of Easypoi 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.