本文要解决的问题

利用Spring 下载excel的参数传递问题
POST请求无法直接下载文档，报错excel打不开
下载接口response返回乱码问题

背景

最近项目用到了excel导出功能，在实际的运用中，场景覆盖了GET和POST请求。接入的过程中，遇到了不少坑：

坑1，乱码问题（GET和POST中均遇到了该问题）。

Response截图

返回头

坑2，post请求无法直接发送请求下载excel文档

原因：我们在后台改变了响应头的内容：
Content-Type: application/vnd.ms-excel
导致post请求无法识别这种消息头,导致无法直接下载。
解决方法：
改成使用form表单提交方式即可

具体实现

GET

js文件(重点：设置responseType)

// 全部下载
export const download_all = async sendData => {
    return await request({
        url: '/api/downloadAll',
        method: 'get',
        params: sendData,
        responseType: 'blob'    //这个很重要！！！
    });
};

vue文件

allDownload() {
  this.$confirm('此操作会将符合检索条件的全部结果下载, 是否继续?', '提示', {
    confirmButtonText: '确定',
    cancelButtonText: '取消',
    type: 'warning'
  }).then(() => {
    let startTime = null;
    let endTime = null;
    if (this.timeValue !== null && this.timeValue.length === 2) {
      startTime = this.timeValue[0];
      endTime = this.timeValue[1];
    }
    api.download_all({
      taskName: this.taskName,
      startTime: startTime,
      endTime: endTime
    }).then(res => {
      let fileName = res.headers['content-disposition'].split('=')[1];
      // 获取文件名
      let objectUrl = URL.createObjectURL(new Blob([res.data]));
      // 文件地址
      const link = document.createElement('a');
      link.download = fileName;
      link.href = objectUrl;
      link.click();
    });
  }).catch(() => {
    this.$message({
      type: 'info',
      message: '已取消下载'
    });
  });
}

POST

js文件

// 多选下载
export const download_multi = async sendData => {
    return await request({
        url: '/api/downloadMulti',
        method: 'post',
        data: sendData
    });
};

vue文件(采用form表单提交来完成)

multiDownload() {
  this.$confirm('此操作会将所勾选的全部结果下载, 是否继续?', '提示', {
    confirmButtonText: '确定',
    cancelButtonText: '取消',
    type: 'warning'
  }).then(() => {
    var form = document.createElement("form");
    form.style.display = 'none';
    form.action = '/api/downloadMulti';
    form.method = "post";
    document.body.appendChild(form);
    let params = {list: this.getIdList()};  //参数
    for(var key in params){
      var input = document.createElement("input");
      input.type = "hidden";
      input.name = key;
      input.value = params[key];
      form.appendChild(input);
    }
    form.submit();
    form.remove();
  }).catch((e) => {
    this.$message({
      type: 'info',
      message: '已取消下载'
    });
    console.log(e);
  });
}

Controller

@RequestMapping(value = "api/downloadMulti", method = {RequestMethod.POST})
public void downloadMulti(HttpServletRequest request, HttpServletResponse response) throws IOException {
    String list = request.getParameter("list");
    String[] split = list.split(",");
    List<Long> idList = new ArrayList<>();
    for (String id: split) {
        idList.add(Long.valueOf(id));
    }
    List<Result> resultList = service.getResult(idList);    //具体的service不展示，查数据库
    service.getExcel(resultList, response);
}

@RequestMapping(value = "api/downloadAll", method = {RequestMethod.GET})
public void downloadAll(@RequestParam(value = "taskName", required = false) String taskName,
                        @RequestParam(value = "startTime", required = false) String startTime,
                        @RequestParam(value = "endTime", required = false) String endTime,
                        HttpServletResponse response
) throws IOException {
    List<Result> resultList = service.getAllResult(taskName, startTime, endTime);  //具体的service不展示，查数据库
    service.getExcel(resultList, response);
}

Service

