赞
踩
本篇博客主要记录一下,springBoot + vue前后端分离的项目,如何实现Excel导出功能,项目代码可以参考之前的一篇博文:springBoot+shiro+vue的学生管理系统(二、总体功能及登录功能)
1.添加POI依赖:
- <!-- excel导出需要的依赖POI-->
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>3.6</version>
- <exclusions>
- <exclusion>
- <groupId>javax.servlet</groupId>
- <artifactId>servlet-api</artifactId>
- </exclusion>
- <exclusion>
- <groupId>log4j</groupId>
- <artifactId>log4j</artifactId>
- </exclusion>
- </exclusions>
- </dependency>

这里同时排除了多余的依赖,因为项目中已经引入了,不然就是重复引入了。
2.Excel导出工具类:
- package com.qxf.utils;
-
- import org.apache.poi.hssf.usermodel.*;
-
- /**
- * @Auther: qiuxinfa
- * @Date: 2020/4/23
- * @Description: com.qxf.utils
- */
- public class ExcelUtil {
- /**
- * 导出Excel
- * @param sheetName sheet名称
- * @param title 标题
- * @param values 内容
- * @return
- */
- public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String [][]values){
-
- // 第一步,创建一个HSSFWorkbook,对应一个Excel文件
- HSSFWorkbook wb = new HSSFWorkbook();
-
- // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
- HSSFSheet sheet = wb.createSheet(sheetName);
-
- // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
- HSSFRow row = sheet.createRow(0);
-
- // 第四步,创建单元格样式,并设置值表头 设置表头居中
- HSSFCellStyle style = wb.createCellStyle();
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
-
- //声明单元格
- HSSFCell cell = null;
-
- //创建标题
- for(int i=0;i<title.length;i++){
- //创建一个单元格
- cell = row.createCell(i);
- //给单元格赋值
- cell.setCellValue(title[i]);
- //给单元格设置样式
- cell.setCellStyle(style);
- }
-
- //创建内容
- if (values != null && values[0].length > 0){
- for(int i=0;i<values.length;i++){
- //从第二行开始创建数据填充的行,下标为1
- row = sheet.createRow(i + 1);
- for(int j=0;j<values[i].length;j++){
- //将内容按顺序赋给对应的列对象
- row.createCell(j).setCellValue(values[i][j]);
- }
- }
- }
- return wb;
- }
- }

3.接口有一个前缀/user,没有贴出来了:
- /**
- * 导出报表,这里get和post请求复用了该方法,仅仅是为了测试
- *
- * @return
- */
- @RequestMapping(value = "/export")
- @ResponseBody
- public void export(@RequestBody(required = false) User user,String username,HttpServletResponse response) throws Exception {
- if (user ==null && !StringUtils.isEmpty(username)){
- //GET 请求的参数
- user = new User();
- user.setUsername(username);
- }
- //获取数据
- List<User> list = userService.findAllUser(user);
-
- //excel标题
- String[] title = {"姓名", "邮箱", "创建时间", "最近登录时间","角色","是否可用"};
-
- //excel文件名
- String fileName = System.currentTimeMillis() + ".xls";
-
- //sheet名
- String sheetName = "用户信息";
-
- //没有数据就传入null吧,Excel工具类有对null判断
- String [][] content = null;
-
- if (list != null && list.size() > 0){
- content = new String[list.size()][title.length];
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
- for (int i = 0; i < list.size(); i++) {
- content[i] = new String[title.length];
- User obj = list.get(i);
- content[i][0] = obj.getUsername();
- content[i][1] = obj.getEmail();
- content[i][2] = obj.getCreateTime() == null ? "" : sdf.format(obj.getCreateTime());
- content[i][3] = obj.getLastLoginTime() == null ? "": sdf.format(obj.getLastLoginTime());
- content[i][4] = obj.getRoleName();
- content[i][5] = obj.getEnable()==1 ? "是" : "否";
- }
- }
-
- //创建HSSFWorkbook
- HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content);
-
- //响应到客户端
- try {
- fileName = new String(fileName.getBytes(), "UTF-8");
- response.setContentType("application/vnd.ms-excel;charset=utf-8");
- response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
- OutputStream os = response.getOutputStream();
- wb.write(os);
- os.flush();
- os.close();
- } catch (Exception e) {
- e.printStackTrace();
- }
- }

service层之类的代码就不贴了
4.前端代码:
两种导出方式:
- <el-form-item>
- <el-button type="primary" plain @click="exportUser">blob导出</el-button>
- </el-form-item>
- <el-form-item>
- <el-button type="infor" round @click="exportUserByA">a标签导出</el-button>
- </el-form-item>
具体的js方法:
- // 导出用户,通过blob
- exportUser () {
- axios({
- method: 'post',
- url: 'http://192.168.43.152:8089/user/export',
- data: {
- username: this.filters.keyword
- },
- responseType: 'blob'
- }).then((res) => {
- console.log(res)
- const link = document.createElement('a')
- let blob = new Blob([res.data],{type: 'application/vnd.ms-excel'});
- link.style.display = 'none'
- link.href = URL.createObjectURL(blob);
- console.log("href:"+link.href)
- let num = ''
- for(let i=0;i < 10;i++){
- num += Math.ceil(Math.random() * 10)
- }
- link.setAttribute('download', num + '.xls')
- document.body.appendChild(link)
- link.click()
- document.body.removeChild(link)
- }).catch(error => {
- console.log(error)
- })
-
- },
- // 导出用户,通过a标签
- exportUserByA () {
- let username = this.filters.keyword
- const link = document.createElement('a')
- link.href = "http://192.168.43.152:8089/user/export?username="+username
- document.body.appendChild(link)
- link.click()
- document.body.removeChild(link)
-
- },

其实用a标签简单很多
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。