业务场景:很多项目中,都会有报表统计功能,同时为了更加人性化(产品的要求的)要加入查询结果导出功能,之前做过导出word模
板,这次是通过Hutool工具类将报表导出Excel。
一、引入Hutool及poi-ooxml相关的依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>${hutool.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>
导出实现简单版
二、实现代码
2.1 Service层
@Override
public ResultMsg exproList(Object object,HttpServletRequest request, HttpServletResponse response) {
//这里拿到你要导出的list 集合
ArrayList<Object> list = new ArrayList<>();
ExcelWriter writer = ExcelUtil.getWriter();
// 设置只导出有别名的字段
writer.setOnlyAlias(true);
writer.addHeaderAlias("name", "名称");
writer.addHeaderAlias("property", "属性");
writer.addHeaderAlias("type", "类别");
writer.addHeaderAlias("useObject", "作用对象");
writer.addHeaderAlias("calcMethod", "计算方法");
writer.addHeaderAlias("score", "分数");
writer.addHeaderAlias("operate", "操作");
writer.addHeaderAlias("userId", "操作人员");
writer.addHeaderAlias("createTime", "创建时间");
writer.write(list, true);
//out为OutputStream,需要写出到的目标流
//response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码
String fileName="示例名称";
try {
String userAgent = request.getHeader("user-agent");
// response.setHeader("Content-disposition", "attachment; filename=" + new String("部门账单明细".getBytes("gb2312"), "ISO8859-1"));
if (userAgent != null && userAgent.indexOf("Firefox") >= 0 ||
userAgent.indexOf("Chrome") >= 0 ||
userAgent.indexOf("Safari") >= 0) {
fileName= new String((fileName).getBytes(), "ISO8859-1");
} else {
fileName= URLEncoder.encode(fileName,"UTF8"); //其他浏览器
}
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
log.info("fileName : " + fileName);
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
ServletOutputStream out = null;
try {
out = response.getOutputStream();
writer.flush(out, true);
} catch (IOException e) {
log.error(e);
} finally {
// 关闭writer,释放内存
writer.close();
}
//此处记得关闭输出Servlet流
IoUtil.close(out);
return null;
}
三、参考阅览
常用工具类Hutool(一)的导入导出功能的实现
poi系列(二):通过poi、poi-ooxml读取写出excel
Hutool介绍(详)