依赖
<!--hutool-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
controller层代码
//导出文件
@GetMapping("/export")
public void export(HttpServletResponse response)throws Exception{
//数据库查出所有数据
List<Role>list=roleService.list();
//通过writer工具类写出到浏览
ExcelWriter writer = ExcelUtil.getWriter();
//设置别名
writer.addHeaderAlias("name","名称");
writer.addHeaderAlias("description","描述");
//一次性写出list内的对象到excel,使用默认格式,强制输出标题
writer.write(list,true);
//设置浏览器响应的格式
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
String fileName= URLEncoder.encode("用户信息","UTF-8");
response.setHeader("Content-Disposition","attachment;filename=test.xls");
ServletOutputStream out=response.getOutputStream();
writer.flush(out,true);
out.close();
writer.close();
}
//导入
@PostMapping("/import")
public List<Role> imp(MultipartFile file) throws Exception{
System.out.println("我进来了********");
InputStream inputStream=file.getInputStream();
ExcelReader reader=ExcelUtil.getReader(inputStream);
List<Role>list=reader.readAll(Role.class);
for(int i=0;i<list.size();i++)
{
list.get(i).setId(null);//这里是因为数据库设置了id不能重复,并且会自增,所以把导入的数据的id设成null
}
roleService.saveBatch(list);
return list;
}
如果设置了别名,excel生成数据的列数据头是别名显示,如果导入的文件也是以别名显示,那么就会报错,这时候就要在实现类加上@Alias注解定义别名