springboot+vue+mysql+easyexcel实现文件导出+导出的excel单元格添加下拉列表

Excel导出

EasyExcel官方文档
官方文档本身写的非常详细，我就是根据官方文档内的写Excel里web中的写实现的导出

后端

对象

需要写一个实体类
其中涉及到一些用到的EasyExcel的注解

• @ColumnWidth(20) 列宽设为20，自定义的，放在实体类上面是整个实体类的每个字段的列宽都为20，放在单个字段上则是改字段为20
• @ContentRowHeight(10) 设置数据行的行高为10个单位
• @HeadRowHeight(20) 设置Excel表格的表头行高度为20
• @ExcelIgnore 在导出文件的时候忽略该字段列
• @ExcelProperty("xxx") 导出文件原本标题行字段是数据库内写的字段，加上这个注解就会变为括号内写入的文字
• @DateTimeFormat("yyyy-MM-dd") 设置日期格式，自定义

@Data
@ColumnWidth(20)
public class WarnVo {
    //传输给前端展示
    //id
    @ExcelIgnore
    private Long id;
    //车间
    @ExcelProperty("车间")
    private String workshops;
    //设备编号
    @ExcelProperty("设备编号")
    private String deviceNumber;
    //设备名称
    @ExcelProperty("设备名称")
    private String deviceName;
    //设备型号
    @ExcelProperty("设备型号")
    private String deviceModel;
    //维保到期时间
    @ExcelProperty("维保到期时间")
    @DateTimeFormat("yyyy-MM-dd")
    private Date tainEndTime;
    //预警时间
    @ExcelProperty("预警时间")
    @DateTimeFormat("yyyy-MM-dd")
    private Date warnTime;
    //状态（是否生成预警信息 是否被保养）
    @ExcelIgnore
    private Integer status;
}
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

controller层

• copy了官网的导出代码，做了一些改变，比如导出的一个字段workshops是字典（不了解的可以点击vue实现查询搜索框下拉字典了解一下，如果没有做字典的可以忽略这一段），只能选择字典项进行输入，在controller层获取到要导出的数据以及字典集合，进行一个匹配判断然后置换

    //文件导出
    /**
     * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
     * 这种方法是将Excel文件的生成过程放在后端进行。前端发起一个请求到后端，后端处理数据并生成Excel文件，然后将文件返回给前端进行下载。
     * 这种方法的优点是可以将数据处理的压力放在后端，前端只需要处理请求和下载文件的逻辑。
     * @since 2.1.1
     */
    @GetMapping("/download")
    public void download(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            this.setExcelResponseProp(response,"保养预警");
            List<WarnVo> warnVos = iTainWarnService.listAllWarn();
            List<SysDictEntity> workList = sysDictService.maintenanceList("chejian");
            for (WarnVo warnVo : warnVos) {
                for (SysDictEntity sysDictEntity : workList) {
                    if (sysDictEntity.getValue().compareTo(warnVo.getWorkshops())==0){
                        warnVo.setWorkshops(sysDictEntity.getName());
                    }
                }
            }
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), WarnVo.class)
                    // 导出Excel时在此处注册handler
                    .registerWriteHandler(new CustomSheetWriteHandler(sysDictService))
                    .autoCloseStream(Boolean.FALSE)
                    .sheet("保养预警")
                    .doWrite(warnVos);
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }
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

• 字典项做了下拉框，需要在导出Excel的时候注册handler，是一个监听器，这个监听器需要自己手动装配构造器，因为这个监听器没被扫描到，不属于可自动装配范围
  • 重写afterSheetCreate方法，先写一个空的集合，再获取字典集合
  • 遍历字典集合，根据数据库的字典特性，拿到字典项的某个我们需要展示出来的字段，比如这里就要展示字典项的name，所以把字典项的name全放入上面写的空集合中
  • 再设置下拉选项单元格列的位置，key：下拉选项要放到哪个单元格，比如A列的单元格那就是0，C列的单元格，那就是2，value：key对应的那个单元格下拉列表里的数据项，比如这里就是下拉选项放入字典项name的集合，其余内容不变，copy即可

package com.wedu.modules.tain.listener;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.wedu.modules.sys.entity.SysDictEntity;
import com.wedu.modules.sys.service.SysDictService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

public class CustomSheetWriteHandler implements SheetWriteHandler {

    private SysDictService sysDictService;


