java mvc 导出excel_SpringMVC 导出Excel功能实现与常见问题解决

这周做了一个导出功能，使用万能的POI工具.

一切顺利~

后台代码是这样子的：

@RequestMapping("/****")

public void exportForm(@RequestParam Long id) {

ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();

HttpServletResponse response = servletRequestAttributes.getResponse();

response.reset(); // 重置.

String fileName = System.currentTimeMillis() + ".xls";

try {

response.setContentType("application/vnd.ms-excel;charset=UTF-8");

response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "UTF-8"));

response.addHeader("filename", new String(fileName.getBytes(), "UTF-8"));

ServletOutputStream out = response.getOutputStream();

XXXService.loadStreamToRes(getUser(), id, out);

if (out != null) {

out.flush();

out.close();

}

} catch (Exception ex) {

log.error("创建下载输出流异常. ", ex);

} finally{

try{

if(response.getOutputStream() != null){

out.flush();

out.close();

}

} catch(IOException ex){}

}

}

下面是工具类的主要方法..

public static void exportExcel(Map headers, Collection dataset, OutputStream out,

String pattern) {

// 声明一个工作薄

HSSFWorkbook workbook = new HSSFWorkbook();

// 生成一个表格

HSSFSheet sheet = workbook.createSheet();

write2Sheet(sheet, headers, dataset, pattern);

try {

workbook.write(out);

} catch (IOException e) {

LOGGER.error(e.toString(), e);

}

}

就传入outputstream流，把生成的excel写到那边去，在浏览器响应下载.

贴一下万能的前端的代码

downTemplate (row) {

axios({

method: 'get',

headers: {'token': getCookie('token')},

url:'xxxx/xxxx', // 后台导出接口地址.

params: {

'id': row.id

},

responseType: 'blob'

}).then(res => this.downloads(res))

},

downloads(res){ // 这里用的elementui, 下载，不怎么会写，就模拟一个a连接点击进行下载操作

if(!res.data){

return

}

let filename = res.headers.filename;

let url = window.URL.createObjectURL(new Blob([res.data]))

let link = document.createElement('a')

link.style.display ='none'

link.href = url

link.setAttribute('download', `${filename}`)

document.body.appendChild(link)

link.click()

document.body.removeChild(link)

window.URL.revokeObjectURL(url)

}

这里有几个需要注意的点.

如果有全局拦截器，请看我

导出格式如果为 xlsx 设置如下

1. 设置内 容响应类型.

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

2. 设置Header

response.addHeader("Content-Disposition", "attachment;filename=xxxx" + ".xlsx");

3. POI创建Sheet对象为：

new XSSFWorkbook();

导出格式如果为 xls , 设置如下

1. response.setContentType("application/vnd.ms-excel");

2. response.addHeader("Content-Disposition", "attachment;filename=xxxx"+".xls");

3. POI创建Sheet对象为：

new HSSFWorkbook();

按照上面的设置就好了，我试过了用第一个contentType下载xls的话，打开excel是会报错的，我的是office2013版本.

打开xxxx.xls失败，因为格式文件或文件扩展名无效。请确认文件未损坏并且格式匹配xxxxxxxx