Springboot+Vue+Mybatis-Plus+Easyexcel实现文件导入+导出的excel单元格下拉列表

引言

文件的导入与导出功能扮演着至关重要的角色，特别是在处理大量数据和复杂的表格时。通过整合Spring Boot、Vue、Mybatis-Plus和Easyexcel等先进技术，我们可以构建一个高效、灵活的文件处理系统。其中，Excel作为广泛使用的电子表格软件，其单元格下拉列表功能对于数据录入和校验尤为有用。本文将探讨如何利用这些技术实现文件导入导出，并重点关注Excel单元格下拉列表的模拟与实现，从而提升数据处理的便捷性和准确性。

Excel导出

EasyExcel官方文档
官方文档本身写的非常详细，有需要可以进行查阅。

后端导出

先进行导入依赖

        <dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>3.3.3</version>
		</dependency>

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

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

@Data
@TableName("cp_leave_info")
public class CpLeaveEntity{
//    请假的id
//    @TableField(value = "id")
    @ExcelIgnore
    private Integer id;
//   类型
    @ExcelProperty(value = "类型", converter = TypeLeaveConverter.class)
    private Integer type;
//    开始时间
    @ExcelProperty(value = "开始时间")
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private Date beginTime;
//    结束时间
    @ExcelProperty(value = "结束时间")
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private Date endTime;
//    时长
    @ExcelProperty(value = "时长")
    private Double duration;
//    请假理由
    @ExcelProperty(value = "请假理由")
    private String reason;
//    图片
    @ExcelProperty(value = "图片")
    private String pictures;
//    单位
    @ExcelProperty(value = "单位")
    private String unit;
    /*
    审批结果（通过 ：1  ；   未通过： 0  ：没有：2）
    * */
    @ExcelProperty(value = "审批结果", converter = ResultLeaveConverter.class)
    private Integer examineResult;
//    审批状态
    @ExcelProperty(value = "审批状态", converter = StatusLeaveConverter.class)
    private Integer examineStatus;
//    创建时间
    @ExcelProperty(value = "创建时间")
    private Date createTime;
//    更新时间
    @ExcelProperty(value = "更新时间")
    private Date updateTime;
//    拒绝理由
    @ExcelProperty(value = "拒绝理由")
    private String reasonsForRefusal;
    /**
     * 获取登录信息的用户名
     */
    @ExcelProperty(value = "用户名")
    private String username;

    //逻辑删除(删除： -1， 未删除： 0)
    @TableLogic
    @ExcelIgnore
    private Integer isDelete;

}

controller层

文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
这种方法是将Excel文件的生成过程放在后端进行。前端发起一个请求到后端，后端处理数据并生成Excel文件，然后将文件返回给前端进行下载。
这种方法的优点是可以将数据处理的压力放在后端，前端只需要处理请求和下载文件的逻辑。

 /**
     * 导出excel文件
     * @param response 响应数据
     */
    @PostMapping("exportExcel")
    @RequiresPermissions("cp:leave:export")
    public void exportExcel(HttpServletResponse response) throws IOException {
        try {
            String fileName = URLEncoder.encode("请假信息", "UTF-8");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String headerValue = "attachment; filename*=UTF-8''" + fileName + ".xlsx";
            response.setHeader("Content-Disposition", headerValue);

            List<CpLeaveEntity> dataList = cpLeaveService.getData();
            
            EasyExcel.write(response.getOutputStream(), CpLeaveEntity.class)
                    // 导出Excel时在此处注册handler
                    .registerWriteHandler(new CustomSheetWriteHandler(sysDictService))
                    .sheet("用户数据")
                    .doWrite(dataList);
        }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));
        }
    }

自定义转换器

在EasyExcel中，如果想实现枚举类型到字符串类型转换（例如examineResult属性：0 -> 未通过，1 -> 已通过，2 -> 无状态），需实现Converter接口来自定义转换器，下面为自定义GenderConverter性别转换器代码实现：

