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

Preface

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

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

Basic import and export

1. Import dependency

<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>

2. Annotate the data entity class to be exported

@Excel is a necessary annotation. If the requirement is simple, it is OK to use only one annotation. 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

Export code

/**
     * easypoi export
     */
    @Test
    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

 /**
     * easypoi Import
     */
    @Test
    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();
        importParams.setTitleRows(1);
        importParams.setHeadRows(1);
        /**
         * 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) {
            System.out.println(teacher);
        }
    }

Value import and export

Replacement of values

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

Image import

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

api

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

The export code is as follows

@Test
    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)

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,
            null);
        batachServer.closeExportBigExcel();
    }

Implementation code

 @Test
    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++) {
            System.out.println(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);
            users.clear();
        }
        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:

Tips:

1. There is no detailed performance test for the time problem. There are official tests: http://easypoi.mydoc.io/#text_202983

2. Database query usage MybatisPlus If you are interested, please read my relevant articles SpringBoot Integrate MybatisPlus

3. For millions of data xssf Better use CSV and SXSSF( POI Export for large data volume,A class is provided)

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

    @Test
    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 + "");

            listMap.add(lm);
        }
        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");
        workbook.write(fos);
        fos.close();
    }

give the result as follows

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