通过EasyExcel实现导入导出功能（包含偏复杂表头）

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目

 首先引入maven

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

一.导出（写）

1.创建导出对象

加@ExcelIgnore 注解后将不作为导出字段  @ExcelProperty("XX")导出的表头

@Data
public class OlderVo {
    @ExcelIgnore
    private String id;
 
    @ExcelProperty("地区")
    private String area;
 
    @ExcelProperty("总数")
    private String total;
 
    private String code;
 
}

导出，如果不自定义表头就去掉.head(dead())

    public void exportOlder(HttpServletResponse response) throws IOException {
        List<OlderVo> list = XXXXService.exportXXX();
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("导出", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), OlderVo.class).sheet("模板").head(head()).doWrite(list);
    }
 
 
 
//自定义表头
    private static List<List<String>> head() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("地区");
        List<String> head1 = new ArrayList<String>();
        head1.add("总数");
        List<String> head2 = new ArrayList<String>();
        head2.add("60-69周岁");
        head2.add("数量");
        List<String> head3 = new ArrayList<String>();
        head3.add("60-69周岁");
        head3.add("占比");
 
        list.add(head0);
        list.add(head1);
        list.add(head2);
        list.add(head3);
  
        return list;
    }

二.读操作(导入获取数据)

1.创建接收的实体

@Data
public class TemplateEntity {
 
    @ExcelProperty("标号")
    private Integer label;
 
    @ExcelProperty("字符串")
    private String str;
 
    @ExcelProperty("数字")
    private Integer num;
}

2.导出接口

    @PostMapping("/getinfo")
    public R importExcel(@RequestPart("file") MultipartFile file) throws IOException {
        R r = templateService.impExcel(file);
        return r;
    }

3.实现类方法

@Service
public class TemplateServiceImpl implements TemplateService {
    @Override
    public R impExcel(MultipartFile file) throws IOException {
        List<TemplateEntity> entities = getTemplateEntities(file);
        System.out.println(entities);
        return R.ok(entities);
    }
    private List<TemplateEntity> getTemplateEntities(MultipartFile file) throws IOException {
        TemplateListener templateListener = new TemplateListener();
        EasyExcel.read(file.getInputStream(),TemplateEntity.class,templateListener).sheet().doRead();
        return templateListener.getDate();
    }
}

4.监听器

public class TemplateListener extends AnalysisEventListener<TemplateEntity> {
 
    private List<TemplateEntity> list = new ArrayList<>();
 
    @Override
    public void invoke(TemplateEntity data, AnalysisContext analysisContext) {
        list.add(data);
    }
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
 
    }
 
    public List<TemplateEntity> getDate(){
        return list;
    }
}