Spring boot picture and text tutorial 10 - template export | million data Excel export | picture export | easypoi "

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


In the last article, I briefly introduced the use of Poi, but it's really tedious to write code with Poi. If you want to achieve complex requirements, such as: image export, multi table data export, template export, large data export, etc., it's not a good choice to use the most native Poi.

Do you want to encapsulate the tool class yourself?

no no wheel is good, it's better to have it written by someone else. So this article introduces two excellent Poi tools Easypoi and Alibaba's open source EasyExcel.


EasyPoi is a tool library encapsulated with poi, which encapsulates some common Excel operations

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

Let's implement the above functions together

Official website address: http://easypoi.mydoc.io/#category_50222

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


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 annotate Teacher with Easypoi EasyPoi is an annotation based development. Please refer to the official documents for all the annotation details. However, if the requirement to complete is the simplest import and export, a @ Excel annotation is enough.

@Excel This is an annotation that must be used. If the requirement is simple, only this annotation can be used. It covers common Excel requirements. You need to be familiar with this function. It is mainly divided into basic, image processing, time processing, and merge processing

@Excel Official api http://easypoi.mydoc.io/#text_186900

The annotated entity classes are as follows:

3. Directly use the tool class in EasyPoi to import and export

EasyPoi is an annotation development. All definitions, styles and date formats of Excel are defined in the annotation of entity class

Export code
     * easypoi export
    public void test4() throws IOException {
//        Analog 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 parameter object
         * Parameter 1 title
         * Name of parameter 2 table
        ExportParams exportParams = new ExportParams("All teacher data","teacher");
         * exportExcel Export Excel file
         * Parameter 1 export parameter object
         * Parameter 2 class object of entity class to export
         * Parameter 3: the data to be exported needs 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"));


The exported Excel is as follows:

Import code

There is no configuration that needs to be defined separately for import. As long as it can be exported, it can write the imported code directly

     * easypoi Import
    public void test5() throws Exception {
        FileInputStream inputStream = new FileInputStream("/Users/lubingyang/Desktop/teachers.xls");
         * ImportParams Import parameter object
         * Define title block and header data
        ImportParams importParams = new ImportParams();
         * importExcel Import method
         * Parameter 1 stream reads the file to import
         * Parameter 2 class object of entity class to be imported class object of master object
         * Parameter 3 import parameter object
         * The return value import data is directly encapsulated as a collection object
        List<Teacher> teachers = ExcelImportUtil.importExcel(inputStream, Teacher.class, importParams);

        for (Teacher teacher : teachers) {

Value import and export

Replacement of values

It's easy to find the following through official documents

Modify entity class according to document

Execute the export code to get the following results

Picture export

Modify entity class

You can export directly after modification

According to the test, if the image address field stores the relative path, it is better to deal with the absolute network path or the local absolute path

Image import

You need to set the saving path after importing the picture on the annotation of entity class


Collection data import and export

In addition to an entity class Student, there is a Student collection in the Teacher class. When exporting the Teacher, you need to export the Student's data as well. The corresponding database operations are generally linked table queries. How can such data be exported to Excel?

Annotation of entity class

For the import and export of the Teacher class collection attribute, you need to annotate the attribute @ excelconcurrence Official document address: http://easypoi.mydoc.io/#text_197841

The export code is as follows

    public void test4() throws IOException {
        List<Student> students = new ArrayList<>();
        students.add(new Student("hh","male"));
        students.add(new Student("hh","male"));
//        Analog 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 parameter object
         * Parameter 1 title
         * Name of parameter 2 table
        ExportParams exportParams = new ExportParams("All teacher data","teacher");
         * exportExcel Export Excel file
         * Parameter 1 export parameter object
         * Parameter 2 class object of entity class to export
         * Parameter 3: the data to be exported needs 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"));


The effect is as follows

Mass data export (million data)

It is recommended to use the Alibaba open source EasyExcel for exporting millions of data. The official introduction can control the memory in kb

Big data export means that when we export tens of thousands to millions of data, so many data will be loaded into memory by querying from the database at one time, and then writing will put pressure on our memory and CPU. At this time, we need to process the export like paging, and write to Excel in sections to relieve the pressure

EasyPoi provides two methods to enforce 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() {
        ExcelBatchExportServer batachServer = ExcelBatchExportServer.getExcelBatchExportServer(null,


  1. Paging read data
  2. Write the data read each time to Excel

Implementation code

Prepare a user table with millions of data

    public void test10() throws IOException {
        Date start = new Date();
//        Total number of query database user tables
        Integer userCount = userDao.selectCount(null);
//        Calculate total pages
        Integer pageCount = userCount / 200000 + 1;

        List<CmfzUser> users = null;
        Workbook workbook = null;
        ExportParams params = new ExportParams("Big data test", "test");

//        Query test pages, query 20w pieces of data each time
        for (int i = 1; i <= pageCount; i++) {
            users = userDao.selectPage(new Page<>(i, 200000), null).getRecords();
//            Export through EasyPoi's big data export method
            workbook = ExcelExportUtil.exportBigExcel(params, CmfzUser.class, users);
        Date end = new Date();
        System.out.println(new Date().getTime() - start.getTime());
        workbook.write(new FileOutputStream("/Users/lubingyang/Desktop/hhhh.xlsx"));


The total execution time is:


  1. There is no detailed performance test for the time problem. There are official tests: http://easypoi.mydoc.io/#text_202983
  2. If you are interested in using MybatisPlus for database query, please read my related article spring boot integration of MybatisPlus
  3. It is better to use CSV and SXSSF for millions of data than xssf (POI provides a special class for large data export)

Template export

Template is a simple way to deal with complex excel. Complex excel style can be edited directly by Excel, which avoids the minefield of code writing style perfectly. At the same time, the support of instructions also improves the effectiveness of template. Instructions and functions supported by EasyPoi

Space division
 Trinary operation {{test? Obj: Obj 2}}
n: Indicates that the cell is of numerical type {{n:}}}
le: represents the length {{le: ()}} using {{le: () > 8? Obj 1: Obj 2}} in if/else
 fd: format time {{fd: (obj; yyyy MM DD)}}}
fn: format number {{fn: (obj; ××××. 00)}}
fe: traverse data and create row
 ! fe: traverse data without creating row
 $fe: move insert down, move the current row and all the following rows down. size() row, and then insert
 #fe: traversal
 v_fe: traverse value
 ! if: delete the current column {{! if:(test)}}
Single quotation marks for constant value '' like '1', the output is 1
 &Null & Space
 ]]Line break multiline traversal export
 sum: Statistics

The writing method used is {}} to represent the expression, and then take the value according to the data in the expression

The code is as follows

Template file address for example code:

    public void fe_map() throws Exception {
//        Read template file
        TemplateExportParams params = new TemplateExportParams(
                "/Users/k/Desktop/Application for special expenditure_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", "Writing in Stealth Technology Co., Ltd");
        map.put("bureau", "The Bureau of Finance");
        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 + "stage");
            lm.put("quancheng", "Open source project");
            lm.put("sqje", i * 10000 + "");
            lm.put("hdje", i * 10000 + "");

        map.put("maplist", listMap);

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

        FileOutputStream fos = new FileOutputStream("/Users/k/Desktop/Application for special expenditure 111_map.xls");

give the result as follows


Tips: through EasyPoi, you can basically complete all the work related to Excel

You can pay attention to the relevant articles Poi, EasyExcel and Poi Chinese API document "40 postures for operating Excel files"

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

Keywords: Programming Excel Spring Database Java

Added by alivec on Thu, 05 Mar 2020 08:18:26 +0200