EasyExcel easy and flexible to read Excel content

Written in front

Java back-end programmers should meet the needs of reading excel information to DB. They may suddenly think of Apache POI as a technical solution. However, when the data volume of Excel is very large, you may find that POI is to read all the contents of Excel and put them into memory, so memory consumption is very serious. If large data is included at the same time, The amount of Excel read operation can easily cause memory overflow.

However, the emergence of EasyExcel has solved the problems related to poi. Originally, a 3M excel needs about 100M of memory to use POI, and EasyExcel can reduce it to a few meters. At the same time, no big excel will have memory overflow, because it is to read the contents of Excel line by line (old rules, don't care too much about the figure below, just have an impression in mind, and read the following. If you look back at this diagram, it will be very simple.)

In addition, EasyExcel encapsulates the model transformation in the upper layer, which does not need cell and other related operations, making the user more simple and convenient.

Simple reading

Suppose we have the following in excel:

We need to create a new User entity and add member variables to it

@Data
public class User {

    /**
     * Full name
     */
    @ExcelProperty(index = 0)
    private String name;

    /**
     * Age
     */
    @ExcelProperty(index = 1)
    private Integer age;
}

You may be concerned about the @ ExcelProperty annotation, which uses the index property (0 for the first column, and so on). The annotation also supports matching by "column name". For example:

@ExcelProperty("Full name")
private String name;

Follow the instructions in the github documentation:

It is not recommended to use index and name at the same time. Either an object only uses index or an object only uses name to match.

If the read Excel template information column is fixed, it is recommended to use the form of index here, because if the name is used to match, the name is repeated, which will result in only one field reading the data, so index is a more secure way.
If the column index of the Excel template often changes, it is better to choose the name method instead of modifying the annotation index value of the entity.

So you can choose according to your own situation.

Write test cases

Many read methods are overloaded in the EasyExcel class, which are not listed here. Please check them by yourself. At the same time, the sheet method can also specify sheetNo, which is the first sheet information by default.

The new UserExcelListener() in the above code is very eye-catching, which is also the key point for EasyExcel to read Excel content line by line. The user-defined UserExcelListener inherits the AnalysisEventListener.

@Slf4j
public class UserExcelListener extends AnalysisEventListener<User> {

    /**
     * Batch threshold
     */
    private static final int BATCH_COUNT = 2;
    List<User> list = new ArrayList<User>(BATCH_COUNT);

    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        log.info("Parse to a piece of data:{}", JSON.toJSONString(user));
        list.add(user);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        log.info("All data analysis completed!");
    }

    private void saveData(){
        log.info("{}Data, start to store database!", list.size());
        log.info("Storage database succeeded!");
    }
}

Please go back to the schematic diagram of EasyExcel at the beginning of this article. The invoke method reads the data line by line, which corresponds to subscriber 1; the doafterallanallysed method corresponds to subscriber 2. Do you understand?

Print results:

It can be seen from this that although the data is parsed line by line, we can customize the threshold value to complete the batch operation of data, which shows the flexibility of EasyExcel operation.

Custom converter

This is the most basic data reading and writing. Our business data is usually not so simple, and sometimes even needs to be converted into program readable data.

Gender information conversion

For example, we need to convert the gender information in Excel into program information: "1: male; 2: female".

First, add the member variable gender in the User entity:

@ExcelProperty(index = 2)
private Integer gender;

EasyExcel supports us to customize the converter and convert the content of excel into the information needed by our program. Here, we create a new GenderConverter to convert the gender information.

public class GenderConverter implements Converter<Integer> {

    public static final String MALE = "male";
    public static final String FEMALE = "female";

    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String stringValue = cellData.getStringValue();
        if (MALE.equals(stringValue)){
            return 1;
        }else {
            return 2;
        }
    }

    @Override
    public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }
}

The generic type of the Converter interface of the above program refers to the Java data type to be converted, which is consistent with the return value type in the supportJavaTypeKey method.

Open the @ ExcelProperty annotation, which supports custom converter, so we add the gender member variable for the User entity and specify the converter.

/**
 * Gender 1: male; 2: Female
 */
@ExcelProperty(index = 2, converter = GenderConverter.class)
private Integer gender;

See the operation results:

The data has been transformed as we expected. From here, we can see that Converter can define the convenience of being used everywhere at one time.

Date information conversion

Date information is also our common conversion data. For example, the "date of birth" column is added in Excel. If we want to parse it into yyyy MM DD format, we need to format it. EasyExcel uses @ DateTimeFormat annotation to format it.

Add the member variable birth to the User entity, and apply the @ DateTimeFormat annotation to format as required.

/**
 * Date of birth
 */
@ExcelProperty(index = 3)
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private String birth;

See the operation results:

If you specify the birth type as Date here, try it. What are your results?

So far, we are writing program code in the way of testing. As a Java Web developer, especially under the current mainstream Spring Boot architecture, how to implement the Web way to read Excel information?

web read

Simple Web

Simply move the key code of the test case to the Controller. We create a new user Controller and add the upload method to it.

