java 实现Excel导入

//controller
@PostMapping("/importManageOrg")
@ApiOperation(value = "导入管理机构", notes = "导入管理机构")
public JsonResult importManageOrg(@ApiParam(value = "数据文件", required = true) @RequestParam(value = "file") MultipartFile file){
    orgMemberService.importManageOrg(file);
    return  JsonResult.ok();
}
//service
void importManageOrg(MultipartFile file);

//impl实现类
@Override
    public void importManageOrg(MultipartFile file) {

        ManageOrgImportListener manageOrgImportListener = new ManageOrgImportListener(enterpriseService,enterpriseDao,enterpriseInfoDao,enterpriseInfoService,platformTransactionManager,transactionDefinition);
        // 开始读取excel文件
        try (InputStream inputStream = file.getInputStream()){
            EasyExcel.read(inputStream, ManageOrgImportData.class,manageOrgImportListener).sheet().doRead();

//            if (virtualOrgImportListener.getErrorCount()>0){
//                throw new UCenterException("excel中存在"+importMember.getErrorCount()+"处错误",ErrorCode.IMPORT_USER_FAIL.getErrCode());
//            }
        } catch (IOException e) {
            log.info("读取excel异常:{}",e);
            throw new UCenterException("读取excel失败！");
        }
    }

//各种校验
package com.necibook.ucenter.ExcelListener;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.necibook.ucenter.dao.EnterpriseDao;
import com.necibook.ucenter.dao.power.EnterpriseInfoDao;
import com.necibook.ucenter.enums.PowerDicEnum;
import com.necibook.ucenter.model.entity.EnterpriseEntity;
import com.necibook.ucenter.model.entity.EnterpriseInfoEntity;
import com.necibook.ucenter.model.excel.ManageOrgImportData;
import com.necibook.ucenter.model.excel.VirtualOrgImportData;
import com.necibook.ucenter.service.EnterpriseService;
import com.necibook.ucenter.service.school.EnterpriseInfoService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;


@Slf4j
public class ManageOrgImportListener extends AnalysisEventListener<ManageOrgImportData> {

    /**
     * 每200条数据 插入一次数据库
     */
    private static final int BATCH_INSERT_COUNT = 100;
    /**
     * 要插入的机构集合
     */
    private List<EnterpriseEntity> orgList = CollectionUtil.newArrayList();
    /**
     * 要插入的机构info集合
     */
    private List<EnterpriseInfoEntity> orgInfoList = CollectionUtil.newArrayList();

    /**
     *  可管理机构集合
     */
    private static final Map<String, Integer> MANAGE_ORG_TYPE_MAP = CollectionUtil.newHashMap();
    static {
        MANAGE_ORG_TYPE_MAP.put(PowerDicEnum.ENTERPRISE_TYPE_Y.getDesc(),PowerDicEnum.ENTERPRISE_TYPE_Y.getcode());
        MANAGE_ORG_TYPE_MAP.put(PowerDicEnum.ENTERPRISE_TYPE_Q.getDesc(),PowerDicEnum.ENTERPRISE_TYPE_Q.getcode());
        MANAGE_ORG_TYPE_MAP.put(PowerDicEnum.ENTERPRISE_TYPE_J.getDesc(),PowerDicEnum.ENTERPRISE_TYPE_J.getcode());
        MANAGE_ORG_TYPE_MAP.put(PowerDicEnum.ENTERPRISE_TYPE_L.getDesc(),PowerDicEnum.ENTERPRISE_TYPE_L.getcode());
        MANAGE_ORG_TYPE_MAP.put(PowerDicEnum.ENTERPRISE_TYPE_G.getDesc(),PowerDicEnum.ENTERPRISE_TYPE_G.getcode());
        MANAGE_ORG_TYPE_MAP.put(PowerDicEnum.ENTERPRISE_TYPE_X.getDesc(),PowerDicEnum.ENTERPRISE_TYPE_X.getcode());
    }
    /**
     * 错误记录的条数
     */
    private Integer errorCount = 0;

    private EnterpriseDao enterpriseDao;
    private EnterpriseService enterpriseService;
    private EnterpriseInfoDao enterpriseInfoDao;
    private EnterpriseInfoService enterpriseInfoService;
    private PlatformTransactionManager platformTransactionManager;
    private TransactionDefinition transactionDefinition;

    public ManageOrgImportListener(EnterpriseService enterpriseService,EnterpriseDao enterpriseDao,EnterpriseInfoDao enterpriseInfoDao,
                                    EnterpriseInfoService enterpriseInfoService,PlatformTransactionManager platformTransactionManager, TransactionDefinition transactionDefinition) {
        this.enterpriseService = enterpriseService;
        this.enterpriseDao = enterpriseDao;
        this.enterpriseInfoDao = enterpriseInfoDao;
        this.enterpriseInfoService = enterpriseInfoService;
        this.platformTransactionManager = platformTransactionManager;
        this.transactionDefinition = transactionDefinition;
    }

