Easyexcel Java API usage

1、 First acquaintance with easyexcel

1.1、Apache POI

Apache POIIt is the open source function library of Apache Software Foundation, which provides cross platformJava APIrealizationMicrosoft OfficeFormat file reading and writing. However, there are some problems as follows: the cost of learning and using is high, the memory consumption of POI is large, the code writing is redundant and complicated, the memory consumption of reading and writing large files is large, and it is easy to oom. Of course, powerful.

1.2、EasyExcel

1. Encapsulated at the data model level,Easy to use
2. Rewriting the parsing code of 07 version of Excel to reduce memory consumption and effectively avoid oom
3. Can only operate Excel
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>
</dependency>

2.1. Simple reading

Read out the data in Excel, including name, gender and date of birth. The Excel data is as follows

2.1.1 entity for compiling exported data

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 of the file to read
         *Each row of data in the head file should be stored in the class of the entity type
         *Readlistener reads and listens. Every time a line is read, it will call the invoke of the object. 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("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 you read a line of content, you will call the invoke of the object, where you can operate and use the read data
     *Each time the encapsulated data of the object is read to param @ param
     * @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 of the file to write
         *Head encapsulates the type of entity written
         *Return the workbook object written
         */
        //Workbook object
        ExcelWriterBuilder writerBuilder = EasyExcel.write("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 table", "name"}, index = 0)
    private String name;
    @Excelproperty (value = {"student information table", "date of birth"}, index = 2)
    @DateTimeFormat("YYYY-MM-dd")
    @ColumnWidth(20)
    private Date birthday;
    @Excelproperty (value = {"student information table", "gender"}, index = 1)
    private String gender;
    /**
     *Ignore field
     */
    @ExcelIgnore
    private String id;

The effect is as follows

3、 Common APIs and annotations

1. Common class

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

• ExcelReaderBuilderBuild a readworkbook object, that is, a workbook object, corresponding to an excel file;

• ExcelWriterBuilderBuild a writeworkbook object, that is, a workbook object, corresponding to an excel file;

• ExcelReaderSheetBuilderBuild a readsheet object, that is, the object of a worksheet. For each sheet in the corresponding excel, a workbook can have multiple worksheets;

• ExcelWriterSheetBuilderBuild a writesheet object, that is, the object of a worksheet. For each sheet in the corresponding excel, a workbook can have multiple worksheets;

• ReadListenerAfter each line is read, readlistener will be called to process the data. We can write the code calling service in itinvokeMethod internal;

• WriteHandlerIn each operation, including creating cells and tables, writehandler will be called to process data, which is transparent and invisible to users;

• All configurations are inheritedThe configuration of workbook will be inherited by 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

Use location: the standard acts on the member variable, associating the attributes in the entity class with the columns in the excel table

Optional attributes:

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

Suggestions for use:Either don’t write it 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

Marked on member variables, date conversion, used in codeMember variable of type stringTo receiveDate format data in ExcelThis annotation will be called. Insidevaluereferencejava.text.SimpleDateFormat

@NumberFormat

Marked on member variables, number conversion, used in codeMember variable of type stringTo receiveData in Excel digital formatThis annotation will be called. Insidevaluereferencejava.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 they are added to the member variables@ExcelPropertyNotes for.

After the annotation is marked, if the member variable in the class is not marked@ExcelPropertyAnnotations will not participate in reading and writing.

3. General parameters during reading

ReadWorkbook,ReadSheetIf it is blank, the parent will be used by default.

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

• readListenerListener, in the process of reading data, will constantly call the listener.

• headRowNumberSpecifies the number of column header rows that need to be read from the table. There is a row header by default, that is, the second row is considered as data from the beginning.

• headAndclazzeither-or. 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.

• clazzAndheadeither-or. Read the class corresponding to the header of the file, or use annotations. If neither is specified, all data is read.

• autoTrimAutomatic trim of string, header and other data

• passwordDo you need to use a password when reading

4. Readworkbook (Workbook object) parameter

• excelTypeThe type of current excel will be judged automatically when reading, without setting.

• inputStreamAndfileeither-or. File is recommended.

• fileAndinputStreameither-or. Read the file of the file.

• autoCloseStreamAutomatically close the flow.

• readCacheThe default memory is less than 5m, and more than 5m will be usedEhCache, this parameter is not recommended.

