package com.cool.store.utils.poi;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.EasyExcel;
import com.aliyun.openservices.shade.com.google.common.collect.Lists;
import com.cool.store.annotation.Excel;
import com.cool.store.annotation.Excels;
import com.cool.store.utils.poi.constant.Constants;
import com.cool.store.utils.poi.reflect.ReflectUtils;
import com.cool.store.utils.poi.text.Convert;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;
/**
 * Excel相关处理
 *
 * @author ruoyi
 */
public class ExcelUtil<T> {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
    /**
     * 字体变红
     */
    public static String FONT_RED = "fontRed";

    /**
     * 背景色变绿
     */
    public static String BACKGROUND_COLOR_YELLOW = "backgroundColorYellow";

    /**
     * Excel sheet最大行数，默认65536
     */
    public static final int SHEET_SIZE = 65536;

    /**
     * 工作表名称
     */
    private String sheetName;

    /**
     * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
     */
    private Excel.Type type;

    /**
     * 工作薄对象
     */
    private Workbook wb;

    /**
     * 工作表对象
     */
    private Sheet sheet;

    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;

    /**
     * 设置样式列表  map<行,map<列,样式>>
     */
    private Map<Integer, Map<Integer, List<String>>> styleCells;

    /**
     * 导入导出数据列表
     */
    private List<T> list;

    /**
     * 注解列表
     */
    private List<Object[]> fields;

    /**
     * 统计列表
     */
    private Map<Integer, Double> statistics = new HashMap<>();

    /**
     * 数字格式
     */
    private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");

    /**
     * 实体对象
     */
    public Class<T> clazz;

    public ExcelUtil(Class<T> clazz) {
        this.clazz = clazz;
    }


    public void init(List<T> list, String sheetName, Excel.Type type) {
        if (list == null) {
            list = new ArrayList<>();
        }
        this.list = list;
        this.sheetName = sheetName;
        this.type = type;
        createExcelField();
        createWorkbook();
    }

    public void init(List<T> list, String sheetName, Excel.Type type, Map<Integer, Map<Integer, List<String>>> styleCells) {
        if (list == null) {
            list = new ArrayList<>();
        }
        this.list = list;
        this.sheetName = sheetName;
        this.type = type;
        this.styleCells = styleCells;
        createExcelField();
        createWorkbook();
    }

    /**
     * 对excel表单默认第一个索引名转换成list
     *
     * @param is 输入流
     * @return 转换后集合
     */
    public List<T> importExcel(InputStream is) throws Exception {
        return importExcel(StringUtils.EMPTY, is);
    }

    /**
     * 对excel表单默认第一个索引名转换成list（EasyExcel）
     *
     * @param is 输入流
     * @return 转换后集合
     */
    public List<T> importEasyExcel(InputStream is) throws Exception
    {
        return EasyExcel.read(is).head(clazz).sheet().doReadSync();
    }

