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
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
objects = ExcelUtil.readLessThan1000Row(filePath, sheet);

Read all data from Sheet2.

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

More than 1000 rows

Default Read

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

Specific Read

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

Export Excel

Single Sheet Export

Export without Model Mapping

String filePath = "/home/chenmingjian/Downloads/测试.xlsx";
List
> 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
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
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
list1 = new ArrayList<>();
for (int j = 1; j < 4; j++) {
    ArrayList
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.