package com.simuwang.base.common.util;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.exceptions.ExceptionUtil;
import com.simuwang.base.ano.Excel;
import com.simuwang.base.pojo.valuation.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author Rain
 * @date 2023/6/5 10:02
 * @description
 */
@Slf4j
public class ValuationBusinessUtils {

    private static final String ERROR_MSG_NOT_EXCEL_FILE = "文件格式错误";

    private static final String ERROR_MSG_NOT_MATCH_TEMPLATE = "估值表模板暂不支持，请联系管理员";

    private static final String ERROR_MSG_NO_DATA = "无数据";

    private static final String ERROR_MSG_NOT_NUMBER = "非数值数据，请检查模板是否匹配";

    private static final String ERROR_MSG_NOT_MARKET_VALUE_OR_AMOUNT = "无市值列或者无数量列，无法导入";

    public static PreAssetsValuationInfo<PreAssetsValuationBase> importFile(ValuationNeedParseParam valuationNeedParseParam) {
        /*
         * 异常： 1.扩展名非Excel 2.非Excel，恶意篡改扩展名 3.没有找到匹配的模板 4.空数据
         */
        PreAssetsValuationInfo<PreAssetsValuationBase> preInfo = new PreAssetsValuationInfo<>();
        PreAssetsValuationInfo<PreAssetsValuationBase>.Error error = null;

        File file = valuationNeedParseParam.getFile();
        assert file != null;
        InputStream inputStream = file2InStream(file);
        Workbook workbook = null;
        assert inputStream != null;
        ZipSecureFile.setMinInflateRatio(0.001);
        boolean is2007ExcelFile = is2007ExcelFile(file);
        try {
            try {
                workbook = is2007ExcelFile ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
            } catch (Exception e) {
                log.error("excel first parse error: {}", ExceptionUtil.stacktraceToString(e));
            }

            // 非Excel，恶意篡改扩展名
            if (null == workbook) {
                error = preInfo.new Error().setCellNum(0).setRowNum(0).setMsg(ERROR_MSG_NOT_EXCEL_FILE);
                error.setExcelName(valuationNeedParseParam.getOriginFileName());
                preInfo.setError(error);
                return preInfo;
            }

            Sheet sheet = workbook.getSheetAt(0);

            // 主动探测
            ExcelInfo excelInfo = getExcelInfo(sheet);

            // 没有找到匹配的模板
            if (excelInfo.getType() == null) {
                error = preInfo.new Error().setCellNum(0).setRowNum(0).setMsg(ERROR_MSG_NOT_MATCH_TEMPLATE);
                error.setExcelName(valuationNeedParseParam.getOriginFileName());
                preInfo.setError(error);
                return preInfo;
            }

            preInfo.setType(excelInfo.getType());

            List<String> headerInfo = excelInfo.getHeaderInfo();
            // 没有找到匹配的模板
            if (CollectionUtil.isEmpty(headerInfo)) {
                error = preInfo.new Error().setCellNum(0).setRowNum(0).setMsg(ERROR_MSG_NOT_MATCH_TEMPLATE);
                error.setExcelName(valuationNeedParseParam.getOriginFileName());
                preInfo.setError(error);
                return preInfo;
            }

            PreAssetsValuationInfo<PreAssetsValuationBase>.Header header = preInfo.new Header();
            getHeader(excelInfo, header);

            // 未找到估值表标题
            if (StringUtils.isEmpty(header.getTitle())) {
                error = preInfo.new Error().setCellNum(0).setRowNum(0).setMsg(ERROR_MSG_NOT_MATCH_TEMPLATE + "：未找到估值表标题");
                error.setExcelName(valuationNeedParseParam.getOriginFileName());
                preInfo.setError(error);
                return preInfo;
            }
            // 未找到估值日期
            if (header.getValuationDate() == null) {
                error = preInfo.new Error().setCellNum(0).setRowNum(0).setMsg(ERROR_MSG_NOT_MATCH_TEMPLATE + "：未找到估值日期");
                error.setExcelName(valuationNeedParseParam.getOriginFileName());
                preInfo.setError(error);
                return preInfo;
            }
            // 未找到有效净值
            if (header.getUnitNetValue() <= 0) {
                error = preInfo.new Error().setCellNum(0).setRowNum(0).setMsg(ERROR_MSG_NOT_MATCH_TEMPLATE + "：未找到有效净值");
                error.setExcelName(valuationNeedParseParam.getOriginFileName());
                preInfo.setError(error);
                return preInfo;
            }

            if (excelInfo.getHeaderRows() == 0 || excelInfo.getTitleRows() == 0) {
                error = preInfo.new Error().setCellNum(0).setRowNum(0).setMsg(ERROR_MSG_NOT_MATCH_TEMPLATE + "：未找到表头信息");
                error.setExcelName(valuationNeedParseParam.getOriginFileName());
                preInfo.setError(error);
                return preInfo;
            }

            preInfo.setHeader(header);
            preInfo.setFooter(excelInfo.getFooterInfo());
            preInfo.setUserId("0");

            preInfo.setExcelOriginName(valuationNeedParseParam.getOriginFileName());

            extractDataV2(sheet, excelInfo, preInfo);

            // 校验成功，保存Excel

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return preInfo;
    }

    private static boolean is2007ExcelFile(File file) {
        try (InputStream inputStream = new FileInputStream(file)) {
            byte[] headerBytes = new byte[8];
            inputStream.read(headerBytes, 0, 8);
            return headerBytes[0] == 0x50 && headerBytes[1] == 0x4B && headerBytes[2] == 0x03 && headerBytes[3] == 0x04;
        } catch (Exception e) {
            log.error("异常={}", e);
        }
        return false;
    }

    @SuppressWarnings("unchecked")
    private static void extractDataV2(Sheet sheet, ExcelInfo excelInfo, PreAssetsValuationInfo<PreAssetsValuationBase> preInfo) {

        //要使用修正后的ExcelEntity
        Map<String, ExcelEntity> fieldMap = excelInfo.getFieldMap();
        // 列名所在列号
        Map<String, Integer> cellIndexMap = excelInfo.getCellIndexMap();

        List<PreAssetsValuationBase> dataList = new ArrayList<>();
        @SuppressWarnings("rawtypes")
        PreAssetsValuationInfo.Error error = null;

        int startRowNum = 0, endRowNum = 0, lastRowNum = sheet.getLastRowNum();
        startRowNum = excelInfo.getHeaderRows() + excelInfo.getTitleRows();
        endRowNum = excelInfo.getFooterRows();

        if (endRowNum > 0) {
            lastRowNum = (lastRowNum - endRowNum - 1);
        }

        for (int rowNum = startRowNum; rowNum <= lastRowNum; rowNum++) {
            Row row = sheet.getRow(rowNum);

            if (null == row) {
                continue;
            }

            // 校验空白行
            boolean isRowBlank = true;
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum);
                if (null == cell) {
                    continue;
                }

                String value;
                switch (cell.getCellTypeEnum()) {
                    case STRING:
                        value = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        value = cell.getNumericCellValue() + "";
                        break;
                    default:
                        value = "";
                }
                if (StringUtils.isNotEmpty(value)) {
                    isRowBlank = false;
                    break;
                }
            }

            if (isRowBlank) {
                continue;
            }

            // 提取数据
            try {
                PreAssetsValuationGeneralTemplate instance = new PreAssetsValuationGeneralTemplate();

                boolean isInstanceValid = false;
                for (String field : cellIndexMap.keySet()) {
                    int cellIndex = cellIndexMap.get(field);
                    Cell cell = row.getCell(cellIndex);
                    if (null == cell) {
                        continue;
                    }

                    //当表头带%，但是表格是真实小数，却被格式化了带%的百分数，代码获取的依旧是真实小数
                    //百分数4位小数： 0.0000%
                    boolean isPercentFormat = false;
                    String dataFormatString = cell.getCellStyle().getDataFormatString();
                    if (null != dataFormatString && dataFormatString.contains("%")) {
                        isPercentFormat = true;
                    }

                    String value;
                    switch (cell.getCellTypeEnum()) {
                        case STRING:
                            value = cell.getStringCellValue();
                            // 特殊处理原因:excel常规类型 1105 会被识别为数字类型,值变为"1105.0" -> 样例估值表:492194交银优享平衡202101号专户资产估值表2021102120211201_103054_488.xlsx
                            if (value.endsWith(".0")) {
                                value = value.replace(".0", "");
                            }
                            break;
                        case NUMERIC:
                            value = cell.getNumericCellValue() + "";
                            break;
                        case BLANK:
                            value = "";
                            break;
                        case _NONE:
                            value = "";
                            break;
                        default:
                            value = "";
                    }
                    String cellValue = value;

                    if (StringUtils.isBlank(cellValue)) {
                        continue;
                    }

                    ExcelEntity excelEntity = fieldMap.get(field);

                    if (excelEntity.isBlankAllowable()) {
                        cellValue = removeBlank(cellValue);
                    }

                    // 校验数值
                    if (excelEntity.isNumerical() && isNotNumber(cellValue)) {
                        error = preInfo.new Error();
                        // 注意，row,cell均从0开始
                        error.setRowNum(rowNum + 1).setCellNum(cellIndex + 1).setMsg(ERROR_MSG_NOT_NUMBER + "：" + cellValue);
                        error.setExcelName(preInfo.getExcelOriginName());
                        preInfo.setError(error);
                        return;
                    }

                    List<Method> methodList = getMethods(instance.getClass());
                    for (Method method : methodList) {
                        if (method.getName().equals("set" + firstUpperCase(field))) {
                            method.setAccessible(true);

                            if (excelEntity.isNumerical()) {
                                if (isPercentFormat) {
                                    method.invoke(instance, toPlainNumber(cellValue));
                                } else if ("ratioOfCostToNetWorth".equals(field)) {
                                    //特殊处理 ratioOfCostToNetWorth
                                    method.invoke(instance, toPlainNumberSpecial(excelEntity, cellValue));
                                } else if ("ratioOfMarketValueToNetWorth".equals(field)) {
                                    //特殊处理 ratioOfMarketValueToNetWorth
                                    method.invoke(instance, toPlainNumberSpecial(excelEntity, cellValue));
                                } else {
                                    method.invoke(instance, toPlainNumber(cellValue));
                                }
                            } else {
                                method.invoke(instance, cellValue);
                            }

                            isInstanceValid = true;
                            break;
                        }
                    }
                }

                if (isInstanceValid) {
                    dataList.add(instance);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        preInfo.setData(dataList);
        if (dataList.size() == 0) {
            error = preInfo.new Error().setCellNum(0).setRowNum(0).setMsg(ERROR_MSG_NO_DATA);
            error.setExcelName(preInfo.getExcelOriginName());
            preInfo.setError(error);
        } else {
            long marketValueCount = dataList.stream().filter(p -> StringUtils.isEmpty(p.getMarketValue())).count();
            long amountCount = dataList.stream().filter(p -> StringUtils.isEmpty(p.getAmount())).count();
            if (marketValueCount == dataList.size() || amountCount == dataList.size()) {
                error = preInfo.new Error().setMsg(ERROR_MSG_NOT_MARKET_VALUE_OR_AMOUNT);
                error.setExcelName(preInfo.getExcelOriginName());
                preInfo.setError(error);
            }
        }
    }

    /**
     * 特殊处理带%的列
     **/
    private static String toPlainNumberSpecial(ExcelEntity excelEntity, String value) {
        if (StringUtils.isEmpty(value) || "0".equals(value)) {
            return value;
        }
        if ("%".equals(value)) {
            return "0";
        }
        String num = value.replace(",", "").replace("，", "");
        if (value.endsWith("%")) {
            num = value.replace("%", "") + "e-2";
        }
        //如果本身是科学计数法
        else if (value.toLowerCase().contains("e")) {
            try {
                num = new BigDecimal(value).toPlainString();
                num += "e-2";
            } catch (Exception e) {
                log.error("转换错误:" + num);
            }
        } else {
            if (excelEntity.isPercent()) {
                num += "e-2";
            }
        }
        try {
            return new BigDecimal(num).toPlainString();
        } catch (Exception e) {
            log.error("转换错误:" + num);
            e.printStackTrace();
        }
        return value;
    }

    /**
     * 主要用于将科学计数法转换为普通数值
     */
    public static String toPlainNumber(String value) {
        if (StringUtils.isEmpty(value) || "0".equals(value)) {
            return value;
        }
        if ("%".equals(value)) {
            return "0";
        }
        String num = value.replace(",", "").replace("，", "");
        if (value.endsWith("%")) {
            num = value.replace("%", "") + "e-2";
        }

        try {
            return new BigDecimal(num).toPlainString();
        } catch (Exception e) {
            log.error("转换错误:" + num);
            e.printStackTrace();
        }
        return value;
    }

    private static String firstUpperCase(String str) {
        return (str.charAt(0) + "").toUpperCase() + str.substring(1);
    }

    private static List<Method> getMethods(Class<? extends PreAssetsValuationBase> clazz) {
        Method[] subMethods = clazz.getDeclaredMethods();
        Method[] superMethods = clazz.getSuperclass().getDeclaredMethods();

        final List<Method> subList = Arrays.asList(subMethods);
        // 过滤父类中被覆盖的方法
        List<Method> superList = Arrays.asList(superMethods).stream().filter(superM -> {
            boolean anyMatch = subList.stream().anyMatch(subM -> subM.getName().equals(superM.getName()));
            return !anyMatch;
        }).collect(Collectors.toList());
        superList.addAll(subList);
        return superList;
    }

    /**
     * 支持正负：int 或 double 或带%数值
     */
    public static boolean isNotNumber(String str) {
        return !isNumber(str);
    }

    /**
     * 支持正负：int 或 double 或带%数值， 科学计数法
     */
    public static boolean isNumber(String str) {
        if (StringUtils.isEmpty(str)) {
            return false;
        }
        if ("%".equals(str)) {
            return true;
        }
        str = str.replace(",", "").replace("，", "");
        //容许没有整数部分 eg:.2
        String regex = "^-?(\\d+)?(\\.)?\\d+%?$";

        boolean judge = str.matches(regex);
        // 非常规数值，可能为科学计数法
        if (!judge) {
            try {
                String plainString = new BigDecimal(str).toPlainString();
                log.info("number is BigDecimal...,number -> {}", plainString);
            } catch (Exception e) {
                return false;
            }
        }
        return true;
    }

    private static void getHeader(ExcelInfo excelInfo, PreAssetsValuationInfo<PreAssetsValuationBase>.Header header) {
        List<String> headerInfo = excelInfo.getHeaderInfo();

        header.setHeaderList(headerInfo);
        for (int i = 0; i < headerInfo.size(); i++) {
            String str = removeBlank(headerInfo.get(i));
            if (str.contains("估值表")) {
                if (null == header.getTitle()) {
                    header.setTitle(str);
                }
            } else if (str.contains("专用表")) {
                if (null == header.getSubTitle()) {
                    header.setSubTitle(str);
                }
            } else if (str.contains("估值日期") || str.contains("日期")) {
                if (null == header.getValuationDate()) {
                    Date valuationDate = getDateFromString(str);
                    //估值日期字段名 和 估值日期值 分为两个单元格的情况,此时取下一个单元格的值作为估值日期值
                    if (null == valuationDate && (i + 1) < headerInfo.size()) {
                        valuationDate = getDateFromString(removeBlank(headerInfo.get(i + 1)));
                    }
                    header.setValuationDate(valuationDate);
                }
            } else if (str.contains("今日单位净值") || str.contains("单位净值") || str.contains("净值")) {
                //header.getUnitNetValue() <= 0 代表还未识别
                if (header.getUnitNetValue() <= 0) {
                    double nav = extraNumberFromString(str);
                    //单位净值字段名 和 单位净值值 分为两个单元格的情况,此时取下一个单元格的值作为单位净值
                    if (nav == -1D && (i + 1) < headerInfo.size()) {
                        nav = extraNumberFromString(removeBlank(headerInfo.get(i + 1)));
                    }
                    header.setUnitNetValue(nav);
                }
            }
        }

        if (null == header.getTitle()) {
            header.setTitle(headerInfo.get(0));
        }

        //从表头中提取日期
        if (null == header.getValuationDate()) {
            for (String str : headerInfo) {
                Date date = getDateFromString(str);
                if (null != date) {
                    header.setValuationDate(date);
                    break;
                }
            }
        }
    }

    /**
     * 从字符串中提取日期，返回该日期的中午12:00 <br/>
     * 支持日期格式: <br/>
     * 2017-10-17 <br/>
     * 2017/10/17 <br/>
     * 2017年10月17日 <br/>
     * 20170703
     */
    public static Date getDateFromString(String str) {
        String regex = "(19\\d{2}|20\\d{2})[-/年]?(\\d{1,2})[-/月]?(\\d{1,2})";
        Matcher matcher = Pattern.compile(regex).matcher(str);
        if (matcher.find()) {
            int year = Integer.parseInt(matcher.group(1));
            // 注意，月份从0开始
            int month = Integer.parseInt(matcher.group(2)) - 1;
            int day = Integer.parseInt(matcher.group(3));

            Calendar calendar = Calendar.getInstance();
            calendar.set(year, month, day, 12, 00, 00);
            return calendar.getTime();
        }
        return null;
    }

    /**
     * 主动探测 Excel非数据部分info
     */
    private static ExcelInfo getExcelInfo(Sheet sheet) {
        ExcelInfo info = new ExcelInfo();

        List<String> headerInfo = new ArrayList<>();
        List<String> footerInfo = new ArrayList<>();
        List<String> titleList = new ArrayList<>();

        Map<String, ExcelEntity> fieldsMap = getFieldsAndExcelAnnotationMap(PreAssetsValuationGeneralTemplate.class);
        info.setFieldMap(fieldsMap);

        // 列名所在列号
        Map<String, Integer> cellIndexMap = new LinkedHashMap<>();

        String netValue = null;
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (null == row) {
                continue;
            }
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum);
                if (null == cell) {
                    continue;
                }

                String value;
                switch (cell.getCellTypeEnum()) {
                    case STRING:
                        value = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        cell.setCellType(CellType.STRING);
                        value = cell.getStringCellValue();
                        break;
                    default:
                        value = "";
                }
                String cellValue = removeBlank(value);

                if (StringUtils.isEmpty(cellValue)) {
                    continue;
                }

                //合并的单元格这里有个坑,表面上合并了，实际每个单元格里合并前是什么值，合并后还是什么值
                if (ImportParams.isEqualHeaderIdentify(cellValue)) {
                    MergedRegion mergedRegion = isMergedRegion(sheet, rowNum, cellNum);
                    if (mergedRegion.isMerged()) {
                        info.setTitleRows(mergedRegion.getLastRowNum() - mergedRegion.getFirstRowNum() + 1);

                        info.setHeaderRows(mergedRegion.getFirstRowNum());
                    } else {
                        info.setTitleRows(1);
                        info.setHeaderRows(rowNum);
                    }

                } else if (ImportParams.isContainFooterIdentify(cellValue)) {
                    info.setFooterRows(sheet.getLastRowNum() - rowNum);
                }

                if (info.getHeaderRows() == 0) {
                    if (null == netValue) {
                        if (cellValue.contains("今日单位净值") || cellValue.contains("单位净值") || cellValue.contains("今日净值")) {
                            double netValueTemp = extraNumberFromString(cellValue);
                            if (netValueTemp > 0) {
                                netValue = cellValue;
                            }
                        }
                    }

                    headerInfo.add(cellValue);
                }
                if (info.getFooterRows() > 0) {
                    footerInfo.add(cellValue);
                }

                if (rowNum >= info.getHeaderRows() && rowNum < info.getHeaderRows() + info.getTitleRows()) {

                    for (Map.Entry<String, ExcelEntity> fm : fieldsMap.entrySet()) {
                        String field = fm.getKey();
                        ExcelEntity excelEntity = fm.getValue();
                        String[] values = excelEntity.getValue();

                        boolean shouldBreak = false;

                        for (String v : values) {
                            if (v.contains(":")) {
                                String[] sCell = v.split(":");
                                if (!sCell[1].equals(cellValue)) {
                                    continue;
                                }

                                //find the previous row and the same cell index
                                Row rowBefore = sheet.getRow(rowNum - 1);
                                if (null == rowBefore) {
                                    break;
                                }
                                Cell cellBefore = rowBefore.getCell(cellNum);
                                if (null == cellBefore) {
                                    break;
                                }

                                String cellBeforeValue = "";

                                MergedRegion mergedRegion = isMergedRegion(sheet, rowNum - 1, cellNum);
                                if (mergedRegion.isMerged()) {
                                    boolean isOver = false;
                                    for (int i = mergedRegion.getFirstRowNum(); i <= mergedRegion.getLastRowNum(); i++) {
                                        Row tRow = sheet.getRow(i);
                                        if (tRow == null) {
                                            continue;
                                        }
                                        for (int j = mergedRegion.getFirstCellNum(); j <= mergedRegion.getLastCellNum(); j++) {
                                            Cell tCell = tRow.getCell(j);
                                            if (tCell == null) {
                                                continue;
                                            }

                                            tCell.setCellType(CellType.STRING);
                                            String tCellValue = removeBlank(tCell.getStringCellValue());
                                            if (StringUtils.isNotEmpty(tCellValue)) {
                                                cellBeforeValue = tCellValue;
                                                isOver = true;
                                                break;
                                            }
                                        }
                                        if (isOver) {
                                            break;
                                        }
                                    }
                                } else {
                                    cellBefore.setCellType(CellType.STRING);
                                    cellBeforeValue = removeBlank(cellBefore.getStringCellValue());
                                }

                                if (sCell[0].equals(cellBeforeValue)) {
                                    cellValue = v;

                                    if (cellValue.contains("%")) {
                                        excelEntity.setPercent(true);
                                    }

                                    cellIndexMap.put(field, cellNum);

                                    shouldBreak = true;

                                    break;
                                }
                            } else if (v.equals(cellValue)) {
                                if (cellValue.contains("%")) {
                                    excelEntity.setPercent(true);
                                }
                                cellIndexMap.put(field, cellNum);
                                shouldBreak = true;
                                break;
                            }
                        }
                        if (shouldBreak) {
                            break;
                        }
                    }
                    titleList.add(cellValue);
                } else {
                    if (null == netValue) {
                        if (cellValue.contains("单位") && cellValue.contains("净值")) {
                            if (cellValue.equals("单位净值") || cellValue.contains("今日") || cellValue.contains("基金")) {
                                //把它跟右边的cell一起拿过来
                                double netValueTemp = extraNumberFromString(cellValue);
                                if (netValueTemp > 0) {
                                    netValue = cellValue;
                                } else {
                                    Cell cellRight = row.getCell(cellNum + 1);

                                    if (null != cellRight) {
                                        cellRight.setCellType(CellType.STRING);
                                        String vDate = cellRight.getStringCellValue();
                                        if (null != vDate && !"".equals(vDate.trim())) {
                                            netValue = cellValue + vDate;
                                            headerInfo.add(netValue);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        if (CollectionUtil.isEmpty(titleList)) {
            info.setType(null);
            return info;
        }

        info.setType(AssetsValuationType.GeneralTemplate);
        info.setCellIndexMap(cellIndexMap);

        if (info.getHeaderRows() > 0) {
            info.setHeaderInfo(headerInfo);
        }
        if (info.getFooterRows() > 0) {
            info.setFooterInfo(footerInfo);
        }

        return info;
    }

    /**
     * 从字符串中提取数值：正负 int 或 double 或带%数值
     */
    public static double extraNumberFromString(String str) {
        if (null == str) {
            return -1D;
        }
        str = str.replace(",", "").replace("，", "");

        String regex = "-?(\\d+)?(\\.)?\\d+";

        Matcher matcher = Pattern.compile(regex).matcher(str);
        if (matcher.find()) {
            String value = matcher.group();

            if (value.endsWith("%")) {
                return Double.parseDouble(value.substring(0, value.length() - 1)) / 100D;
            }

            return Double.parseDouble(value);
        }
        return -1D;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     */
    private static MergedRegion isMergedRegion(Sheet sheet, final int rowNum, final int cellNum) {
        final MergedRegion mergedRegion = new MergedRegion().setMerge(false);

        int numMergedRegions = sheet.getNumMergedRegions();
        if (numMergedRegions <= 0) {
            return mergedRegion;
        }

        for (int i = 0; i < numMergedRegions; i++) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            boolean rowMerge = region.getFirstRow() <= rowNum && rowNum <= region.getLastRow();
            boolean cellMerge = region.getFirstColumn() <= cellNum && cellNum <= region.getLastColumn();
            if (rowMerge && cellMerge) {
                mergedRegion.setMerge(true).setFirstRowNum(region.getFirstRow()).setLastRowNum(region.getLastRow());
                mergedRegion.setFirstCellNum(region.getFirstColumn()).setLastCellNum(region.getLastColumn());
                break;
            }
        }
        return mergedRegion;
    }

    private static Map<String, ExcelEntity> getFieldsAndExcelAnnotationMap(Class<? extends PreAssetsValuationBase> clazz) {
        // 处理父类
        Map<String, ExcelEntity> superMap = getExcelFieldsMap(PreAssetsValuationBase.class);
        // 处理子类
        Map<String, ExcelEntity> clazzMap = getExcelFieldsMap(clazz);

        // 子类覆盖父类相同属性
        for (String key : superMap.keySet()) {
            if (clazzMap.containsKey(key)) {
                continue;
            }
            clazzMap.put(key, superMap.get(key));
        }
        return clazzMap;
    }

    private static <T> Map<String, ExcelEntity> getExcelFieldsMap(Class<T> clazz) {
        Map<String, ExcelEntity> map = new HashMap<>();

        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            Excel excel = field.getDeclaredAnnotation(Excel.class);
            if (excel == null) {
                continue;
            }

            ExcelEntity excelEntity = new ExcelEntity();
            excelEntity.setNumerical(excel.numerical());
            excelEntity.setNullable(excel.nullable());
            excelEntity.setBlankAllowable(excel.blankAllowable());
            excelEntity.setPercent(excel.percent());

            String values = excel.values();
            if (!"".equals(values.trim())) {

                String[] valuesArr = values.split(",");
                if (excel.blankAllowable()) {
                    excelEntity.setValue(removeBlank(valuesArr));
                } else {
                    excelEntity.setValue(valuesArr);
                }

            } else {

                if (excel.blankAllowable()) {
                    excelEntity.setValue(removeBlank(excel.value()));
                } else {
                    excelEntity.setValue(excel.value());
                }
            }

            map.put(field.getName(), excelEntity);
        }
        return map;
    }

    public static String[] removeBlank(String[] array) {
        for (int i = 0; i < array.length; i++) {
            array[i] = array[i].replaceAll("\\s+", "");
        }
        return array;
    }

    public static String removeBlank(String str) {
        return str.replaceAll("\\s+", "");
    }

    public static InputStream file2InStream(File file) {
        try {
            return new FileInputStream(file);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static class MergedRegion {
        private boolean merge;
        private int firstRowNum;
        private int lastRowNum;
        private int firstCellNum;
        private int lastCellNum;

        public boolean isMerged() {
            return merge;
        }

        public MergedRegion setMerge(boolean merge) {
            this.merge = merge;
            return this;
        }

        public int getFirstRowNum() {
            return firstRowNum;
        }

        public MergedRegion setFirstRowNum(int firstRowNum) {
            this.firstRowNum = firstRowNum;
            return this;
        }

        public int getLastRowNum() {
            return lastRowNum;
        }

        public MergedRegion setLastRowNum(int lastRowNum) {
            this.lastRowNum = lastRowNum;
            return this;
        }

        public int getFirstCellNum() {
            return firstCellNum;
        }

        public MergedRegion setFirstCellNum(int firstCellNum) {
            this.firstCellNum = firstCellNum;
            return this;
        }

        public int getLastCellNum() {
            return lastCellNum;
        }

        public MergedRegion setLastCellNum(int lastCellNum) {
            this.lastCellNum = lastCellNum;
            return this;
        }
    }
}
