package com.zbkj.common.utils.excel;

import com.alibaba.fastjson.JSON;
import com.zbkj.common.annotation.Excel;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public final class ExcelImportUtil {

    /**
     * Excel 导入 字段类型 Integer,Long,Double,Date,String,Boolean,BigDecimal
     */
    public static Collection<?> importExcel(File file, Class<?> pojoClass, ImportParams params) {
        FileInputStream in = null;
        Collection<?> result = null;
        try {
            in = new FileInputStream(file);
            result = importExcelByIs(in, pojoClass, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * Excel 导入 field 字段类型 Integer,Long,Double,Date,String,Boolean,BigDecimal
     */
    public static Collection<?> importExcelByIs(InputStream inputstream,
                                                Class<?> pojoClass, ImportParams params) throws Exception {
        Collection<T> result = new ArrayList<T>();
        Workbook book = null;
        if (!inputstream.markSupported()) {
            inputstream = new BufferedInputStream(inputstream);
        }
        if (POIFSFileSystem.hasPOIFSHeader(inputstream)) {
            book = new HSSFWorkbook(inputstream);
        } else if (POIXMLDocument.hasOOXMLHeader(inputstream)) {
            book = new XSSFWorkbook(OPCPackage.open(inputstream));
        }
        for (int i = 0; i < params.getSheetNum(); i++) {
            result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params));
        }
        return result;
    }


    @SuppressWarnings({"rawtypes", "unchecked"})
    private static Collection<? extends T> importExcel(Collection<T> result, Sheet sheet,
                                                       Class<?> pojoClass, ImportParams params) throws Exception {
        Collection collection = new ArrayList();
        Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
        Field fileds[] = ExcelPublicUtil.getClassFields(pojoClass);
        getAllExcelField(fileds, excelParams, pojoClass, null);
        Iterator<Row> rows = sheet.rowIterator();
        for (int j = 0; j < params.getTitleRows(); j++) {
            rows.next();
        }
        Row row = null;
        Iterator<Cell> cellTitle;
        Map<Integer, String> titlemap = new HashMap<Integer, String>();
        for (int j = 0; j < params.getSecondTitleRows(); j++) {
            row = rows.next();
            cellTitle = row.cellIterator();
            int i = row.getFirstCellNum();
            while (cellTitle.hasNext()) {
                Cell cell = cellTitle.next();
                String value = cell.getStringCellValue();
                if (!StringUtils.isEmpty(value)) {
                    titlemap.put(i, value);
                }
                i = i + 1;
            }
        }
        Object object = null;
        while (rows.hasNext()) {
            row = rows.next();

            object = ExcelPublicUtil.createObject(pojoClass);
            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                String titleString = (String) titlemap.get(i);
                if (excelParams.containsKey(titleString)) {
                    if (cell == null || ("").equals(cell.toString())) {
                        continue;
                    }
                    judgeTypeAndSetValue(object, cell, excelParams, titleString);
                }
            }
            collection.add(object);
        }
        return collection;
    }

    /**
     * 设置值
     */
    private static void judgeTypeAndSetValue(Object object, Cell cell,
                                             Map<String, ExcelImportEntity> excelParams, String titleString)
            throws Exception {
        ExcelImportEntity entity = excelParams.get(titleString);
        Method setMethod = entity.getSetMethods() != null && entity.getSetMethods().size() > 0 ? entity
                .getSetMethods().get(entity.getSetMethods().size() - 1)
                : entity.getSetMethod();
        Type[] ts = setMethod.getGenericParameterTypes();
        String xclass = ts[0].toString();
        if (xclass.equals("class java.lang.String")) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String cellValue = cell.getStringCellValue();
            //NumberUtils.isCreatable()判断是否是数字类型
            if (NumberUtils.isCreatable(cellValue)) {
                NumberFormat numberFormat = NumberFormat.getInstance();
                //如果在格式化和分析数字时使用千分隔符，则为true.
                numberFormat.setGroupingUsed(false);
                //x设置小数点保留位数，这里保留10位小数
                numberFormat.setMaximumFractionDigits(10);
                //numberFormat.format()格式化小数
                cellValue = numberFormat.format(Double.valueOf(cellValue));
            }
            setValues(entity, object, cellValue);
        } else if (xclass.equals("class java.util.Date")) {
            Date cellDate = null;
            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                // 日期格式
                cellDate = cell.getDateCellValue();
                setValues(entity, object, cellDate);
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cellDate = getDateData(entity, cell.getStringCellValue());
                setValues(entity, object, cellDate);
            }
        } else if (xclass.equals("class java.lang.Boolean")) {
            boolean valBool;
            if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
                valBool = cell.getBooleanCellValue();
            } else {
                valBool = cell.getStringCellValue().equalsIgnoreCase("true")
                        || (!cell.getStringCellValue().equals("0"));
            }
            setValues(entity, object, valBool);
        } else if (xclass.equals("class java.lang.Integer")) {
            Integer valInt;
            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                valInt = (new Double(cell.getNumericCellValue())).intValue();
            } else {
                valInt = new Integer(cell.getStringCellValue());
            }
            setValues(entity, object, valInt);
        } else if (xclass.equals("class java.lang.Long")) {
            Long valLong;
            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                valLong = (new Double(cell.getNumericCellValue())).longValue();
            } else {
                valLong = new Long(cell.getStringCellValue());
            }
            setValues(entity, object, valLong);
        } else if (xclass.equals("class java.math.BigDecimal")) {
            BigDecimal valDecimal;
            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                valDecimal = new BigDecimal(cell.getNumericCellValue());
            } else {
                valDecimal = new BigDecimal(cell.getStringCellValue());
            }
            setValues(entity, object, valDecimal);
        } else if (xclass.equals("class java.lang.Double")) {
            Double valDouble;
            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                valDouble = new Double(cell.getNumericCellValue());
            } else {
                valDouble = new Double(cell.getStringCellValue());
            }
            setValues(entity, object, valDouble);
        } else if (xclass.contains("java.util.List")) {
            int start = xclass.indexOf('<');
            int end = xclass.indexOf('>');
            Class<?> clazz = Class.forName(xclass.substring(start + 1, end));
            Object instance = clazz.newInstance();
            List list = JSON.parseArray(cell.getStringCellValue(), instance.getClass());
            setValues(entity, object, list);
        }
    }

    /**
     * 获取日期类型数据
     */
    private static Date getDateData(ExcelImportEntity entity, String value) {
        if (StringUtils.isNotEmpty(entity.getImportFormat()) &&
                StringUtils.isNotEmpty(value)) {
            SimpleDateFormat format = new SimpleDateFormat(entity.getImportFormat());
            try {
                return format.parse(value);
            } catch (ParseException e) {
            }
        }
        return null;
    }

    private static void setValues(ExcelImportEntity entity, Object object,
                                  Object value) throws Exception {
        if (entity.getSetMethods() != null) {
            setFieldBySomeMethod(entity.getSetMethods(), object, value);
        } else {
            entity.getSetMethod().invoke(object, value);
        }
    }

    /**
     * 多个get 最后再set
     */
    private static void setFieldBySomeMethod(List<Method> setMethods,
                                             Object object, Object value) throws Exception {
        Object t = getFieldBySomeMethod(setMethods, object);
        setMethods.get(setMethods.size() - 1).invoke(t, value);
    }

    private static Object getFieldBySomeMethod(List<Method> list, Object t)
            throws Exception {
        Method m;
        for (int i = 0; i < list.size() - 1; i++) {
            m = list.get(i);
            t = m.invoke(t, new Object[]{});
        }
        return t;
    }

    /**
     * 获取需要的全部字段
     */
    private static void getAllExcelField(Field[] fields,
                                         Map<String, ExcelImportEntity> excelParams, Class<?> pojoClass,
                                         List<Method> getMethods) throws Exception {
        ExcelImportEntity excelEntity = null;
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            if (ExcelPublicUtil.isNotUserExcelUserThis(field)) {
                continue;
            }
            if (ExcelPublicUtil.isJavaClass(field)) {
                addEntityToMap(field, excelEntity, pojoClass, getMethods, excelParams);
            } else {
                List<Method> newMethods = new ArrayList<Method>();
                if (getMethods != null) {
                    newMethods.addAll(getMethods);
                }
                newMethods.add(ExcelPublicUtil.getMethod(field.getName(),
                        pojoClass));
                getAllExcelField(ExcelPublicUtil.getClassFields(field.getType()),
                        excelParams, field.getType(),
                        newMethods);
            }
        }
    }

    /**
     * 把这个注解解析放到类型对象中
     */
    private static void addEntityToMap(Field field,
                                       ExcelImportEntity excelEntity, Class<?> pojoClass,
                                       List<Method> getMethods, Map<String, ExcelImportEntity> temp) throws Exception {
        Excel excel = field.getAnnotation(Excel.class);
        excelEntity = new ExcelImportEntity();
        getExcelField(field, excelEntity, excel, pojoClass);
        if (getMethods != null) {
            List<Method> newMethods = new ArrayList<Method>();
            newMethods.addAll(getMethods);
            newMethods.add(excelEntity.getSetMethod());
            excelEntity.setSetMethods(newMethods);
        }
        for (String name : excelEntity.getName().split(",")) {
            temp.put(name, excelEntity);
        }

    }

    private static void getExcelField(Field field,
                                      ExcelImportEntity excelEntity, Excel excel, Class<?> pojoClass)
            throws Exception {
        excelEntity.setName(getExcelName(excel.exportName()));
        String fieldname = field.getName();
        excelEntity.setSetMethod(ExcelPublicUtil.getMethod(fieldname, pojoClass, field.getType()));
        if (StringUtils.isNotBlank(excel.importFormat())) {
            excelEntity.setImportFormat(excel.importFormat());
        }
    }

    /**
     * 判断在这个单元格显示的名称
     */
    private static String getExcelName(String exportName) {
        if (exportName.indexOf("_") < 0) {
            return exportName;
        }
        String[] arr = exportName.split(",");
        for (String str : arr) {
            return str.split("_")[0];
        }
        return null;
    }

    /**
     * 校验标头完整性
     * Excel 导入 field 字段类型 Integer,Long,Double,Date,String,Boolean,BigDecimal
     */
    public static String importExcelByTitleCheck(InputStream inputstream,
                                                 Class<?> pojoClass, ImportParams params) throws Exception {
        Workbook book = null;
        if (!(inputstream.markSupported())) {
            inputstream = new PushbackInputStream(inputstream, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(inputstream)) {
            book = new HSSFWorkbook(inputstream);
        } else if (POIXMLDocument.hasOOXMLHeader(inputstream)) {
            book = new XSSFWorkbook(OPCPackage.open(inputstream));
        }

        Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
        Field fileds[] = ExcelPublicUtil.getClassFields(pojoClass);
        getAllExcelField(fileds, excelParams, pojoClass, null);
        Set<String> nemeSet = excelParams.keySet();
        List<String> listExcelTitleName = new ArrayList<String>(nemeSet);

        Sheet sheet = book.getSheetAt(0);
        Row row = null;
        Iterator<Cell> cellTitle;
        List<String> sheetTitleNames = new ArrayList<String>();
        Iterator<Row> rows = sheet.rowIterator();
        for (int j = 0; j < params.getSecondTitleRows(); j++) {
            row = rows.next();
            cellTitle = row.cellIterator();
            while (cellTitle.hasNext()) {
                Cell cell = cellTitle.next();
                String value = cell.getStringCellValue();
                if (!StringUtils.isEmpty(value)) {
                    sheetTitleNames.add(value);
                }
            }
        }

        String result = null;
        List<String> resultList = new ArrayList<String>();
        if (null != listExcelTitleName && listExcelTitleName.size() > 0) {
            for (String name : listExcelTitleName) {
                if (!sheetTitleNames.contains(name)) {
                    resultList.add(name);
                }
            }
        }
        if (resultList.size() > 0) {
            for (int i = 0; i < resultList.size(); i++) {
                if (i == 0) {
                    result = resultList.get(i);
                } else {
                    result += "," + resultList.get(i);
                }
            }
        }
        return result;
    }

    /*
    	获取sheet名称k
	 */
    public static String getSheetName(InputStream inputstream) throws Exception {
        String sheetName = "";
        Collection<T> result = new ArrayList<T>();
        if (!(inputstream.markSupported())) {
            inputstream = new PushbackInputStream(inputstream, 8);
        }
        Workbook book = WorkbookFactory.create(inputstream);
        return book.getSheetName(0);
    }
}
