data-daq/service-daq/src/main/java/com/simuwang/daq/service/NavEmailParser.java

package com.simuwang.daq.service;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.exceptions.ExceptionUtil;
import cn.hutool.core.lang.Pair;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.StrUtil;
import com.simuwang.base.common.conts.EmailDataDirectionConst;
import com.simuwang.base.common.conts.EmailFieldConst;
import com.simuwang.base.common.conts.EmailTypeConst;
import com.simuwang.base.common.util.DateUtils;
import com.simuwang.base.common.util.ExcelUtil;
import com.simuwang.base.common.util.StringUtil;
import com.simuwang.base.pojo.dto.EmailContentInfoDTO;
import com.simuwang.base.pojo.dto.EmailFundNavDTO;
import com.simuwang.base.pojo.dto.FieldPositionDTO;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.File;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author mozuwen
 * @date 2024-09-04
 * @description 净值邮件解析器
 */
@Component
public class NavEmailParser extends AbstractEmailParser {

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

    @Value("${email.file.path}")
    private String path;

    @Value("${email.parse.force-template-enable}")
    private boolean forceTemplateEnable;

    private final ValuationEmailParser valuationEmailParser;
    private final EmailTemplateService emailTemplateService;

    private static final int MAX_COLUMN = 20;

    private static final List<String> NOT_CONVERT_FIELD_LIST = ListUtil.toList("TA代码");

    public NavEmailParser(ValuationEmailParser valuationEmailParser, EmailTemplateService emailTemplateService) {
        this.valuationEmailParser = valuationEmailParser;
        this.emailTemplateService = emailTemplateService;
    }

    @Override
    public boolean isSupport(Integer emailType) {
        return EmailTypeConst.NAV_EMAIL_TYPE.equals(emailType);
    }

    @Override
    public List<EmailFundNavDTO> parse(EmailContentInfoDTO emailContentInfoDTO, Map<String, List<String>> emailFieldMap) {
        List<EmailFundNavDTO> emailFundNavDTOList = CollUtil.newArrayList();
        String emailContent = emailContentInfoDTO.getEmailContent();
        // 1.解析邮件正文
        if (StrUtil.isNotBlank(emailContentInfoDTO.getFilePath()) && ExcelUtil.isHTML(emailContentInfoDTO.getFileName())) {
            String excelFilePath = emailContentInfoDTO.getFilePath().replace(".html", ".xlsx");
            emailFundNavDTOList = parseEmailContent(emailContent, excelFilePath, emailFieldMap);
        }
        // 2.解析邮件excel附件
        if (StrUtil.isNotBlank(emailContentInfoDTO.getFilePath()) && ExcelUtil.isExcel(emailContentInfoDTO.getFileName())) {
            List<EmailFundNavDTO> fundNavDTOList = parseExcelFile(emailContentInfoDTO.getFilePath(), emailFieldMap);
            Optional.ofNullable(fundNavDTOList).ifPresent(emailFundNavDTOList::addAll);
        }
        // 3.解析邮件pdf附件
        if (StrUtil.isNotBlank(emailContentInfoDTO.getFilePath()) && ExcelUtil.isPdf(emailContentInfoDTO.getFileName())) {
            String excelFilePath = path + emailContentInfoDTO.getEmailAddress() + "/" + emailContentInfoDTO.getEmailDate().substring(0, 10).replaceAll("-", "")
                    + "/" + emailContentInfoDTO.getFileName().replace(".pdf", ".xlsx").replace(".PDF", ".xlsx");
            List<EmailFundNavDTO> fundNavDTOList = parsePdfFile(emailContentInfoDTO.getFilePath(), excelFilePath, emailFieldMap);
            Optional.ofNullable(fundNavDTOList).ifPresent(emailFundNavDTOList::addAll);
        }
        // 4.解析邮件zip,rar附件
        if (StrUtil.isNotBlank(emailContentInfoDTO.getFilePath()) && (ExcelUtil.isZip(emailContentInfoDTO.getFileName()) || ExcelUtil.isRAR(emailContentInfoDTO.getFileName()))) {
            List<EmailFundNavDTO> fundNavDTOList = parsePackageFile(emailContentInfoDTO, emailContentInfoDTO.getFileName(), emailContentInfoDTO.getFilePath(), emailFieldMap);
            Optional.ofNullable(fundNavDTOList).ifPresent(emailFundNavDTOList::addAll);
        }

        // 兼容净值邮件,但附件是估值表的情况
        if (CollUtil.isEmpty(emailFundNavDTOList)) {
            // 判断文件名是否包含"估值表"
            String fileName = emailContentInfoDTO.getFileName();
            if (StrUtil.isNotBlank(fileName) && fileName.contains("估值表")) {
                emailFundNavDTOList = valuationEmailParser.parse(emailContentInfoDTO, emailFieldMap);
            }
        }
        // email_fund_nav增加template_id字段
        if (CollUtil.isNotEmpty(emailFundNavDTOList)) {
            emailFundNavDTOList.forEach(e -> e.setTemplateId(0));
        }

        // 通用模版解析 -> 根据配置的模板进行解析
        long successNavCount = 0;
        if (CollUtil.isNotEmpty(emailFundNavDTOList)) {
            successNavCount = emailFundNavDTOList.stream().filter(e -> e != null && StrUtil.isBlank(e.getFailReason())).count();
        }
        //如果通用模板解析不到正确数据，就走模板解析一次
        if (successNavCount == 0 || forceTemplateEnable) {
            List<EmailFundNavDTO> templateFundNavDTOList = emailTemplateService.parseUsingTemplate(emailContentInfoDTO);
            if(CollUtil.isNotEmpty(templateFundNavDTOList)){
                emailFundNavDTOList.addAll(templateFundNavDTOList);
            }
        }
        return emailFundNavDTOList;
    }

