三三微末
0%

springboot系列（014）之接入easypoi

发表于 更新于 分类于

SpringBoot整合easypoi实现excel的导入导出及其简单、只需要导入easypoi-spring-boot-starter.jar即可。

一、引入包

为了方便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>cn.afterturn</groupId>
        <artifactId>easypoi-spring-boot-starter</artifactId>
        <version>2.1.13.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.24</version>
    </dependency>
</dependencies>

二、定义Bean

Bean中可以定义excel的列名,列宽,顺序,单元格合并,样式等等。例如用户Bean（UserDTO.java的定义)。
@Excel标识出此字段将作为excel的一列、其中name为列名、width为列宽,orderNum为此字段在第几列（从0开始,导出的字段只要有一个不定义orderNum，导出就按字段定义顺序排列）
@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
24
25
26
package net.zuze.dto;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import lombok.Data;
import lombok.NoArgsConstructor;

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

@Data
public class UserDTO {

    @Excel(name = "姓名",width = 30)
    private String name;

    @Excel(name = "年龄")
    @ExcelIgnore
    private String age;

    @Excel(name = "国籍",orderNum = "1")
    private String country;
}

三、导出导入测试

在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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
package net.zuze.controller;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import net.zuze.dto.UserDTO;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

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

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

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


    @RequestMapping(value = "/export", method = RequestMethod.GET)
    public void exportExcel(HttpServletResponse response) throws IOException {

        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        //导出时的文件名
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("文件名.xlsx", "UTF-8"));


        List<UserDTO> userList = new ArrayList<>();
        for(int i=0;i<10;i++) {
            UserDTO userDTO = new UserDTO();
            userDTO.setName("李四"+i);
            userDTO.setAge(i);
            userDTO.setCountry("陕北");
            userList.add(userDTO);
        }

        //定义sheet页标题，sheet页命名
        ExportParams exportParams = new ExportParams("文件标题", "用户信息", ExcelType.XSSF);
        exportParams.set
        //数据转换、以及生成 excel 文件
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, UserDTO.class, userList);

        ServletOutputStream out  = response.getOutputStream();
        workbook.write(out);


    }

    @RequestMapping(value = "/import", method = RequestMethod.POST)
    public List<UserDTO> importExcel(@RequestParam("file") final MultipartFile file)  throws IOException{

        InputStream inputStream = file.getInputStream();

        //定义解析excel的方式
        ImportParams params = new ImportParams();
        //开始标题所在的行
        params.setTitleRows(1);
        //导入的源文件是否保存
        params.setNeedSave(false);
        //导入的文件存放的路径（根据NeedSave标识决定、如果NeedSave = false、则不保存导入的源文件）
        params.setSaveUrl("/excel/");

        try {
            //解析excel
            List<UserDTO> list = ExcelImportUtil.importExcel(inputStream, UserDTO.class, params);
            return list;
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }finally {
            inputStream.close();
        }
    }
}

四、效果

1、导出效果

浏览器中get方式调用http://127.0.0.1:8080/export接口、导出excel文件
导出效果

2、导如效果

导入的数据
导入的数据
postman中post方式调用http://127.0.0.1:8080/import接口、导入excel文件
导如的效果
http://127.0.0.1:8080/export接口完整返回的结果

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
[
    {
        "name": "李四0",
        "age": null,
        "country": "陕北"
    },
    {
        "name": "李四1",
        "age": null,
        "country": "陕北"
    },
    {
        "name": "李四2",
        "age": null,
        "country": "陕北"
    },
    {
        "name": "李四3",
        "age": null,
        "country": "陕北"
    },
    {
        "name": "李四4",
        "age": null,
        "country": "陕北"
    },
    {
        "name": "李四5",
        "age": null,
        "country": "陕北"
    },
    {
        "name": "李四6",
        "age": null,
        "country": "陕北"
    },
    {
        "name": "李四7",
        "age": null,
        "country": "陕北"
    },
    {
        "name": "李四8",
        "age": null,
        "country": "陕北"
    },
    {
        "name": "李四9",
        "age": null,
        "country": "陕北"
    }
]