EasyExcel 轻松读取Excel

风尘博客 2019-12-10

1057

一、背景

通过Java
读写Excel
大概有以下几种： poi
/csv
/jxl
/jxls
/easyPoi
/easyExcel
（本文重点）。

1.easyExcel
基于注解的方式将以前poi
的复杂的代码进模块抽离。我们基本上的需求只需要在excelProperty
注解中就可以解决。2.easyExcel
最大的特点就是解决了内存泄漏的问题。以上几种poi
在导出Excel
的时候都受到了数据的影响.而且性能上还不是很好。easyExcel
是poi
系列产品的最佳之选

easyExcel 官方文档^[1]

二、项目准备

2.1 步骤

1.创建Excel
对应的实体对象(本文的ImportModel.java
)；2.由于默认异步读取Excel
，所以需要创建Excel
一行一行的回调监听器（本文的ExcelListener.java
）3.读取Excel
。

2.2 项目依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- easy excel-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>1.1.1</version>
    </dependency>
    <!--Swagger-ui配置-->
    <dependency>
        <groupId>io.springfox</groupId>
        <artifactId>springfox-swagger2</artifactId>
        <version>2.9.2</version>
    </dependency>
    <dependency>
        <groupId>io.springfox</groupId>
        <artifactId>springfox-swagger-ui</artifactId>
        <version>2.9.2</version>
    </dependency>
    <!-- lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <scope>1.8.4</scope>
    </dependency>
    <!-- fastjson -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.60</version>
    </dependency>
</dependencies>

2.3 读取的`Excel`
文本

本文使用的 easyexcel-demo.xlsx^[2], 内容如下图

2.4 读取对象模版

该类必须要继承自 BaseRowModel.java

@Data
public class ImportModel extends BaseRowModel {


    /**
     * 通过 @ExcelProperty 的value 指定每个字段的列名称，index 为列的序号。
     */
    @ExcelProperty(value = "姓名", index = 0)
    private String studentName;


    @ExcelProperty(value = "年级", index = 1)
    private String grade;


    @ExcelProperty(value = "学科", index = 2)
    private String subject;


    @ExcelProperty(value = "分数", index = 3)
    private Integer fraction;


    public ImportModel() {


    }
}

三、核心实现

3.1 `Excel`
监听器

默认异步读取Excel
,invoke
方法逐行读取数据

@Slf4j
public class ExcelListener extends AnalysisEventListener {


    /**
     * 自定义用于暂时存储data。
     */
    private List<Object> datas = new ArrayList<>();


    /**
     * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
     */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));


        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
        datas.add(data);
        //根据自己业务做处理（通用业务可以不需要该项）
        doSomething(data);
    }


    private void doSomething(Object object) {
        log.info("doSomething.....");
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("所有数据解析完成！");
    }


    public List<Object> getDatas() {
        return datas;
    }


    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }
}

3.2 读取 `Excel`
工具类`ImportExcelUtil.java`

•返回 ExcelReader

检验Excel
文件格式，支持.xls
和. xlsx
文件，其他文件格式会抛错。

private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) {
    String filename = excel.getOriginalFilename();
    if (filename == null) {
        throw new ExcelException("文件格式错误！");
    }
    if (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx")) {
        throw new ExcelException("文件格式错误！");
    }
    InputStream inputStream;
    try {
        inputStream = new BufferedInputStream(excel.getInputStream());
        return new ExcelReader(inputStream, null, excelListener, false);
    } catch (IOException e) {
        e.printStackTrace();
    }
    return null;
}

•读取整个Excel
方法

如果Excel
内有多个 sheet
需要各个 sheet
字段相同

public static List readExcel(MultipartFile excel, BaseRowModel baseRowModel) {
    ExcelListener excelListener = new ExcelListener();
    ExcelReader reader = getReader(excel, excelListener);
    if (reader == null) {
        return null;
    }
    for (Sheet sheet : reader.getSheets()) {
        if (baseRowModel != null) {
            sheet.setClazz(baseRowModel.getClass());
        }
        reader.read(sheet);
    }
    return excelListener.getDatas();
}

•读取 Excel
的指定 sheet
指定数据方法

public static List readExcel(MultipartFile excel, BaseRowModel baseRowModel,
                                         int sheetNo, int headLineNum) {
    ExcelListener excelListener = new ExcelListener();
    ExcelReader reader = getReader(excel, excelListener);
    if (reader == null) {
        return null;
    }
    reader.read(new Sheet(sheetNo, headLineNum, baseRowModel.getClass()));
    return excelListener.getDatas();
}

•读取 Excel
的指定 sheet
全部数据

该方法其实为读取 Excel 的指定 sheet 指定数据方法中的指定行设为默认值1
。

