Excel导入导出 EasyExcel的使用- 导入组织人员_导入组织机构人员-优快云博客

本文链接：https://blog.youkuaiyun.com/sinosoft12345/article/details/120894270

模板

表结构

HSGD_BASE_GROUP_CUSTOMER_IMPORT_RECORD

HSGD_BASE_GROUP_CUSTOMER_IMPORT_COMPLETION

HSGD_BASE_GROUP_CUSTOMER_NOT_IMPORT_RECORD

代码

package com.zhgd.base.controller.api;

import com.zhgd.base.service.IZhgdSrBaseGroupCustomerImportService;
import com.zhgd.common.util.JsonUtil;
import com.zhgd.common.web.ResponseResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.List;

/**
 * 描述  掌柜组织改版：1-导入组织人员
 *
 * @author 
 * @version 1.0.0
 * @date 2021/10/11 16:07:20
 */
@RestController
@Api(tags = "Zhgd Sr Base Group Import Customer Controller")
@RequestMapping(value = "/api/srBaseGroupCustomerImport")
@Slf4j
public class ApiZhgdSrBaseGroupCustomerImportController {

    @Resource
    private IZhgdSrBaseGroupCustomerImportService iZhgdSrBaseGroupCustomerImportService;

    @ApiOperation(value = "掌柜组织改版导入组织人员接口", response = String.class, notes = "Import Base Group Customer")
    @PostMapping(value = "/11202/v1/importBaseGroupCustomer")
    public ResponseResult<List<String>> importBaseGroupCustomer(@RequestParam("importFile") MultipartFile importFile){
        String importFileName = importFile != null ? importFile.getOriginalFilename() : "--";
        log.info("[掌柜组织改版]导入部门组织人员，导入文件名：{}",importFileName);
        ResponseResult<List<String>> result = new ResponseResult<>();
        result.setData(iZhgdSrBaseGroupCustomerImportService.importBaseGroupCustomerProcess(importFile));
        log.info("[掌柜组织改版]导入部门组织人员，导入文件名：{}", JsonUtil.toJSONString(result));
        return result;
    }

//    @ApiOperation(value = "掌柜组织改版 初始化组织人员上下级关系接口", response = String.class, notes = "Initialize Base Group Customer Relation")
//    @PostMapping(value = "/11203/v1/initializeBaseGroupCustomerRelation")
//    public ResponseResult<Boolean> initializeBaseGroupCustomerRelation(HttpServletRequest request){
//        log.info("[掌柜组织改版]初始化组织人员上下级关系,开始...");
//        ResponseResult<Boolean> result = new ResponseResult<>();
//        result.setData(iZhgdSrBaseGroupCustomerImportService.initializeBaseGroupCustomerRelationProcess());
//        log.info("[掌柜组织改版]初始化组织人员上下级关系,响应结果：{}", JsonUtil.toJSONString(result));
//        return result;
//    }
}

package com.zhgd.base.service;

import com.zhgd.base.entity.HsgdBaseGroupCustomerImportRecord;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

public interface IZhgdSrBaseGroupCustomerImportService {

    void batchInsertAll(List<HsgdBaseGroupCustomerImportRecord> recordList);

    List<String> importBaseGroupCustomerProcess(MultipartFile importFile);

    Boolean initializeBaseGroupCustomerRelationProcess();

    void insert(HsgdBaseGroupCustomerImportRecord record);
}

package com.zhgd.base.service.impl;

import com.alibaba.excel.EasyExcel;
import com.github.pagehelper.PageHelper;
import com.zhgd.base.domain.model.ZhgdSrGroupCustomerImportModel;
import com.zhgd.base.domain.vo.HsgdBaseGroupCustomerSimpleVO;
import com.zhgd.base.domain.vo.HsgdBaseGroupLevelNameVO;
import com.zhgd.base.domain.vo.HsgdBaseGroupSimpleVO;
import com.zhgd.base.entity.*;
import com.zhgd.base.enums.HsgdBaseGroupLevelEnum;
import com.zhgd.base.listener.ZhgdSrBaseGroupCustomerImportExcelListener;
import com.zhgd.base.mapper.HsgdBaseGroupCustomerImportCompletionMapper;
import com.zhgd.base.mapper.HsgdBaseGroupCustomerImportRecordMapper;
import com.zhgd.base.mapper.HsgdBaseGroupCustomerMapper;
import com.zhgd.base.mapper.HsgdBaseGroupMapper;
import com.zhgd.base.service.*;
import com.zhgd.common.util.StringUtil;
import com.zhgd.common.util.UUIDUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
@Slf4j
public class ZhgdSrBaseGroupCustomerImportServiceImpl implements IZhgdSrBaseGroupCustomerImportService {

