前言

一、什么是EasyExcel？

EasyExcel是阿里巴巴开发的一款开源Java库，专门用于处理Excel文件。与传统的POI相比，EasyExcel拥有更简洁、高效的API，使得开发者可以更轻松地实现Excel文件的读取和写入操作。无论是处理小型还是大型Excel文件，EasyExcel都能提供出色的性能和稳定性。

二、使用步骤

1.引入库

代码如下（示例）：

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>3.1.0</version>
		</dependency>

2.导入数据

代码如下（示例）：

    /**
     * 导入自然人数据
     *
     * @param files 文件
     * @return {@link JsonResponse}<{@link List}<{@link String}>>
     * @throws Exception 例外
     */
    @ApiOperation(value = "关联自然人导入", httpMethod = "POST")
    @PostMapping("/importNaturalPersonData")
    public JsonResponse<List<String>> importNaturalPersonData(@RequestParam("files") List<MultipartFile> files) throws Exception {
        JsonResponse<List<String>> response = new JsonResponse<>();
        response.setResult(relatedpartyService.importNaturalPersonData(files.get(0)));
        return response;
    }

 @Transactional
    public List<String> importNaturalPersonData(MultipartFile file) throws IOException {
        NaturalPersonListener naturalPersonListener = new NaturalPersonListener(codeMapper, relatedPartyIdentityRelationshipBizMapper, relatedPartyReportedService, naturalPersonService, relatedPartyMapper, relatedPartyRecordMapper, relatedPartyReportedBizMapper);
        EasyExcel.read(file.getInputStream(), NaturalPersonImportDto.class, naturalPersonListener).sheet().doRead();
        return naturalPersonListener.getErrorMsgList();
    }

package com.stock.mr.enterprise.components.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.stock.core.exception.ApplicationException;
import com.stock.core.util.BeanUtil;
import com.stock.core.util.JsonUtil;
import com.stock.mr.enterprise.categoryconfig.dao.RelatedPartyIdentityRelationshipBizMapper;
import com.stock.mr.enterprise.common.constant.CodeTableConstant;
import com.stock.mr.enterprise.common.constant.LogicConstant;
import com.stock.mr.enterprise.common.dao.CodeMapper;
import com.stock.mr.enterprise.common.dao.RelatedPartyMapper;
import com.stock.mr.enterprise.common.dao.RelatedPartyRecordMapper;
import com.stock.mr.enterprise.common.entity.*;
import com.stock.mr.enterprise.components.dto.NaturalPersonImportDto;
import com.stock.mr.enterprise.insideKnowledge.dao.RelatedPartyReportedBizMapper;
import com.stock.mr.enterprise.insideKnowledge.dto.RelatedPartyDto;
import com.stock.mr.enterprise.insideKnowledge.dto.RelatedPartyIdentityRelaDto;
import com.stock.mr.enterprise.insideKnowledge.dto.RelatedPartyParamDto;
import com.stock.mr.enterprise.insideKnowledge.dto.RelatedPartyResultDto;
import com.stock.mr.enterprise.insideKnowledge.service.NaturalPersonService;
import com.stock.mr.enterprise.insideKnowledge.service.RelatedPartyReportedService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/*
 * @author: LiuKeXin
 * @description: 模板的读取类-自然人
 * @date: 2022/11/6 10:03
 */
public class NaturalPersonListener implements ReadListener<NaturalPersonImportDto> {

    private static final Logger log = LoggerFactory.getLogger(NaturalPersonListener.class);


    private List<String> errorMsgList = new ArrayList<>();

    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private CodeMapper codeMapper;

    private RelatedPartyIdentityRelationshipBizMapper relatedPartyIdentityRelationshipBizMapper;

    private RelatedPartyReportedService relatedPartyReportedService;

    private NaturalPersonService naturalPersonService;

    private RelatedPartyMapper relatedPartyMapper;

    private RelatedPartyRecordMapper relatedPartyRecordMapper;

