vue+springboot，easyexcel的excel文件下载

1.效果展示

本文主要介绍如何使用easyexcel ，配合前端导出Excel文件。同时提供Excel的两种导出形式：单一sheet，多sheet。

1.1 前端界面

1.2 下载的excel

单一sheet文件

多sheet文件

2.思路介绍

前端：直接通过window.location.href = url，跳转到对应url，借助浏览器直接下载文件。这样前端就无需做出额外的操作，也减少遇到bug的几率

后端：通过response返回的流数据，借助easyexcel的api:ExcelWriterBuilder write(OutputStream outputStream, Class head)返回流数据

3.前端代码展示

<div id="app">
    <el-row>
        <el-col :span="4">
            年级:<el-input v-model="year" placeholder="Input 1" disabled></el-input>
        </el-col>
        <el-col :span="4">
            组别:<el-input v-model="group" placeholder="Input 2" disabled></el-input>
        </el-col>
        <el-col :span="4">
            第几周:<el-input v-model="week" placeholder="请输入导出的周"></el-input>
        </el-col>
        <el-col :span="2">
            <el-button type="primary" @click="exportExcel(week)">导出</el-button>
        </el-col>
    </el-row>
    <el-row>
        <el-button type="primary" @click="exportExcel('全部')">全部导出</el-button>
    </el-row>
</div>

<script>
    new Vue({
        el: "#app",
        data: {
            year: '2023',
            group: 'java',
            week: '3',
        },
        methods: {
            exportExcel(week) {
                var year = this.year;
                var group = this.group;
                var url = address + `/sign/excel?year=${year}&group=${group}&week=${week}`;
                window.location.href = url;
            }
        },
    });
</script>
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

4.后端代码展示

controller

    /**
     * 返回excel导出数据
     */
    @GetMapping("/excel")
    public void getExcel(@RequestParam Map<String, Object> params, HttpServletResponse response) throws IOException {
        signInfoService.getExcel(params, response);
    }
1
2
3
4
5
6
7

service

    @Override
    public R getExcel(Map<String, Object> params, HttpServletResponse response) throws IOException {
        Object year = params.get("year");
        Object week = params.get("week");
        Object group = params.get("group");
        if (year == null) {
            year = "2023";
        }
        if (group == null) {
            group = "java";
        }
        // 请直接用浏览器或者用postman, 其它的入swagger容易报错
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");

            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(group + "组-" + year + "级-" + week + "周" + "签到记录", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

            // 判断是否需要导出全部的sheet
            String s = (String) week;
            if (s.equals("全部")) {
                // 指定文件
                ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), SignListDto.class).autoCloseStream(Boolean.FALSE).build();
                // 循环添加sheet
                for (int i = 2; i <= len; ++i) {
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, "第" + i + "周").build();
                    // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                    List<SignListDto> data = this.getSignListDto(year, i, group);
                    excelWriter.write(data, writeSheet);
                }
                // 关闭流数据
                excelWriter.finish();
            }else {
            	// 导出一个sheet文件
                EasyExcel.write(response.getOutputStream(), SignListDto.class).autoCloseStream(Boolean.FALSE).sheet("第" + week + "周")
                        .doWrite(this.getSignListDto(params));
            }
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = MapUtils.newHashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(mapper.writeValueAsString(map));
        }
        return R.ok();
    }
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

dto

@Data
public class SignListDto {
    @ExcelProperty("用户id")
    private Integer id;
    @ExcelProperty("用户姓名")
    private String name;

    @ExcelProperty({"星期一", "签到"})
    private String up1;
    @ExcelProperty({"星期一", "签退"})
    private String out1;

    @ExcelProperty({"星期二", "签到"})
    private String up2;
    @ExcelProperty({"星期二", "签退"})
    private String out2;

    @ExcelProperty({"星期三", "签到"})
    private String up3;
    @ExcelProperty({"星期三", "签退"})
    private String out3;

    @ExcelProperty({"星期四", "签到"})
    private String up4;
    @ExcelProperty({"星期四", "签退"})
    private String out4;

    @ExcelProperty({"星期五", "签到"})
    private String up5;
    @ExcelProperty({"星期五", "签退"})
    private String out5;

    @ExcelProperty({"星期六", "签到"})
    private String out6;
    @ExcelProperty({"星期六", "签退"})
    private String up6;

    @ExcelProperty({"星期日", "签到"})
    private String up7;
    @ExcelProperty({"星期日", "签退"})
    private String out7;
}
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

@ExcelProperty("用户id")这个注解可以实现Java实体类的字段和excel的列数据进行对应，数据填充时，Java数据就会赋值到对应列中
@ExcelProperty({"星期日", "签退"})这样的写法可以实现excel单元格的合并，具体效果如下

5.核心代码解释

前端
前端代码的核心只有一个：如何把文件下载的操作甩锅给浏览器，只要把活甩给浏览器，前端就不需要编写额外代码

new Vue({
        el: "#app",
        data: {...},
        methods: {
            exportExcel(week) {
                var year = this.year;
                var group = this.group;
                var url = address + `/sign/excel?year=${year}&group=${group}&week=${week}`;
				// 核心代码
                window.location.href = url;
            }
        },
    });
1
2
3
4
5
6
7
8
9
10
11
12
13

后端
后端的核心有2。1：如何传递流数据 2：如何操作easyexcel

1.如何传递流数据
通过HttpServletResponse response获取

response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.getOutputStream();
1
2
3

有了通向前端的流管道，我们就只需要操作easyexcel，制作excel文件

2.如何操作easyexcel

• 单一sheet文件

// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), SignListDto.class).autoCloseStream(Boolean.FALSE).sheet("第" + week + "周")
                        .doWrite(this.getSignListDto(params));
1
2
3

其中，SignListDto.class，指定这个类去写excel。this.getSignListDto(params)返回excel所需的list数据：List

• 多sheet文件

// 创建ExcelWriter, 用于操作excel
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), SignListDto.class).autoCloseStream(Boolean.FALSE).build();
for (int i = 2; i <= len; ++i) {
	// 创建sheet
	WriteSheet writeSheet = EasyExcel.writerSheet(i, "第" + i + "周").build();
	// 查询数据
	List<SignListDto> data = this.getSignListDto(year, i, group);
	// 将数据写入sheet中
	excelWriter.write(data, writeSheet);
}
// 关闭IO
excelWriter.finish();
1
2
3
4
5
6
7
8
9
10
11
12