    private final String SYSTEM_USER_NAME = "system";

    @Resource
    private HsgdBaseGroupCustomerImportRecordMapper hsgdBaseGroupCustomerImportRecordMapper;

    @Resource
    private HsgdBaseGroupCustomerImportCompletionMapper hsgdBaseGroupCustomerImportCompletionMapper;

    @Resource
    private IZhgdSrBaseGroupImportCustomerHandleService iZhgdSrBaseGroupImportCustomerHandleService;

    @Resource
    private HsgdBaseGroupCustomerMapper hsgdBaseGroupCustomerMapper;

    @Resource
    private HsgdBaseGroupMapper hsgdBaseGroupMapper;

    @Resource
    private IZhgdSrBaseGroupImportService iZhgdSrBaseGroupImportService;

    @Resource
    private IHsgdBaseGroupCustomerService iHsgdBaseGroupCustomerService;

    @Resource
    private IHsgdBaseGroupCustomerNotImportRecordService iHsgdBaseGroupCustomerNotImportRecordService;

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void batchInsertAll(List<HsgdBaseGroupCustomerImportRecord> recordList) {
        hsgdBaseGroupCustomerImportRecordMapper.insertAll(recordList);
    }

    @Override
    public void insert(HsgdBaseGroupCustomerImportRecord record) {
        hsgdBaseGroupCustomerImportRecordMapper.insert(record);
    }

    @Override
    public List<String> importBaseGroupCustomerProcess(MultipartFile importFile) {
        List<String> importResultList = new ArrayList<>();
        try {
            String batchNo = UUIDUtils.getUUID();
            ZhgdSrBaseGroupCustomerImportExcelListener listener = new ZhgdSrBaseGroupCustomerImportExcelListener(iZhgdSrBaseGroupImportCustomerHandleService, batchNo);
            EasyExcel.read(importFile.getInputStream(), ZhgdSrGroupCustomerImportModel.class, listener).sheet(0).doRead();
            // 2、文件校验(待处理)
            // 3、组织数据
            int totalRecord =  this.organizeAndSaveBaseGroupCustomerCompletionData(batchNo);
            this.saveBaseGroupCustomerData(batchNo,totalRecord);
        } catch (Exception e) {
            log.info("导入异常", e);
            importResultList.add("导入异常 ：" + e.getMessage());
            return importResultList;
        }

        importResultList.add("执行完成,请检查是否全部导入成功！");
        return importResultList;
    }

    @Override
    public Boolean initializeBaseGroupCustomerRelationProcess() {
        return null;
    }

    private int organizeAndSaveBaseGroupCustomerCompletionData(String batchNo) {

        int importDataTotal = hsgdBaseGroupCustomerImportRecordMapper.countImportBaseGroupCustomerDataTotalByBatchNo(batchNo);
        
        if (importDataTotal > 0) {
            // 每页记录数
            int pageSize = 120, startPage = 1;
            // 总页数
            int totalPage = importDataTotal % pageSize == 0 ? (importDataTotal / pageSize) : (importDataTotal / pageSize + 1);

            for (int pageN = 0; pageN < totalPage; pageN++) {
                PageHelper.startPage(startPage + pageN, pageSize, false);
                List<HsgdBaseGroupCustomerImportRecord> list = hsgdBaseGroupCustomerImportRecordMapper.selectListByBatchNo(batchNo);

                if (CollectionUtils.isNotEmpty(list)) {
                    List<HsgdBaseGroupCustomerImportCompletion> completionList = new ArrayList<>();
                    HsgdBaseGroupCustomerImportCompletion completion;

                    for (int i = 0, listSize = list.size(); i < listSize; i++) {
                        HsgdBaseGroupCustomerImportRecord record = list.get(i);
                        completion = new HsgdBaseGroupCustomerImportCompletion();

                        if (StringUtils.isNotBlank(record.getLevelOneGroup())) {
                            completion.setGroupLevel(HsgdBaseGroupLevelEnum.ONE.getGroupLevel());
                        }

                        if (StringUtils.isNotBlank(record.getLevelTwoGroup())) {
                            completion.setGroupLevel(HsgdBaseGroupLevelEnum.TWO.getGroupLevel());
                        }

                        if (StringUtils.isNotBlank(record.getLevelThreeGroup())) {
                            completion.setGroupLevel(HsgdBaseGroupLevelEnum.THREE.getGroupLevel());
                        }

                        if (StringUtils.isNotBlank(record.getLevelFourGroup())) {
                            completion.setGroupLevel(HsgdBaseGroupLevelEnum.FOUR.getGroupLevel());
                        }

                        if (StringUtils.isNotBlank(record.getLevelFiveGroup())) {
                            completion.setGroupLevel(HsgdBaseGroupLevelEnum.FIVE.getGroupLevel());
                        }

                        if (StringUtils.isNotBlank(record.getLevelSixGroup())) {
                            completion.setGroupLevel(HsgdBaseGroupLevelEnum.SIX.getGroupLevel());
                        }

                        if (StringUtils.isNotBlank(record.getLevelSevenGroup())) {
                            completion.setGroupLevel(HsgdBaseGroupLevelEnum.SEVEN.getGroupLevel());
                        }

                        if (StringUtils.isNotBlank(record.getLevelEightGroup())) {
                            completion.setGroupLevel(HsgdBaseGroupLevelEnum.EIGHT.getGroupLevel());
                        }

                        BeanUtils.copyProperties(record, completion);
                        completionList.add(completion);
                    }

                    iZhgdSrBaseGroupImportCustomerHandleService.insertAllImportCompletion(completionList);
                }
            }
        }

        return importDataTotal;
    }


