package com.manage.service.ImportExcel;

import com.manage.encrypt.UUIDUtils;
import com.manage.vo.ImportExcelEntity;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.context.ContextLoader;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.multipart.MultipartFile;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @ProjectName:
 * @Description:
 * @Param 传输参数
 * @Return
 * @Author: 曾文和
 * @CreateDate: 2019/10/11 9:24
 * @UpdateUser: 曾文和
 * @UpdateDate: 2019/10/11 9:24
 * @UpdateRemark: 更新说明
 * @Version: 1.0
 */
public class ImportExcelUtil {
    //公共类
    private static String IMPORTEXCELJUDGEMETHOD = "importExcelJudgeMethod";
    //批量加入mapper
    private static String MAPPERNAME = null;
    //实体类对象
    private static Object object = null;
    //实体类
    private static Class ObjectClass = null;
    //公共批量插入方法
    private static final String SIMPLEINSERT = "SimpleInsert";

    public static void  newInstance(String mapperName,Object o,Class oClass){
        MAPPERNAME = mapperName;
        object = o;
        ObjectClass = oClass;
    }
    /**
     * 导入功能
     *
     * @param multipartFile
     * @return
     */
    public static ImportExcelEntity fileImport(MultipartFile multipartFile,String[] fieldNames, String[] judgeMethods) {
        Workbook workbook = null;
        int successCount = 0;
        int wrongCount = 0;
        String workBookKey = "";
        //正确行数下角标
        List<Integer> wrongRowIndex = new ArrayList<>();
        try {
            /*把文件流copy读取到文件中*/
            workbook = WorkbookFactory.create(multipartFile.getInputStream());
            /*遍历sheet页*/
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) {
                    continue;
                }
                //获取表头行
                Row head = sheet.getRow(0);
                /*遍历行，这里j的初始值取1是因为我的表格里第一行是表头*/
                List<Object> list = new ArrayList<>();
                for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
                    Row row = sheet.getRow(j);
                    boolean flag = isRowEmpty(row);
                    if(!flag){
                        if(row.getPhysicalNumberOfCells() !=0){
                            StringBuilder wrongStr = new StringBuilder();
                            for (int k = 0; k < fieldNames.length; k++) {
                                //对象赋值后返回错误对象
                                wrongStr.append(getValueByField(object, fieldNames[k], getCellValue(row.getCell(k)), getCellValue(head.getCell(k)),judgeMethods[k]));
                            }
                            //有错误信息在行尾创建cell，并标红提示文字
                            if(StringUtils.isNotBlank(wrongStr)){
                                //设置错误信息字体为红色加粗
                                Font font = workbook.createFont();
                                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                                font.setColor(HSSFColor.RED.index);
                                CellStyle cellStyle = workbook.createCellStyle();
                                cellStyle.setFont(font);
                                Cell cell = row.createCell( fieldNames.length);
                                cell.setCellStyle(cellStyle);
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                wrongStr = new StringBuilder(wrongStr.substring(0,wrongStr.length()-1));
                                cell.setCellValue(wrongStr.toString());
                                wrongCount++;
                            }else{
                                successCount++;
                                wrongRowIndex.add(j);
                                Constructor constructor = ObjectClass.getDeclaredConstructor();
                                Object obj = (Object) constructor.newInstance();
                                //复制
                                copy(object,obj);
                                list.add(obj);
                            }
                        }
                    }
                }
                //批量新增成功记录
                SimpleInsert(list);
                //删除正确行
                if(!wrongRowIndex.isEmpty()){
                    //定义删除次数，对应后方下角标都要减去删除次数
                    int deleteCount = 0;
                    //递归删除
                    removeSheet(sheet,deleteCount,wrongRowIndex);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
        //错误excel加入缓存
        if(wrongCount != 0){
            workBookKey = UUIDUtils.getUUID();
            workBookMap.put(workBookKey,workbook);
        }
        ImportExcelEntity excelEntity = new ImportExcelEntity();
        excelEntity.setSuccessCount(successCount);
        excelEntity.setWrongCount(wrongCount);
        excelEntity.setWorkBookKey(workBookKey);
        return excelEntity;
    }

    //批量新增成功数据方法
    private static int SimpleInsert(List<Object> list)throws Exception{
        if(null != list && !list.isEmpty()){
            //注入判断方法集中类
            //插入数据库
            //反射执行插入方法
            //获取当前上下文环境，spring容器
            WebApplicationContext wac = ContextLoader.getCurrentWebApplicationContext();
            //获取类实例
            Object bean = wac.getBean(IMPORTEXCELJUDGEMETHOD);
            Class<?>  cls = bean.getClass();
            //获取执行方法
            Method method = cls.getDeclaredMethod(SIMPLEINSERT,List.class,String.class);
            return (int)method.invoke(bean,list,MAPPERNAME);
            //执行方法
        }else{
            return 0;
        }
    }

