一、介绍和入门使用

1.1、Excel的应用场景

数据导入：减轻录入工作量

数据导出：统计信息归档

数据传输：异构系统之间数据传输

1.2、EasyExcel

Github地址：EasyExcel

官方网站：EasyExcel

1、介绍

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

2、特点

  • Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc。
  • EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。
  • EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理（AnalysisEventListener）。

1.3、入门使用

1、创建项目并引入依赖

创建一个测试用Maven项目，并导入以下依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<dependencies>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.7</version>
    </dependency>

    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-simple</artifactId>
        <version>1.7.5</version>
    </dependency>

    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>3.1.0</version>
    </dependency>

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

    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

2、最简单的写

创建实体类

  • 使用@ExcelProperty注解可以指定excel列名，即name对应excel中的”姓名”列，birthday属性对应excel中的生日列…
  • 使用Lombok插件简化开发
  • 可以使用@ExcelIgnore字段来让EasyExcel忽略该属性
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Data
public class ExcelEmpData {
    @ExcelProperty("姓名")
    private String name;
    
    @ExcelProperty("生日")
    private Date birthday;
    
    @ExcelProperty("薪资")
    private Double salary;

    /***
     * 使用@ExcelIgnore注解来使EasyExcel忽略这个属性
     */
    @ExcelIgnore
    private String password;
}

测试用例，在test包下创建一个TestWriteExcelData测试类，用于测试EasyExcel的写功能

  • 07版本的Excel和03版本的写入方式有所不同
  • 03版本的Excel写入最多一次可写65536行

编写一个静态方法，这个方法用于生成测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
private static List<ExcelEmpData> getEmpData() {
    List<ExcelEmpData> excelEmpDataList = new ArrayList<>();
    ExcelEmpData data = null;
    for (int i = 0; i < 65535; i++) {
        data = new ExcelEmpData();
        data.setName("芜湖" + i);
        //password属性的值不会被写入Excel中
        data.setPassword("123");
        data.setSalary(43.96);
        data.setBirthday(new Date());
        excelEmpDataList.add(data);
    }
    return excelEmpDataList;
}

在测试类中编写一个testWrite07方法，用于对.xlsx文件进行数据写入

1
2
3
4
5
6
7
8
@Test
public void testWrite07() {
    // 指定数据写入到哪个excel文件
    String fileName = "F:/testExcel/01-simpleWrite-07.xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, ExcelEmpData.class).sheet("模板").doWrite(getEmpData());
    System.out.println("excel写入成功！");
}
  • 结果

image-20210209235416173

image-20210209235558290

在测试类中编写一个testWrite03方法，用于对.xls文件进行数据写入

1
2
3
4
5
6
7
@Test
public void simpleWrite03() {
    String fileName = "F:/testExcel/02-simpleWrite-03.xls";
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, ExcelEmpData.class).excelType(ExcelTypeEnum.XLS).sheet("模板").doWrite(getEmpData());
    System.out.println("excel写入成功！");
}
  • 结果

image-20210209235835844

向.xls文件写入数据时，一次最多写入65536行

  • 将getData()函数中的循环次数提高到65537
1
2
3
4
5
6
7
@Test
public void testWrongWrite03() {
    String fileName = "F:/testExcel/03-simpleWrite-03.xls";
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, ExcelEmpData.class).excelType(ExcelTypeEnum.XLS).sheet("模板").doWrite(getEmpData());
    System.out.println("excel写入成功！");
}
  • 结果

image-20210210000144286

image-20210210000202523

3、指定easyExcel的写入列

为实体类中的字段配置index属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Data
public class ExcelEmpData {
    @ExcelProperty(value = "姓名",index = 1)
    private String name;

    @ExcelProperty(value = "生日",index = 3)
    private Date birthday;

    @ExcelProperty(value = "薪资",index = 5)
    private Double salary;

    /***
     * 使用@ExcelIgnore注解来使EasyExcel忽略这个属性
     */
    @ExcelIgnore
    private String password;
}

