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();//校驗成功數(shù)據(jù)

    private List<ApiExcelErrorDto> errorDatas = Lists.newArrayList();//校驗失敗數(shù)據(jù)

    public static ExcelListener listener;

    @Autowired
    private ApiInfoService apiInfoService;

    @Autowired
    private ApiInfoImportErrorService apiInfoImportErrorService;

    /**
     * 通過@PostConstruct實現(xiàn)初始化bean之前進行的操作
     * 解決apiInfoService 無法注入的問題
     */
    @PostConstruct
    public void init(){
        listener = this;
        listener.apiInfoService = this.apiInfoService;
        listener.apiInfoImportErrorService = apiInfoImportErrorService;
    }

    /**
     * 這個每一條數(shù)據(jù)解析都會來調(diào)用
     *
     * @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);//校驗數(shù)據(jù)
        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();
                //保存校驗成功數(shù)據(jù) ...省略過程
                listener.apiInfoService.saveApi(formDto);
            }
            datas.clear();
        }
        if (!CollectionUtils.isEmpty(errorDatas)) {
            for (ApiExcelErrorDto dto : errorDatas) {
                //保存校驗成功數(shù)據(jù) ...省略過程
                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) {
      //構(gòu)造數(shù)據(jù)data
      ...
    EasyexcelUtil.exportWithBeanVersion1(resp, data,ApiExcelDto.class);
    return null;
}