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.date.DateUtil;
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.DateConst;
import com.simuwang.base.common.conts.EmailFieldConst;
import com.simuwang.base.common.conts.EmailTypeConst;
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 org.apache.pdfbox.Loader;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import technology.tabula.*;
import technology.tabula.extractors.SpreadsheetExtractionAlgorithm;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
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;

    private static final Integer ROW_DIRECTION_TYPE = 1;
    private static final Integer COLUMN_DIRECTION_TYPE = 2;

    private static final int MAX_COLUMN = 20;

    @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(emailContent)) {
            emailFundNavDTOList = parseEmailContent(emailContentInfoDTO, emailContent, 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())) {
            List<EmailFundNavDTO> fundNavDTOList = parsePackageFile(emailContentInfoDTO, emailContentInfoDTO.getFileName(), emailContentInfoDTO.getFilePath(), emailFieldMap);
            Optional.ofNullable(fundNavDTOList).ifPresent(emailFundNavDTOList::addAll);
        }

        // 校验净值数据格式
        if (CollUtil.isNotEmpty(emailFundNavDTOList)) {
            emailFundNavDTOList = emailFundNavDTOList.stream().filter(super::dataFormat).collect(Collectors.toList());
        }
        return emailFundNavDTOList;
    }

    private List<EmailFundNavDTO> parsePackageFile(EmailContentInfoDTO emailContentInfoDTO, String fileName, String filePath, Map<String, List<String>> emailFieldMap) {
        String destPath = filePath.substring(0, filePath.indexOf(fileName)) + fileName.replaceAll(".zip","").replaceAll(".ZIP","") ;
        log.info("压缩包地址:{},解压后文件地址:{}", filePath, destPath);
        List<String> dir = ExcelUtil.extractCompressedFiles(filePath, destPath);
        List<EmailFundNavDTO> emailFundNavDTOList = CollUtil.newArrayList();
        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));
                }
            }
        }
        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;
    }

    /**
     * 解析邮件pdf附件
     *
     * @param filePath      邮件pdf附件路径
     * @param excelFilePath pdf转excel路径
     * @param emailFieldMap 邮件字段识别规则映射表
     * @return 解析到的净值数据
     */
    private List<EmailFundNavDTO> parsePdfFile(String filePath, String excelFilePath, Map<String, List<String>> emailFieldMap) {
        File savefile = new File(excelFilePath);
        if (!savefile.exists()) {
            if (!savefile.getParentFile().exists()) {
                savefile.getParentFile().mkdirs();
                savefile.getParentFile().setExecutable(true);
            }
        }
        try (OutputStream outputStream = Files.newOutputStream(Paths.get(excelFilePath))) {
            PDDocument document = Loader.loadPDF(new File(filePath));
            PageIterator extract = new ObjectExtractor(document).extract();
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");
            while (extract.hasNext()) {
                Page next = extract.next();
                List<Table> tableList = new SpreadsheetExtractionAlgorithm().extract(next);
                for (Table table : tableList) {
                    List<List<RectangularTextContainer>> rows = table.getRows();
                    for (int rowNum = 0; rowNum < rows.size(); rowNum++) {
                        Row sheetRow = sheet.createRow(rowNum);
                        List<RectangularTextContainer> textContainerList = rows.get(rowNum);
                        for (int cellNum = 0; cellNum < textContainerList.size(); cellNum++) {
                            Cell cell = sheetRow.createCell(cellNum);
                            RectangularTextContainer textContainer = textContainerList.get(cellNum);
                            if (textContainer != null) {
                                cell.setCellValue(textContainer.getText());
                            }
                        }
                    }
                }
            }
            // 将Excel工作簿写入输出流
            workbook.write(outputStream);
        } catch (Exception e) {
            log.error("解析邮件pdf附件报错 -> 堆栈信息:{}", ExceptionUtil.stacktraceToString(e));
        }
        return parseExcelFile(excelFilePath, emailFieldMap);
    }

    /**
     * 解析邮件excel附件
     *
     * @param filePath      邮件excel附件地址
     * @param emailFieldMap 邮件字段识别规则映射表
     * @return 解析到的净值数据
     */
    private List<EmailFundNavDTO> parseExcelFile(String filePath, Map<String, List<String>> emailFieldMap) {
        File file = new File(filePath);
        Sheet sheet = ExcelUtil.getSheet(file, 0);
        // 1.找到表头所在位置
        Map<String, Pair<Integer, Integer>> fieldPositionMap = getFieldPosition(sheet, emailFieldMap);
        if (MapUtil.isEmpty(fieldPositionMap)) {
            log.warn("找不到文件表头字段 -> 文件:{}", filePath);
            return CollUtil.newArrayList();
        }
        // 2.解析sheet中的净值数据
        return parseSheetData(sheet, fieldPositionMap);
    }

    /**
     * 解析邮件正文
     *
     * @param emailContentInfoDTO 邮件信息
     * @param emailContent        正文内容
     * @param emailFieldMap       邮件字段识别规则映射表
     * @return 解析到的净值数据
     */
    private List<EmailFundNavDTO> parseEmailContent(EmailContentInfoDTO emailContentInfoDTO, String emailContent, Map<String, List<String>> emailFieldMap) {
        Document doc = Jsoup.parse(emailContent);
        Element table = doc.select("table").first();
        Elements rows = table.select("tr");
        String excelFilePath = "/data/file/content/" + emailContentInfoDTO.getEmailAddress() + "/" + emailContentInfoDTO.getEmailDate().substring(0, 10).replaceAll("-", "") + "/"
                + emailContentInfoDTO.getFileName().replace(".html", ".xlsx");
        File savefile = new File(excelFilePath);
        if (!savefile.exists()) {
            if (!savefile.getParentFile().exists()) {
                savefile.getParentFile().mkdirs();
                savefile.getParentFile().setExecutable(true);
            }
        }
        try (OutputStream outputStream = new FileOutputStream(savefile)) {
            // 创建一个新的Excel工作簿
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");
            writeDataToSheet(sheet, rows);
            // 将Excel工作簿写入输出流
            workbook.write(outputStream);
        } catch (Exception e) {
            log.error("解析正文报错 -> 邮件主题:{},邮件日期:{},堆栈信息:{}", emailContentInfoDTO.getEmailTitle(), emailContentInfoDTO.getEmailDate(), ExceptionUtil.stacktraceToString(e));
        }
        return parseExcelFile(excelFilePath, emailFieldMap);
    }

    private List<EmailFundNavDTO> parseSheetData(Sheet sheet, Map<String, Pair<Integer, Integer>> fieldPositionMap) {
        List<EmailFundNavDTO> fundNavDTOList = CollUtil.newArrayList();
        // 通过表头所在位置判断是行数据还是列数据
        Integer dataDirectionType = detectDataDirection(fieldPositionMap);
        // 数据起始行,起始列
        int initRow = dataDirectionType.equals(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(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);
                Optional.ofNullable(readSheetRowData(sheetRow, fieldColumnMap)).ifPresent(fundNavDTOList::addAll);
            }
        }
        if (dataDirectionType.equals(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 < MAX_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), cell.getStringCellValue());
                }
                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 = cell.getStringCellValue();
                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;
                }
                boolean isValidDate = StringUtil.isValidDate(cellValue);
                if (isValidDate) {
                    String date = cellValue.replaceAll("年", "-").replaceAll("月", "-");
                    priceDateMap.put(2, date);
                }
            }
        }
        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));
        fundNavDTO.setPriceDate(fieldValueMap.get(EmailFieldConst.PRICE_DATE));
        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(numberDataHandler(assetNet));

        String assetShares = fieldValueMap.get(EmailFieldConst.ASSET_NET);
        fundNavDTO.setAssetNet(numberDataHandler(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;
        if (StrUtil.isBlank(nav) && StrUtil.isBlank(cumulativeNavWithdrawal)) {
            return 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;

        // 份额基金净值文件格式
        long parentFiledCount = columnFieldMap.keySet().stream().filter(e -> e.contains("parent")).count();
        if (parentFiledCount >= 1) {
            Optional.ofNullable(buildParentNav(sheetRow, columnFieldMap, priceDate)).ifPresent(fundNavDTOList::add);
        }
        // 正常净值文件格式
        if (StrUtil.isNotBlank(priceDate) && !priceDate.contains("-")) {
            // 处理日期yyyyMMdd格式 -> 转成yyyy-MM-dd
            priceDate = DateUtil.format(DateUtil.parse(priceDate, DateConst.YYYYMMDD), DateConst.YYYY_MM_DD);
        }
        emailFundNavDTO.setPriceDate(priceDate);
        String fundName = columnFieldMap.get(EmailFieldConst.FUND_NAME) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.FUND_NAME)).getStringCellValue() != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.FUND_NAME))) : null;
        emailFundNavDTO.setFundName(fundName);

        String registerNumber = columnFieldMap.get(EmailFieldConst.REGISTER_NUMBER) != null && sheetRow.getCell(columnFieldMap.get(EmailFieldConst.REGISTER_NUMBER)) != null ?
                ExcelUtil.getCellValue(sheetRow.getCell(columnFieldMap.get(EmailFieldConst.REGISTER_NUMBER))) : null;
        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);

        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;
        emailFundNavDTO.setAssetNet(numberDataHandler(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(numberDataHandler(assetShares));

        fundNavDTOList.add(emailFundNavDTO);
        return fundNavDTOList;
    }

    private String numberDataHandler(String numberData) {
        if (StrUtil.isBlank(numberData)) {
            return null;
        }
        // pdf解析到的值带有",",比如:"10,656,097.37"
        String data = numberData.replaceAll(",", "");
        if (!StringUtil.isNumeric(data)) {
            return null;
        }
        return data;
    }

    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;
        }
        if (StrUtil.isNotBlank(priceDate) && !priceDate.contains("-")) {
            // 处理日期yyyyMMdd格式 -> 转成yyyy-MM-dd
            priceDate = DateUtil.format(DateUtil.parse(priceDate, DateConst.YYYYMMDD), DateConst.YYYY_MM_DD);
        }
        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(numberDataHandler(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.setAssetNet(numberDataHandler(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) {
            // 存在合并单元格的方式 -> 日期字段所在位置可能会存在错误
            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;
    }

    /**
     * 通过表头所在位置判断是行数据还是列数据
     *
     * @param fieldPositionMap excel中表头所在的位置
     * @return 行方向-1,,列方向-2
     */
    private Integer detectDataDirection(Map<String, Pair<Integer, Integer>> fieldPositionMap) {
        long count = fieldPositionMap.values().stream().map(Pair::getValue).distinct().count();
        return count == 1 ? COLUMN_DIRECTION_TYPE : ROW_DIRECTION_TYPE;
    }

    /**
     * 找出excel中表头所在的位置
     *
     * @param sheet         表格工作簿
     * @param emailFieldMap 邮件字段识别规则映射表
     * @return excel中表头所在的位置(行, 列)
     */
    private Map<String, Pair<Integer, Integer>> getFieldPosition(Sheet sheet, Map<String, List<String>> emailFieldMap) {
        Map<String, List<Pair<Integer, Integer>>> 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 field = fieldMatch(cellValue, emailFieldMap);
                if (StrUtil.isNotBlank(field)) {
                    List<Pair<Integer, Integer>> pairList = tempFieldPositionMap.getOrDefault(field, new ArrayList<>());
                    pairList.add(Pair.of(rowNum, cellNum));
                    tempFieldPositionMap.put(field, pairList);
                }
            }
        }
        // 判断是不是份额基金净值文件格式(同时存在两个备案编码字段)
        return handlerFieldPosition(tempFieldPositionMap);
    }

    private Map<String, Pair<Integer, Integer>> handlerFieldPosition(Map<String, List<Pair<Integer, Integer>>> tempFieldPositionMap) {
        Map<String, Pair<Integer, Integer>> fieldPositionMap = MapUtil.newHashMap();
        boolean hasParentField = tempFieldPositionMap.keySet().stream().anyMatch(e -> e.contains("parent"));
        for (Map.Entry<String, List<Pair<Integer, Integer>>> entry : tempFieldPositionMap.entrySet()) {
            List<Pair<Integer, Integer>> pairList = entry.getValue();
            if (pairList.size() == 1) {
                fieldPositionMap.put(entry.getKey(), pairList.get(0));
                continue;
            }
            if ((!hasParentField && pairList.size() > 1)) {
                fieldPositionMap.put(entry.getKey(), pairList.get(pairList.size() - 1));
                continue;
            }
            if ((hasParentField && pairList.size() > 1)) {
                fieldPositionMap.put(entry.getKey(), pairList.get(0));
            }
        }
        return fieldPositionMap;
    }

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

    private void writeDataToSheet(Sheet sheet, Elements rows) {
        int rowSize = rows.size();
        for (int rowNum = 0; rowNum < rowSize; rowNum++) {
            Row sheetRow = sheet.createRow(rowNum);

            Element elementRow = rows.get(rowNum);
            Elements cells = elementRow.select("td");
            int cellSize = cells.size();
            for (int cellNum = 0; cellNum < cellSize; cellNum++) {
                Cell sheetRowCell = sheetRow.createCell(cellNum);
                sheetRowCell.setCellValue(cells.get(cellNum).text());
            }
        }
    }
}