    @Override
    public void invoke(ManageOrgImportData manageOrgImportData, AnalysisContext analysisContext) {
        log.info("当前行号：{},数据：{}",analysisContext.readRowHolder().getRowIndex(), JSON.toJSONString(manageOrgImportData));
        Integer integer = checkData(manageOrgImportData, analysisContext);
        if (integer.equals(0)){
            return ;
        }
        EnterpriseEntity enterpriseEntity = new EnterpriseEntity();
        enterpriseEntity.setId(IdUtil.fastSimpleUUID());
        enterpriseEntity.setEnterpriseName(manageOrgImportData.getEnterpriseName());
        enterpriseEntity.setEnterpriseDomain(manageOrgImportData.getEnterpriseDomain());
        enterpriseEntity.setEnterpriseCode(manageOrgImportData.getEnterpriseCode());
        //todo 虚拟机构换成取枚举
        enterpriseEntity.setEnterpriseType(5);
        //暂时加默认值
        enterpriseEntity.setUseRange(0);
        enterpriseEntity.setSource(0);
        enterpriseEntity.setIfOpened(1);

        enterpriseEntity.setCreateTime(new Date());
        enterpriseEntity.setUpdateTime(new Date());
        enterpriseEntity.setIsDeleted(0);
        if (StrUtil.isNotBlank(manageOrgImportData.getEnterpriseDomain())){
            enterpriseEntity.setIsOpenDomain(1);
        }else{
            enterpriseEntity.setIsOpenDomain(0);
        }

        orgList.add(enterpriseEntity);

        //拼接所属区域字段
        String[] split = manageOrgImportData.getManageRange().split("、");
        ArrayList<String> strings = CollectionUtil.newArrayList(split);
        String join = StrUtil.join(",", strings);

        //管理机构可管机构类型
        String[] manageOrg = manageOrgImportData.getManageOrgType().split("、");
        log.info("取出的可管机构类型：{}",CollectionUtil.newArrayList(manageOrg));
        ArrayList<Integer> manageStrings = CollectionUtil.newArrayList();
        for (int i = 0; i < manageOrg.length; i++){
            log.info("遍历到的类型:{}",manageOrg[i]);
            if(MANAGE_ORG_TYPE_MAP.containsKey(manageOrg[i])){
                manageStrings.add(MANAGE_ORG_TYPE_MAP.get(manageOrg[i]));
            }
        }
        String manageOrgType = StrUtil.join(",", manageStrings);
        EnterpriseInfoEntity enterpriseInfoEntity = EnterpriseInfoEntity.builder()
                .enterpriseId(enterpriseEntity.getId())
                .shorterForm(manageOrgImportData.getShorterForm())
                .simpleDesc(manageOrgImportData.getSimpleDesc())
                .settledDate(LocalDateTime.now())
                .settledEndDate(LocalDateTime.now())
                .manageRange(join)
                .manageOrgType(manageOrgType)
                .dataRange(0)
                .createTime(LocalDateTime.now())
                .updateTime(LocalDateTime.now())
                .build();

        log.info("manageRange：{}", join);
        orgInfoList.add(enterpriseInfoEntity);

        if(orgList.size() >= BATCH_INSERT_COUNT && errorCount==0){
            //插入一批数据
            insert();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("excel读取完成");
        if(CollUtil.isNotEmpty(orgList)&& errorCount ==0){
            insert();
        }
        if (errorCount > 0){
            log.info("出现错误");
        }
    }

    private Integer checkData(ManageOrgImportData manageOrgImportData,AnalysisContext analysisContext){
        if(analysisContext.readRowHolder().getRowIndex() <= 0){
            return 0;
        }
        if(manageOrgImportData == null){
            log.info("第{}行没有数据",analysisContext.readRowHolder().getRowIndex());
            errorCount=errorCount+1;
            return 0;
        }
        if (StrUtil.isBlank(manageOrgImportData.getEnterpriseName())){
            errorCount=errorCount+1;
            return 0;
        }
        //管理机构可管机构类型判断
//        if (MANAGE_ORG_TYPE_MAP.containsKey()){
//            errorCount=errorCount+1;
//            return 0;
//        }
        return 1;
    }

    private void insert(){
        log.info("插入的数据:{}",orgList);
        TransactionStatus transaction = platformTransactionManager.getTransaction(transactionDefinition);
        try{
            boolean b = enterpriseService.saveBatch(orgList);
            log.info("插入结果:{}",b);
            enterpriseInfoService.saveBatch(orgInfoList);
            // 提交事务
            platformTransactionManager.commit(transaction);
        }catch (Exception e){
            log.error("批量插入数据库异常:{}",e);
            // 回滚事务
            platformTransactionManager.rollback(transaction);
        }
        orgList.clear();
        orgInfoList.clear();
    }
}