java利用easyexcel操作excel表格

导出excel

引入easyexcel依赖

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

编写controller

@GetMapping("/exportDoctor")
    public void exportDoctor(@RequestParam(value = "key",required = false) String key,
                          @RequestParam(value = "province",required = false) String province,
                          @RequestParam(value = "city",required = false) String city,
                          @RequestParam(value = "area",required = false) String area,
                          HttpServletResponse response) throws IOException {
        doctorService.exportDoctor(key,province,city,area,response);
    }

编写excel导出实体类DoctorExcelVo

@Data
public class DoctorExcelVo {

    @ExcelProperty(value = "ID",index = 0)
    private Integer id;
    @ExcelProperty(value = "名字",index = 1)
    private String name;
}

编写doctorService实现接口exportDoctor

@Override
    public void exportDoctor(String key, String province, String city, String area, HttpServletResponse response) throws IOException {
        //设置下载信息
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = "村医名单.xlsx";
        response.setHeader("Content-Disposition", "attachment;filename*= UTF-8''"+ URLEncoder.encode(fileName,"UTF-8"));
        //查询数据库
        //1、获取key province city area
        DoctorDao doctorDao = this.baseMapper;
        QueryWrapper<DoctorEntity> queryWrapper = new QueryWrapper<>();
        if (!StringUtils.isEmpty(key)) {
            queryWrapper
                    .and(e -> {
                        e.eq("id", key).or().like("name", key);
                    }).or().eq("phone", key);
        }
        if(!StringUtils.isEmpty(province)){
            queryWrapper.eq("province",province);
        }

        if(!StringUtils.isEmpty(city)){
            queryWrapper.eq("city",city);
        }
        if(!StringUtils.isEmpty(area)){
            queryWrapper.eq("area",area);
        }
        queryWrapper.orderByDesc("id");

        List<DoctorEntity> list = doctorDao.selectList(queryWrapper);
        //DoctorEntity转换为DoctorExcelVo
        ArrayList<DoctorExcelVo> doctorExcelVoList = new ArrayList<>();
        for (DoctorEntity doctorEntity : list) {
            DoctorExcelVo doctorExcelVo = new DoctorExcelVo();
            BeanUtils.copyProperties(doctorEntity,doctorExcelVo);
            doctorExcelVoList.add(doctorExcelVo);
        }

        //调用方法进行写操作
        EasyExcel.write(response.getOutputStream(), DoctorExcelVo.class).sheet().doWrite(doctorExcelVoList);
    }

导入excel

编写controller

@GetMapping("/readExcel")
    public void readExcel(){
        String fileName = "F:\\gk\\xxx.xlsx";
        EasyExcel.read(fileName,DoctorExcelVo.class,new ExcelListener()).sheet().doRead();
    }

读取excel需要创建一个监听器继承AnalysisEventListener

public class ExcelListener extends AnalysisEventListener<DoctorExcelVo> {

    //一行一行去读取excel内容，从第二行开始读取
    @Override
    public void invoke(DoctorExcelVo doctorExcelVo, AnalysisContext analysisContext) {
        System.out.println(doctorExcelVo);
    }

    //读取之后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}