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);// 创建一个WorkbookHSSFWorkbook 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、准备将对象写入我们的 Listwriter.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})
}