public static List readExcel(MultipartFile excel, BaseRowModel baseRowModel, int sheetNo) {
    return readExcel(excel, baseRowModel, sheetNo, 1);
}

四、测试

这里只给出Controller
层接口代码，Service
具体实现很简单，这里就略过了，详见文末地址代码示例。

4.1 读取整个`Excel`

•接口

@ApiOperation(value = "读取 整个Excel(多个 sheet 需要 各个 sheet 字段相同)")
@PostMapping(value = "/readAllExcel")
public List<ImportModel> readAllExcel(MultipartFile excel) {
    return readExcelService.readExcel(excel, new ImportModel());
}

•返回结果

结果读取了全部数据

[
  {
    "cellStyleMap": {},
    "studentName": "张三",
    "grade": "高三",
    "subject": "语文",
    "fraction": 130
  },
  {
    "cellStyleMap": {},
    "studentName": "张三",
    "grade": "高三",
    "subject": "数学",
    "fraction": 140
  },
  {
    "cellStyleMap": {},
    "studentName": "张三",
    "grade": "高三",
    "subject": "英语",
    "fraction": 125
  },
  {
    "cellStyleMap": {},
    "studentName": "张三",
    "grade": "高三",
    "subject": "化学",
    "fraction": 90
  },
  {
    "cellStyleMap": {},
    "studentName": "李四",
    "grade": "高一",
    "subject": "语文",
    "fraction": 135
  },
  {
    "cellStyleMap": {},
    "studentName": "李四",
    "grade": "高一",
    "subject": "数学",
    "fraction": 125
  },
  {
    "cellStyleMap": {},
    "studentName": "李四",
    "grade": "高一",
    "subject": "英语",
    "fraction": 145
  },
  {
    "cellStyleMap": {},
    "studentName": "李四",
    "grade": "高一",
    "subject": "化学",
    "fraction": 88
  }
]

•打印日志