    private List<EmailFundNavDTO> parsePackageFile(EmailContentInfoDTO emailContentInfoDTO, String fileName, String filePath, Map<String, List<String>> emailFieldMap) {
        List<EmailFundNavDTO> emailFundNavDTOList = CollUtil.newArrayList();
        if(ExcelUtil.isZip(filePath)){
            String destPath = filePath.replaceAll(".zip", "").replaceAll(".ZIP", "");
            log.info("压缩包地址:{},解压后文件地址:{}", filePath, destPath);
            List<String> dir = ExcelUtil.extractCompressedFiles(filePath, destPath);
            for (String zipFilePath : dir) {
                emailFundNavDTOList.addAll(parseZipFile(emailContentInfoDTO, zipFilePath, emailFieldMap));
                File file = new File(zipFilePath);
                if (file.isDirectory()) {
                    for (String navFilePath : Objects.requireNonNull(file.list())) {
                        emailFundNavDTOList.addAll(parseZipFile(emailContentInfoDTO, navFilePath, emailFieldMap));
                    }
                }
            }
        }
        try{
            if(ExcelUtil.isRAR(filePath)){
                String destPath = filePath.replaceAll(".rar", "").replaceAll(".RAR", "");
                File destFile = new File(destPath);
                if (!destFile.exists()) {
                    destFile.mkdirs();
                }
                List<String> rarDir = ExcelUtil.extractRar(filePath, destPath);
                for (String rarFilePath : rarDir) {
                    emailFundNavDTOList.addAll(parseZipFile(emailContentInfoDTO, rarFilePath, emailFieldMap));
                    File file = new File(rarFilePath);
                    if (file.isDirectory()) {
                        for (String navFilePath : Objects.requireNonNull(file.list())) {
                            emailFundNavDTOList.addAll(parseZipFile(emailContentInfoDTO, navFilePath, emailFieldMap));
                        }
                    }
                }
            }
        }catch (Exception e){
            log.error(e.getMessage(),e);
        }
        return emailFundNavDTOList;
    }

    private List<EmailFundNavDTO> parseZipFile(EmailContentInfoDTO emailContentInfoDTO, String zipFilePath, Map<String, List<String>> emailFieldMap) {
        List<EmailFundNavDTO> fundNavDTOList = CollUtil.newArrayList();
        if (ExcelUtil.isPdf(zipFilePath)) {
            String excelFilePath = zipFilePath.replace(".pdf", ".xlsx").replace(".PDF", ".xlsx");
            fundNavDTOList = parsePdfFile(zipFilePath, excelFilePath, emailFieldMap);
        }
        if (ExcelUtil.isExcel(zipFilePath)) {
            fundNavDTOList = parseExcelFile(zipFilePath, emailFieldMap);
        }
        if (ExcelUtil.isZip(zipFilePath)) {
            String name = new File(zipFilePath).getName();
            fundNavDTOList = parsePackageFile(emailContentInfoDTO, name, zipFilePath, emailFieldMap);
        }
        return fundNavDTOList;
    }

