Vue+SpringBoot实现Excel模版上传、下载、Excel数据导出

环境准备：导入Maven依赖

        <!-- hutool -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>${hutool.version}</version>
        </dependency>
        <!--******** 处理excel文件使用poi包 start********-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.ooxml.version}</version>
        </dependency>
        <!--******** 处理excel文件使用poi包 end********-->

Excel 自定义列名模版下载

Excel 自定义列名模版下载效果图

 后台代码

 public static BaseResponse downloadTemplate(HttpServletResponse response) {
        try {
            String outFileName = "客户端账号管理" + System.currentTimeMillis() + ".xlsx";
            String ENCODING = "UTF-8";

            ExcelWriter writer = ExcelUtil.getWriter(true);

            // 创建一个Workbook
            HSSFWorkbook workbook = new HSSFWorkbook();

            // 创建一个sheet页
            HSSFSheet sheet = workbook.createSheet("sheet1");

            // 创建第一行
            HSSFRow row = sheet.createRow(0);

            // 创建单元格
            HSSFCell cell1 = row.createCell(0);
            HSSFCell cell2 = row.createCell(1);
            HSSFCell cell3 = row.createCell(2);
            HSSFCell cell4 = row.createCell(3);
         

            // 设置表头
            cell1.setCellValue("姓名");
            cell2.setCellValue("性别");
            cell3.setCellValue("年龄");
            cell4.setCellValue("爱好");
           

            // 获取我们的输出流
            final OutputStream output = response.getOutputStream();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + outFileName + ";filename*=utf-8''"
                    + URLEncoder.encode(outFileName, ENCODING));
            workbook.write(output);
            writer.flush(output, true);
            writer.close();
            // 这里可以自行关闭资源或者写一个关闭资源的工具类
            IoUtil.close(output);
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return BaseResponse.returnFault("用户导出失败!");
        }
    }

Element-UI前端编写

Element-UI 官网地址：Element - The world's most popular Vue UI framework

//模版下载
    downloadTemplate() {
      this.$axios({
        method: 'post',
        url: '/cdanm/client/downloadTemplate/',
        //data: params,
        responseType: 'blob',//定义接口响应的格式,很重要
        headers: {
          'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8',
        }
      }).then(response => {
        if (response.status == 200) {
          var blob = response.data;
          this.downloadFile('客户端账户管理Excel.xlsx', blob)  //直接通过blob数据下载
        } else {
          self.$message({
            type: 'error',
            message: "导出失败！"
          });
        }
      }).catch(err => {
        self.$message({
          type: 'error',
          message: "导出失败！"
        });
      })
    },

Excel 数据导出

效果图 

后台代码编写

Controller层编写

    /**
     * 客户端账号管理数据 Excel下载
     */
    @RequestMapping("/getUserTemplate/{userId}")
    public BaseResponse getClientTemplateDown(@PathVariable("userId") String userId, HttpServletResponse response) {
        try {
            List<UsersDto> list = UserService.selectUsersByUserId(userId);
            ExcelUtils.downLoadFile(list, response);
            //注：return null,是为了避免多次response，关闭了socke继续请求问题
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return BaseResponse.returnFault(e.getMessage());
        }
    }

工具类 ExcelUtils 方法 downLoadFile编写

 /**
     * Excel模版下载
     *
     * @param response
     */
    public static BaseResponse downLoadFile(List<ClientsDto> clientsList, HttpServletResponse response) {
        String outFileName = "客户端账号管理" + System.currentTimeMillis() + ".xlsx";
        String ENCODING = "UTF-8";

        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 1、通过工具类创建writer并且进行别名
        assembleWriter(writer);
        //2、封装Excel模版下载的数据
        List<ExcelInfo> excelInfoList = getExcelInfoList(clientsList);
        //3、准备将对象写入我们的 List
        writer.write(excelInfoList, true);
        try {
            // 获取我们的输出流
            final OutputStream output = response.getOutputStream();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + outFileName + ";filename*=utf-8''"
                    + URLEncoder.encode(outFileName, ENCODING));
            writer.flush(output, true);
            writer.close();
            // 这里可以自行关闭资源或者写一个关闭资源的工具类
            IoUtil.close(output);
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return BaseResponse.returnFault("用户导出失败!");
        }
    }


 private static void assembleWriter(ExcelWriter writer) {
        writer.addHeaderAlias("name", "名称");
        writer.addHeaderAlias("userName", "姓名");
        writer.addHeaderAlias("address", "所在地");
        writer.addHeaderAlias("loginName", "账号");
        writer.addHeaderAlias("password", "密码");
        writer.addHeaderAlias("clientStatus", "账号状态");
    }

    /**
     * 封装模版下载的数据
     */
    private static List<ExcelInfo> getExcelInfoList(List<UsersDto> usersList) {
        List<ExcelInfo> list = new ArrayList<>();
        ExcelInfo info = null;
        for (UsersDto dto : usersList) {
            info = new ExcelInfo();
            info.setName(dto.getName());
            info.setUserName(dto.getUserName());
            info.setAddress(dto.getAddress());
            info.setLoginName(dto.getLoginName());
            info.setPassword(dto.getPassword());
            info.setClientStatus(dto.getClientStatus());
            list.add(info);
        }
        return list;
    }

/**
 * @author by LMGD
 * @date 2021-10-20 16:14
 * @description 客户端账号管理数据下载模版
 */
