“This is the 20th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

In project development, there are a lot of business requirements to import and export the relevant data of pages, and for pages with a large amount of data, it is necessary to consider that the stability of service operation can not be affected during the export. Therefore, when selecting the data import and export framework, it is necessary to consider the business requirements.

EasyExcel framework will have a good effect on low memory footprint and large amount of data export scenarios.

1. EaseExcel

1.1 introduce EaseExcel

EasyExcel is a Java based simple, save memory read and write Excel open source project, EasyExcel is in the Apache POI framework on the basis of its depth optimization, reduce the Excel file import and export of the system memory occupation, Use as little memory as possible to read and write Excel files with hundreds of megabytes in size.

Apache POI will first load files into memory for operation, so for tens of hundreds of M files may occur OOM memory overflow leading to service unavailable, and EasyExcel optimization can achieve 20s read 75M files only take up 64M memory, effectively avoid OOM.

1.2 EaseExcel learning

EasyExcel is an excellent open source framework of Alibaba, which is maintained by excellent developers. You can learn to use EasyExcel official documents, you can also download the project source code to understand the implementation principle.

  • The official documentation
  • Making the address

1.3 use EaseExcel

The project only needs to import EaseExcel dependencies, and it can code the import and export of Excel files using the class methods defined in the Jar package.

<! EaseExcel dependency information -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>
Copy the code

2. Export implementation of EasyExcel

Export function is more widely used than import, so first to learn the implementation of EasyExcel export Excel.

2.1 Establish model classes

In The Java language, all data is based on object existence, and data export is also separated from non-data objects, so the first step is to build the same model class as the exported data structure.

Define a simple Java class to host user Data and use Lombok’s @data for quick object creation.

// Create a data model to store user-exported data
@Data
public class UserModel {
    private String id;
    private String code;
    private String name;
}
Copy the code

2.2 Implementation of local Export

The local simple implementation of Excel file export, only need to call the EasyExcel class related methods to define the attributes of the export file, and use doWrite() to set the export data to complete Excel export.

@Test
public void exportTemplate(a){
    List<UserModel> list = new ArrayList<>();
    UserModel userModel = new UserModel();
    userModel.setCode("123");
    userModel.setName("tom");
    list.add(userModel);

    EasyExcel.write("User data.xlsx", UserModel.class)
            .sheet("Sheet1")
            .doWrite(list);
}
Copy the code

Class methods in the exported code can be briefly described as:

  • EasyExcelAs a utility class, inherit EasyExcelFactory, set the properties of Excel in builder mode, and finally complete the creation and export
  • write()Method defines the file export path information pathName, and which class to use to write data to
  • sheet()The Excel export Sheet () method specifies a name for the sheet
  • doWrite()Used to specify data in Excel
  • The file flow is automatically closed after the file is exported

3. Implementation of EasyExcel import

Excel import is the operation of reading the content of selected files. The process of reading files with EasyExcel is as follows:

  1. Create an entity object to hold the data read from the file. The object structure must be the same as the data in the file
  2. Define a callback listener that performs the callback as the data is read line by line
  3. Read files through EasyExcel

3.1 Creating Entity Objects

The creation method is the same as that used to export data. Ensure that the attributes of the object are the same as those of the imported data columns.

// No additional columns are required for import. Imports and exports can also use the same entity class
@Data
public class UserModel {
    private String code;
    private String name;
}
Copy the code

3.2 Defining listeners

EasyExcel requires you to define a listener implementing the ReadListener class, where T represents the current entity class to be imported, and implement its invoke() and doAfterAllAnalysed() methods.

  • invoke()Method is used to call back after each row of data is read
  • doAfterAllAnalysed()Method is used to call back after the entire file has been read
// Listeners are created for each imported object class
public class UserModelListener implements ReadListener<UserModel> {
    private static final Logger log  = LoggerFactory.getLogger(UserModelListener.class);
    @Override
    public void invoke(UserModel userModel, AnalysisContext analysisContext) {
        log.info("Parse to a single data :{}", userModel.toString());
        // Specify other operations
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("All data parsed!");
        // Specify other operations}}Copy the code

3.3 Implementation of local import

Once you have defined the entity class and the corresponding callback listener, you can use EasyExcel to read and import Excel files.

  • read()Method is used to specify information about the path to read the file, the entity class to read, the callback listener class, etc
  • sheet()The sheet () method is used to specify the name of the sheet in the read file
  • doRead()The method is to perform a read operation, with a callback for each row read
// Implement import based on the specified path file, entity class, entity class listener
@Test
public void importExcel(a){
    EasyExcel.read("User data.xlsx", UserModel.class, new UserModelListener())
            .sheet()
            .doRead();
}
Copy the code