Master Excel Processing in Java with Alibaba EasyExcel: Setup, Read, and Export

Environment Setup

Add 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);

Less than 1000 rows (specified read)

String filePath = "/home/chenmingjian/Downloads/学生表.xlsx";
// first parameter: sheet number (1), second: start row (1)
Sheet sheet = new Sheet(1, 1);
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);

More than 1000 rows (specified read)

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

Export Excel

Single sheet 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("表头1", "表头2", "表头3");
ExcelUtil.writeBySimple(filePath, data, head);

Single sheet with model mapping

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

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 sheets

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

Utility Class (ExcelUtil)

public class ExcelUtil {
    private static Sheet initSheet;
    static {
        initSheet = new Sheet(1, 0);
        initSheet.setSheetName("sheet");
        initSheet.setAutoWidth(Boolean.TRUE);
    }
    public static List<Object> readLessThan1000Row(String filePath) {
        return readLessThan1000RowBySheet(filePath, null);
    }
    public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet) {
        if (!StringUtils.hasText(filePath)) return null;
        sheet = (sheet != null) ? sheet : initSheet;
        try (InputStream in = new FileInputStream(filePath)) {
            return EasyExcelFactory.read(in, sheet);
        } catch (Exception e) {
            log.info("File not found or error: {}", filePath);
        }
        return null;
    }
    public static List<Object> readMoreThan1000Row(String filePath) {
        return readMoreThan1000RowBySheet(filePath, null);
    }
    public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet) {
        if (!StringUtils.hasText(filePath)) return null;
        sheet = (sheet != null) ? sheet : initSheet;
        try (InputStream in = new FileInputStream(filePath)) {
            ExcelListener listener = new ExcelListener();
            EasyExcelFactory.readBySax(in, sheet, listener);
            return listener.getDatas();
        } catch (Exception e) {
            log.error("File not found or error: {}", filePath);
        }
        return null;
    }
    public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head) {
        writeSimpleBySheet(filePath, data, head, null);
    }
    public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet) {
        sheet = (sheet != null) ? sheet : initSheet;
        if (head != null) {
            List<List<String>> headList = new ArrayList<>();
            head.forEach(h -> headList.add(Collections.singletonList(h)));
            sheet.setHead(headList);
        }
        try (OutputStream out = new FileOutputStream(filePath);
             ExcelWriter writer = EasyExcelFactory.getWriter(out)) {
            writer.write1(data, sheet);
        } catch (Exception e) {
            log.error("Export failed: {}", filePath);
        }
    }
    public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data) {
        writeWithTemplateAndSheet(filePath, data, null);
    }
    public static void writeWithTemplateAndSheet(String filePath, List<? extends BaseRowModel> data, Sheet sheet) {
        if (CollectionUtils.isEmpty(data)) return;
        sheet = (sheet != null) ? sheet : initSheet;
        sheet.setClazz(data.get(0).getClass());
        try (OutputStream out = new FileOutputStream(filePath);
             ExcelWriter writer = EasyExcelFactory.getWriter(out)) {
            writer.write(data, sheet);
        } catch (Exception e) {
            log.error("Export failed: {}", filePath);
        }
    }
    public static void writeWithMultipleSheel(String filePath, List<MultipleSheelPropety> props) {
        if (CollectionUtils.isEmpty(props)) return;
        try (OutputStream out = new FileOutputStream(filePath);
             ExcelWriter writer = EasyExcelFactory.getWriter(out)) {
            for (MultipleSheelPropety p : props) {
                Sheet s = (p.getSheet() != null) ? p.getSheet() : initSheet;
                if (!CollectionUtils.isEmpty(p.getData())) {
                    s.setClazz(p.getData().get(0).getClass());
                }
                writer.write(p.getData(), s);
            }
        } catch (Exception e) {
            log.error("Export failed: {}", filePath);
        }
    }
    @Data
    public static class MultipleSheelPropety {
        private List<? extends BaseRowModel> data;
        private Sheet sheet;
    }
    public static class ExcelListener extends AnalysisEventListener {
        private List<Object> datas = new ArrayList<>();
        @Override
        public void invoke(Object object, AnalysisContext context) {
            if (object != null) datas.add(object);
        }
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {}
        public List<Object> getDatas() { return datas; }
    }
}

Test Class

public class Test {
    @Test
    public void readLessThan1000Row() {
        String filePath = "/home/chenmingjian/Downloads/测试.xlsx";
        List<Object> objects = ExcelUtil.readLessThan1000Row(filePath);
        objects.forEach(System.out::println);
    }
    @Test
    public void readLessThan1000RowBySheet() {
        String filePath = "/home/chenmingjian/Downloads/测试.xlsx";
        Sheet sheet = new Sheet(1, 1);
        List<Object> objects = ExcelUtil.readLessThan1000RowBySheet(filePath, sheet);
        objects.forEach(System.out::println);
    }
    @Test
    public void readMoreThan1000Row() {
        String filePath = "/home/chenmingjian/Downloads/测试.xlsx";
        List<Object> objects = ExcelUtil.readMoreThan1000Row(filePath);
        objects.forEach(System.out::println);
    }
    @Test
    public void writeBySimple() {
        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("表头1", "表头2", "表头3");
        ExcelUtil.writeBySimple(filePath, data, head);
    }
    @Test
    public void writeWithTemplate() {
        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);
    }
    @Test
    public void writeWithMultipleSheel() {
        ArrayList<ExcelUtil.MultipleSheelPropety> list = new ArrayList<>();
        for (int j = 1; j < 4; j++) {
            ArrayList<TableHeaderExcelProperty> sheetData = new ArrayList<>();
            for (int i = 0; i < 4; i++) {
                TableHeaderExcelProperty p = new TableHeaderExcelProperty();
                p.setName("cmj" + i);
                p.setAge(22 + i);
                p.setSchool("清华大学" + i);
                sheetData.add(p);
            }
            Sheet sheet = new Sheet(j, 0);
            sheet.setSheetName("sheet" + j);
            ExcelUtil.MultipleSheelPropety prop = new ExcelUtil.MultipleSheelPropety();
            prop.setData(sheetData);
            prop.setSheet(sheet);
            list.add(prop);
        }
        ExcelUtil.writeWithMultipleSheel("/home/chenmingjian/Downloads/aaa.xlsx", list);
    }
}