public void getExcel(List<Result> resultList, HttpServletResponse response) throws IOException {
    //表头数据
    String[] header = {"任务名称", "产生时间", "备注"};
    //声明一个工作簿
    HSSFWorkbook workbook = new HSSFWorkbook();
    //生成一个表格，设置表格名称为"结果表"
    HSSFSheet sheet = workbook.createSheet("结果");
    //设置表格列宽度为10个字节
    sheet.setDefaultColumnWidth(10);
    //创建第一行表头
    HSSFRow headrow = sheet.createRow(0);
    //遍历添加表头
    for (int i = 0; i < header.length; i++) {
        //创建一个单元格
        HSSFCell cell = headrow.createCell(i);
        //创建一个内容对象
        HSSFRichTextString text = new HSSFRichTextString(header[i]);
        //将内容对象的文字内容写入到单元格中
        cell.setCellValue(text);
    }
    int count = 1;
    //模拟遍历结果集，把内容加入表格
    for (Result result: resultList) {
        List<String> rowList = new ArrayList<>(13);
        rowList.add(result.getTaskName());
        rowList.add(result.getCreateTime());
        rowList.add(result.getComment());
        int subCount = 0;
        HSSFRow row = sheet.createRow(count);
        for (String str: rowList) {
            HSSFCell cell = row.createCell(subCount);
            HSSFRichTextString text = new HSSFRichTextString(str);
            cell.setCellValue(text);
            subCount ++;
        }
        count ++;
    }
    response.setCharacterEncoding("utf-8");
    //准备将Excel的输出流通过response输出到页面下载
    response.setContentType("application/vnd.ms-excel");
    //这后面可以设置导出Excel的名称，此例中名为result.xls
    response.setHeader("Content-disposition", "attachment;filename=result.xls");
    //刷新缓冲
    response.flushBuffer();
    //workbook将Excel写入到response的输出流中，供页面下载
    workbook.write(response.getOutputStream());
}

另外一种POST方式

export const download_sensitive_result = async sendData => {
    return await request({
        url: '/api/v3/dbscan/downloadSensitiveResult',
        method: 'post',
        data: sendData,
        responseType: 'blob'
    });
};
api.download_sensitive_result({
    aList: aList,
    appkey: this.value_appkey,
    dataStatus: this.dataStatus
}).then(res => {
    console.log(res.status);
    if (res.status === 200) {
        this.$message({
                message: '成功获取下载数据',
                type: 'success'
                            });
        let fileName = res.headers['content-disposition'].split('=')[1];
        // 获取文件名
        let objectUrl = URL.createObjectURL(new Blob([res.data]));
        // 文件地址
                            const link = document.createElement('a');
        link.download = fileName;
        link.href = objectUrl;
        link.click();
    } else if (res.status === 202) {
        this.$message({
                message: '没找到相关数据',
                type: 'info'
                            });
    } else {
        this.$message({
          message: '获取数据报错',
          type: 'error'
        });
    }
});
@RequestMapping(value = "downloadSensitiveResult", method = {RequestMethod.POST})
    public Result downloadSensitiveResult(@RequestBody String resultStr, HttpServletResponse response) {
        Map<String, Object> resultMap = JsonUtil.toMap(resultStr, String.class, Object.class);
        String aStr = JsonUtil.fromObject(resultMap.get("aList"));
        List<String> aList = JsonUtil.toList(aStr, String.class);
        String appkey = (String) resultMap.get("appkey");
        Boolean dataStatus = (Boolean) resultMap.get("dataStatus");
        Map<String, Object> result;
        result = aService.getSensitiveResultExcel(aList, appkey, dataStatus, response);
        if ((Integer) result.get("code") == 200) {
            return Result.success(result.get("msg"));
        } else if ((Integer) result.get("code") == 200){
            return Result.fail(202, result.get("msg").toString());
        } else {
            return Result.fail(500, result.get("msg").toString());
        }
    }

Springboot+vue+poi查询mysql下载excel（GET、POST爬坑）