    private RelatedPartyReportedBizMapper relatedPartyReportedBizMapper;

    /**
     * 使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param codeMapper,relatedPartyIdentityRelationshipBizMapper
     */
    public NaturalPersonListener(CodeMapper codeMapper, RelatedPartyIdentityRelationshipBizMapper relatedPartyIdentityRelationshipBizMapper, RelatedPartyReportedService relatedPartyReportedService, NaturalPersonService naturalPersonService, RelatedPartyMapper relatedPartyMapper, RelatedPartyRecordMapper relatedPartyRecordMapper, RelatedPartyReportedBizMapper relatedPartyReportedBizMapper) {
        this.codeMapper = codeMapper;
        this.relatedPartyIdentityRelationshipBizMapper = relatedPartyIdentityRelationshipBizMapper;
        this.relatedPartyReportedService = relatedPartyReportedService;
        this.naturalPersonService = naturalPersonService;
        this.relatedPartyMapper = relatedPartyMapper;
        this.relatedPartyRecordMapper = relatedPartyRecordMapper;
        this.relatedPartyReportedBizMapper = relatedPartyReportedBizMapper;
    }


    /**
     * 每隔500条存储数据库,然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 500;
    private List<RelatedParty> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);


    @Override
    public void invoke(NaturalPersonImportDto naturalPersonImportDto, AnalysisContext analysisContext) {

        if (!LogicConstant.NATRURAL_PERSON_FILE_NAME.equals(analysisContext.readSheetHolder().getSheetName())) {
            throw new ApplicationException("error.990016");
        }

        log.debug("关联自然人导入解析到一条数据:{}", JsonUtil.toJson(naturalPersonImportDto));

        if (Objects.nonNull(naturalPersonImportDto)) {
            Integer rowIndex = analysisContext.readRowHolder().getRowIndex() + 1;

            //必填和合法性校验
            RelatedPartyDto relatedPartyDto = null;
            //错误消息
            List<String> errorMsg  = new ArrayList<>();

            //2023-2-1 适用范围仅会计准则跳过校验
            if (!"会计准则".equals(naturalPersonImportDto.getApplicableGuidelines())) {
                if (StringUtils.isBlank(naturalPersonImportDto.getSerialNumber()) || !naturalPersonImportDto.getSerialNumber().contains("注")) {
                    relatedPartyDto = legalityCheck(rowIndex, naturalPersonImportDto, errorMsg);
                }
            } else {
                //会计准则名称重复跳过
                RelatedPartyExample example = new RelatedPartyExample();
                example.createCriteria().andPersonNameEqualTo(naturalPersonImportDto.getPersonName()).andStatusEqualTo(CodeTableConstant.YES);
                List<RelatedParty> relatedParties = relatedPartyMapper.selectByExample(example);
                if (CollectionUtils.isEmpty(relatedParties)) {
                    relatedPartyDto = new RelatedPartyDto();
                    relatedPartyDto.setStandardAFlag(CodeTableConstant.DATA_STATUS_0);
                    relatedPartyDto.setAccountingStandardsFlag(CodeTableConstant.DATA_STATUS_1);
                } else {
                    //103145添加错误信息
                    errorMsg.add("第" + rowIndex + "行【该对象已经审核通过，无法新增保存!】");
                    errorMsgList.addAll(errorMsg);
                }
            }

            if (errorMsg.isEmpty() && Objects.nonNull(relatedPartyDto)) {
                RelatedParty relatedParty = new RelatedParty();
                BeanUtil.copy(naturalPersonImportDto, relatedParty);

                relatedParty.setLegalPersonFlag(CodeTableConstant.PERSON_TYPE_ZERO);
                //有效
                relatedParty.setStatus(CodeTableConstant.YES);
                //审核通过
                relatedParty.setProcessStatus(CodeTableConstant.PROCESS_STATUS.approved.value());
                relatedParty.setListedFlag("1");
                //主体身份
                relatedParty.setIndentitytypeId(relatedPartyDto.getIndentitytypeId());
                //关联关系
                relatedParty.setRelationshipId(relatedPartyDto.getRelationshipId());
                //从属主体id
                relatedParty.setSubordinatePrincipalId(relatedPartyDto.getSubordinatePrincipalId());

                //证件类型
                if (!StringUtils.isEmpty(naturalPersonImportDto.getCertificateType())) {
                    relatedParty.setIdTypeCode(certificateTypeConversion(naturalPersonImportDto.getCertificateType()));
                }

                //适用准则范围
                relatedParty.setStandardAFlag(relatedPartyDto.getStandardAFlag());
                relatedParty.setStandardHFlag(relatedPartyDto.getStandardHFlag());
                relatedParty.setAccountingStandardsFlag(relatedPartyDto.getAccountingStandardsFlag());
                //是否主体(会计准则默认为主体其余根据关联关系是否本人判断是否主体)
                relatedParty.setPrincipalFlag("会计准则".equals(naturalPersonImportDto.getApplicableGuidelines()) ? CodeTableConstant.DATA_STATUS_1 : "本人".equals(naturalPersonImportDto.getRelation()) ? "1" : "0");
                saveData(relatedParty);

                //cachedDataList.add(relatedParty);
                //
                 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
                //if (cachedDataList.size() >= BATCH_COUNT) {
                //    saveData();
                //    // 存储完成清理 list
                //    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                //}
            }
        }
    }


    /*
     * @author: LiuKeXin
     * @description: 证件类型转换
     * @date: 2022/11/4 10:20
     */
    public String certificateTypeConversion(String certificateType) {
        CodeExample example = new CodeExample();
        example.createCriteria().andCodeNoEqualTo("CERTIFICATE").andCodeNameEqualTo(certificateType);
        List<Code> codes = codeMapper.selectByExample(example);
        if (!CollectionUtils.isEmpty(codes)) {
            return codes.get(0).getCodeValue();
        }
        return null;
    }