    //判断空行
    private static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }
    //递归删除已添加进数据库的行
    private static void removeSheet(Sheet sheet,int deleteCount,List<Integer> wrongRowIndex){
        int index = wrongRowIndex.get(0)-deleteCount;
        //删除第一个
        wrongRowIndex.remove(0);
        //删除此行
        removeRow(sheet,index);
        deleteCount++;
        if(!wrongRowIndex.isEmpty()){
            removeSheet(sheet,deleteCount,wrongRowIndex);
        }
    }

    //递归删除行
    /**
     * 通过反射获取属性值
     * object:赋值对象，fieldName:属性名，value:excel表格值,head:表格头名称(中文名)，judgeMethod:判断方法
     * */
    private static StringBuilder getValueByField(Object object,String fieldName,String value,String head,String judgeMethod){
        StringBuilder wrongStr = new StringBuilder();
        try {
            Class<?> clazz = object.getClass();
            for (; clazz != Object.class; clazz = clazz.getSuperclass()) {
                Field[] field = clazz.getDeclaredFields();
                for (Field f : field) {
                    if(fieldName.equals(f.getName())){
                        f.setAccessible(true);
                        //是否有判断方法，有则判断，返回错误提示。没有则设置
                        if(StringUtils.isNotBlank(judgeMethod)){
                            //注入判断方法集中类
                            WebApplicationContext context = ContextLoader.getCurrentWebApplicationContext();
                            ImportExcelJudgeMethod importExcelJudgeMethod = (ImportExcelJudgeMethod) context.getBean(IMPORTEXCELJUDGEMETHOD);
                            //获取匹配方法
                            Method method = importExcelJudgeMethod.getClass().getMethod(judgeMethod,String.class,String.class,Field.class,Object.class,String.class);
                            //执行方法
                            String returnWrongStr = (String)method.invoke(importExcelJudgeMethod, fieldName, value, f, object, head);
                            if(StringUtils.isNotBlank(returnWrongStr)){
                                wrongStr.append(returnWrongStr);
                            }
                        }else{
                            if(StringUtils.isNotBlank(value)){
                                Class<?> filedType = f.getType();
                                String typeName = filedType.getName();
                                if("java.lang.Integer".equals(typeName)){
                                    f.set(object,Integer.valueOf(value));
                                }else{
                                    f.set(object,value);
                                }
                            }
                        }
                        break;
                    }
                }
            }
        }catch (Exception e){
            e.printStackTrace();
            wrongStr.append("\"").append(head).append("\"格式不正确,");
        }
        return wrongStr;
    }

    /**
     * 删除行
     * */
    private static void removeRow(Sheet sheet, int rowIndex) {
        int lastRowNum=sheet.getLastRowNum();
        if(rowIndex>=0&&rowIndex<lastRowNum) {
            sheet.shiftRows(rowIndex + 1, lastRowNum, -1);//将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
        }
        if(rowIndex==lastRowNum){
            Row removingRow=sheet.getRow(rowIndex);
            if(removingRow!=null) {
                sheet.removeRow(removingRow);
            }
        }
    }

    /**
     * 转换格式
     * */
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    // 验证short值
                    if (cell.getCellStyle().getDataFormat() == 14) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd");
                    } else if (cell.getCellStyle().getDataFormat() == 21) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    } else if (cell.getCellStyle().getDataFormat() == 22) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    } else {
                        throw new RuntimeException("日期格式错误!!!");
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = null;
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    private static final Map<String, Workbook> workBookMap = new HashMap<>();
    //通过key获取工作簿
    public static Workbook getWorkBookMapByKey(String workBookKey){
        return workBookMap.get(workBookKey);
    }

    public static void copy(Object source, Object dest) throws Exception {
        Class destClz = dest.getClass();

        // 获取目标的所有成员
        Field[] destFields = destClz.getDeclaredFields();
        Object value;
        for (Field field : destFields) { // 遍历所有的成员，并赋值
            // 获取value值
            value = getVal(field.getName(), source);
            field.setAccessible(true);
            field.set(dest, value);
        }
    }
    private static Object getVal(String name, Object obj) throws Exception {
        try {
            // 优先获取obj中同名的成员变量
            Field field = obj.getClass().getDeclaredField(name);
            field.setAccessible(true);
            return field.get(obj);
        } catch (NoSuchFieldException e) {
            // 表示没有同名的变量
        }

        // 获取对应的 getXxx() 或者 isXxx() 方法
        name = name.substring(0, 1).toUpperCase() + name.substring(1);
        String methodName = "get" + name;
        String methodName2 = "is" + name;
        Method[] methods = obj.getClass().getMethods();
        for (Method method : methods) {
            // 只获取无参的方法
            if (method.getParameterCount() > 0) {
                continue;
            }

            if (method.getName().equals(methodName)
                    || method.getName().equals(methodName2)) {
                return method.invoke(obj);
            }
        }
        return null;
    }
}
