package com.hdkj.lt.utils.poi;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.util.*;

/**
 * @Description
 * @Author Admin
 * @Date 2022/8/15 10:37
 * @Version 1.0
 */
public class ExcelReadUtils {

    private ExcelReadUtils() {
    }

    public static final String ROW_NUM = "rowNum";
    private static final String ROW_DATA = "rowData";
    private static final String ROW_TIPS = "rowTips";
    private static final int CELL_OTHER = 0;
    private static final int BYTES_DEFAULT_LENGTH = 10240;
    private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();

    /**
     * 读取Excel内容
     */
    public static <T> JSONObject readExcel(InputStream inputStream, String fileName, Class<T> clazz) throws IOException {
        Workbook book;
        JSONObject jsonObject = new JSONObject();
        if (StringUtils.endsWithIgnoreCase(fileName, ".xls")) {
            book = new HSSFWorkbook(inputStream);
        } else if (StringUtils.endsWithIgnoreCase(fileName, ".xlsx")) {
            book = new XSSFWorkbook(inputStream);
        } else {
            return jsonObject;
        }
        // 获取 Excel 文件 Sheet 页面
        int sheetNums = book.getNumberOfSheets();
        for (int i = 0; i < sheetNums; i++) {
            Sheet sheet = book.getSheetAt(i);
            String sheetName = sheet.getSheetName();
            if (StringUtils.isBlank(sheetName)) {
                throw new RuntimeException("文档内容出错，请检查文档内容");
            }
            try {
                Method valuesMethod = clazz.getMethod("values");
                if (Objects.isNull(valuesMethod)) {
                    return null;
                }
                BaseReadExcelConfig[] baseEnums = (BaseReadExcelConfig[]) valuesMethod.invoke(null);
                for (BaseReadExcelConfig baseEnum : baseEnums) {
                    if (Objects.equals(baseEnum.getSheetName(), sheetName)) {
                        JSONArray array = readSheet(sheet, baseEnum.getStartRead(), baseEnum.getColumns());
                        jsonObject.put(sheetName, array);
                        break;
                    }
                }
            } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
                throw new RuntimeException(e.getMessage());
            }
        }
        book.close();
        inputStream.close();
        return jsonObject;
    }

    /**
     * 读取sheet内容
     */
    private static JSONArray readSheet(Sheet sheet, int startRead, List<String> columns) {
        // 首行下标
        int rowStart = sheet.getFirstRowNum() + startRead;
        // 尾行下标
        int rowEnd = sheet.getLastRowNum();
        // 获取表格头行数据
        Row headRow = sheet.getRow(rowStart);
        if (headRow == null) {
            return new JSONArray();
        }
        int cellStart = headRow.getFirstCellNum();
        int cellEnd = headRow.getLastCellNum();
        Map<Integer, String> keyMap = new HashMap<>();
        for (int j = cellStart; j < cellEnd; j++) {
            // 获取表头数据
            Object colVal = getCellValue(headRow.getCell(j), false);
            boolean isMerged = isMergedRegion(sheet, rowStart, j);
            if ((Objects.nonNull(colVal) || isMerged) && null != columns && columns.size() > j) {
                // 是合并单元格或者值不为空时，并且字段列大于单元格列下标
                String val = columns.get(j);
                keyMap.put(j, val);
            }
        }
        // 如果表头没有数据则不进行解析
        if (keyMap.isEmpty()) {
            return new JSONArray();
        }
        // 获取每行JSON对象的值
        JSONArray array = new JSONArray();
        // 如果首行与尾行相同，表明只有一行，返回表头数据
        if (rowStart == rowEnd) {
            JSONObject obj = new JSONObject();
            // 添加行号
            obj.put(ROW_NUM, 1);
            keyMap.entrySet().forEach(e -> obj.put(e.getValue(), ""));
            array.add(obj);
            return array;
        }
        for (int i = rowStart + 1; i <= rowEnd; i++) {
            Row eachRow = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            // 添加行号
            obj.put(ROW_NUM, i + 1);
            StringBuilder sb = new StringBuilder();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null && StringUtils.isNotBlank(keyMap.get(k))) {
                    Object val = getCellValue(eachRow.getCell(k), false);
                    // 所有数据添加到里面，用于判断该行是否为空
                    sb.append(val);
                    obj.put(keyMap.get(k), val);
                }
            }
            if (sb.length() > 0) {
                array.add(obj);
            }
        }
        return array;
    }

    /**
     * 获取单元格数据
     */
    private static Object getCellValue(Cell cell, Boolean flag) {
        // 空白或空
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return null;
        }
        // String类型
        if (cell.getCellType() == CellType.STRING) {
            String val = cell.getStringCellValue();
            if (val == null || val.trim().length() == 0) {
                return null;
            }
            return val.trim();
        }
        // 数字类型
        if (cell.getCellType() == CellType.NUMERIC) {
            // 科学计数法类型
            BigDecimal cellValue = BigDecimal.valueOf(cell.getNumericCellValue());
            if (flag) {// 需要将万元转换成元
                cellValue = cellValue.multiply(BigDecimal.valueOf(10000));
            }
            return NUMBER_FORMAT.format(cellValue).replace(",", "");
        }
        // 布尔值类型
        if (cell.getCellType() == CellType.BOOLEAN) {
            return cell.getBooleanCellValue();
        }
        // 错误类型
        return cell.getCellFormula();
    }

    /**
     * 是否是合并单元格
     */
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        boolean isMerged = false;//判断是否是合并单元格标记
        int sheetMergedCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergedCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow && column >= firstColumn && column <= lastColumn) {
                isMerged = true;// 是合并单元格
                break;
            }
        }
        return isMerged;
    }

    /**
     * 获取合并单元格内容
     */
    private static Object getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergedCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergedCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell, false);
                }
            }
        }
        return null;
    }

    /**
     * 获取每个对象的数据
     */
    public static <T> T getBean(Class<T> c, JSONObject obj, Map<Integer, String> uniqueMap) throws Exception {
        T t = c.newInstance();
        Field[] fields = c.getDeclaredFields();
        List<String> errMsgList = new ArrayList<>();
        boolean hasRowTipsField = false;
        StringBuilder uniqueBuilder = new StringBuilder();
        int rowNum = 0;
        for (Field field : fields) {
            // 行号
            if (ROW_NUM.equals(field.getName())) {
                rowNum = obj.getInteger(ROW_NUM);
                field.setAccessible(true);
                field.set(t, rowNum);
                continue;
            }
            // 是否需要设置异常信息
            if (ROW_TIPS.equals(field.getName())) {
                hasRowTipsField = true;
                continue;
            }
            // 原始数据
            if (ROW_DATA.equals(field.getName())) {
                field.setAccessible(true);
                field.set(t, obj.toString());
                continue;
            }
            // 设置对应属性值
            setFieldValue(t, field, obj, uniqueBuilder, errMsgList);
        }
        // 数据唯一性校验
        if (uniqueBuilder.length() > 0) {
            if (uniqueMap.containsValue(uniqueBuilder.toString())) {
                //Set<Integer> rowNumKeys = uniqueMap.keySet();
                Iterator<Map.Entry<Integer, String>> iterable = uniqueMap.entrySet().iterator();
                while (iterable.hasNext()) {
                    Map.Entry<Integer, String> entry = iterable.next();
                    if (entry.getValue().equals(uniqueBuilder.toString())) {
                        errMsgList.add(String.format("数据唯一性校验失败,(%s)与第%s行重复)", uniqueBuilder, entry.getKey()));
                    }
                }
                /*for (Integer num : rowNumKeys) {
                    if (uniqueMap.get(num).equals(uniqueBuilder.toString())) {
                        errMsgList.add(String.format("数据唯一性校验失败,(%s)与第%s行重复)", uniqueBuilder, num));
                    }
                }*/
            } else {
                uniqueMap.put(rowNum, uniqueBuilder.toString());
            }
        }
        // 失败处理
        if (errMsgList.isEmpty() && !hasRowTipsField) {
            return t;
        }
        StringBuilder sb = new StringBuilder();
        int size = errMsgList.size();
        for (int i = 0; i < size; i++) {
            if (i == size - 1) {
                sb.append(errMsgList.get(i));
            } else {
                sb.append(errMsgList.get(i)).append(";");
            }
        }
        // 设置错误信息
        for (Field field : fields) {
            if (ROW_TIPS.equals(field.getName())) {
                field.setAccessible(true);
                field.set(t, sb.toString());
            }
        }
        return t;
    }

    /**
     * 设置对应属性值
     */
    private static <T> void setFieldValue(T t, Field field, JSONObject obj, StringBuilder uniqueBuilder, List<String> errMsgList) {
        // 获取 ExcelImport 注解属性
        ExcelImport annotation = field.getAnnotation(ExcelImport.class);
        if (annotation == null) {
            return;
        }
        String cname = annotation.value();
        if (cname.trim().length() == 0) {
            return;
        }
        // 获取具体值
        String val = null;
        if (obj.containsKey(cname)) {
            val = getString(obj.getString(cname));
        }
        if (val == null) {
            return;
        }
        field.setAccessible(true);
        // 判断是否必填
        boolean require = annotation.required();
        if (require && val.isEmpty()) {
            errMsgList.add(String.format("[%s]不能为空", cname));
            return;
        }
        // 数据唯一性获取
        boolean unique = annotation.unique();
        if (unique) {
            if (uniqueBuilder.length() > 0) {
                uniqueBuilder.append("--").append(val);
            } else {
                uniqueBuilder.append(val);
            }
        }
        // 判断是否超过最大长度
        int maxLength = annotation.maxLength();
        if (maxLength > 0 && val.length() > maxLength) {
            errMsgList.add(String.format("[%s]长度不能超过%s个字符(当前%s个字符)", cname, maxLength, val.length()));
        }
        // 判断当前属性是否有映射关系
        LinkedHashMap<String, String> kvMap = getKvMap(annotation.kv());
        if (!kvMap.isEmpty()) {
            boolean isMatch = false;
            Iterator<Map.Entry<String, String>> iterable = kvMap.entrySet().iterator();
            while (iterable.hasNext()) {
                Map.Entry<String, String> entry = iterable.next();
                if (entry.getValue().equals(val)) {
                    val = entry.getKey();
                    isMatch = true;
                    break;
                }
            }
            /*for (String key : kvMap.keySet()) {
                if (kvMap.get(key).equals(val)) {
                    val = key;
                    isMatch = true;
                    break;
                }
            }*/
            if (!isMatch) {
                errMsgList.add(String.format("[%s]的值不正确(当前值为%s)", cname, val));
                return;
            }
        }
        // 其余情况根据类型赋值
        String fieldClassName = field.getType().getSimpleName();
        try {
            if ("String".equalsIgnoreCase(fieldClassName)) {
                field.set(t, val);
            } else if ("boolean".equalsIgnoreCase(fieldClassName)) {
                field.set(t, Boolean.valueOf(val));
            } else if ("int".equalsIgnoreCase(fieldClassName) || "Integer".equals(fieldClassName)) {
                try {
                    field.set(t, Integer.valueOf(val));
                } catch (NumberFormatException e) {
                    errMsgList.add(String.format("[%s]的值格式不正确(当前值为%s)", cname, val));
                }
            } else if ("double".equalsIgnoreCase(fieldClassName)) {
                field.set(t, Double.valueOf(val));
            } else if ("long".equalsIgnoreCase(fieldClassName)) {
                field.set(t, Long.valueOf(val));
            } else if ("BigDecimal".equalsIgnoreCase(fieldClassName)) {
                field.set(t, new BigDecimal(val));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 处理String的值
     */
    private static String getString(String s) {
        if (s == null) {
            return "";
        }
        if (s.isEmpty()) {
            return s;
        }
        return s.trim();
    }

    /**
     * 判断属性是否有映射关系
     */
    private static LinkedHashMap<String, String> getKvMap(String kv) {
        LinkedHashMap<String, String> kvMap = new LinkedHashMap<>();
        if (kv.isEmpty()) {
            return kvMap;
        }
        String[] kvs = kv.split(";");
        if (kvs.length == 0) {
            return kvMap;
        }
        for (String each : kvs) {
            String[] eachKv = getString(each).split("-");
            if (eachKv.length != 2) {
                continue;
            }
            String k = eachKv[0];
            String v = eachKv[1];
            if (k.isEmpty() || v.isEmpty()) {
                continue;
            }
            kvMap.put(k, v);
        }
        return kvMap;
    }

}