    private RelatedPartyDto legalityCheck(Integer rowIndex, NaturalPersonImportDto naturalPersonImportDto, List<String> errorMsg) {

        //必填校验
        if (StringUtils.isBlank(naturalPersonImportDto.getPersonName())) {
            errorMsg.add("第" + rowIndex + "行【姓名】为空，请核实。");
        }
        if (StringUtils.isBlank(naturalPersonImportDto.getCertificateType())) {
            errorMsg.add("第" + rowIndex + "行【证件类型】为空，请核实。");
        }
        if (StringUtils.isBlank(naturalPersonImportDto.getIdNo())) {
            errorMsg.add("第" + rowIndex + "行【证件号】为空，请核实。");
        }
        if (StringUtils.isBlank(naturalPersonImportDto.getSubordinateSubject())) {
            errorMsg.add("第" + rowIndex + "行【从属主体】为空，请核实。");
        }
        if (StringUtils.isBlank(naturalPersonImportDto.getSubjectIdentity())) {
            errorMsg.add("第" + rowIndex + "行【主体身份】为空，请核实。");
        }
        if (StringUtils.isBlank(naturalPersonImportDto.getRelation())) {
            errorMsg.add("第" + rowIndex + "行【关联关系】为空，请核实。");
        }
        if (StringUtils.isBlank(naturalPersonImportDto.getPublishRelatePartyName())) {
            errorMsg.add("第" + rowIndex + "行【披露关联方名称】为空，请核实。");
        }
        //适用准则范围
        if (StringUtils.isBlank(naturalPersonImportDto.getApplicableGuidelines())) {
            errorMsg.add("第" + rowIndex + "行【适用准则范围】为空，请核实。");
        }
        //if (StringUtils.isBlank(naturalPersonImportDto.getBirthday())) {
        //    errorMsg.add("第" + rowIndex + "行【出生日期】为空，请核实。");
        //}

        Map<String, Object> map = BeanUtil.map(naturalPersonImportDto, Map.class);
        RelatedPartyDto relatedPartyDto = relatedPartyIdentityRelationshipBizMapper.selectCommonRelatedParty(map);
        if (Objects.nonNull(relatedPartyDto)) {
            //非本人不允许为空
            //if (StringUtils.isBlank(relatedPartyDto.getSubordinatePrincipalId()) && !"本人".equals(naturalPersonImportDto.getRelation())) {
            //    errorMsg.add("第" + rowIndex + "行【从属主体】不合法，请核实。");
            //}
            if (StringUtils.isBlank(relatedPartyDto.getIndentitytypeId())) {
                errorMsg.add("第" + rowIndex + "行【主体身份】不合法，请核实。");
            }
            //关联关系
            if (StringUtils.isBlank(relatedPartyDto.getRelationshipId())) {
                errorMsg.add("第" + rowIndex + "行【关联关系】不合法，请核实。");
            } else {
                RelatedPartyIdentityRelaDto inputParams = new RelatedPartyIdentityRelaDto();
                inputParams.setIdentityTypeId(relatedPartyDto.getIndentitytypeId());
                inputParams.setRelatedPartyFlag(CodeTableConstant.PERSON_TYPE_ZERO);
                RelatedPartyIdentityRelaDto relatedPartyIdentityRelaDto = relatedPartyReportedService.queryRelationshipList(inputParams);
                //关联关系集合
                List<String> list = relatedPartyIdentityRelaDto.getRelatedList().stream().map(RelatedPartyIdentityRelaDto::getId).collect(Collectors.toList());
                if (!list.contains(relatedPartyDto.getRelationshipId())) {
                    //页面不可选的关联关系
                    errorMsg.add("第" + rowIndex + "行【关联关系】不在可选范围，请核实。");
                } else {
                    inputParams.setRelationshipId(relatedPartyDto.getRelationshipId());
                    List<RelatedPartyIdentityRelaDto> resultList = relatedPartyReportedBizMapper.queryRelationshipList(inputParams);
                    if (StringUtils.isNotBlank(naturalPersonImportDto.getApplicableGuidelines())) {
                        for (String relationShipId : list) {
                            String standardAFlag = resultList.get(0).getStandardAFlag();
                            String accountingStandardsFlag = resultList.get(0).getAccountingStandardsFlag();
                            if (relationShipId.equals(relatedPartyDto.getRelationshipId())) {
                                relatedPartyDto.setStandardHFlag(CodeTableConstant.DATA_STATUS_0);
                                switch (naturalPersonImportDto.getApplicableGuidelines()) {
                                    case "全部":
                                        if (CodeTableConstant.DATA_STATUS_1.equals(standardAFlag) && CodeTableConstant.DATA_STATUS_1.equals(accountingStandardsFlag)) {
                                            relatedPartyDto.setStandardAFlag(CodeTableConstant.DATA_STATUS_1);
                                            relatedPartyDto.setAccountingStandardsFlag(CodeTableConstant.DATA_STATUS_1);
                                        } else {
                                            errorMsg.add("第" + rowIndex + "行【适用准则范围】与关联方配置中的适用范围不一致");
                                        }
                                        break;
                                    case "会计准则":
                                        if (CodeTableConstant.DATA_STATUS_1.equals(accountingStandardsFlag)) {
                                            relatedPartyDto.setStandardAFlag(CodeTableConstant.DATA_STATUS_0);
                                            relatedPartyDto.setAccountingStandardsFlag(CodeTableConstant.DATA_STATUS_1);
                                        } else {
                                            errorMsg.add("第" + rowIndex + "行【适用准则范围】与关联方配置中的适用范围不一致");
                                        }
                                        break;
                                    case "A股":
                                        if (CodeTableConstant.DATA_STATUS_1.equals(standardAFlag)) {
                                            relatedPartyDto.setStandardAFlag(CodeTableConstant.DATA_STATUS_1);
                                            relatedPartyDto.setAccountingStandardsFlag(CodeTableConstant.DATA_STATUS_0);
                                        } else {
                                            errorMsg.add("第" + rowIndex + "行【适用准则范围】与关联方配置中的适用范围不一致");
                                        }
                                        break;
                                    default:
                                        relatedPartyDto.setStandardAFlag(CodeTableConstant.DATA_STATUS_0);
                                        relatedPartyDto.setAccountingStandardsFlag(CodeTableConstant.DATA_STATUS_0);
                                        break;
                                }
                                break;
                            }
                        }
                    }
                }
            }

            if (!"本人".equals(naturalPersonImportDto.getRelation())) {
                //从属主体校验
                RelatedPartyParamDto queryInfo = new RelatedPartyParamDto();
                queryInfo.setIndentitytypeId(relatedPartyDto.getIndentitytypeId());
                queryInfo.setStatus("1");
                queryInfo.setProcessStatus("3");
                queryInfo.setPrincipalFlag("1");
                List<RelatedPartyResultDto> relatedPartyResultDtos = naturalPersonService.queryNaturalPrincipal(queryInfo);
                if (CollectionUtils.isEmpty(relatedPartyResultDtos)) {
                    //页面没有可选的从属主体
                    errorMsg.add("第" + rowIndex + "行【没有可选的从属主体】");
                } else {
                    List<String> list = relatedPartyResultDtos.stream().map(RelatedPartyResultDto::getId).collect(Collectors.toList());
                    if (!list.contains(relatedPartyDto.getSubordinatePrincipalId())) {
                        errorMsg.add("第" + rowIndex + "行【从属主体】不在可选的范围");
                    }
                }
            }

            if (StringUtils.isNotBlank(naturalPersonImportDto.getIdNo()) &&
                    StringUtils.isNotBlank(naturalPersonImportDto.getCertificateType())
                    && StringUtils.isNotBlank(naturalPersonImportDto.getPersonName())
                    && StringUtils.isNotBlank(relatedPartyDto.getRelationshipId())
                    && StringUtils.isNotBlank(relatedPartyDto.getIndentitytypeId())
                    && StringUtils.isNotBlank(naturalPersonImportDto.getPersonName())) {
                RelatedPartyExample relatedPartyExample = new RelatedPartyExample();
                relatedPartyExample.createCriteria()
                        .andIndentitytypeIdEqualTo(relatedPartyDto.getIndentitytypeId())
                        .andRelationshipIdEqualTo(relatedPartyDto.getRelationshipId())
                        .andPersonNameEqualTo(naturalPersonImportDto.getPersonName())
                        .andIdTypeCodeEqualTo(certificateTypeConversion(naturalPersonImportDto.getCertificateType()))
                        .andIdNoEqualTo(naturalPersonImportDto.getIdNo())
                        .andStatusEqualTo(CodeTableConstant.YES);
                List<RelatedParty> list = relatedPartyMapper.selectByExample(relatedPartyExample);
                if (list.size() != 0) {
                    log.debug("该对象已经审核通过，无法新增保存!");
                    errorMsg.add("第" + rowIndex + "行【该对象已经审核通过，无法新增保存!】");
                } else {
                    RelatedPartyRecordExample relatedPartyRecordExample = new RelatedPartyRecordExample();
                    relatedPartyRecordExample.createCriteria()
                            .andIndentitytypeIdEqualTo(relatedPartyDto.getIndentitytypeId())
                            .andRelationshipIdEqualTo(relatedPartyDto.getRelationshipId())
                            .andPersonNameEqualTo(naturalPersonImportDto.getPersonName())
                            .andIdTypeCodeEqualTo(certificateTypeConversion(naturalPersonImportDto.getCertificateType()))
                            .andIdNoEqualTo(naturalPersonImportDto.getIdNo())
                            .andProcessStatusEqualTo(CodeTableConstant.PROCESS_STATUS.review.value())
                            .andStatusEqualTo(CodeTableConstant.YES);
                    List<RelatedPartyRecord> rprList = relatedPartyRecordMapper.selectByExample(relatedPartyRecordExample);
                    if (rprList.size() != 0) {
                        errorMsg.add("第" + rowIndex + "行【该对象有正在审核中的事项，无法编辑保存!】");
                    }
                }
            }
            errorMsgList.addAll(errorMsg);
        } else {
            errorMsgList.addAll(errorMsg);
        }
        return relatedPartyDto;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
    }