重新执行测试方法

1
2
3
4
5
6
7
8
@Test
public void testWrite07() {
    // 指定数据写入到哪个excel文件
    String fileName = "F:/testExcel/04-simpleWrite-07.xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, ExcelEmpData.class).sheet("模板").doWrite(getEmpData());
    System.out.println("excel写入成功！");
}

结果

image-20210210000729446

4、指定属性的格式化

根据实体类属性的类型为属性添加不同的格式化注解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Data
public class ExcelEmpData {
    @ExcelProperty(value = "姓名")
    private String name;

    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty(value = "生日")
    private Date birthday;

    @NumberFormat("#.##%")//百分比表示，保留两位小数
    @ExcelProperty(value = "薪资")
    private Double salary;

    /***
     * 使用@ExcelIgnore注解来使EasyExcel忽略这个属性
     */
    @ExcelIgnore
    private String password;
}

重新执行方法

1
2
3
4
5
6
7
8
@Test
public void testWrite07() {
    // 指定数据写入到哪个excel文件
    String fileName = "F:/testExcel/05-simpleWrite-07.xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, ExcelEmpData.class).sheet("模板").doWrite(getEmpData());
    System.out.println("excel写入成功！");
}

结果

image-20210210001029235

5、xlsx和xls的区别

存储相同数量的数据，用xlsx占用的空间较小

image-20210210001155925

xls一次性最多写入65536条数据

1.4、使用EasyExcel读取文件

参考地址：https://www.yuque.com/easyexcel/doc/read

1、创建监听器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
@Slf4j
public class ExcelEmpDataListener extends AnalysisEventListener<ExcelEmpData> {

    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<ExcelEmpData> list = new ArrayList<>();

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(ExcelEmpData data, AnalysisContext context) {
        log.info("解析到一条数据:{}", data);
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            log.info("存数据库");
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

        log.info("所有数据解析完成！");
    }

}

2、测试

创建testRead.xlsx文件，添加测试数据

image-20210210002105220

创建测试方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
 * 最简单的读
 */
@Test
public void simpleRead07() {

    String fileName = "F:/testExcel/testRead.xlsx";
    // 这里默认读取第一个sheet
    EasyExcel.read(fileName, ExcelEmpData.class, new ExcelEmpDataListener()).sheet().doRead();
}

@Test
public void simpleRead03() {

    String fileName = "F:/testExcel/testRead.xls";
    // 这里默认读取第一个sheet
    EasyExcel.read(fileName, ExcelEmpData.class, new ExcelEmpDataListener()).excelType(ExcelTypeEnum.XLS).sheet().doRead();
}

结果

image-20210210002619823

二、在线教育项目整合EasyExcel

在在线教育项目的课程分类管理中，我们需要接收管理员从前端传过来的课程分类excel，在解析后将excel中的数据存入课程分类表

2.1、添加依赖

1
2
3
4
5
6
7
8
9
10
<dependencies>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
    </dependency>
</dependencies>

2.2、创建与Excel对应的实体类

excel模板文件格式如下：

image-20210210003102353

1
2
3
4
5
6
7
8
9
@Data
public class ExcelSubjectData {

    @ExcelProperty(value = "一级分类")
    private String levelOneTitle;

    @ExcelProperty(value = "二级分类")
    private String levelTwoTitle;
}

2.3、实现Excel导入

1、创建监听器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
@Slf4j
@AllArgsConstructor //全参
@NoArgsConstructor //无参
public class ExcelSubjectDataListener extends AnalysisEventListener<ExcelSubjectData> {

    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private SubjectMapper subjectMapper;

    /**
     *遍历每一行的记录
     * @param data
     * @param context
     */
    @Override
    public void invoke(ExcelSubjectData data, AnalysisContext context) {
        log.info("解析到一条记录: {}", data);
        //处理读取出来的数据
        String levelOneTitle = data.getLevelOneTitle();//一级标题
        String levelTwoTitle = data.getLevelTwoTitle();//二级标题
        log.info("levelOneTitle: {}", levelOneTitle);
        log.info("levelTwoTitle: {}", levelTwoTitle);

        // 组装数据：Subject
        // 存入数据库：subjectMapper.insert()
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("所有数据解析完成！");
    }
}

