1, Get to know EasyExcel

1.1,Apache POI

Apache POI is an open source function library of the Apache Software Foundation, which provides cross platform Java API s to read and write files in Microsoft Office format. However, there are some problems as follows: high learning cost, large memory consumption of POI, redundant and complicated code writing, large memory consumption of reading and writing large files, and easy to OOM. Of course, powerful.

1.2,EasyExcel

1. It is encapsulated at the data model level and easy to use
2. Rewriting the 07 version of Excel parsing code to reduce memory consumption and effectively avoid OOM
3. Only Excel can be operated
4. Cannot read picture

2, Quick start

Introducing coordinate dependency

<!-- EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.10</version>
</dependency>
<!-- lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.20</version>
</dependency>
<!-- junit -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

2.1. Simple reading

take excel Read out the data in, including name, gender and date of birth, excel The data are as follows

2.1.1. Write entities for exporting data

be based on Lombok
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private String name;
    private Date birthday;
    private String gender;
    private String id;
}

2.1.2. Read Excel file

Among them, you need to customize the listener, inherit the listener provided by the official, and implement two methods

public class ExcelEasyRead {
    /**
     * Workbook: an excel file is a workbook
     * Worksheet: there can be multiple sheets in a workbook
     */
    public static void main(String[] args) {
        /**
         * Build a workbook
         * pathName The path to the file to read
         * head Each line of data in the file is stored in the class of the entity type
         * readListener Read listening. Every time a line is read, the object's invoke will be called. The read data can be used in the invoke operation
         * sheet Method parameter: the sequence number of the worksheet (starting from 0) or the name of the worksheet. It is 0 by default
         */
        // Get a workbook object
        ExcelReaderBuilder readerBuilder = EasyExcel.read("/Users/hudu/Documents/Study/EasyExcel/read.xlsx", Student.class, new StudentListener());

        // Get a worksheet object
        ExcelReaderSheetBuilder sheet = readerBuilder.sheet();

        // Read the contents of the worksheet
        sheet.doRead();
    }
}

public class StudentListener extends AnalysisEventListener<Student> {
    /**
     * Every time a line of content is read, the invoke of the object will be called. The read data can be used in the invoke operation
     * @param data The object encapsulated by each read data
     * @param context
     */
    @Override
    public void invoke(Student data, AnalysisContext context) {
        System.out.println("student = " + data);
    }

    /**
     * This method will be called after reading all
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }
}

The operation effect is as follows

2.2. Simple writing

Import the information of multiple students into Excel table

2.2.1. Write data directly

public class ExcelEasyWrite {
    public static void main(String[] args) {
        /**
         * pathName File path to write
         * head Encapsulates the type of entity written
         * return Written workbook object
         */
        // Workbook object
        ExcelWriterBuilder writerBuilder = EasyExcel.write("/Users/hudu/Documents/Study/EasyExcel/write.xlsx", Student.class);
        // Worksheet object
        ExcelWriterSheetBuilder sheet = writerBuilder.sheet();
        // Prepare data
        List<Student> students = initData();
        // write
        sheet.doWrite(students);
    }

    private static List<Student> initData() {
        ArrayList<Student> students = new ArrayList<>();
        for (int i = 10; i < 20; i++) {
            Student student = new Student();
            student.setName("test"+i);
            student.setBirthday(new Date());
            student.setGender("male");
            students.add(student);
        }
        return students;
    }
}

The effect is as follows

Custom column name, column width and other attributes