    public CustomSheetWriteHandler(SysDictService sysDictService) {
        this.sysDictService = sysDictService;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * 想实现Excel引用其他sheet页数据作为单元格下拉选项值，
     * 需要重写该方法
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 构造样例数据，该数据可根据实际需要，换成业务数据
        // 实际数据可通过构造方法，get、set方法等由外界传入
        List<String> selectDataList = new ArrayList<>();
        List<SysDictEntity> list = sysDictService.maintenanceList("chejian");
        for (SysDictEntity sysDictEntity : list) {
            if (sysDictEntity.getSort()!=null){
                selectDataList.add(sysDictEntity.getName());
            }
        }

        // 构造下拉选项单元格列的位置，以及下拉选项可选参数值的map集合
        // key：下拉选项要放到哪个单元格，比如A列的单元格那就是0，C列的单元格，那就是2
        // value：key对应的那个单元格下拉列表里的数据项，比如这里就是下拉选项1..100
        Map<Integer, List<String>> selectParamMap = new HashMap<>();
        selectParamMap.put(0, selectDataList);

        // 获取第一个sheet页
        Sheet sheet = writeSheetHolder.getCachedSheet();
        // 获取sheet页的数据校验对象
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 获取工作簿对象，用于创建存放下拉数据的字典sheet数据页
        Workbook workbook = writeWorkbookHolder.getWorkbook();

        // 迭代索引，用于存放下拉数据的字典sheet数据页命名
        int index = 1;
        for (Map.Entry<Integer, List<String>> entry : selectParamMap.entrySet()) {

            // 设置存放下拉数据的字典sheet，并把这些sheet隐藏掉，这样用户交互更友好
            String dictSheetName = "dict_hide_sheet" + index;
            Sheet dictSheet = workbook.createSheet(dictSheetName);
            // 隐藏字典sheet页
            workbook.setSheetHidden(index++, true);

            // 设置下拉列表覆盖的行数，从第一行开始到最后一行，这里注意，Excel行的
            // 索引是从0开始的，我这边第0行是标题行，第1行开始时数据化，可根据实
            // 际业务设置真正的数据开始行，如果要设置到最后一行，那么一定注意，
            // 最后一行的行索引是1048575，千万别写成1048576，不然会导致下拉列表
            // 失效，出不来
            CellRangeAddressList infoList = new CellRangeAddressList(1, 1048575, entry.getKey(), entry.getKey());
            int rowLen = entry.getValue().size();
            for (int i = 0; i < rowLen; i++) {
                // 向字典sheet写数据，从第一行开始写，此处可根据自己业务需要，自定
                // 义从第几行还是写，写的时候注意一下行索引是从0开始的即可
                dictSheet.createRow(i).createCell(0).setCellValue(entry.getValue().get(i));
            }

            // 设置关联数据公式，这个格式跟Excel设置有效性数据的表达式是一样的
            String refers = dictSheetName + "!$A$1:$A$" + entry.getValue().size();
            Name name = workbook.createName();
            name.setNameName(dictSheetName);
            // 将关联公式和sheet页做关联
            name.setRefersToFormula(refers);

            // 将上面设置好的下拉列表字典sheet页和目标sheet关联起来
            DataValidationConstraint constraint = helper.createFormulaListConstraint(dictSheetName);
            DataValidation dataValidation = helper.createValidation(constraint, infoList);
            sheet.addValidationData(dataValidation);
        }
    }
}

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
90
91
92
93
94
95
96
97
98
99
100
101

测试

后端写好了可以使用postman进行测试

token失效可以去网页上f12找一个有效的token复制一下

发送请求并且下载，就可以导出文件进行查看了

前端

template

<el-button type="primary" icon="el-icon-share" @click="download()">一键导出</el-button>
1

methods

设置相应类型非常重要，不设置导出的文件也不能用
link.setAttribute("download", "保养预警.xlsx") 这句为导出文件命名

//文件导出
    download() {
      this.$http({
        url: this.$http.adornUrl("/tain/warn/download"),
        method: "get",
        //设置响应类型（重要
        responseType: "blob",
      }).then((response) => {
        const url = window.URL.createObjectURL(new Blob([response.data]));
        const link = document.createElement("a");
        link.href = url;
        link.setAttribute("download", "保养预警.xlsx");
        document.body.appendChild(link);
        link.click();
      });
    },
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

前后端都写完后，就可以启动项目，在前端页面点击下载按钮，发送请求成功就可以成功下载文件

前端发起一个请求到后端，后端处理数据并生成Excel文件，然后将文件返回给前端进行下载。这种方法的优点是可以将数据处理的压力放在后端，前端只需要处理请求和下载文件的逻辑。缺点是需要前后端的配合，增加了后端的工作量。