2、监听器中添加辅助方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
     * 根据分类名称查询这个一级分类是否存在
     * @param title
     * @return
     */
private Subject getByTitle(String title) {
    QueryWrapper<Subject> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("title", title);
    queryWrapper.eq("parent_id", "0");//一级分类
    return subjectMappter.selectOne(queryWrapper);
}
/**
     * 根据分类名称和父id查询这个二级分类是否存在
     * @param title
     * @return
     */
private Subject getSubByTitle(String title, String parentId) {
    QueryWrapper<Subject> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("title", title);
    queryWrapper.eq("parent_id", parentId);
    return subjectMappter.selectOne(queryWrapper);
}

3、完善invoke方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/**
     * 这个每一条数据解析都会来调用
     */
@Override
public void invoke(ExcelSubjectData data, AnalysisContext context) {
    log.info("解析到一条数据:{}", data);
    //处理读取进来的数据
    String titleLevelOne = data.getLevelOneTitle();
    String titleLevelTwo = data.getLevelTwoTitle();
    //判断一级分类是否重复
    Subject subjectLevelOne = this.getByTitle(titleLevelOne);
    String parentId = null;
    if(subjectLevelOne == null) {
        //将一级分类存入数据库
        Subject subject = new Subject();
        subject.setParentId("0");
        subject.setTitle(titleLevelOne);//一级分类名称
        subjectMappter.insert(subject);
        parentId = subject.getId();
    }else{
        parentId = subjectLevelOne.getId();
    }
    //判断二级分类是否重复
    Subject subjectLevelTwo = this.getSubByTitle(titleLevelTwo, parentId);
    if(subjectLevelTwo == null){
        //将二级分类存入数据库
        Subject subject = new Subject();
        subject.setTitle(titleLevelTwo);
        subject.setParentId(parentId);
        subjectMappter.insert(subject);//添加
    }
}

4、SubjectService

接口

1
void batchImport(InputStream inputStream);

实现：获取Excel记录并逐条导入

1
2
3
4
5
6
7
@Transactional(rollbackFor = Exception.class)
@Override
public void batchImport(InputStream inputStream) {
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(inputStream, ExcelSubjectData.class, new ExcelSubjectDataListener(baseMapper))
        .excelType(ExcelTypeEnum.XLS).sheet().doRead();
}

5、Controller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
@Slf4j
@RestController
@Api(tags = "课程分类管理")
@RequestMapping("/admin/edu/subject")
public class SubjectController {
    @Autowired
    private SubjectService subjectService;

    @PostMapping("import")
    @ApiOperation("Excel批量导入课程分类")
    public R batchImport(
            @ApiParam(value = "Excel文件",required = true)
            @RequestParam("file") MultipartFile file) {
        //调用service层的批量导入方法
        try {
            subjectService.batchImport(file.getInputStream());
        } catch (Exception e) {
            //打印日志
            log.error(ExceptionUtils.getMessage(e));
            //抛出一个Excel导入失败异常
            throw new GrainException(ResultCodeEnum.EXCEL_DATA_IMPORT_ERROR);
        }
        return R.ok().message("批量导入成功");
    }

    @GetMapping("nested")
    @ApiOperation("嵌套分类数据列表")
    public R nestedList() {
        //返回分类VO列表
        List<SubjectVo> subjectVoList = subjectService.nestedList();
        return R.ok().data("items",subjectVoList);
    }
}

6、使用swagger进行测试

删除数据库中的所有课程分类

image-20210210003821966

打开swagger，选择文件进行上传

  • swagger页面

image-20210210004001328

  • 要上传的文件

image-20210210003944206

结果

image-20210210004050124

  • 数据库

image-20210210004116897