经常遇到一些网站,尤其是管理员后台,有一个导出数据的功能。
在做暑期作业的时候,正需要这个功能,想起了之前用过的POI,查了一些资料发现POI吃内存比较严重,正好国内阿里改进了POI,形成了EasyExcel,并且已经开源。
官方说明文档
https://www.yuque.com/easyexcel/doc/easyexcel
功能预览
前端通过按钮,向后端接口发送post请求,实现文件下载。
接口实现
导入Maven依赖
1 2 3 4 5 6 7 8 9 10 11
| <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency> <dependency> <groupId>org.projectlombok </groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> </dependency>
|
Mapper.xml
1 2 3 4
| <select id="selectAll" resultMap="BaseResultMap"> select * from uusers </select>
|
Mapper接口声明
1 2 3
| public interface UusersMapper { List<Uusers> selectAll(); }
|
Uusers表实体类
继承自com.alibaba.excel.metadata.BaseRowModel
类,每个属性前添加@ExcelProperty
注解,声明导出Excel的表头。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| public class Uusers extends BaseRowModel { @ExcelProperty(value = "id",index = 0 ) private Integer id; @ExcelProperty(value = "用户名",index = 1 ) private String username; @ExcelProperty(value = "密码",index = 2 ) private String password; @ExcelProperty(value = "身份",index = 3 ) private String identity; @ExcelProperty(value = "手机号",index = 4 ) private String phone; @ExcelProperty(value = "性别",index = 5 ) private String gender; @ExcelProperty(value = "姓名",index = 6 ) private String name; @ExcelProperty(value = "年龄",index = 7 ) private Integer age; @ExcelProperty(value = "邮箱",index = 8 ) private String email; }
|
Service层
1 2 3 4 5 6
| @Autowired private UusersMapper uusersMapper; @Override public List<Uusers> getAll() { return uusersMapper.selectAll(); }
|
Controller层
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| @RequestMapping("/downloadUsers") @ResponseBody public void downloadUsers(HttpServletResponse response) throws Exception { List<Uusers> list = uusersService.getAll(); ServletOutputStream out = response.getOutputStream(); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true); String fileName = "用户信息表"; Sheet sheet = new Sheet(1, 0,Uusers.class); sheet.setAutoWidth(Boolean.TRUE); sheet.setSheetName("用户信息"); writer.write(list, sheet); response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xlsx"); writer.finish(); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); out.flush(); out.close(); }
|