经常遇到一些网站，尤其是管理员后台，有一个导出数据的功能。
在做暑期作业的时候，正需要这个功能，想起了之前用过的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();
}
|