public class ExcelInfo {

    private String name;

    private String userName;

    /**
     * 所在地
     */
    private String address;
    /**
     * 账号id
     */
    private String loginName;
    /**
     * 密码
     */
    private String password;
    /**
     * 账号状态
     */
    private String clientStatus;

    //省略get/set
}

Element-Ui 组件前端编写

 exportClients() {
      if (this.queryParams.userId== null || this.queryParams.userId== '') {
        this.$message({
          message: '选择【所属用户】才可导出!!',
          type: 'warning'
        })
        return;
      }

      const self = this;
      const params = JSON.parse(JSON.stringify(this.queryParams.userId));

      this.$axios({
        method: 'post',
        url: '/client/getUserTemplate/' + this.queryParams.userId,
        //data: params,
        responseType: 'blob',//定义接口响应的格式,很重要
        headers: {
          'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8',
        }
      }).then(response => {
        if (response.status == 200) {
          var blob = response.data;
          this.downloadFile('excel.xlsx', blob)  //直接通过blob数据下载
        } else {
          self.$message({
            type: 'error',
            message: "导出失败！"
          });
        }
      }).catch(err => {
        self.$message({
          type: 'error',
          message: "导出失败！"
        });

      })
    },
    downloadFile(fileName, blob) {
      let aLink = document.createElement('a');
      var blob = blob; //new Blob([content]);

      let evt = document.createEvent("HTMLEvents");
      evt.initEvent("click", true, true);//initEvent 不加后两个参数在FF下会报错  事件类型，是否冒泡，是否阻止浏览器的默认行为
      aLink.download = fileName;
      aLink.href = URL.createObjectURL(blob);

      aLink.dispatchEvent(new MouseEvent('click', { bubbles: true, cancelable: true, view: window }));//兼容火狐
    },

Excel 模版批量导入

Controller层编写

  /***
     * 客户端账号管理信息模版上传
     */
    @RequestMapping("/uploadClientTemplate")
    public BaseResponse uploadClientTemplate(@RequestParam("file") MultipartFile file, @RequestParam("projectId") String projectId) {
        try {
            if (StringUtils.isEmpty(projectId)) {
                return BaseResponse.returnFault("选择【所属项目】才可导入");
            }

            //往数据库插入数据
            BaseResponse baseResponse = ExcelUtils.importFile(file);
            List<Client> list = (List<Client>) baseResponse.getResponse().getData();
            clientService.saveClientList(list, projectId);
            return BaseResponse.returnSuccess();
        } catch (Exception e) {
            e.printStackTrace();
            return BaseResponse.returnFault(e.getMessage());
        }
    }

工具类  ExcelUtils 方法 importFile 编写

 /**
     * Excel文件导入
     *
     * @param file
     * @throws Exception
     */
    public static BaseResponse importFile(MultipartFile file) {
        try {
            String fileName = file.getOriginalFilename();
            String message = null;

            // 上传文件为空
            if (StringUtils.isEmpty(fileName)) {
                message = "没有导入文件";
                return BaseResponse.returnFault(message);
            }
            //上传文件大小为1000条数据
            if (file.getSize() > 1024 * 1024 * 10) {
                message = "上传失败: 文件大小不能超过10M!";
                return BaseResponse.returnFault(message);
            }
            // 上传文件名格式不正确
            if (fileName.lastIndexOf(".") != -1 && !".xlsx".equals(fileName.substring(fileName.lastIndexOf(".")))) {
                message = "文件名格式不正确, 请使用后缀名为.xlsx的文件";
                return BaseResponse.returnFault(message);
            }
            InputStream inputStream = file.getInputStream();
            ExcelReader excelReader = ExcelUtil.getReader(inputStream, "sheet1");
            assembleReader(excelReader);

            //转成集合,用于存储到数据库
            List<Client> populationList = excelReader.readAll(Client.class);
            return BaseResponse.returnSuccess(populationList);
        } catch (Exception e) {
            return BaseResponse.returnFault(e.getMessage());
        }
    }

private static void assembleReader(ExcelReader reader) {
        reader.addHeaderAlias("监测点代码", "pointId");
        reader.addHeaderAlias("监测点名称", "pointName");
        reader.addHeaderAlias("调查员名称", "clientName");
        reader.addHeaderAlias("所在地", "address");
        reader.addHeaderAlias("账户名称", "loginName");
        reader.addHeaderAlias("账户状态", "clientStatus");
    }

@TableName("T_CLIENT")
public class Client {
    /**
     * 客户端id
     */
    private String clientId;
    /**
     * 项目id
     */
    private String projectId;
    
    private String pointCode;

    private String clientName;
  
    private String address;
  
    private String loginName;
   
    private String password;
  
    private String clientStatus;

	//省略get/set 方法
}

前端编写

importClients(file) {
      let fd = new FormData()
      fd.append('file', file)
      fd.append('projectId', this.queryParams.projectId)
      uploadClientTemplate(fd).then(res => {
        if (res.response.messageHeader.code == 0) {
          this.searchClientsList()
          this.$message({
            type: 'success',
            message: res.response.messageHeader.desc
          });
        }
      })
      return true;
    },

<script>
import {uploadClientTemplate } from '@/api/api'
</script>

api.js 里面写的接口

// 客户端账户管理-导入
export function uploadClientTemplate(data) {
  return request({
    url: '/client/uploadClientTemplate',
    method: 'post',
    data
  })
}