public class ResultLeaveConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) {
        return ResultEnum.convert(context.getReadCellData().getStringValue()).getValue();
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) {
        return new WriteCellData<>(ResultEnum.convert(context.getValue()).getDescription());
    }

}

@Getter
@AllArgsConstructor
public enum ResultEnum {

    /**
     * 未通过
     */
    NOTPASS(0, "未通过"),

    /**
     * 已通过
     */
    ALREADYPASSED(1, "已通过"),

    /**
     * 无状态
     */
    STATELESS(2, "无状态"),
    /**
     * 未知
     */
    UNKNOWN(3, "未知");

    private final Integer value;

    @JsonFormat
    private final String description;

    public static ResultEnum convert(Integer value) {
        return Stream.of(values())
                .filter(bean -> bean.value.equals(value))
                .findAny()
                .orElse(UNKNOWN);
    }

    public static ResultEnum convert(String description) {
        return Stream.of(values())
                .filter(bean -> bean.description.equals(description))
                .findAny()
                .orElse(UNKNOWN);
    }

}

字典

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

public class CustomSheetWriteHandler implements SheetWriteHandler {

    @Autowired
    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> leaveTypeList = new ArrayList<>();
        List<String> examineResultList = new ArrayList<>();
        List<String> examineStatusList = new ArrayList<>();
        List<SysDictEntity> leaveType = sysDictService.maintenanceList("leaveType");
        List<SysDictEntity> examineResult = sysDictService.maintenanceList("examineResult");
        List<SysDictEntity> examineStatus = sysDictService.maintenanceList("examineStatus");

        for (SysDictEntity sysDictEntity : leaveType) {
            if (sysDictEntity.getSort()!= null){
                leaveTypeList.add(sysDictEntity.getName());
            }
        }

        for (SysDictEntity sysDictEntity : examineResult) {
            if (sysDictEntity.getSort()!= null){
                examineResultList.add(sysDictEntity.getName());
            }
        }

        for (SysDictEntity sysDictEntity : examineStatus) {
            if (sysDictEntity.getSort()!= null){
                examineStatusList.add(sysDictEntity.getName());
            }
        }

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

        // 获取第一个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);
        }
    }


}

 service层

直接使用mybatis-plus的方法

    @Override
    public List<CpLeaveEntity> getData() {
        return cpLeaveDao.selectList(null);
    }

测试

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

发送请求就可以直接下载文件 

前端导出

  // 导出
    exportExcel(){
      this.$http({
          url: this.$http.adornUrl("/cp/leave/exportExcel"),
          method: "post",
          //设置响应类型（重要)
          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();
        });
    },

后端导入

  /**
     * 导入
     */
    @PostMapping("importExcel")
    @RequiresPermissions("cp:leave:import")
    public R importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        try {
            List<CpLeaveEntity> userList = EasyExcel.read(file.getInputStream())
                    .head(CpLeaveEntity.class)
                    .sheet()
                    .doReadSync();
            cpLeaveService.saveBatch(userList);
            return R.ok().put("userList", userList);
        } catch (IOException e) {
            return R.error("失败");
        }
    }

前端导入

          <el-upload action="http://localhost:80/wedu/cp/leave/importExcel"
          :headers="tokenInfo"
          style="display: inline-block;margin-left: 10px" 
          :show-file-list="false" 
          :on-success="importExcel">
          <el-button v-if="isAuth('cp:leave:import')" type="success"  round>导入</el-button>
          </el-upload>

  // 导入
      importExcel(data, file, fileList){
        if(data && data.code === 0){
          this.$message({
                message: "导入成功",
                type: "success",
                duration: 1500,
                onClose: () => {
                  this.getLeaveData();
                },
              });
        this.load(1)
      }else{
         this.$message({
                message: "导入失败",
                type: "error",
                duration: 1500,
                onClose: () => {
                  this.getLeaveData();
                },
              });
      }
      },