@RestController
@RequestMapping("/users")
@Slf4j
public class UserController {
    @PostMapping("/upload")
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), User.class, new UserExcelListener()).sheet().doRead();
        return "success";
    }
}

In fact, when writing test cases, you may have found that the listener is passed into the EasyExcel.read method as a parameter in the form of new, which does not conform to the rules of Spring IoC. After reading Excel data, we usually write some business logic for the read data, and the business logic is usually written in the service layer. How do we use the listener What about calling our service code?

Don't look down. What are the plans in your mind?

Anonymous inner class mode

Anonymous inner class is the simplest way. We need to create a new Service layer first:
To create a new IUser interface:

public interface IUser {
    public boolean saveData(List<User> users);
}

New IUser interface implementation class UserServiceImpl:

@Service
@Slf4j
public class UserServiceImpl implements IUser {
    @Override
    public boolean saveData(List<User> users) {
        log.info("UserService {}Data, start to store database!", users.size());
        log.info(JSON.toJSONString(users));
        log.info("UserService Storage database succeeded!");
        return true;
    }
}

Next, inject IUser into the Controller:

@Autowired
private IUser iUser;

Modify the upload method to override the listener method with an anonymous inner class.

@PostMapping("/uploadWithAnonyInnerClass")
    public String uploadWithAnonyInnerClass(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), User.class, new AnalysisEventListener<User>(){
            /**
             * Batch threshold
             */
            private static final int BATCH_COUNT = 2;
            List<User> list = new ArrayList<User>();

            @Override
            public void invoke(User user, AnalysisContext analysisContext) {
                log.info("Parse to a piece of data:{}", JSON.toJSONString(user));
                list.add(user);
                if (list.size() >= BATCH_COUNT) {
                    saveData();
                    list.clear();
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                saveData();
                log.info("All data analysis completed!");
            }

            private void saveData(){
                iUser.saveData(list);
            }
        }).sheet().doRead();
        return "success";
    }

View results:

This way of implementation, in fact, is just to rewrite all the content in the listener and show it in the controller. When you look at such a bloated controller, isn't it very hard? Obviously, this is not our best coding implementation.

Constructor parameters

When analyzing the Spring boot unified return source code before, I don't know if you find that the Spring underlying source code mostly passes parameters in the form of a constructor, so we can add a parameter constructor for the listener, and pass IUser in the Controller dependent injection into the listener in the form of a constructor:

@Slf4j
public class UserExcelListener extends AnalysisEventListener<User> {

    private IUser iUser;

    public UserExcelListener(IUser iUser){
        this.iUser = iUser;
    }

    // Omit corresponding code...

    private void saveData(){
        iUser.saveData(list); //Call saveData method in userService
    }

Change Controller method:

@PostMapping("/uploadWithConstructor")
public String uploadWithConstructor(MultipartFile file) throws IOException {
    EasyExcel.read(file.getInputStream(), User.class, new UserExcelListener(iUser)).sheet().doRead();
    return "success";
}

Operation result: the same as above

After this change, the controller code looks very clear, but if there are other services that need to be injected in the subsequent business, should we always add a parameter constructor? Obviously, this approach is also not very flexible.

In fact, when using anonymous inner classes, you may think that we can solve this problem through Java8 lambda.

Lambda parameters

In order to solve the pain point of parameter passing from the constructor, and at the same time, we want the listener to be more general, it is not necessary to create a new listener for each excel business, because the listener reads the Excel data line by line, and only needs to pass our business logic code to the listener, so we need to use the consumer < T > as the parameter to construct the listener.

Create a new tool class ExcelDemoUtils to construct listener:

We can see that the getListener method receives a parameter of consumer < list < T > > so that when the following code is called, our business logic will be executed accordingly:

consumer.accept(linkedList);

Continue to modify the Controller method:

Operation result: the same as above

Here, we just need to customize the business logic in the batchInsert method:

Meet the simplicity of Controller RESTful API
listener is more general and flexible. It plays the role of abstract class. The concrete logic is handed over to the implementation of abstract method.
Business logic has better scalability and clearer logic

summary

So far, the basic usage of how to use EasyExcel to read Excel information has been introduced. There are still many details that haven't been explained. You can consult them by yourself. EasyExcel Github Document to find out more. Flexible use of Java 8's functional interface makes it easier for you to improve the reusability of your code, and at the same time, it looks more concise and standardized.

In addition to reading Excel, there are also writing Excel. If you need to write it to a specific location, it is very convenient to use it with the tool class FileWriter of HuTool. For the use of EasyExcel, if you have any questions, please go to the bottom of the blog to discuss.

For the complete code, please reply "demo" on the public account, click the link to view the contents of "easy excel demo" folder. In addition, the home page of personal blog is temporarily closed for special reasons, and all other directories are normal. More articles can be accessed from https://dayarch.top/archives Entry view

Thank

Thank you very much for EasyExcel's author 🌹 to make reading and writing of Excel more convenient

Soul questioning

In addition to Consumer, if you need the business logic of return value, which functional interface do you need?
How to deal with complex headers?
How to write DB data to Excel and download it?
What have you learned from the design of EasyExcel? Please leave a message below to discuss

Programming VIP