SpringBoot+vue前后端分离实现下载导出Excel功能，下载Excel为undefined，null，乱码...等坑

1. 功能描述

前后端分离项目，后端基于SpringBoot，前端基于vue，需要实现点击按钮导出Excel的功能。

2.后端代码

2.1.引入maven依赖

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.17</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml-schemas</artifactId>
   <version>3.17</version>
</dependency>

2.2.后端controller层代码

@RestController
@RequestMapping(value = "/excel")
public class ExcelController {
    @Autowired
    private ExcelService excelService;
    @RequestMapping(value = "/downloadExcel", method = RequestMethod.POST)
    public void downloadExcel(HttpServletResponse response){
        excelService.downloadExcel(response);
    }
}

2.3.后端Service层代码

确认reponse hesder设置了

 response.setHeader("Content-Disposition", "attachment; filename=" + filename);//设置文件头
 response.setContentType("application/vnd.ms-excel");//设置内容类型为excel

并返回文件流

hssfWorkbook.write(response.getOutputStream());

Service层代码

@Service
public class ExcelServiceImpl implements ExcelService {
    @Override
    public void downloadExcel(HttpServletResponse response){
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet hssfSheet =hssfWorkbook.createSheet("sheet");
        for(int i=0;i<3;i++){
            HSSFRow row = hssfSheet.createRow(i);
            for(int j=0;j<3;j++){
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        try{
            String filename="myExcel.xls";
            filename=new String(filename.getBytes(),"UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + filename);//设置文件头
            response.setContentType("application/vnd.ms-excel");//设置内容类型为excel
            response.flushBuffer();
            hssfWorkbook.write(response.getOutputStream());
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

3.前端vue代码

3.1.下载按钮

<el-button type="primary" @click="downloadExcel">下载Excel</el-button>

3.2.axios请求

downloadExcel(){
      let params = {};
      params.data={};
      axios({
        method:'post',
        url:'http://localhost:/8080/excel/downloadExcel',
        data:{},
        responseType:'blob'
      }).then(response=>{
        let url = window.URL.createObjectURL(new Blob([response.data]));
        let link = document.createElement('a');
        link.style.display = 'none';
        link.href = url;
        link.setAttribute('download',  'excel.xls')
        document.body.appendChild(link)
        link.click()
      }).catch((error)=>{})
    }

4.更简单的前端访问方式

4.1.基于a标签

<a class="download" href="http://localhost:/8080/excel/downloadExcel' download='myexcel.xls' title='excel下载’>excel下载</a>

4.2.form表单

<form action="" method="POST">
      <input type="submit" value="excel下载">
</form>

5.可能出现的坑

1. 如果你的项目中返回报文采用ResponseWrapper的方式进行封装，是不能正确获得文件流的，controller层downloadExcel方法只能返回void，采用默认的HttpServletResponse。
2. 在本地测试时我们可能会发现：虽然axios设置了responseType: ‘blob’，但是拿到的res.data并不是Blob类型，而是一大串乱码，这是因为Vue项目中可能使用了mockjs，mock模块会影响原生的ajax请求，使得服务器返回的blob类型变成乱码。在Vue的main.js中把require(’./mock’)注释掉即可。