Take a quick look at EasyExcel

1, EasyExcel

1. Introduction

EasyExcel is a simple and memory saving open source project of reading and writing Excel based on Java.
In the case of saving memory as much as possible, it supports reading and writing 100M Excel (instead of reading data into memory at one time, reading one row from the disk, thus saving memory).
EasyExcel adopts a line by line parsing mode, and notifies the analysis EventListener of the parsing results in the observer mode.
Simple understanding: it is convenient and fast to read and write execl content.

[Official document address:]
    https://alibaba-easyexcel.github.io/index.html
    https://www.yuque.com/easyexcel

 

2. Spring boot quickly integrates EasyExcel (realizing write operation)

(1) Create a spring boot project.

 

 

(2) Add dependency information.

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>

 

 

(3) Add entity class information corresponding to excel.

package com.lyh.test.testeasyexcel.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class User {
    @ExcelProperty("full name")
    private String userName;
    @ExcelProperty("mailbox")
    private String email;
    @ExcelProperty("Age")
    private Integer age;
}

 

 

(4) Write test method and file

package com.lyh.test.testeasyexcel;

import com.alibaba.excel.EasyExcel;
import com.lyh.test.testeasyexcel.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.List;

@SpringBootTest
class TestEasyexcelApplicationTests {

    @Test
    void testWrite() {
        // Where to save files
        String fileName = "G:/test/test.xlsx";
        EasyExcel.write(fileName, User.class).sheet("User table").doWrite(list());
    }

    // Simple build data for testing
    public List<User> list() {
        List<User> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setUserName("tom" + i);
            user.setAge((int)(Math.random() * 20));
            user.setEmail(user.getUserName() + "@163.com");
            list.add(user);
        }
        return list;
    }
}

 

 

(5) Execution results

 

 

3. Spring boot quickly integrates EasyExcel (to realize reading operation)

Basically similar to write operations, but you need to define a listener for listening operations.
(1) Create project, introduce dependency, write entity class (same as write operation, omitted here)

(2) Write a monitor to monitor the reading operation of excel.
This listener is not managed by Spring, so the relevant injection (such as @ Autowired) will not take effect.
If you want to use service, dao and other related classes, you can introduce them through construction methods.

package com.lyh.test.testeasyexcel.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.lyh.test.testeasyexcel.entity.User;

import java.util.Map;

/**
 * Define a data listener that inherits the AnalysisEventListener abstract class.
 * The listener is not managed by Spring, so if you need to use service and dao related classes, you need to introduce it through the construction method.
 */

public class DataListener extends AnalysisEventListener<User> {
    // Read table contents in one row
    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        System.out.println("===============" + user);
    }

    // Read header content
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("======Header content========" + headMap);
    }

    // Operation after reading
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("========Read complete========");
    }
}

 

 

(3) Write test methods and read files

package com.lyh.test.testeasyexcel;

import com.alibaba.excel.EasyExcel;
import com.lyh.test.testeasyexcel.entity.User;
import com.lyh.test.testeasyexcel.excel.DataListener;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class TestEasyexcelApplicationTests {

    @Test
    void testRead() {
        // Where to read files
        String fileName = "G:/test/test.xlsx";
        EasyExcel.read(fileName, User.class, new DataListener()).sheet().doRead();
    }
}

 

 

Here is just a simple understanding. Please refer to the official documents for more details.

[Official document address:]
    https://alibaba-easyexcel.github.io/index.html
    https://www.yuque.com/easyexcel

Tags: Java Excel Spring github

Posted on Tue, 19 May 2020 07:15:29 -0400 by nigel_belanger