三三微末
0%

spring系列（015）之接入easyexcel

发表于 分类于

EasyExcel是一个开源的操作excel的java项目，处理excel时节约内存又高效，在支持百兆Excel的读写

一、引入包

为了方便Bean的定义，额外引入lombok.jar、以及为了测试效果而引入spring-boot-starter-web.jar

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>2.1.13.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.6</version>
        </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.24</version>
    </dependency>
</dependencies>

二、定义Bean

Bean中可以定义excel的列名。例如用户Bean（UserDTO.java的定义)。@ExcelProperty标识出此字段将作为excel的一列，括号里是列名。
@ExcelIgnore 表示该列将不出现在导出的excel的导入导出中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package net.zuze.dto;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * @author Administrator
 * @date 2022/12/13
 **/
@Data
public class UserDTO {

    @ExcelProperty("姓名")
    private String username;

    @ExcelProperty("年龄")
    @ExcelIgnore
    private String age;

    @ExcelProperty("性别")
    private String sex;
}

三、导出测试

在controller中测试

1、导出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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
package net.zuze.controller;

import com.alibaba.excel.EasyExcel;
import net.zuze.dto.UserDTO;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Administrator
 * @date 2022/12/13
 **/

@RestController
@RequestMapping("/")
public class ExportController {

    /**
     * web导出
     * @param response
     * @throws IOException
     */
    @GetMapping("exportWeb")
    public void exportWebExcel(HttpServletResponse response) throws IOException {

        //模拟导出数据
        List<UserDTO> userList = this.createData();

        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        //导出时的文件名
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("文件名.xlsx", "UTF-8"));
        EasyExcel.write(response.getOutputStream(), UserDTO.class).sheet("模板").doWrite(userList);
    }

    /**
     * 服务端导出
     */
    @GetMapping("exportDir")
    public void exportDirExcel() {
        //模拟导出数据
        List<UserDTO> userList = this.createData();

        EasyExcel.write("D:/文件名.xlsx", UserDTO.class).sheet("模板").doWrite(userList);
    }


    /**
     * 模拟导出数据
     * @return
     */
    private List<UserDTO> createData(){
        List<UserDTO> userList = new ArrayList<>();
        for(int i=0;i<10;i++) {
            UserDTO userDTO = new UserDTO();
            userDTO.setUsername("李四"+i);
            userDTO.setAge(i+"age");
            userDTO.setSex(i+"sex");
            userList.add(userDTO);
        }
        return userList;
    }

}


2、导出效果

2.1、web方式导出

浏览器中Get方式调用接口 http://localhost:8080/exportWeb
浏览器会在下载目录中下载一个文件名.xlsx文件
下载效果

2.2、服务端目录下载

浏览器中Get方式调用接口 http://localhost:8080/exportDir
会在服务器上的 D盘根目录（代码里指定的路径） 下载一个文件名.xlsx文件
下载效果

四、导入

EasyExcel的导入需要添加一个监听器，用于处理导入结果。监听器需要实现com.alibaba.excel.read.listener.ReadListener接口

1、ReadListener接口有6个方法需要实现

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
 
package com.alibaba.excel.read.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.Listener;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.CellExtra;
import java.util.Map;

public interface ReadListener<T> extends Listener {
    //解析出现异常时执行
    void onException(Exception var1, AnalysisContext var2) throws Exception;

    //excle表头处理
    void invokeHead(Map<Integer, CellData> var1, AnalysisContext var2);

    //excel数据处理
    void invoke(T var1, AnalysisContext var2);

    void extra(CellExtra var1, AnalysisContext var2);

    //excel解析完后执行
    void doAfterAllAnalysed(AnalysisContext var1);

    //否还有下一条记录（invoke执行前会执行）
    boolean hasNext(AnalysisContext var1);
}

2、ReadListener接口实现

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
package net.zuze.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import net.zuze.dto.UserDTO;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author Administrator
 * @date 2022/12/14
 **/
@Slf4j
@Data
public class UserDataListener implements ReadListener<UserDTO> {

    /**
     * 为了将解析的数据返回给调用发，定义此变量封装解析的excel内容
     */
    private List<UserDTO> userList;

    public UserDataListener(){
        userList = new ArrayList<>();
    }

    @Override
    public void onException(Exception e, AnalysisContext analysisContext) throws Exception {
    }

    @Override
    public void invokeHead(Map<Integer, CellData> map, AnalysisContext analysisContext) {
        log.info("invokeHead{},{}",map,analysisContext);
    }

    @Override
    public void invoke(UserDTO userDTO, AnalysisContext analysisContext) {
        //解析的记录集合
        userList.add(userDTO);
    }

    @Override
    public void extra(CellExtra cellExtra, AnalysisContext analysisContext) {
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("处理完成");
    }

    @Override
    public boolean hasNext(AnalysisContext analysisContext) {
        return true;
    }
}

3、导入接口调用

将解析的数据返回接口调用方

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
package net.zuze.controller;

import com.alibaba.excel.EasyExcel;
import net.zuze.dto.UserDTO;
import net.zuze.listener.UserDataListener;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;

/**
 * @author Administrator
 * @date 2022/12/14
 **/
@RestController
@RequestMapping("/")
public class ImportController {
    @PostMapping("import")
    public List<UserDTO> importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        //定义监听对象
        UserDataListener userListener = new UserDataListener();
        EasyExcel.read(file.getInputStream(), UserDTO.class,userListener).sheet().doRead();
        //将监听对象中解析的数据封装后返回
        return userListener.getUserList();
    }
}

4、导入接口调用效果

postman中post方式调用http://localhost:8080/import接口、导入excel文件
导入效果
http://localhost:8080/import接口调用，完整返回

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
41
42
43
44
45
46
47
48
49
50
51
52
[
    {
        "username": "李四0",
        "age": null,
        "sex": "0sex"
    },
    {
        "username": "李四1",
        "age": null,
        "sex": "1sex"
    },
    {
        "username": "李四2",
        "age": null,
        "sex": "2sex"
    },
    {
        "username": "李四3",
        "age": null,
        "sex": "3sex"
    },
    {
        "username": "李四4",
        "age": null,
        "sex": "4sex"
    },
    {
        "username": "李四5",
        "age": null,
        "sex": "5sex"
    },
    {
        "username": "李四6",
        "age": null,
        "sex": "6sex"
    },
    {
        "username": "李四7",
        "age": null,
        "sex": "7sex"
    },
    {
        "username": "李四8",
        "age": null,
        "sex": "8sex"
    },
    {
        "username": "李四9",
        "age": null,
        "sex": "9sex"
    }
]