    private void saveBaseGroupCustomerData(String batchNo, int totalRecord) {
        if(totalRecord > 0) {
            int pageSize = 120, startPage = 1;
            int totalPage = totalRecord % pageSize == 0 ? (totalRecord / pageSize) : (totalRecord / pageSize + 1);
            Date nowDate = new Date();
            Long parentId;
            String oneMapKey, twoMapKey, threeMapKey, fourMapKey, fiveMapKey, sixMapKey, sevenMapKey, eightMapKey;
            HsgdBaseGroupSimpleVO groupSimpleVo = null;
            Map<String, HsgdBaseGroupSimpleVO> groupOneLevelMap = new HashMap<>();
            Map<String, HsgdBaseGroupSimpleVO> groupTwoLevelMap = new HashMap<>();
            Map<String, HsgdBaseGroupSimpleVO> groupThreeLevelMap = new HashMap<>();
            Map<String, HsgdBaseGroupSimpleVO> groupFourLevelMap = new HashMap<>();
            Map<String, HsgdBaseGroupSimpleVO> groupFiveLevelMap = new HashMap<>();
            Map<String, HsgdBaseGroupSimpleVO> groupSixLevelMap = new HashMap<>();
            Map<String, HsgdBaseGroupSimpleVO> groupSevenLevelMap = new HashMap<>();
            Map<String, HsgdBaseGroupSimpleVO> groupEightLevelMap = new HashMap<>();

            for (int pageN = 0; pageN < totalPage; pageN++) {
                PageHelper.startPage(startPage + pageN, pageSize, false);
                List<HsgdBaseGroupCustomerImportCompletion> completionPageList  = hsgdBaseGroupCustomerImportCompletionMapper.selectListByBatchNo(batchNo);
                if(CollectionUtils.isNotEmpty(completionPageList)){

                    List<Long> customerIdList = completionPageList.stream().map(HsgdBaseGroupCustomerImportCompletion::getCustomerId).collect(Collectors.toList());
                    List<HsgdBaseGroupCustomerSimpleVO>  customerSiList = hsgdBaseGroupCustomerImportCompletionMapper.querySrCustomerInfoByIds(customerIdList);
                    List<HsgdBaseGroupCustomerSimpleVO>  customerDealerList = hsgdBaseGroupCustomerImportCompletionMapper.queryDealerCustomerInfoByIds(customerIdList);
                    Map<Long,HsgdBaseGroupCustomerSimpleVO> customerSiMap = new HashMap<>();
                    if (CollectionUtils.isNotEmpty(customerSiList)) {
                        Map<Long, HsgdBaseGroupCustomerSimpleVO> srCustomerSiMap = customerSiList.stream().collect(Collectors.toMap(HsgdBaseGroupCustomerSimpleVO::getCustomerId, Function.identity(), (key1, key2) -> key2));
                        customerSiMap.putAll(srCustomerSiMap);
                    }
                    if (CollectionUtils.isNotEmpty(customerDealerList)) {
                        Map<Long, HsgdBaseGroupCustomerSimpleVO> dealerCustomerSiMap = customerDealerList.stream().collect(Collectors.toMap(HsgdBaseGroupCustomerSimpleVO::getCustomerId, Function.identity(), (key1, key2) -> key2));