excel导入

@PostMapping("/import")
public ResponseMessage importPerson(@RequestParam("file") MultipartFile excel) {
    ResponseMessage responseMessage = new ResponseMessage(0);
    try {
        personInfoService.importPerson(excel);
    } catch (Exception e) {
        logger.error("人员导入异常,", e);
        return genernateErrorMsg(e.getMessage());
    }
    return responseMessage;
}

 

@Override
public void importPerson(MultipartFile excel) throws IOException {
    InputStream inputStream = excel.getInputStream();
    ExcelReader excelReader =
        EasyExcel.read(inputStream, PersonExcel.class, new PersonDataListener(personInfoDao, objAdDao, organizationInfoDao)).headRowNumber(3).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet);
    excelReader.finish();
}

public class PersonDataListener extends AnalysisEventListener<PersonExcel> {
    private static final Logger LOGGER = LoggerFactory.getLogger(PersonDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 3000;
    List<PersonExcel> list = new ArrayList<PersonExcel>();

    private PersonInfoDao personInfoDao;
    private ObjAdDao objAdDao;
    private OrganizationInfoDao organizationInfoDao;

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param personInfoDao
     */
    public PersonDataListener(PersonInfoDao personInfoDao, ObjAdDao objAdDao, OrganizationInfoDao organizationInfoDao) {
        this.personInfoDao = personInfoDao;
        this.objAdDao = objAdDao;
        this.organizationInfoDao = organizationInfoDao;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(PersonExcel data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        ArrayList<PersonInfo> personInfos = new ArrayList<>();
        for (PersonExcel personExcel : list) {
            String idNum = personExcel.getIdNum();
            // 过滤空数据
            if (idNum == null) {
                continue;
            }
            //查询重复数据
            if (isExist(idNum)) {
                continue;
            }
            //转换数据
            PersonInfo exchange = exchange(personExcel);
            personInfos.add(exchange);
        }
        personInfoDao.saveAll(personInfos);
        LOGGER.info("存储数据库成功！");
    }

    private boolean isExist(String idNum) {
        PersonInfo byIdNum = personInfoDao.findByIdNum(idNum);
        if (byIdNum == null) {
            return false;
        }
        return true;
    }
    private PersonInfo exchange(PersonExcel personExcel) {
。。。
}
}

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

@Data
public class PersonExcel implements Serializable {
    private static final long serialVersionUID = 5523036180824837630L;

    @ExcelProperty("所在县（市、区）")
    private String adName;

    @ExcelProperty("工作单位")
    private String organizationName;
}