抱歉,您的浏览器无法访问本站
本页面需要浏览器支持(启用)JavaScript
了解详情 >

经常遇到一些网站,尤其是管理员后台,有一个导出数据的功能。

在做暑期作业的时候,正需要这个功能,想起了之前用过的POI,查了一些资料发现POI吃内存比较严重,正好国内阿里改进了POI,形成了EasyExcel,并且已经开源。

官方说明文档

https://www.yuque.com/easyexcel/doc/easyexcel

功能预览

前端通过按钮,向后端接口发送post请求,实现文件下载。

image
image

接口实现

导入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 名称
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();
}

评论