超好用的Excel导入导出类库easyexcel

1、在pom.xml文件中引入easyexcel

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>

2、定义Bean

对需要导入和导出的属性加上@ExcelProperty属性，可以将index去掉，只保留value

注意如果该实体是对应数据库表，则需加上

@TableField(exist = false)
private Map<Integer, CellStyle> cellStyleMap;

3、定义模板

导出模板

导入模板

4、导出方法

@RequestMapping("/export")
public void export(HttpServletResponse response, @RequestBody Map<String, Object> params){
    String templateFileName = "classpath:template/template_room_download.xlsx";
    params.put(Constant.LIMIT, "-1");
    PageUtils page = resourceRoomService.selectPage(params);
    List<ResourceRoomEntity> list = (List<ResourceRoomEntity>) page.getList();
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = null;
    try {
        File templateFile = ResourceUtils.getFile(templateFileName);
        templateFileName = templateFile.getAbsolutePath();
        fileName = URLEncoder.encode("导出", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), ResourceRoomEntity.class).withTemplate(templateFileName).sheet().doFill(list);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

5、导出

@RequestMapping("/import")
public R importData(@RequestParam("file") MultipartFile file){
     // 判断excel空，列是否正确等 读取Excel
        List<Object> list = EasyExcelFactory.read(file.getInputStream(),new Sheet(1, 1, ResourceRoomEntity.class));
      //具体业务处理
      return R.ok();
}