Using Alibaba EasyExcel for Reading and Writing Excel Files in Java

Hello everyone, I am Lei.

Thanks to the Alibaba project team for providing the EasyExcel utility library. GitHub repository: https://github.com/alibaba/easyexcel

Table of Contents

Environment Setup

Read Excel Files

Default Read

Specific Read

Data < 1000 rows

Data > 1000 rows

Export Excel

Export without Model Mapping

Export with Model Mapping

Single Sheet Export

Multiple Sheet Export

Utility Classes

Test Classes

Environment Setup

EasyExcel dependency (required)

Spring Boot (optional)

Lombok (optional)

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.2-beat1</version>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.2</version>
</dependency>

Read Excel Files

Less than 1000 rows

Default Read

Read all data from Sheet1.

String filePath = "/home/chenmingjian/Downloads/学生表.xlsx";
List<Object> objects = ExcelUtil.readLessThan1000Row(filePath);

Specific Read

Read data from Sheet1 and Sheet2.

Read rows starting from the second row of Sheet1.

String filePath = "/home/chenmingjian/Downloads/学生表.xlsx";
// first parameter: sheet index (1 = Sheet1), second: start row (1 = second row)
Sheet sheet = new Sheet(1, 1);
List<Object> objects = ExcelUtil.readLessThan1000Row(filePath, sheet);

Read all data from Sheet2.

String filePath = "/home/chenmingjian/Downloads/学生表.xlsx";
Sheet sheet = new Sheet(2, 0);
List<Object> objects = ExcelUtil.readLessThan1000Row(filePath, sheet);

More than 1000 rows

Default Read

String filePath = "/home/chenmingjian/Downloads/学生表.xlsx";
List<Object> objects = ExcelUtil.readMoreThan1000Row(filePath);

Specific Read

String filePath = "/home/chenmingjian/Downloads/学生表.xlsx";
Sheet sheet = new Sheet(1, 2);
List<Object> objects = ExcelUtil.readMoreThan1000Row(filePath, sheet);

Export Excel

Single Sheet Export

Export without Model Mapping

String filePath = "/home/chenmingjian/Downloads/测试.xlsx";
List<List<Object>> data = new ArrayList<>();
data.add(Arrays.asList("111", "222", "333"));
data.add(Arrays.asList("111", "222", "333"));
data.add(Arrays.asList("111", "222", "333"));
List<String> head = Arrays.asList("Header1", "Header2", "Header3");
ExcelUtil.writeBySimple(filePath, data, head);

Result:

Export with Model Mapping

1. Define a model class.

package com.springboot.utils.excel.test;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.EqualsAndHashCode;

@EqualsAndHashCode(callSuper = true)
@Data
public class TableHeaderExcelProperty extends BaseRowModel {
    @ExcelProperty(value = "姓名", index = 0)
    private String name;

    @ExcelProperty(value = "年龄", index = 1)
    private int age;

    @ExcelProperty(value = "学校", index = 2)
    private String school;
}

2. Write data using the model.

String filePath = "/home/chenmingjian/Downloads/测试.xlsx";
ArrayList<TableHeaderExcelProperty> data = new ArrayList<>();
for (int i = 0; i < 4; i++) {
    TableHeaderExcelProperty p = new TableHeaderExcelProperty();
    p.setName("cmj" + i);
    p.setAge(22 + i);
    p.setSchool("清华大学" + i);
    data.add(p);
}
ExcelUtil.writeWithTemplate(filePath, data);

Multiple Sheet Export

1. Define the same model class as above.

... (same TableHeaderExcelProperty definition) ...

2. Write data to several sheets.

ArrayList<ExcelUtil.MultipleSheelPropety> list1 = new ArrayList<>();
for (int j = 1; j < 4; j++) {
    ArrayList<TableHeaderExcelProperty> list = new ArrayList<>();
    for (int i = 0; i < 4; i++) {
        TableHeaderExcelProperty p = new TableHeaderExcelProperty();
        p.setName("cmj" + i);
        p.setAge(22 + i);
        p.setSchool("清华大学" + i);
        list.add(p);
    }
    Sheet sheet = new Sheet(j, 0);
    sheet.setSheetName("sheet" + j);
    ExcelUtil.MultipleSheelPropety prop = new ExcelUtil.MultipleSheelPropety();
    prop.setData(list);
    prop.setSheet(sheet);
    list1.add(prop);
}
ExcelUtil.writeWithMultipleSheel("/home/chenmingjian/Downloads/aaa.xlsx", list1);

Utility Class (ExcelUtil)

The ExcelUtil class provides static methods for reading ( readLessThan1000Row, readMoreThan1000Row), writing simple data ( writeBySimple), writing with templates ( writeWithTemplate), and writing multiple sheets ( writeWithMultipleSheel). It also contains inner classes for sheet properties and a listener ( ExcelListener) for SAX parsing of large files.

Test Class

A JUnit test class demonstrates how to call the utility methods for reading small and large Excel files, exporting simple data, exporting with a model, and exporting multiple sheets.

Note

At the end of the article there is a reference to a 7701‑page PDF containing interview questions from major internet companies.