    /**
     * 对excel表单指定表格索引名转换成list
     *
     * @param sheetName 表格索引名
     * @param is        输入流
     * @return 转换后集合
     */
    public List<T> importExcel(String sheetName, InputStream is) throws Exception {
        this.type = Excel.Type.IMPORT;
        this.wb = WorkbookFactory.create(is);
        List<T> list = null;
        Sheet sheet;
        if (StringUtils.isNotEmpty(sheetName)) {
            // 如果指定sheet名,则取指定sheet中的内容.
            sheet = wb.getSheet(sheetName);
        } else {
            // 如果传入的sheet名不存在则默认指向第1个sheet.
            sheet = wb.getSheetAt(0);
        }

        if (sheet == null) {
            throw new IOException("文件sheet不存在");
        }

        int num = sheet.getLastRowNum();
        //删除的空行(包含空白行和带有格式的空行)
        int bk = 0;
        //记录空白行的集合
        List<Integer> nums = Lists.newArrayList();
        for (int i = 1; i <= num; i++) {
            Row row = sheet.getRow(i);
            boolean flag = false;
            //带有格式的空白行
            if (row != null) {
                for (Cell cell : row) {
                    //判断该单元格是否全为空
                    if (StringUtils.isNotEmpty(cell.toString())) {
                        flag = true;
                        break;
                    }
                }
                if (!flag) {
                    nums.add(i);
                    bk++;
                }
                //空白行
            } else {
                nums.add(i);
                bk++;
            }
        }
        //删除无效数据行(空白行,带格式的空白行)
        for (Integer n : nums) {
            if (sheet.getRow(n) != null) {
                sheet.removeRow(sheet.getRow(n));
            }
        }
        int rows = sheet.getPhysicalNumberOfRows();

        if (rows > 0) {
            // 定义一个map用于存放excel列的序号和field.
            Map<String, Integer> cellMap = new HashMap<>(16);
            // 获取表头
            Row heard = sheet.getRow(0);
            for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {
                Cell cell = heard.getCell(i);
                if (ObjectUtil.isNotNull(cell)) {
                    String value = this.getCellValue(heard, i).toString();
                    cellMap.put(value, i);
                } else {
                    cellMap.put(null, i);
                }
            }
            // 有数据时才处理 得到类的所有field.
            Field[] allFields = clazz.getDeclaredFields();
            // 定义一个map用于存放列的序号和field.
            Map<Integer, Field> fieldsMap = new HashMap<>(16);
            for (Field field : allFields) {
                Excel attr = field.getAnnotation(Excel.class);
                // 判断字段是否为导出导入或为this.type
                boolean isExportAndImport = attr != null && (attr.type() == Excel.Type.ALL || attr.type() == type);
                if (isExportAndImport) {
                    // 设置类的私有字段属性可访问.
                    field.setAccessible(true);
                    Integer column = cellMap.get(attr.name());
                    if (column != null) {
                        fieldsMap.put(column, field);
                    }
                }
            }
            // 将数据填入集合
            list = fillCollection(sheet, rows, fieldsMap);
        }
        return list;
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param response  返回数据
     * @param list      导出数据集合
     * @param sheetName 工作表的名称
     */
    public void exportExcel(HttpServletResponse response, List<T> list, String sheetName) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        this.init(list, sheetName, Excel.Type.EXPORT);
        exportExcel(response.getOutputStream(),false);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param response  返回数据
     * @param list      导出数据集合
     * @param sheetName 工作表的名称
     */
    public void exportExcel(HttpServletResponse response, List<T> list, String sheetName, Map<Integer, Map<Integer, List<String>>> styleCells) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        this.init(list, sheetName, Excel.Type.EXPORT, styleCells);
        exportExcel(response.getOutputStream(),true);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param list      导出数据集合
     * @param sheetName 工作表的名称
     */
    public void exportExcel(OutputStream outputStream, List<T> list, String sheetName, Map<Integer, Map<Integer, List<String>>> styleCells) throws IOException {
        if (styleCells.size()==0) {
            this.init(list, sheetName, Excel.Type.EXPORT);
        }else {
            this.init(list, sheetName, Excel.Type.EXPORT,styleCells);
        }
        exportExcel(outputStream,true);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param sheetName 工作表的名称
     */
    public void importTemplateExcel(HttpServletResponse response, String sheetName) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        this.init(null, sheetName, Excel.Type.IMPORT);
        exportExcel(response.getOutputStream(),false);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     * @param outputStream
     * @param pan 是否设置样式
     */
    public void exportExcel(OutputStream outputStream,boolean pan) {
        try {
            // 取出一共有多少个sheet.
            int sheetNo = list.size() % SHEET_SIZE > 0 ? list.size() / SHEET_SIZE + 1 : list.size() / SHEET_SIZE;
            createSheet(sheetNo, 0);
            for (int index = 0; index <= sheetNo; index++) {
                // 产生一行
                Row row = sheet.createRow(0);
                int column = 0;
                // 写入各个字段的列头名称
                for (Object[] os : fields) {
                    Excel excel = (Excel) os[1];
                    this.createCell(excel, row, column++);
                }
                if (Excel.Type.EXPORT.equals(type)) {
                    fillExcelData(index, row,pan);
                    addStatisticsRow();
                }
            }
            wb.write(outputStream);
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }

    /**
     * 填充excel数据
     *
     * @param index 序号
     * @param row   单元格行
     */
    public void fillExcelData(int index, Row row,boolean pan) {
        int startNo = index * SHEET_SIZE;
        int endNo = Math.min(startNo + SHEET_SIZE, list.size());
        for (int i = startNo; i < endNo; i++) {
            row = sheet.createRow(i + 1 - startNo);
            // 得到导出对象.
            T vo = (T) list.get(i);
            int column = 0;
            for (Object[] os : fields) {
                Field field = (Field) os[0];
                Excel excel = (Excel) os[1];
                // 设置实体类私有属性可访问
                field.setAccessible(true);
                this.addCell(excel, row, vo, field, column++, i,pan);
            }
        }
    }

    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private Map<String, CellStyle> createStyles(Workbook wb) {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<>(16);
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font totalFont = wb.createFont();
        totalFont.setFontName("Arial");
        totalFont.setFontHeightInPoints((short) 10);
        style.setFont(totalFont);
        styles.put("total", style);

        return styles;
    }


    /**
     * 创建单元格
     *
     * @param attr   属性配置
     * @param row    行
     * @param column 列
     * @return 单元格
     */
    public Cell createCell(Excel attr, Row row, int column) {
        // 创建列
        Cell cell = row.createCell(column);
        // 写入列信息
        cell.setCellValue(attr.name());
        setDataValidation(attr, row, column);
        //默认样式
        CellStyle cellStyle = styles.get("header");
        cell.setCellStyle(cellStyle);
        return cell;
    }

    /**
     * 设置单元格信息
     *
     * @param value 单元格值
     * @param attr  注解相关
     * @param cell  单元格信息
     */
    public void setCellVo(Object value, Excel attr, Cell cell) {
        if (Excel.ColumnType.STRING == attr.cellType()) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
        } else if (Excel.ColumnType.NUMERIC == attr.cellType()) {
            cell.setCellType(CellType.NUMERIC);
            if (StringUtils.isNull(value)) {
                cell.setCellValue(attr.defaultValue());
            } else {
                cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
            }
        }
    }

    /**
     * 创建表格样式
     */
    public void setDataValidation(Excel attr, Row row, int column) {
        String prefix = "注：";
        if (attr.name().contains(prefix)) {
            sheet.setColumnWidth(column, 6000);
        } else {
            // 设置列宽
            sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
            row.setHeight((short) (attr.height() * 20));
        }
        // 如果设置了提示信息则鼠标放上去提示.
        if (StringUtils.isNotEmpty(attr.prompt())) {
            // 这里默认设了2-101列提示.
            setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
        }
        // 如果设置了combo属性则本列只能选择不能输入
        if (attr.combo().length > 0) {
            // 这里默认设了2-101列只能选择不能输入.
            setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
        }
    }

    /**
     * 添加单元格
     *
     * @param attr   注解相关
     * @param row    单元行
     * @param vo     实体对象
     * @param field  字段
     * @param column 列
     * @return 单元格
     */
    public Cell addCell(Excel attr, Row row, T vo, Field field, int column, int lineNumber,boolean stylePan) {
        Cell cell = null;
        try {
            // 设置行高
            row.setHeight((short) (attr.height() * 20));
            // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
            if (attr.isExport()) {
                // 创建cell
                cell = row.createCell(column);
                // 用于读取对象中的属性
                Object value = getTargetValue(vo, field, attr);
                String dateFormat = attr.dateFormat();
                String readConverterExp = attr.readConverterExp();
                String separator = attr.separator();
                //默认样式
                CellStyle cellStyle = cell.getRow().getSheet().getWorkbook().createCellStyle();
                cellStyle.cloneStyleFrom(styles.get("data"));
                if(stylePan&&!CollectionUtils.isEmpty(styleCells)){
                    if (styleCells.containsKey(lineNumber)) {
                        Map<Integer, List<String>> columnMap = styleCells.get(lineNumber);
                        if (columnMap.containsKey(column)) {
                            List<String> styleFlagList = columnMap.get(column);
                            boolean pan = true;
                            for (String styleFlag : styleFlagList) {
                                if (FONT_RED.equals(styleFlag)) {
                                    //设置字体颜色
                                    setFontColor(cellStyle, IndexedColors.RED.index);
                                }
                                if (BACKGROUND_COLOR_YELLOW.equals(styleFlag)) {
                                    //设置背景颜色
                                    cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                                    //设置了背景颜色就不设置默认自定义背景颜色
                                    pan = false;
                                }
                            }
                            if (pan) {
                                setColumnsColor(attr, cellStyle);
                            }
                        } else {
                            //自定义样式
                            setColumnsColor(attr, cellStyle);
                        }
                        cell.setCellStyle(cellStyle);
                    } else {
                        //自定义样式
                        short columnsColor = attr.columnsColor();
                        if (columnsColor != 0) {
                            //设置单元格的填充颜色
                            cellStyle.setFillForegroundColor(columnsColor);
                            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            cell.setCellStyle(cellStyle);
                        } else {
                            cell.setCellStyle(cellStyle);
                        }
                    }
                }
                if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) {
                    cell.setCellValue(parseDateToStr(dateFormat, (Date) value));
                } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) {
                    cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
                } else if (value instanceof BigDecimal && -1 != attr.scale()) {
                    cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).stripTrailingZeros().toPlainString());
                } else {
                    // 设置列类型
                    setCellVo(value, attr, cell);
                }
                addStatisticsData(column, Convert.toStr(value), attr);
            }
        } catch (Exception e) {
            log.error("导出Excel失败{}", e);
        }
        return cell;
    }


    /**
     * 格式化日期
     * @param format 格式化规则
     * @param date 日期
     * @return 格式化完毕的日期
     */
    public static String parseDateToStr(final String format, final Date date)
    {
        return new SimpleDateFormat(format).format(date);
    }


    /**
     * 设置样式列
     *
     * @param styleMap    样式map
     * @param row         行号
     * @param sort        列号
     * @param styles       样式字符
     */
    public static void setStyleMap(Map<Integer, Map<Integer, List<String>>> styleMap, int row, int sort, String[] styles) {
        if (styleMap.containsKey(row)) {
            Map<Integer, List<String>> columnListMap = styleMap.get(row);
            if (columnListMap.containsKey(sort)) {
                List<String> list = columnListMap.get(sort);
                List<String> arrList = new ArrayList<String>(Arrays.asList(styles));
                list.addAll(arrList);
                columnListMap.put(sort, list);
            }else {
                columnListMap.put(sort,  new ArrayList<String>(Arrays.asList(styles)));
            }
        } else {
            styleMap.put(row, new HashMap<Integer, List<String>>() {{
                put(sort, new ArrayList<String>(Arrays.asList(styles)));
            }});
        }
    }


    /**
     * 设置整行样式
     *
     * @param styleMap
     * @param style
     * @param columnNumCount 总列数
     */
    public static void setRowStyle(Map<Integer, Map<Integer, List<String>>> styleMap, String style, int columnNumCount) {
        for (Integer row : styleMap.keySet()) {
            boolean pan = false;
            Map<Integer, List<String>> integerListMap = styleMap.get(row);
            for (int i = 0; i <= columnNumCount; i++) {
                if (pan) {
                    continue;
                }
                if (integerListMap.containsKey(i)) {
                    if (i == 0) {
                        pan = true;
                    } else {
                        List<String> stringList = integerListMap.get(i);
                        stringList.add(style);
                    }
                } else {
                    integerListMap.put(i, new ArrayList<String>() {{
                        add(style);
                    }});
                }
            }
        }
    }


    /**
     * 自定义设置背景颜色
     *
     * @param attr
     * @param baseCellStyle
     */
    private void setColumnsColor(Excel attr, CellStyle baseCellStyle) {
        //自定义样式
        short columnsColor = attr.columnsColor();
        if (columnsColor != 0) {
            //设置单元格的填充颜色
            baseCellStyle.setFillForegroundColor(columnsColor);
            baseCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
    }

    /**
     * 自定义设置字体颜色
     *
     * @param baseCellStyle
     * @param color
     */
    private void setFontColor(CellStyle baseCellStyle, short color) {
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        dataFont.setColor(color);
        baseCellStyle.setFont(dataFont);
    }

    /**
     * 设置 POI XSSFSheet 单元格提示
     *
     * @param sheet         表单
     * @param promptTitle   提示标题
     * @param promptContent 提示内容
     * @param firstRow      开始行
     * @param endRow        结束行
     * @param firstCol      开始列
     * @param endCol        结束列
     */
    public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
                              int firstCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        dataValidation.createPromptBox(promptTitle, promptContent);
        dataValidation.setShowPromptBox(true);
        sheet.addValidationData(dataValidation);
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     *
     * @param sheet    要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow   结束行
     * @param firstCol 开始列
     * @param endCol   结束列
     */
    public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
    }

    /**
     * 解析导出值 0=男,1=女,2=未知
     *
     * @param propertyValue 参数值
     * @param converterExp  翻译注解
     * @param separator     分隔符
     * @return 解析后值
     */
    public static String convertByExp(String propertyValue, String converterExp, String separator) {
        StringBuilder propertyString = new StringBuilder();
        String[] convertSource = converterExp.split(",");
        for (String item : convertSource) {
            String[] itemArray = item.split(Constants.EQUAL_SIGN);
            if (StringUtils.containsAny(separator, propertyValue)) {
                for (String value : propertyValue.split(separator)) {
                    if (itemArray[0].equals(value)) {
                        propertyString.append(itemArray[1]).append(separator);
                        break;
                    }
                }
            } else {
                if (itemArray[0].equals(propertyValue)) {
                    return itemArray[1];
                }
            }
        }
        return StringUtils.stripEnd(propertyString.toString(), separator);
    }

    /**
     * 反向解析值 男=0,女=1,未知=2
     *
     * @param propertyValue 参数值
     * @param converterExp  翻译注解
     * @param separator     分隔符
     * @return 解析后值
     */
    public static String reverseByExp(String propertyValue, String converterExp, String separator) {
        StringBuilder propertyString = new StringBuilder();
        String[] convertSource = converterExp.split(",");
        for (String item : convertSource) {
            String[] itemArray = item.split(Constants.EQUAL_SIGN);
            if (StringUtils.containsAny(separator, propertyValue)) {
                for (String value : propertyValue.split(separator)) {
                    if (itemArray[1].equals(value)) {
                        propertyString.append(itemArray[0]).append(separator);
                        break;
                    }
                }
            } else {
                if (itemArray[1].equals(propertyValue)) {
                    return itemArray[0];
                }
            }
        }
        return StringUtils.stripEnd(propertyString.toString(), separator);
    }

    /**
     * 合计统计信息
     */
    private void addStatisticsData(Integer index, String text, Excel entity) {
        if (entity != null && entity.isStatistics()) {
            Double temp = 0D;
            if (!statistics.containsKey(index)) {
                statistics.put(index, temp);
            }
            try {
                temp = Double.valueOf(text);
            } catch (NumberFormatException e) {
                e.printStackTrace();
            }
            statistics.put(index, statistics.get(index) + temp);
        }
    }

    /**
     * 创建统计行
     */
    public void addStatisticsRow() {
        if (statistics.size() > 0) {
            Cell cell = null;
            Row row = sheet.createRow(sheet.getLastRowNum() + 1);
            Set<Integer> keys = statistics.keySet();
            cell = row.createCell(0);
            cell.setCellStyle(styles.get("total"));
            cell.setCellValue("合计");

            for (Integer key : keys) {
                cell = row.createCell(key);
                cell.setCellStyle(styles.get("total"));
                cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
            }
            statistics.clear();
        }
    }

    /**
     * 获取bean中的属性值
     *
     * @param vo    实体对象
     * @param field 字段
     * @param excel 注解
     * @return 最终的属性值
     * @throws Exception 没有找到字段
     */
    private Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
        Object o = field.get(vo);
        if (StringUtils.isNotEmpty(excel.targetAttr())) {
            String target = excel.targetAttr();
            if (target.contains(Constants.POMINT)) {
                String[] targets = target.split("[.]");
                for (String name : targets) {
                    o = getValue(o, name);
                }
            } else {
                o = getValue(o, target);
            }
        }
        return o;
    }

    /**
     * 以类的属性的get方法方法形式获取值
     *
     * @param o    实体对象
     * @param name 字段名
     * @return value
     * @throws Exception 没有找到该字段
     */
    private Object getValue(Object o, String name) throws Exception {
        if (StringUtils.isNotEmpty(name)) {
            Class<?> clazz = o.getClass();
            Field field = clazz.getDeclaredField(name);
            field.setAccessible(true);
            o = field.get(o);
        }
        return o;
    }

    /**
     * 得到所有定义字段
     */
    private void createExcelField() {
        this.fields = new ArrayList<>();
        List<Field> tempFields = new ArrayList<>();
        tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
        tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
        for (Field field : tempFields) {
            // 单注解
            if (field.isAnnotationPresent(Excel.class)) {
                putToField(field, field.getAnnotation(Excel.class));
            }

            // 多注解
            if (field.isAnnotationPresent(Excels.class)) {
                Excels attrs = field.getAnnotation(Excels.class);
                Excel[] excels = attrs.value();
                for (Excel excel : excels) {
                    putToField(field, excel);
                }
            }
        }
        this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
    }

    /**
     * 放到字段集合中
     */
    private void putToField(Field field, Excel attr) {
        // 判断字段是否为导出导入或为this.type
        boolean isExportAndImport = attr != null && (attr.type() == Excel.Type.ALL || attr.type() == type);
        if (isExportAndImport) {
            this.fields.add(new Object[]{field, attr});
        }
    }

    /**
     * 创建一个工作簿
     */
    public void createWorkbook() {
        this.wb = new SXSSFWorkbook(800);
    }

    /**
     * 创建工作表
     *
     * @param sheetNo sheet数量
     * @param index   序号
     */
    public void createSheet(double sheetNo, int index) {
        this.sheet = wb.createSheet();
        this.styles = createStyles(wb);
        // 设置工作表的名称.
        if (sheetNo == 0) {
            wb.setSheetName(index, sheetName);
        } else {
            wb.setSheetName(index, sheetName + index);
        }
    }

    /**
     * 获取单元格值
     *
     * @param row    获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    public Object getCellValue(Row row, int column) {
        if (row == null) {
            return row;
        }
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (StringUtils.isNotNull(cell)) {
                if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) {
                    val = cell.getNumericCellValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // POI Excel 日期格式转换
                        val = DateUtil.getJavaDate((Double) val);
                    } else {
                        if ((Double) val % 1 > 0) {
                            val = new BigDecimal(val.toString());
                        } else {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }

            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }


    private static ArrayList<String> PoiDateList = new ArrayList<String>() {
        {
            add("年");
            add("月");
            add("日");
        }
    };

    /**
     * 获取单元格值
     *
     * @param cell 单元格
     * @return String单元格值
     */
    public static String getCellValue(Cell cell) {
        //判断是否为null或空串
        if (cell == null || "".equals(cell.toString().trim())) {
            return "";
        }
        CellType cellType = cell.getCellTypeEnum();
        String cellValue = "";
        // 1:字符串,2:数字,3:浮点，4:日期
        switch (cellType) {
            //字符串类型
            case STRING:
                cellValue = cell.getStringCellValue();
                //去除unicode字符串中的不可见字符
                cellValue = StringUtils.isEmpty(cellValue) ? "" : cellValue.trim()
                        .replaceAll("\\p{C}", "");
                break;
            case BLANK:
                cellValue = "";
                break;
            // 数字
            case NUMERIC:
                //  获取单元格值的格式化信息
                String dataFormat = cell.getCellStyle().getDataFormatString();
                //  判断格式化信息中是否存在：年月日
                AtomicReference<Boolean> isDate = new AtomicReference<>(false);
                if (StringUtils.isNotNull(dataFormat)) {
                    PoiDateList.forEach(x -> isDate.set(isDate.get() || dataFormat.contains(x)));
                }

                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else if (DateUtil.isCellInternalDateFormatted(cell)) {
                    cellValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else if (isDate.get()) {
                    cellValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
                } else if (dataFormat == null) {
                    cellValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    if (StringUtils.isNull(dataFormat)) {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    } else {
                        if (cell.getCellStyle().getDataFormatString().contains(Constants.USD)) {
                            cellValue = Constants.USD + cell.getNumericCellValue();
                        } else if (cell.getCellStyle().getDataFormatString().contains(Constants.RMB_FULL)) {
                            cellValue = Constants.RMB_FULL + cell.getNumericCellValue();
                        } else if (cell.getCellStyle().getDataFormatString().contains(Constants.RMB_HALF)) {
                            cellValue = Constants.RMB_HALF + cell.getNumericCellValue();
                        } else if (cell.getCellStyle().getDataFormatString().contains(Constants.EUR)) {
                            cellValue = Constants.EUR + cell.getNumericCellValue();
                        } else {
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        }
                    }
                }
                break;
            default:
                cellValue = "";
                break;
        }
        return cellValue;
    }

    /**
     * 获取单元格值
     *
     * @param cell     单元格
     * @param cellType 单元格类型
     * @return String单元格值
     */
    public static String getCellValue(Cell cell, int cellType) {
        //判断是否为null或空串
        if (cell == null || "".equals(cell.toString().trim())) {
            return "";
        }
        String cellValue = "";
        // 1:字符串,2:数字,3:浮点，4:日期
        switch (cellType) {
            //字符串类型
            case 1:
                cell.setCellType(CellType.STRING);
                cellValue = cell.getStringCellValue();
                //去除unicode字符串中的不可见字符
                cellValue = StringUtils.isEmpty(cellValue) ? "" : cellValue.trim()
                        .replaceAll("\\p{C}", "");
                break;
            // 数字
            case 2:
                cellValue = new DecimalFormat("#").format(cell.getNumericCellValue());
                break;
            //日期
            case 3:
                cellValue = new DecimalFormat("#.###").format(cell.getNumericCellValue());
                break;
            case 4:
                //  获取单元格值的格式化信息
                String dataFormat = cell.getCellStyle().getDataFormatString();
                //  判断格式化信息中是否存在：年月日
                AtomicReference<Boolean> isDate = new AtomicReference<>(false);
                if (StringUtils.isNotNull(dataFormat)) {
                    PoiDateList.forEach(x -> isDate.set(isDate.get() || dataFormat.contains(x)));
                }

                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else if (DateUtil.isCellInternalDateFormatted(cell)) {
                    cellValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else if (isDate.get()) {
                    cellValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
                } else if (dataFormat == null) {
                    cellValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    if (cell.getCellStyle().getDataFormatString().contains(Constants.USD)) {
                        cellValue = Constants.USD + cell.getNumericCellValue();
                    } else if (cell.getCellStyle().getDataFormatString().contains(Constants.RMB_FULL)) {
                        cellValue = Constants.RMB_FULL + cell.getNumericCellValue();
                    } else if (cell.getCellStyle().getDataFormatString().contains(Constants.RMB_HALF)) {
                        cellValue = Constants.RMB_HALF + cell.getNumericCellValue();
                    } else if (cell.getCellStyle().getDataFormatString().contains(Constants.EUR)) {
                        cellValue = Constants.EUR + cell.getNumericCellValue();
                    } else {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                }
                break;
            default:
                cellValue = "";
                break;
        }
        return cellValue;
    }


    /**
     * 将数据填入集合
     *
     * @param sheet     sheet
     * @param rows      行数
     * @param fieldsMap 用于存放列的序号和field
     * @throws InstantiationException 实例创建异常
     * @throws IllegalAccessException 实例创建异常
     */
    private List<T> fillCollection(Sheet sheet, int rows, Map<Integer, Field> fieldsMap) throws InstantiationException, IllegalAccessException {
        List<T> list = new ArrayList<>();
        int j=1;
        for (int i = 0; i < rows-1; i++) {
            // 从第2行开始取数据,默认第一行是表头.
            Row row = sheet.getRow(j);
            j++;
            T entity = null;
            for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet()) {
                Object val = this.getCellValue(row, entry.getKey());

                // 如果不存在实例则新建.
                entity = (entity == null ? clazz.newInstance() : entity);
                // 从map中得到对应列的field.
                Field field = fieldsMap.get(entry.getKey());
                // 取得类型,并根据对象类型设置值.
                Class<?> fieldType = field.getType();
                boolean isNumber = (Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val));
                if (String.class == fieldType) {
                    String s = Convert.toStr(val);
                    if (StringUtils.endsWith(s, ".0")) {
                        val = StringUtils.substringBefore(s, ".0");
                    } else {
                        val = Convert.toStr(val);
                    }
                } else if (isNumber) {
                    val = Convert.toInt(val);
                } else if (Long.TYPE == fieldType || Long.class == fieldType) {
                    val = Convert.toLong(val);
                } else if (Double.TYPE == fieldType || Double.class == fieldType) {
                    val = Convert.toDouble(val);
                } else if (Float.TYPE == fieldType || Float.class == fieldType) {
                    val = Convert.toFloat(val);
                } else if (BigDecimal.class == fieldType) {
                    val = Convert.toBigDecimal(val);
                } else if (Date.class == fieldType) {
                    if (val instanceof String) {
                        val = DateUtils.parseDate(val);
                    } else if (val instanceof Double) {
                        val = DateUtil.getJavaDate((Double) val);
                    }
                }
                Excel attr = field.getAnnotation(Excel.class);
                String propertyName = field.getName();
                if (StringUtils.isNotEmpty(attr.targetAttr())) {
                    propertyName = field.getName() + "." + attr.targetAttr();
                } else if (StringUtils.isNotEmpty(attr.readConverterExp())) {
                    val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
                }
                ReflectUtils.invokeSetter(entity, propertyName, val);
            }
            list.add(entity);
        }
        return list;
    }



}
