SpringBoot、EasyExcel实现导入导出

参考信息：EasyExcel · 语雀EasyExcel是一个基于Java的简单、省内存的读...https://www.yuque.com/easyexcel/doc/easyexcel 1、增加easyexcel依赖

    com.alibaba
    easyexcel
    2.2.10
    
        
            org.apache.poi
            poi
        
        
            org.apache.poi
            poi-ooxml
        
        
            org.apache.poi
            poi-ooxml-schemas
        
    



    org.apache.poi
    poi
    4.1.0



    org.apache.poi
    poi-ooxml
    4.1.0



    org.apache.poi
    poi-ooxml-schemas
    4.1.0

由于easyexcel本身依赖了poi的3.17版本的jar，然后出现了版本冲突，所以在上面将poi一系列jar进行了exclusion的排除，然后重新引用了4.1.0版本的jar依赖。

2、定义导出excel的模型DTO，ExcelProperty中的value值就是导出的excel的表头title

@Data
public class CauseExcel {

    @ExcelProperty(value = "id", index = 0)
    private Long id;

    @ExcelProperty(value = "原因1", index = 1)
    private String cause1;

    @ExcelProperty(value = "原因2", index = 2)
    private String cause2;

    @ExcelProperty(value = "原因3", index = 3)
    private String cause3;

    @ExcelProperty(value = "原因4", index = 4)
    private String cause4;
   
}

3、调用controller的get方法，将文件流设置在resoponse中进行浏览器下载

 @GetMapping("/exportList")
    public void exportList(HttpServletResponse response, CauseQuery query) throws IOException {
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode("原因列表", "UTF-8")+"-"+ DateUtils.getDateyyyyMMddHHString(new Date());
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

       
        List excelList = new ArrayList<>();
        // 查询db数据
       //将业务数据组装到excelList
           do something......
        
        EasyExcel.write(response.getOutputStream(), CauseExcel.class).
            sheet("原因列表").doWrite(excelList);
    }

4、导入excel，定义导入model对象，用于接收解析excel后的业务数据信息

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.baseRowModel;
import lombok.Data;

@Data
public class ProcessimportModel extends baseRowModel {

    @ExcelProperty(value = "试验编号", index = 0)
    private String testCardNO;

    @ExcelProperty(value = "试验编号1", index = 1)
    private String testCardNO1;

    @ExcelProperty(value = "试验编号2", index = 2)
    private String testCardNO2;

    @ExcelProperty(value = "试验编号3", index =3)
    private String testCardNO3;

    @ExcelProperty(value = "试验编号4", index = 4)
    private String testCardNO4;

    @ExcelProperty(value = "试验编号5", index = 5)
    private String testCardNO5;

    @ExcelProperty(value = "试验编号6", index = 6)
    private String testCardNO6;

    @ExcelProperty(value = "试验编号7", index = 7)
    private String testCardNO7;

    @ExcelProperty(value = "试验编号8", index = 8)
    private String testCardNO8;

}

5、定义excel导入的监听处理类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;


@Slf4j
public class IssueimportListener extends AnalysisEventListener {
    //可以通过实例获取excel行数据
    private List importModelList = new ArrayList<>();

    public List getimportModelList() {
        return importModelList;
    }

    public void setimportModelList(List importModelList) {
        this.importModelList = importModelList;
    }

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        log.info("invoke: get excel data={}", JSON.toJSonString(o));
        //数据存储到importModelList
        importModelList.add((ProcessimportModel)o);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("doAfterAllAnalysed: import excel datas parsing complete..");
    }
}

6、编写导入的controller接口方法

@PostMapping("/import")
    public baseResult