• useDefaultListener @since 2.1.4It will be added by defaultModelBuildEventListenerTo help convert to incomingclassObject, set tofalseThe object will not be converted after, and the custom listener will receive itMap<Integer,CellData>Object, if you want to continue to answer toclassObject, please callreadListenerMethod to add custombeforeListener、 ModelBuildEventListener, customafterListenerJust.

5. Readsheet (worksheet object) parameter

• sheetNoThe sheet number needs to be read. It is recommended to use this to specify which sheet to read

• sheetNameMatching sheets by name is not supported in Excel 2003

6. Comments on writing

@ExcelProperty

Use location: criteria apply to member variables

Optional attributes:

Use effect：indexSpecify the column to write to. If not specified, it will be sorted according to the position of member variables;

valueSpecify the column header to be written. If not specified, the name of the member variable will be 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 the class or attribute, specifying the content row height

• @Headrowheight() is marked on the class or attribute and specifies the column header row height

• @Columnwidth() is marked on the class or attribute to specify the column width

• Excelignore ` all fields will be written to excel by default, and this annotation will ignore this field

• DateTimeFormatDate conversionDateWriting to excel will call this annotation. Insidevaluereferencejava.text.SimpleDateFormat

• NumberFormatDigital conversion, usingNumberWriting excel will call this annotation. Insidevaluereferencejava.text.DecimalFormat

• ExcelIgnoreUnannotatedDefault noExcelPropertyAll annotations will participate in reading and writing, and will not participate if added

7. General parameters during writing

WriteWorkbook、WriteSheetIf it is blank, the parent will be used by default.

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

• writeHandlerWrite processor. Can achieveWorkbookWriteHandler,SheetWriteHandler,RowWriteHandler,CellWriteHandler, it will be called at different stages of writing excel, which is transparent and invisible to users.

• relativeHeadRowIndexHow many lines from the start. That is, a few blank lines at the beginning

• needHeadWhether to lead out the head

• headAndclazzeither-or. Write the header list of the file. It is recommended to use class.

• clazzAndheadeither-or. Write the class corresponding to the header of the file. You can also use annotations.

• autoTrimAutomatic trim of string, header and other data

8. Writeworkbook parameter

• excelTypeThe current excel type. The default isxlsx

• outputStreamAndfileeither-or. Stream written to file

• fileAndoutputStreameither-or. Files written

• templateInputStreamFile flow of template

• templateFiletemplate file

• autoCloseStreamAutomatically close the flow.

• passwordDo you need to use a password when writing

• useDefaultStyleIs the default header used when writing

9. Writesheet (worksheet object) parameter

• sheetNoThe number to be written. Default 0

• sheetNameThe name of the sheet you need 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 the excel table, {} is used to represent the variables to be filled in the package. If there are variables in the cell text{、}The left and right braces need to be escaped with slashes in front of the braces\{、\}。

The member variable name of the entity object filled with data in the code or the key of the filled map set should 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 the filling data. The name of the member variable in the entity class needs to match the name of the variable in each {package in the 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

Used in Excel tables{.}To represent the variables to be filled in the package. If there are variables in the cell text{、}The left and right braces need to be escaped with slashes in front of the braces\{ 、\}。

The member variable name of the entity object filled with data in the code or the key of the filled map set should 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 = "fill_data_template2.xlsx";
    ExcelWriterBuilder excelWriterBuilder = EasyExcel.write("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, it is necessary to fill in multiple groupsFillConfigObject to set line breaks.

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 object
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    //When combined filling, because the amount of data filled by multiple groups is uncertain, another row needs to be started 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 it needs to be filled throughFillConfigObject sets the horizontal fill.

4.4.2. Filling data

/**
*Horizontal fill
*/
public static void fillData4() {
    //Prepare template
    String template = "fill_data_template4.xlsx";
    //Target file
    String target = "export/fill_data4.xlsx";
    //Prepare data
    List<FillData> fillData = initDataList();
    //Generate workbook object
    ExcelWriter excelWriter = EasyExcel.write(target).withTemplate(template).build();
    //Generate worksheet object
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    //When combined filling, because the amount of data filled by multiple groups is uncertain, another row needs to be started 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.

This work adoptsCC agreement, reprint must indicate the author and the link to this article