2019-12-08 17:10:23.324  INFO 23855 --- [nio-8081-exec-1] o.a.c.c.C.[.[localhost].[/easyexcel]     : Initializing Spring DispatcherServlet 'dispatcherServlet'
2019-12-08 17:10:23.324  INFO 23855 --- [nio-8081-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2019-12-08 17:10:23.335  INFO 23855 --- [nio-8081-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 11 ms
2019-12-08 17:10:46.940  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":130,"grade":"高三","studentName":"张三","subject":"语文"}
2019-12-08 17:10:46.940  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:10:46.940  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":140,"grade":"高三","studentName":"张三","subject":"数学"}
2019-12-08 17:10:46.941  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:10:46.941  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":125,"grade":"高三","studentName":"张三","subject":"英语"}
2019-12-08 17:10:46.941  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:10:46.941  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":90,"grade":"高三","studentName":"张三","subject":"化学"}
2019-12-08 17:10:46.941  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:10:46.941  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : 所有数据解析完成！
2019-12-08 17:10:46.943  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":135,"grade":"高一","studentName":"李四","subject":"语文"}
2019-12-08 17:10:46.943  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:10:46.944  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":125,"grade":"高一","studentName":"李四","subject":"数学"}
2019-12-08 17:10:46.944  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:10:46.944  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":145,"grade":"高一","studentName":"李四","subject":"英语"}
2019-12-08 17:10:46.944  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:10:46.945  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":88,"grade":"高一","studentName":"李四","subject":"化学"}
2019-12-08 17:10:46.945  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:10:46.945  INFO 23855 --- [nio-8081-exec-1] c.v.easyexcel.export.util.ExcelListener  : 所有数据解析完成！

4.2 读取 `Excel`
的第二个 `sheet`
全部数据

•接口

@ApiImplicitParam( name = "sheetNo", value = "读第几个表单", required = true)
@ApiOperation(value = "读取 Excel 的指定 sheet 全部数据")
@PostMapping(value = "/readOneSheet")
public List<ImportModel> readOneSheet(MultipartFile excel,
                                      @RequestParam Integer sheetNo) {
    return readExcelService.readExcel(excel, new ImportModel(), sheetNo);
}

•返回结果

只读取了第二页李四的数据

[
  {
    "cellStyleMap": {},
    "studentName": "李四",
    "grade": "高一",
    "subject": "语文",
    "fraction": 135
  },
  {
    "cellStyleMap": {},
    "studentName": "李四",
    "grade": "高一",
    "subject": "数学",
    "fraction": 125
  },
  {
    "cellStyleMap": {},
    "studentName": "李四",
    "grade": "高一",
    "subject": "英语",
    "fraction": 145
  },
  {
    "cellStyleMap": {},
    "studentName": "李四",
    "grade": "高一",
    "subject": "化学",
    "fraction": 88
  }
]

•打印日志

2019-12-08 17:13:00.384  INFO 23855 --- [nio-8081-exec-5] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":135,"grade":"高一","studentName":"李四","subject":"语文"}
2019-12-08 17:13:00.384  INFO 23855 --- [nio-8081-exec-5] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:13:00.384  INFO 23855 --- [nio-8081-exec-5] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":125,"grade":"高一","studentName":"李四","subject":"数学"}
2019-12-08 17:13:00.384  INFO 23855 --- [nio-8081-exec-5] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:13:00.385  INFO 23855 --- [nio-8081-exec-5] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":145,"grade":"高一","studentName":"李四","subject":"英语"}
2019-12-08 17:13:00.385  INFO 23855 --- [nio-8081-exec-5] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:13:00.385  INFO 23855 --- [nio-8081-exec-5] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":88,"grade":"高一","studentName":"李四","subject":"化学"}
2019-12-08 17:13:00.385  INFO 23855 --- [nio-8081-exec-5] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:13:00.386  INFO 23855 --- [nio-8081-exec-5] c.v.easyexcel.export.util.ExcelListener  : 所有数据解析完成！

4.3 读取`Excel`
第一个`sheet`
,从第二行开始读

•接口

@ApiImplicitParams({
        @ApiImplicitParam( name = "sheetNo", value = "读第几个表单", required = true),
        @ApiImplicitParam( name = "headLineNum", value = "从第几行数据开始读", required = true)
})
@ApiOperation(value = "读取 Excel 的指定 sheet 指定数据")
@PostMapping(value = "/readExcel")
public List<ImportModel> readExcel(MultipartFile excel,
                                   @RequestParam Integer sheetNo,
                                   @RequestParam Integer headLineNum) {
    return readExcelService.readExcel(excel, new ImportModel(), sheetNo,headLineNum);
}

•返回结果

[
  {
    "cellStyleMap": {},
    "studentName": "张三",
    "grade": "高三",
    "subject": "数学",
    "fraction": 140
  },
  {
    "cellStyleMap": {},
    "studentName": "张三",
    "grade": "高三",
    "subject": "英语",
    "fraction": 125
  },
  {
    "cellStyleMap": {},
    "studentName": "张三",
    "grade": "高三",
    "subject": "化学",
    "fraction": 90
  }
]

•打印日志

2019-12-08 17:16:12.337  INFO 23855 --- [nio-8081-exec-8] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":140,"grade":"高三","studentName":"张三","subject":"数学"}
2019-12-08 17:16:12.339  INFO 23855 --- [nio-8081-exec-8] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:16:12.342  INFO 23855 --- [nio-8081-exec-8] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":125,"grade":"高三","studentName":"张三","subject":"英语"}
2019-12-08 17:16:12.345  INFO 23855 --- [nio-8081-exec-8] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:16:12.346  INFO 23855 --- [nio-8081-exec-8] c.v.easyexcel.export.util.ExcelListener  : 解析到一条数据:{"cellStyleMap":{},"fraction":90,"grade":"高三","studentName":"张三","subject":"化学"}
2019-12-08 17:16:12.346  INFO 23855 --- [nio-8081-exec-8] c.v.easyexcel.export.util.ExcelListener  : doSomething.....
2019-12-08 17:16:12.346  INFO 23855 --- [nio-8081-exec-8] c.v.easyexcel.export.util.ExcelListener  : 所有数据解析完成！

导出示例见下文

Github 示例代码^[3]

文中链接

[1]
easyExcel 官方文档: https://alibaba-easyexcel.github.io/quickstart/write.html
[2]
easyexcel-demo.xlsx: https://github.com/vanDusty/springboot-home/blob/master/springboot-demo-excel/file/easyexcel-demo.xlsx
[3]
Github 示例代码: https://github.com/vanDusty/SpringBoot-Home/tree/master/springboot-demo-excel

数据库

文章转载自风尘博客，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

EasyExcel 轻松读取Excel

一、背景

二、项目准备

2.1 步骤

2.2 项目依赖

2.3 读取的Excel文本

2.4 读取对象模版

三、核心实现

3.1 Excel监听器

3.2 读取 Excel 工具类ImportExcelUtil.java

四、测试

4.1 读取整个Excel

4.2 读取 Excel 的第二个 sheet 全部数据

4.3 读取Excel第一个sheet,从第二行开始读

文中链接

评论

2.3 读取的`Excel`
文本

3.1 `Excel`
监听器

3.2 读取 `Excel`
工具类`ImportExcelUtil.java`

4.1 读取整个`Excel`

4.2 读取 `Excel`
的第二个 `sheet`
全部数据

4.3 读取`Excel`
第一个`sheet`
,从第二行开始读