一、依赖添加
操作手册:Hutool — 🍬A set of tools that keep Java sweet.
<!--hutool工具包-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.20</version>
</dependency>
<!--Apache POI,用于读写excel文档-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
二、实现接口
excel导入
/**
* 上传excel
* @param file 请求头设置为form-data,参数名需一致(例如:file)
* @throws Exception
*/
@PostMapping("/import")
public void importExcel(MultipartFile file) throws Exception {
// 获取数据流
InputStream inputStream = file.getInputStream();
ExcelReader reader = ExcelUtil.getReader(inputStream);
// 读取数据
List<Login> list = reader.readAll(Login.class);
System.out.println(list);
}
excel导出
/**
* 导出excel
* @param response
* @throws Exception
*/
@GetMapping("/export")
public void exportExcel(HttpServletResponse response) throws Exception {
List<Login> list = loginService.list();
// 从内存操作,直接写出到浏览器
ExcelWriter writer = ExcelUtil.getWriter(true);
// 自定义列名
writer.addHeaderAlias("id", "账号");
writer.addHeaderAlias("name", "用户名");
writer.addHeaderAlias("job", "职业");
writer.addHeaderAlias("address", "地址");
// 写出到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=" + fileName + ".xlsx");
// 执行导出,并释放
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
out.close();
writer.close();
}