Easypoi导入导出Excel

起步

新建一个springboot工程，引入以下依赖

1 2 3 4 5

<dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-spring-boot-starter</artifactId> <version>3.3.0</version> </dependency>

实体类

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

@Data public class User{ @Excel(name = "ID") private Integer userId; @Excel(name = "姓名", orderNum = "1") private String name; @Excel(name = "创建时间", exportFormat = "yyyy-MM-dd", orderNum = "2") @JsonFormat(pattern="yyyy-MM-dd") private Date createTime; @Excel(name = "状态", orderNum = "3") private Integer state; @Excel(name = "年龄", orderNum = "4") private Integer age; }

导出

导出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

@RestController @RequestMapping("/user") public class UserController { @Autowired private UserService userService; @RequestMapping("/exportExcel") public void export(HttpServletResponse response){ //从数据库中获取数据 List<User> user = userService.findAll(); // 设置响应输出的头类型(设置响应类型) response.setHeader("content-Type", "application/vnd.ms-excel"); // 下载文件的默认名称(设置下载文件的默认名称) response.setHeader("Content-Disposition", "attachment;filename=user.xls"); //导出操作 // ExportParams 1，第一个参数为 标题，2第二个为sheet表名 try { Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("客户名单","1"),User.class,user); workbook.write(response.getOutputStream()); } catch (IOException e) { e.printStackTrace(); } } }

导入

导入Excel

1 2 3 4 5 6 7 8 9 10

@PostMapping(value = "/import") public void importxls(@RequestParam("file") MultipartFile file) throws Exception { ImportParams params = new ImportParams(); params.setTitleRows(2); List<User> result = ExcelImportUtil.importExcel(file.getInputStream(), User.class, params); for (int i = 0; i < result.size(); i++) { System.out.println(result.get(i).getUserName()); } }

参考

官方文档