    /**
     * 加上存储数据库
     * @return
     */
    private void saveData(RelatedParty relatedParty) {
        log.debug("开始存储数据库！{}", JsonUtil.toJson(relatedParty));
        relatedPartyMapper.insertSelective(relatedParty);
        log.debug("存储数据库成功！");
    }


    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.debug("{}条数据，开始存储数据库！{}", cachedDataList.size(), JsonUtil.toJson(cachedDataList));
        if (!CollectionUtils.isEmpty(cachedDataList)) {
            relatedPartyIdentityRelationshipBizMapper.bulkInsertRelatedParty(cachedDataList);
        }
        log.info("存储数据库成功！");
    }

    public List<String> getErrorMsgList() {
        return errorMsgList;
    }
}

3.导出数据（分sheet）

  ExcelWriter excelWriter = null;

        String fileName = String.valueOf(System.currentTimeMillis()).concat("_监测数据");
        try {
            excelWriter = EasyExcel.write(ExcelUtil.getOutputStream(fileName, response)).registerWriteHandler(ExcelUtil.getHorizontalCellStyleStrategy()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
            //Map<String, Map<String, String>> key 是 flag值 value 是 chName 和 unitName
            Map<String, Object> flagMap = new HashMap<>();
            //判断开始时间和结束时间
            //如果开始时间和结束时间相差不到一个星期，直接返回数据
            //86400L=一天的秒数
            Long weekMills = 86400L*7;
            if((endTime - startTime) <= weekMills){
                historicalDataExport(deviceIds, deviceNames, startTime, endTime, response);
                return;
            }

            for (int i = 0; i < deviceIds.size(); i++) {

                String deviceId = deviceIds.get(i);
                //查询单个设备的属性快照
                ResponseBean responseBean = apiService.queryDeviceParamStatus(Long.parseLong(deviceId));
                if (StringUtils.equals(BaseCode.API_SUCCESS_CODE.code(), String.valueOf(responseBean.getCode())) && null != responseBean.getData()) {
                    getFlagMapByDevParams(responseBean, flagMap);
                }

                List<LinkedHashMap<String, Object>> maps = new ArrayList<>();
                DevParamHistoryDataEntity devParamHistoryDataEntity = new DevParamHistoryDataEntity();

                while (endTime > startTime){
                    //6.批量查询属性的历史数据接口调整

                    devParamHistoryDataEntity.setStartTime(startTime);
                    if(endTime > (startTime+weekMills)){
                        devParamHistoryDataEntity.setEndTime(startTime+weekMills);
                    }else{
                        devParamHistoryDataEntity.setEndTime(endTime);
                    }
                    devParamHistoryDataEntity.setDeviceId(Long.parseLong(deviceId));
                    ResponseBean result = apiService.queryDeviceParamHistoryData(devParamHistoryDataEntity);

                    if (StringUtils.equals(BaseCode.API_SUCCESS_CODE.code(), String.valueOf(result.getCode())) && null != result.getData()) {
                        maps.addAll((List) JSON.parseArray(result.getData().toString(), LinkedHashMap.class));
                    }
                    startTime += weekMills;
                }

                //头内容
                LinkedHashMap headMap = getHeadMap(flagMap, maps.get(0));

                // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel.writerSheet(i, deviceNames.get(i)).head(head(headMap)).build();

                //内容
                List<List<Object>> lists = dataList(maps);
                excelWriter.write(lists, writeSheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new InterfaceException(INTERFACE_EXCEPTION, "历史数据导出失败！");
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }