vue+springboot,easyexcel的excel文件下载

1.效果展示

excel文件单一sheet,多sheet导出

本文主要介绍如何使用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>

4.后端代码展示

controller

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

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();
    }

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;
}

@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;
            }
        },
    });

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

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

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

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

2.如何操作easyexcel

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

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

  • 多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();
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值