这周做了一个导出功能,使用万能的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