    private List<EmailFundNavDTO> parsePdfFile(String filePath, String excelFilePath, Map<String, List<String>> emailFieldMap) {
        excelFilePath = ExcelUtil.pdfConvertToExcel(filePath, excelFilePath);
        if (StrUtil.isBlank(excelFilePath)) {
            return CollUtil.newArrayList();
        }
        return parseExcelFileFrom(excelFilePath, emailFieldMap);
    }

    private List<EmailFundNavDTO> parseExcelFileFrom(String filePath, Map<String, List<String>> emailFieldMap) {
        Sheet sheet = ExcelUtil.getFirstSheet(filePath);
        if (sheet == null) {
            log.info("获取不到有效的sheet页面,文件路径：{}", filePath);
            return CollUtil.newArrayList();
        }
        // 1.找到表头所在位置
        Map<String, Pair<Integer, Integer>> fieldPositionMap = getFieldPositionFromPdf(sheet, emailFieldMap);
        if (MapUtil.isEmpty(fieldPositionMap)) {
            log.warn("找不到文件表头字段 -> 文件:{}", filePath);
            return CollUtil.newArrayList();
        }
        // 2.解析sheet中的净值数据
        List<EmailFundNavDTO> emailFundNavDTOList = parseSheetData(filePath, sheet, fieldPositionMap, null);
        // 3.校验净值数据格式 并 设置数据校验不通过的原因
        if (CollUtil.isNotEmpty(emailFundNavDTOList)) {
            emailFundNavDTOList.forEach(e -> e.setFailReason(super.checkDataFailReason(e)));
        }
        return emailFundNavDTOList;
    }