@Data
@NoArgsConstructor
@AllArgsConstructor
// Globally define column width
@ColumnWidth(10)
// Content line height
//@ContentRowHeight(10)
// Header row height
@HeadRowHeight(20)
public class Student {
    /**
     * value Field name
     * index Column order
     */
    @ExcelProperty(value = {"Student information sheet","full name"},index = 0)
    private String name;
    @ExcelProperty(value = {"Student information sheet","date of birth"},index = 2)
    @DateTimeFormat("YYYY-MM-dd")
    @ColumnWidth(20)
    private Date birthday;
    @ExcelProperty(value = {"Student information sheet","Gender"},index = 1)
    private String gender;
    /**
     * Ignore field
     */
    @ExcelIgnore
    private String id;

The effect is as follows

3, Common API s and annotations

1. Common class

• EasyExcel entry class is used to build various objects and start various operations;

• ExcelReaderBuilder builds a ReadWorkbook object, that is, a workbook object, corresponding to an Excel file;

• ExcelWriterBuilder builds a WriteWorkbook object, that is, a workbook object, corresponding to an Excel file;

• ExcelReaderSheetBuilder builds a ReadSheet object, that is, a worksheet object. For each sheet in the corresponding Excel, a workbook can have multiple worksheets;

• ExcelWriterSheetBuilder builds a WriteSheet object, that is, the object of a worksheet. For each sheet in the corresponding Excel, a workbook can have multiple worksheets;

• After reading each line, ReadListener will call ReadListener to process data. We can write the code calling service in its invoke method;

• WriteHandler will call WriteHandler to process data in every operation, including creating cells and tables, which is transparent and invisible to users;

• All configurations are inherited, and the workBook configuration will be inherited by the sheet. Therefore, when setting parameters with EasyExcel, the scope is all sheets of the entire workBook before the EasyExcel... sheet() method, and then for a single sheet.

2. Comments on reading

@ExcelProperty

Usage position: the standard acts on member variables to associate attributes in entity classes with columns in excel tables

Optional properties:

Usage effect: the index attribute can specify which column in excel corresponds to the current field. It can be matched according to the column name value or not written.

If @ ExcelProperty annotation is not used, the order of member variables from top to bottom corresponds to the order from left to right in the table;

Usage suggestions: either do not write all, or use index or value to match. Try not to mix the three.

@ExcelIgnore

Marked on the member variable, all fields will match excel by default. If this annotation is added, this field will be ignored

@DateTimeFormat

It is marked on the member variable, date conversion, and this annotation will be called when the member variable of String type is used in the code to receive the date format data in excel. The value inside refers to java.text.SimpleDateFormat

@NumberFormat

It is marked on the member variable for number conversion. This annotation will be called when the member variable of String type is used in the code to receive data in excel number format. The value inside refers to java.text.DecimalFormat

@ExcelIgnoreUnannotated

Label on class.
When this annotation is not marked, all member variables in the default class will participate in reading and writing, regardless of whether the annotation @ ExcelProperty is added to the member variable.

After the annotation is marked, the member variables in the class will not participate in reading and writing if they are not marked with @ ExcelProperty annotation.

3. General parameters when reading

Both readworkbook and readsheet have parameters. If it is empty, the parent is used by default.

• Converter converter. Many converters are loaded by default. It can also be customized.

• The readListener listener will be called continuously during the process of reading data.

• headRowNumber specifies the number of column header rows that need to be read from the table. By default, there is a row header, that is, the second row is considered as data.

• Choose between head and clazz. Read the list corresponding to the file header and match the data according to the list. It is recommended to use class, which is the entity type in the code corresponding to each line of data in the file.

• Choose between clazz and head. Read the class corresponding to the header of the file, or use annotations. If neither is specified, all data is read.

• Auto trim string, header and other data

• Does password need to be used when reading

4. ReadWorkbook (Workbook object) parameter

• Excel type is the type of the current excel. It will be judged automatically when reading. It does not need to be set.

• Either inputStream or file. File is recommended.

• Choose between file and inputStream. Read the file of the file.

• autoCloseStream automatically closes the stream.

• readCache uses less than 5M memory by default. EhCache will be used if it exceeds 5M. This parameter is not recommended.

• Usedefaultlistener @ since 2.1.4 by default, modelbuildeeventlistener will be added to help convert the object into the incoming class. If it is set to false, it will not help convert the object. The custom listener will receive the map < integer, celldata > object. If you want to continue to listen to the class object, please call readListener method to add the custom beforeListener Modelbuildeeventlistener and customized afterListener are enough.

5. ReadSheet (worksheet object) parameter

• sheetNo needs to read the Sheet number. It is recommended to use this to specify which Sheet to read

• sheetName matches Sheet by name. excel 2003 does not support matching Sheet by name

6. Comments on writing

@ExcelProperty

Where to use: the standard works on member variables

Optional properties:

Usage effect: index specifies the column to write to. If it is not specified, it will be sorted according to the position of member variables;

value specifies the column header to be written. If not specified, the name of the member variable is used as the column header;

If you want to set a complex header, you can specify multiple values for value.

Other notes:

Basically the same as when reading

• @ContentRowHeight() is marked on a class or attribute, specifying the content row height

• @HeadRowHeight() is marked on the class or attribute, specifying the column header row height

• @ColumnWidth() is marked on a class or property to specify the column width

• ExcelIgnore ` all fields will be written to excel by default. This annotation will ignore this field

• DateTimeFormat Date conversion. Writing Date to excel will call this annotation. The value inside refers to java.text.SimpleDateFormat

• NumberFormat Number conversion. Writing excel with Number will call this annotation. The value inside refers to java.text.DecimalFormat

• ExcelIgnoreUnannotated by default, those without the annotation of ExcelProperty will participate in reading and writing, and those with the annotation will not participate

7. General parameters on write

WriteWorkbook and WriteSheet all have parameters. If it is blank, the parent will be used by default.

• Converter converter. Many converters are loaded by default. It can also be customized.

• writeHandler writes the processor. Workbookwritehandler, sheetwritehandler, rowwritehandler and cellwritehandler can be implemented. They will be called at different stages of writing to excel and are transparent and invisible to users.

• relativeHeadRowIndex starts after the number of rows away. That is, a few blank lines at the beginning

• Does needHead lead out

• Choose between head and clazz. Write the header list of the file. It is recommended to use class.

• Choose between clazz and head. Write the class corresponding to the header of the file. You can also use annotations.

• Auto trim string, header and other data

8. WriteWorkbook (Workbook object) parameter

• Excel type the type of the current excel. The default is xlsx

• Choose between outputStream and file. Stream to write to file

• Choose between file and outputStream. Files written

• templateInputStream the file stream of the template

• templateFile template file

• autoCloseStream automatically closes the stream.

• Does password need to be used when writing password

• useDefaultStyle whether the default header is used when writing

9. WriteSheet (worksheet object) parameter

• sheetNo the number to be written. Default 0

• sheetName requires some Sheet name, which is the same as sheetNo by default

4, Data filling

4.1. Fill in a set of data

4.1.1 preparation of formwork

In Excel table, {} is used to represent the variables to be filled in the package. If there are {,} left and right braces in the cell text, you need to use slash to escape \ {, \} in front of the brackets.

The member variable name of the entity object filled with data in the code or the key of the filled map set must be consistent with the variable name wrapped by {} in Excel.

4.1.2 package data

Write a class that encapsulates and fills data or select Map

/**
 * @ClassName FillData
 * @Description The entity class encapsulates and fills the data. The name of the member variable in the entity class needs to match the {wrapped variable name} in each Excel table
 * @Author hudu
 * @Date 2021/9/15 2021/9/15
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class FillData {
    private String name;
    private Integer age;
}

4.1.2. Filling data

/**
* Single group data filling
*/
public static void fillData1() {

    // Prepare template
    String template = "fill_data_template1.xlsx";
    // Create a workbook object
    ExcelWriterBuilder excelWriterBuilder = EasyExcel.write("export/fill_data1.xlsx", FillData.class).withTemplate(template);
    // Create a worksheet object
    ExcelWriterSheetBuilder sheet = excelWriterBuilder.sheet();
    // Prepare data
    FillData fillData = initData();
    // Or use map
    HashMap<String, Object> map = new HashMap<>();
    map.put("name","Alex");
    map.put("age",22);
    // Fill data
    sheet.doFill(fillData);
}

public static FillData initData() {
    return new FillData("Alex",22);
}

The effect is as follows

4.2. Fill in multiple groups of data

4.2.1 preparation of formwork

In the Excel table, {.} is used to represent the variables to be filled in the package. If there are {,} left and right braces in the cell text, you need to use slash to escape \ {, \} in front of the brackets.

The member variable name of the entity object filled with data in the code or the key of the filled map set must be consistent with the variable name wrapped by {} in Excel.

4.2.2 start filling

/**
* Multi group data filling
*/
public static void fillData2() {
    List<FillData> fillData = initDataList();
    String template = "/Users/hudu/Documents/Study/EasyExcel/template/fill_data_template2.xlsx";
    ExcelWriterBuilder excelWriterBuilder = EasyExcel.write("/Users/hudu/Documents/Study/EasyExcel/export/fill_data2.xlsx", FillData.class).withTemplate(template);
    ExcelWriterSheetBuilder sheet = excelWriterBuilder.sheet();
    sheet.doFill(fillData);
}

public static List<FillData> initDataList() {
    ArrayList<FillData> arrayList = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        FillData fillData = new FillData("test" + i, i + 20);
        arrayList.add(fillData);
    }
    return arrayList;
}

The effect is as follows

4.3 combined filling

4.3.1 preparation of formwork

There are both multiple groups of data filling and single data filling. In order to avoid conflicting coverage of the two data, line wrapping needs to be set through FillConfig object when filling multiple groups.

4.3.2 data filling

public static List<FillData> initDataList() {
    ArrayList<FillData> arrayList = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        FillData fillData = new FillData("test" + i, i + 20);
        arrayList.add(fillData);
    }
    return arrayList;
}

/**
* Combined filling
*/
public static void fillData3() {
    // Prepare template
    String template = "fill_data_template3.xlsx";
    // Target file
    String target = "fill_data3.xlsx";
    // Prepare data
    List<FillData> fillData = initDataList();
    // Generate workbook object
    ExcelWriter excelWriter = EasyExcel.write(target).withTemplate(template).build();
    // Generate worksheet objects
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    // When combining filling, because the amount of data filled in multiple groups is uncertain, it is necessary to start another row after multiple groups are filled
    FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
    // Fill and wrap
    excelWriter.fill(fillData,fillConfig,writeSheet);

    HashMap<String, String> map = new HashMap<>();
    map.put("date","2021");
    map.put("total","10");
    excelWriter.fill(map,writeSheet);

    // Manual shutdown is required
    excelWriter.finish();
}

The effect is as follows

4.4 horizontal filling

4.4.1 preparation of formwork

Horizontal filling is the same as multiple groups of filling templates. The difference is that horizontal filling needs to be set through FillConfig object.

4.4.2. Filling data

/**
* Horizontal fill
*/
public static void fillData4() {
    // Prepare template
    String template = "/Users/hudu/Documents/Study/EasyExcel/template/fill_data_template4.xlsx";
    // Target file
    String target = "/Users/hudu/Documents/Study/EasyExcel/export/fill_data4.xlsx";
    // Prepare data
    List<FillData> fillData = initDataList();
    // Generate workbook object
    ExcelWriter excelWriter = EasyExcel.write(target).withTemplate(template).build();
    // Generate worksheet objects
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    // When combining filling, because the amount of data filled in multiple groups is uncertain, it is necessary to start another row after multiple groups are filled
    FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
    // Fill and wrap
    excelWriter.fill(fillData,fillConfig,writeSheet);

    // Manual shutdown is required
    excelWriter.finish();
}

The effect is as follows

4.5 precautions

In order to save memory, the whole document is not organized in memory and then written to the file as a whole. Instead, it is written line by line, which can not delete and move lines, and note writing is not supported. When writing multiple groups of data, if you need to add a new row, you can only add it in the last row, not in the middle.