EasyExcel工具类

EasyExcel pom依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.2-beta4</version>
</dependency>

模板样例：

@Data
@EqualsAndHashCode(callSuper = true)
@NoArgsConstructor
public class ApiExcelErrorDto extends BaseRowModel {

   //不需要导入、导出的字段不加注解
    private Integer lineNumber;

    @ExcelProperty(value = {"API名称"}, index = 0)
    private String name;

    @ExcelProperty(value = {"APIId"}, index = 1)
    private String apiId;

    private String errorReasons;

}

导出：

public static final String sheetName = "sheet1";

public static void exportWithBeanVersion1(HttpServletResponse resp, List data,Class clazz) {
    OutputStream out = null;
    try {
        
    //  resp.setContentType("application/vnd.ms-excel;charset=utf-8"); //.xls 格式
        resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");//.xlsx格式
        resp.setHeader("Content-Disposition", "attachment; filename=" + "default" + ".xlsx");
        out = resp.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
        Sheet sheet = new Sheet(1, 0, clazz);
        sheet.setSheetName(sheetName);
        writer.write(data, sheet);
        writer.finish();
        out.flush();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

导入：

public static void importExcel(MultipartFile file,String batchId){
   BufferedInputStream bis = null;
   try {
       bis = new BufferedInputStream(file.getInputStream());
   } catch (IOException e) {
       e.printStackTrace();
   }
   AnalysisEventListener listener = new ExcelListener(batchId);
   ExcelReader excelReader = EasyExcelFactory.getReader(bis, listener);
   excelReader.read(new Sheet(1,1,ApiExcelDto.class));

}

导入Listener

@Component
public class ExcelListener extends AnalysisEventListener {

    public String batchId ;

    public ExcelListener(){}

    public ExcelListener(String id){
        super();
        this.batchId =id;
    }

    private List<ApiExcelDto> datas = Lists.newArrayList();//校验成功数据

    private List<ApiExcelErrorDto> errorDatas = Lists.newArrayList();//校验失败数据

    public static ExcelListener listener;

    @Autowired
    private ApiInfoService apiInfoService;

    @Autowired
    private ApiInfoImportErrorService apiInfoImportErrorService;

    /**
     * 通过@PostConstruct实现初始化bean之前进行的操作
     * 解决apiInfoService 无法注入的问题
     */
    @PostConstruct
    public void init(){
        listener = this;
        listener.apiInfoService = this.apiInfoService;
        listener.apiInfoImportErrorService = apiInfoImportErrorService;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param obj             one row value. It is same as {@link AnalysisContext#}
     * @param analysisContext
     */
    @Override
    public void invoke(Object obj, AnalysisContext analysisContext) {
        ApiExcelDto dto = (ApiExcelDto) obj;
        String reason = validData(dto);//校验数据
        if (StringUtils.isNotBlank(reason)) {
            ApiExcelErrorDto errorDto = new ApiExcelErrorDto();
            BeanUtils.copyProperties(dto, errorDto);
            errorDto.setLineNumber(analysisContext.getCurrentRowNum());
            errorDto.setErrorReasons(reason);
            errorDatas.add(errorDto);
        } else {
            datas.add(dto);
        }
    }

    private String validData(ApiExcelDto dto) {
        String reason = "";
        if (StringUtils.isBlank(dto.getName())) {
            reason += "api名称为空 ";
        }
        return reason;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (!CollectionUtils.isEmpty(datas)) {
            for (ApiExcelDto obj : datas) {
                ApiInfoFormDto formDto = new ApiInfoFormDto();
                //保存校验成功数据 ...省略过程
                listener.apiInfoService.saveApi(formDto);
            }
            datas.clear();
        }
        if (!CollectionUtils.isEmpty(errorDatas)) {
            for (ApiExcelErrorDto dto : errorDatas) {
                //保存校验成功数据 ...省略过程
                listener.apiInfoImportErrorService.save(apiInfoImportError);
            }
            errorDatas.clear();
        }
    }
}

controller：

@ApiOperation(value = "导入excel", notes = "导入excel")
@PostMapping(value = "/api/excel", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
public ResultBody upload(@RequestPart(value = "file", required = false) MultipartFile file) {
    String fileName = file.getOriginalFilename();
    String suffixName = fileName.substring(fileName.lastIndexOf("."));
    if(!(ExcelTypeEnum.XLS.getValue().equalsIgnoreCase(suffixName))&&!(ExcelTypeEnum.XLSX.getValue().equalsIgnoreCase(suffixName))){
        return ResultBody.failed().msg("文件格式不对");
    }
    String batchId = System.currentTimeMillis()+"";
    EasyexcelUtil.importExcel(file,batchId);
    return ResultBody.ok().data(batchId);
}

@ApiOperation(value = "导出excel", notes = "导出excel")
@GetMapping(value = "/api/excel")
public ResultBody export(HttpServletResponse resp, @RequestParam(value = "ids",required = false) Long[] ids) {
      //构造数据data
      ...
    EasyexcelUtil.exportWithBeanVersion1(resp, data,ApiExcelDto.class);
    return null;
}