    private Map<String, Pair<Integer, Integer>> getFieldPositionFromPdf(Sheet sheet, Map<String, List<String>> emailFieldMap) {
        Map<String, List<FieldPositionDTO>> tempFieldPositionMap = MapUtil.newHashMap();
        int lastRowNum = sheet.getLastRowNum();
        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
            Row sheetRow = sheet.getRow(rowNum);
            if (sheetRow == null) {
                continue;
            }
            int lastCellNum = sheetRow.getLastCellNum();
            for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
                Cell cell = sheetRow.getCell(cellNum);
                if (cell == null) {
                    continue;
                }
                String cellValue = ExcelUtil.getCellValue(cell);
                // 移除掉非中文字符
                String newCellValue = StringUtil.retainChineseCharacters(cellValue, NOT_CONVERT_FIELD_LIST);
                String field = fieldMatch(newCellValue, emailFieldMap);
                if (StrUtil.isNotBlank(field)) {
                    List<FieldPositionDTO> fieldPositionDTOList = tempFieldPositionMap.getOrDefault(field, new ArrayList<>());
                    fieldPositionDTOList.add(new FieldPositionDTO(newCellValue, Pair.of(rowNum, cellNum)));
                    tempFieldPositionMap.put(field, fieldPositionDTOList);
                }
            }
        }
        // 判断是不是份额基金净值文件格式(同时存在两个备案编码字段)
        return handlerFieldPositionFromPdf(tempFieldPositionMap);
    }

    private Map<String, Pair<Integer, Integer>> handlerFieldPositionFromPdf(Map<String, List<FieldPositionDTO>> tempFieldPositionMap) {
        Map<String, Pair<Integer, Integer>> fieldPositionMap = MapUtil.newHashMap();
        boolean hasParentField = tempFieldPositionMap.keySet().stream().anyMatch(e -> e.contains("parent"));
        for (Map.Entry<String, List<FieldPositionDTO>> entry : tempFieldPositionMap.entrySet()) {
            String field = entry.getKey();
            List<FieldPositionDTO> fieldPositionDTOList = entry.getValue();
            int size = fieldPositionDTOList.size();
            if (size == 1) {
                fieldPositionMap.put(field, fieldPositionDTOList.get(0).getPair());
                continue;
            }
            if ((!hasParentField && size > 1)) {
                if (EmailFieldConst.REGISTER_NUMBER.equals(field)) {
                    Pair<Integer, Integer> pair = fieldPositionDTOList.stream()
                            .filter(e -> !e.getFieldValue().contains("协会") && !e.getFieldValue().contains("备案")).map(FieldPositionDTO::getPair).findFirst().orElse(null);
                    fieldPositionMap.put(field, pair);
                } else {
                    fieldPositionMap.put(field, fieldPositionDTOList.get(0).getPair());
                }
                continue;
            }
            if ((hasParentField && size > 1)) {
                fieldPositionMap.put(field, fieldPositionDTOList.get(0).getPair());
            }
        }

        // 母基金缺少代码的情况
        if (hasParentField && fieldPositionMap.get(EmailFieldConst.PARENT_REGISTER_NUMBER) == null) {
            List<FieldPositionDTO> fieldPositionDTOS = tempFieldPositionMap.get(EmailFieldConst.REGISTER_NUMBER);
            if (CollUtil.isNotEmpty(fieldPositionDTOS)) {
                Pair<Integer, Integer> parentRegisterNumberPair = fieldPositionDTOS.stream()
                        .filter(e -> e.getFieldValue().contains("协会") || e.getFieldValue().contains("备案")).map(FieldPositionDTO::getPair).findFirst().orElse(null);
                fieldPositionMap.put(EmailFieldConst.PARENT_REGISTER_NUMBER, parentRegisterNumberPair);
            }
        }
        return fieldPositionMap;
    }


    /**
     * 解析邮件excel附件
     *
     * @param filePath      邮件excel附件地址
     * @param emailFieldMap 邮件字段识别规则映射表
     * @return 解析到的净值数据
     */
    private List<EmailFundNavDTO> parseExcelFile(String filePath, Map<String, List<String>> emailFieldMap) {
        Sheet sheet = ExcelUtil.getFirstSheet(filePath);
        if (sheet == null) {
            log.info("获取不到有效的sheet页面,文件路径：{}", filePath);
            return CollUtil.newArrayList();
        }
        // 1.找到表头所在位置
        Map<String, Pair<Integer, Integer>> fieldPositionMap = getFieldPosition(sheet, emailFieldMap);
        if (MapUtil.isEmpty(fieldPositionMap)) {
            log.warn("找不到文件表头字段 -> 文件:{}", filePath);
            return CollUtil.newArrayList();
        }
        // 2.解析sheet中的净值数据
        List<EmailFundNavDTO> emailFundNavDTOList = parseSheetData(filePath, sheet, fieldPositionMap, null);
        // 3.校验净值数据格式 并 设置数据校验不通过的原因
        if (CollUtil.isNotEmpty(emailFundNavDTOList)) {
            emailFundNavDTOList.forEach(e -> e.setFailReason(super.checkDataFailReason(e)));
        }
        return emailFundNavDTOList;
    }

    /**
     * 解析邮件正文
     *
     * @param emailContent  正文内容
     * @param emailFieldMap 邮件字段识别规则映射表
     * @return 解析到的净值数据
     */
    private List<EmailFundNavDTO> parseEmailContent(String emailContent, String excelFilePath, Map<String, List<String>> emailFieldMap) {
        excelFilePath = ExcelUtil.contentConvertToExcel(emailContent, excelFilePath);
        if (StrUtil.isBlank(excelFilePath)) {
            return CollUtil.newArrayList();
        }
        return parseExcelFile(excelFilePath, emailFieldMap);
    }

    /**
     * 根据字段所在表格的位置提取净值数据
     *
     * @param filePath         文件路径·
     * @param sheet            表格中的sheet页
     * @param fieldPositionMap 字段所在表格中的位置
     * @param direction        表格数据的形式：1-行，2-列
     * @return 净值数据
     */
    private List<EmailFundNavDTO> parseSheetData(String filePath, Sheet sheet, Map<String, Pair<Integer, Integer>> fieldPositionMap, Integer direction) {
        List<EmailFundNavDTO> fundNavDTOList = CollUtil.newArrayList();
        // 通过表头所在位置判断是行数据还是列数据
        Integer dataDirectionType = direction != null ? direction : ExcelUtil.detectDataDirection(fieldPositionMap);
        // 数据起始行,起始列
        int initRow = dataDirectionType.equals(EmailDataDirectionConst.ROW_DIRECTION_TYPE) ? fieldPositionMap.values().stream().map(Pair::getKey).max(Integer::compareTo).orElse(0)
                : fieldPositionMap.values().stream().map(Pair::getKey).min(Integer::compareTo).orElse(0);
        int initColumn = fieldPositionMap.values().stream().map(Pair::getValue).min(Integer::compareTo).orElse(0);
        if (dataDirectionType.equals(EmailDataDirectionConst.ROW_DIRECTION_TYPE)) {
            // 表头字段-列号映射关系
            Map<String, Integer> fieldColumnMap = getFieldRow(fieldPositionMap);
            int lastRowNum = sheet.getLastRowNum();
            // 遍历可能的数据行
            for (int rowNum = initRow + 1; rowNum <= lastRowNum; rowNum++) {
                Row sheetRow = sheet.getRow(rowNum);
                try {
                    Optional.ofNullable(readSheetRowData(sheetRow, fieldColumnMap)).ifPresent(fundNavDTOList::addAll);
                } catch (Exception e) {
                    log.error("读取行数据报错 -> 行号:{},文件路径:{},堆栈信息:{}", rowNum, filePath, ExceptionUtil.stacktraceToString(e));
                }
            }
        }
        if (dataDirectionType.equals(EmailDataDirectionConst.COLUMN_DIRECTION_TYPE)) {
            // 表头字段-行号映射关系
            Map<Integer, String> fieldRowMap = getRowField(fieldPositionMap);
            int lastRow = fieldPositionMap.values().stream().map(Pair::getKey).max(Integer::compareTo).orElse(0);
            // 遍历每一列
            for (int columnNum = initColumn + 1; columnNum < EmailDataDirectionConst.MAX_ROW_COLUMN; columnNum++) {
                Map<String, String> fieldValueMap = MapUtil.newHashMap();
                for (int rowNum = initRow; rowNum <= lastRow; rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    Cell cell = row.getCell(columnNum);
                    if (cell == null) {
                        continue;
                    }
                    fieldValueMap.put(fieldRowMap.get(rowNum), ExcelUtil.getCellValue(cell));
                }
                Optional.ofNullable(buildEmailFundNavDTO(fieldValueMap)).ifPresent(fundNavDTOList::add);
            }
        }

        // 兼容净值日期为空的情况
        addPriceDateIfMiss(fundNavDTOList, getPriceDateFromSheet(sheet, initRow));
        return fundNavDTOList;
    }

    private void addPriceDateIfMiss(List<EmailFundNavDTO> fundNavDTOList, String priceDate) {
        if (fundNavDTOList.stream().map(EmailFundNavDTO::getPriceDate).allMatch(StrUtil::isBlank)) {
            fundNavDTOList.forEach(e -> e.setPriceDate(priceDate));
        }
    }

    private String getPriceDateFromSheet(Sheet sheet, Integer maxRowNum) {
        Map<Integer, String> priceDateMap = MapUtil.newHashMap();
        for (int rowNum = 0; rowNum < maxRowNum; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            int lastCellNum = row.getLastCellNum();
            for (int columnNum = 0; columnNum < lastCellNum; columnNum++) {
                Cell cell = row.getCell(columnNum);
                if (cell == null) {
                    continue;
                }
                String cellValue = ExcelUtil.getCellValue(cell);
                if (StrUtil.isNotBlank(cellValue) && cellValue.contains("截至")) {
                    int index = cellValue.indexOf("截至");
                    String date = cellValue.substring(index + 2, index + 2 + 10);
                    if (StrUtil.isNotBlank(date)) {
                        date = date.replaceAll("年", "-").replaceAll("月", "-");
                    }
                    priceDateMap.put(1, date);
                    continue;
                }
                String priceDate = DateUtils.stringToDate(cellValue);
                if (StrUtil.isNotBlank(priceDate)) {
                    priceDateMap.put(2, priceDate);
                }
            }
        }
        if (MapUtil.isNotEmpty(priceDateMap)) {
            Integer key = priceDateMap.keySet().stream().min(Integer::compareTo).orElse(null);
            return priceDateMap.get(key);
        }
        return null;
    }

    private EmailFundNavDTO buildEmailFundNavDTO(Map<String, String> fieldValueMap) {
        if (MapUtil.isEmpty(fieldValueMap) || fieldValueMap.values().stream().allMatch(StrUtil::isBlank)) {
            return null;
        }
        EmailFundNavDTO fundNavDTO = new EmailFundNavDTO();
        fundNavDTO.setFundName(fieldValueMap.get(EmailFieldConst.FUND_NAME));
        fundNavDTO.setRegisterNumber(fieldValueMap.get(EmailFieldConst.REGISTER_NUMBER));
        String priceDate = fieldValueMap.get(EmailFieldConst.PRICE_DATE);
        boolean isDateFormat = StrUtil.isNotBlank(priceDate) && StringUtil.isNumeric(priceDate) && StringUtil.compare2NumericValue(priceDate);
        if (isDateFormat) {
            priceDate = ExcelUtil.convertExcelDateToString(priceDate);
        }
        fundNavDTO.setPriceDate(priceDate);
        fundNavDTO.setNav(fieldValueMap.get(EmailFieldConst.NAV));
        fundNavDTO.setCumulativeNavWithdrawal(fieldValueMap.get(EmailFieldConst.CUMULATIVE_NAV_WITHDRAWAL));

        // pdf解析到的值带有",",比如:"10,656,097.37"
        String assetNet = fieldValueMap.get(EmailFieldConst.ASSET_NET);
        fundNavDTO.setAssetNet(ExcelUtil.numberDataStripCommas(assetNet));

        String assetShares = fieldValueMap.get(EmailFieldConst.ASSET_SHARE);
        fundNavDTO.setAssetShare(ExcelUtil.numberDataStripCommas(assetShares));

        return fundNavDTO;
    }

    private List<EmailFundNavDTO> readSheetRowData(Row sheetRow, Map<String, Integer> columnFieldMap) {
        if (sheetRow == null) {
            return null;
        }
        String nav = columnFieldMap.get(EmailFieldConst.NAV) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.NAV)) != null
                ? ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.NAV))) : null;
        String cumulativeNavWithdrawal = columnFieldMap.get(EmailFieldConst.CUMULATIVE_NAV_WITHDRAWAL) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.CUMULATIVE_NAV_WITHDRAWAL)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.CUMULATIVE_NAV_WITHDRAWAL))) : null;
        String assetNet = columnFieldMap.get(EmailFieldConst.ASSET_NET) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.ASSET_NET)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.ASSET_NET))) : null;
        List<EmailFundNavDTO> fundNavDTOList = CollUtil.newArrayList();
        EmailFundNavDTO emailFundNavDTO = new EmailFundNavDTO();
        String priceDate = columnFieldMap.get(EmailFieldConst.PRICE_DATE) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PRICE_DATE)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PRICE_DATE))) : null;
        boolean isDateFormat = StrUtil.isNotBlank(priceDate) && StringUtil.isNumeric(priceDate) && StringUtil.compare2NumericValue(priceDate);
        if (isDateFormat) {
            priceDate = ExcelUtil.convertExcelDateToString(priceDate);
        }
        priceDate = DateUtils.stringToDate(priceDate);
        //如果返回null，则说明日期格式错误，此时需要保留原始值
        if(StringUtil.isNull(priceDate)){
            priceDate = columnFieldMap.get(EmailFieldConst.PRICE_DATE) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PRICE_DATE)) != null ?
                    ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PRICE_DATE))) : null;
        }
        // 份额基金净值文件格式
        long parentFiledCount = columnFieldMap.keySet().stream().filter(e -> e.contains("parent")).count();
        if (parentFiledCount >= 1) {
            Optional.ofNullable(buildParentNav(sheetRow, columnFieldMap, priceDate)).ifPresent(fundNavDTOList::add);
        }
        emailFundNavDTO.setPriceDate(priceDate);
        String fundName = ExcelUtil.getPriorityFieldValue(sheetRow, columnFieldMap.get(EmailFieldConst.LEVEL_FUND_NAME), columnFieldMap.get(EmailFieldConst.FUND_NAME));
        emailFundNavDTO.setFundName(fundName);

        String registerNumber = ExcelUtil.getPriorityFieldValue(sheetRow, columnFieldMap.get(EmailFieldConst.LEVEL_REGISTER_NUMBER), columnFieldMap.get(EmailFieldConst.REGISTER_NUMBER));
        emailFundNavDTO.setRegisterNumber(registerNumber);

        emailFundNavDTO.setNav(nav);
        emailFundNavDTO.setCumulativeNavWithdrawal(cumulativeNavWithdrawal);
        String virtualNav = columnFieldMap.get(EmailFieldConst.VIRTUAL_NAV) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.VIRTUAL_NAV)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.VIRTUAL_NAV))) : null;
        emailFundNavDTO.setVirtualNav(virtualNav);

        emailFundNavDTO.setAssetNet(ExcelUtil.numberDataStripCommas(assetNet));

        String assetShares = columnFieldMap.get(EmailFieldConst.ASSET_SHARE) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.ASSET_SHARE)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.ASSET_SHARE))) : null;
        emailFundNavDTO.setAssetShare(ExcelUtil.numberDataStripCommas(assetShares));

        fundNavDTOList.add(emailFundNavDTO);
        return fundNavDTOList;
    }

    private EmailFundNavDTO buildParentNav(Row sheetRow, Map<String, Integer> columnFieldMap, String priceDate) {
        EmailFundNavDTO emailFundNavDTO = new EmailFundNavDTO();
        String nav = columnFieldMap.get(EmailFieldConst.PARENT_NAV) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_NAV)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_NAV))) : null;
        String cumulativeNavWithdrawal = columnFieldMap.get(EmailFieldConst.PARENT_CUMULATIVE_NAV_WITHDRAWAL) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_CUMULATIVE_NAV_WITHDRAWAL)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_CUMULATIVE_NAV_WITHDRAWAL))) : null;
        if (StrUtil.isBlank(nav) && StrUtil.isBlank(cumulativeNavWithdrawal)) {
            return null;
        }
        emailFundNavDTO.setPriceDate(priceDate);
        String fundName = columnFieldMap.get(EmailFieldConst.PARENT_FUND_NAME) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_FUND_NAME)).getStringCellValue() != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_FUND_NAME))) : null;
        emailFundNavDTO.setFundName(fundName);
        String registerNumber = columnFieldMap.get(EmailFieldConst.PARENT_REGISTER_NUMBER) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_REGISTER_NUMBER)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_REGISTER_NUMBER))) : null;
        emailFundNavDTO.setRegisterNumber(registerNumber);
        emailFundNavDTO.setNav(nav);
        emailFundNavDTO.setCumulativeNavWithdrawal(cumulativeNavWithdrawal);
        String virtualNav = columnFieldMap.get(EmailFieldConst.PARENT_VIRTUAL_NAV) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_VIRTUAL_NAV)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_VIRTUAL_NAV))) : null;
        emailFundNavDTO.setVirtualNav(virtualNav);

        String assetNet = columnFieldMap.get(EmailFieldConst.PARENT_ASSET_NET) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_ASSET_NET)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_ASSET_NET))) : null;
        emailFundNavDTO.setAssetNet(ExcelUtil.numberDataStripCommas(assetNet));

        String assetShares = columnFieldMap.get(EmailFieldConst.PARENT_ASSET_SHARE) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_ASSET_SHARE)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.PARENT_ASSET_SHARE))) : null;
        emailFundNavDTO.setAssetShare(ExcelUtil.numberDataStripCommas(assetShares));

        return emailFundNavDTO;
    }

    private Map<String, Integer> getFieldRow(Map<String, Pair<Integer, Integer>> fieldPositionMap) {
        // 考虑日期字段识别逻辑的问题
        long rowNumCount = fieldPositionMap.values().stream().map(Pair::getKey).distinct().count();
        if (rowNumCount > 1) {
            // 存在合并单元格的方式 -> 日期字段所在位置可能会存在错误
            Pair<Integer, Integer> priceDatePair = fieldPositionMap.get(EmailFieldConst.PRICE_DATE);
            //补丁，如果存在合并单元格，判断日期所在的行是否有其他字段列，如果存在，则表名是行格式数据，而不是其他格式数据
            if(priceDatePair != null){
                List<Integer> pairKey = fieldPositionMap.values().stream().map(Pair::getKey).collect(Collectors.toList());
                List<Integer> priceDateKey = pairKey.stream().filter(e -> e == priceDatePair.getKey()).collect(Collectors.toList());
                if(priceDateKey.size() < 2){
                    fieldPositionMap.remove(EmailFieldConst.PRICE_DATE);
                }
            }
        }
        Map<String, Integer> fieldRowMap = MapUtil.newHashMap();
        for (Map.Entry<String, Pair<Integer, Integer>> fieldPositionEntry : fieldPositionMap.entrySet()) {
            String field = fieldPositionEntry.getKey();
            Integer column = fieldPositionEntry.getValue().getValue();
            fieldRowMap.put(field, column);
        }
        return fieldRowMap;
    }

    private Map<Integer, String> getRowField(Map<String, Pair<Integer, Integer>> fieldPositionMap) {
        Map<Integer, String> fieldRowMap = MapUtil.newHashMap();
        for (Map.Entry<String, Pair<Integer, Integer>> fieldPositionEntry : fieldPositionMap.entrySet()) {
            String field = fieldPositionEntry.getKey();
            Integer column = fieldPositionEntry.getValue().getKey();
            fieldRowMap.put(column, field);
        }
        return fieldRowMap;
    }

    /**
     * 找出excel中表头所在的位置
     *
     * @param sheet         表格工作簿
     * @param emailFieldMap 邮件字段识别规则映射表
     * @return excel中表头所在的位置(行, 列)
     */
    private Map<String, Pair<Integer, Integer>> getFieldPosition(Sheet sheet, Map<String, List<String>> emailFieldMap) {
        Map<String, List<FieldPositionDTO>> tempFieldPositionMap = MapUtil.newHashMap();
        int lastRowNum = sheet.getLastRowNum();
        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
            Row sheetRow = sheet.getRow(rowNum);
            if (sheetRow == null) {
                continue;
            }
            int lastCellNum = sheetRow.getLastCellNum();
            for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
                Cell cell = sheetRow.getCell(cellNum);
                if (cell == null) {
                    continue;
                }
                String cellValue = ExcelUtil.getCellValue(cell);
                // 移除掉非中文字符
                String newCellValue = StringUtil.retainChineseCharacters(cellValue, NOT_CONVERT_FIELD_LIST);
                String field = fieldMatch(newCellValue, emailFieldMap);
                if (StrUtil.isNotBlank(field)) {
                    List<FieldPositionDTO> fieldPositionDTOList = tempFieldPositionMap.getOrDefault(field, new ArrayList<>());
                    fieldPositionDTOList.add(new FieldPositionDTO(newCellValue, Pair.of(rowNum, cellNum)));
                    tempFieldPositionMap.put(field, fieldPositionDTOList);
                }
            }
        }
        // 判断是不是份额基金净值文件格式(同时存在两个备案编码字段)
        return handlerFieldPosition(tempFieldPositionMap);
    }

    private Map<String, Pair<Integer, Integer>> handlerFieldPosition(Map<String, List<FieldPositionDTO>> tempFieldPositionMap) {
        Map<String, Pair<Integer, Integer>> fieldPositionMap = MapUtil.newHashMap();
        boolean hasParentField = tempFieldPositionMap.keySet().stream().anyMatch(e -> e.contains("parent"));
        for (Map.Entry<String, List<FieldPositionDTO>> entry : tempFieldPositionMap.entrySet()) {
            String field = entry.getKey();
            List<FieldPositionDTO> fieldPositionDTOList = entry.getValue();
            int size = fieldPositionDTOList.size();
            if (size == 1) {
                fieldPositionMap.put(field, fieldPositionDTOList.get(0).getPair());
                continue;
            }
            if ((!hasParentField && size > 1)) {
                if (EmailFieldConst.REGISTER_NUMBER.equals(field)) {
                    Pair<Integer, Integer> pair = fieldPositionDTOList.stream()
                            .filter(e -> !e.getFieldValue().contains("协会") && !e.getFieldValue().contains("备案")).map(FieldPositionDTO::getPair).findFirst().orElse(null);
                    fieldPositionMap.put(field, pair);
                } else {
                    fieldPositionMap.put(field, fieldPositionDTOList.get(size - 1).getPair());
                }
                continue;
            }
            if ((hasParentField && size > 1)) {
                fieldPositionMap.put(field, fieldPositionDTOList.get(0).getPair());
            }
        }

        // 母基金缺少代码的情况
        if (hasParentField && fieldPositionMap.get(EmailFieldConst.PARENT_REGISTER_NUMBER) == null) {
            List<FieldPositionDTO> fieldPositionDTOS = tempFieldPositionMap.get(EmailFieldConst.REGISTER_NUMBER);
            if (CollUtil.isNotEmpty(fieldPositionDTOS)) {
                Pair<Integer, Integer> parentRegisterNumberPair = fieldPositionDTOS.stream()
                        .filter(e -> e.getFieldValue().contains("协会") || e.getFieldValue().contains("备案")).map(FieldPositionDTO::getPair).findFirst().orElse(null);
                fieldPositionMap.put(EmailFieldConst.PARENT_REGISTER_NUMBER, parentRegisterNumberPair);
            }
        }
        return fieldPositionMap;
    }

    /**
     * 判断单元格值是否为表头字段
     *
     * @param cellValue     单元格值
     * @param emailFieldMap 邮件字段识别规则映射表
     * @return 表头对应的标识
     */
    public String fieldMatch(String cellValue, Map<String, List<String>> emailFieldMap) {
        if (StrUtil.isBlank(cellValue)) {
            return null;
        }
        for (Map.Entry<String, List<String>> fieldEntry : emailFieldMap.entrySet()) {
            List<String> fieldList = fieldEntry.getValue();
            for (String field : fieldList) {
                if (cellValue.equals(field)) {
                    return fieldEntry.getKey();
                }
            }
        }
        return null;
    }
}