package com.sy.coinage.core.util;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.NumberToTextConverter;

import com.sy.coinage.core.exception.RetException;

/**
 * excel导入通用工具类
 *
 * @author admin
 */
public class ExcelImportUtil {

    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");

    private static final Map<String, Object> DATA_CACHE = new ConcurrentHashMap<String, Object>();

    /**
     * -解析excel模板数据转换为list
     *
     * @param <T>
     * @param clazz
     * @param wb
     * @param codes 需要导入参数的名称
     * @param names 需要导入表头的名称
     * @param sheetIndex 页签坐标
     * @param headIndex 表头坐标
     * @param stopParam 此列为空时结束读取
     * @return
     */
    public static <T> List<T> convertToList(Class<T> clazz, Workbook workbook, String[] codes, String[] names,
                    int sheetIndex, int headIndex, String stopParam) {
        List<T> list = new ArrayList<>();
        try {
            // 获取页签
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            // 获取所有行
            int rows = sheet.getPhysicalNumberOfRows() - headIndex;

            // 获取第一行验证表头名称
            Row row1 = sheet.getRow(headIndex);
            for (int index = 0; index < codes.length; index++) {
                Cell cell = row1.getCell(index + 1); // 从第二列单元格开始读入
                if (cell == null) {
                    cell = row1.createCell(index);
                }
                String colName = getCellValue(cell);
                if (!colName.equals(names[index])) {
                    throw new RetException("错误的表头名称，" + colName + "/" + names[index]);
                }
            }

            // 表头单元格个数
            for (int i = 0; i < codes.length; i++) {
                // TODO // 验证头
                String code = codes[i];
                String methodName = buildMethodName(code);
                DATA_CACHE.put(clazz.getName() + code,
                                clazz.getMethod(methodName, new Class[] {clazz.getDeclaredField(code).getType()}));
            }

            for (int i = headIndex + 1; i <= rows + headIndex; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    break;
                }
                T o = clazz.getConstructor(new Class[] {}).newInstance(new Object[] {});
                for (int index = 0; index < codes.length; index++) {
                    Cell cell = row.getCell(index + 1); // 从第二列单元格开始读入
                    if (cell == null) {
                        cell = row.createCell(index);
                    }
                    String name = codes[index];
                    String val = getCellValue(cell);
                    // 如果指定的参数为null 结束此行的读取
                    if (StringUtils.isNotBlank(stopParam) && name.equals(stopParam) && StringUtils.isBlank(val)) {
                        o = null;
                        break;
                    }
                    Method method = (Method) DATA_CACHE.get(clazz.getName() + name);
                    setAttrValue(method, o, val, clazz.getDeclaredField(name).getType(), names[index]);
                }
                if (o != null) {
                    list.add(o);
                }
            }
        } catch (RetException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RetException("请检查表格文件是否正确");
        }
        return list;
    }

    /**
     * -将excel数据转换为list
     *
     * @param clazz
     * @param inputStream
     * @param sheetIndex
     * @param <T>
     * @return
     */
    public static <T> List<T> convertToList(Class<T> clazz, InputStream inputStream, int sheetIndex) {
        List<T> list = new ArrayList<>();
        try {
            // 创建工作簿
            Workbook workbook = WorkbookFactory.create(inputStream);
            // 获取页签
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            // 获取所有行
            int rows = sheet.getPhysicalNumberOfRows();
            // 表头单元格个数
            int cells = sheet.getRow(0).getPhysicalNumberOfCells();
            Field[] fields = clazz.getDeclaredFields();
            for (int i = 0; i < cells; i++) {
                Field field = fields[i];
                String methodName = buildMethodName(field.getName());
                DATA_CACHE.put(field.getName(), methodName);
                DATA_CACHE.put(clazz.getName() + field.getName(),
                                clazz.getMethod(methodName, new Class[] {field.getType()}));
            }
            for (int i = 1; i < rows; i++) {
                Row row = sheet.getRow(i);
                T o = clazz.getConstructor(new Class[] {}).newInstance(new Object[] {});
                for (int index = 0; index < cells; index++) {
                    Cell cell = row.getCell(index + 1); // 从第二列单元格开始读入
                    if (cell == null) {
                        cell = row.createCell(index);
                    }
                    Field field = fields[index];
                    Method method = (Method) DATA_CACHE.get(clazz.getName() + field.getName());
                    setAttrValue(method, o, getCellValue(cell), field.getType(), field.getName());
                }
                list.add(o);
            }
        } catch (RetException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RetException("请检查表格文件是否正确");
        }
        return list;
    }

    /**
     * -构建set方法名称
     *
     * @param attr
     * @return
     */
    private static String buildMethodName(String attr) {
        Pattern pattern = Pattern.compile("[a-zA-Z]");
        Matcher matcher = pattern.matcher(attr);
        StringBuilder sb = new StringBuilder("set");
        if (matcher.find() && attr.charAt(0) != '_') {
            sb.append(matcher.replaceFirst(matcher.group().toUpperCase()));
        } else {
            sb.append(attr);
        }
        return sb.toString();
    }

    /**
     * -设置属性值
     *
     * @param method 方法
     * @param o 实体
     * @param val 值
     * @param type
     * @param name 列名
     */
    private static void setAttrValue(Method method, Object o, String val, Class<?> type, String name) {
        try {
            if (StringUtils.isNotBlank(val)) {
                // 参数类型转换：把String放到第一个if里是因为我们导入的数据大都是字符串类型，这样可以减少不必要的判断次数，当数据量很大时候可以体现出来
                if (type == java.lang.String.class) {
                    method.invoke(o, type.cast(val));
                } else if (type == java.util.Date.class) {
                    Date date = null;
                    try {
                        date = DATE_FORMAT.parse(val);
                    } catch (Exception e) {
                        throw new RetException("日期转换错误");
                    }
                    method.invoke(o, date);
                } else if (type == int.class || type == java.lang.Integer.class || type == long.class
                                || type == java.lang.Long.class) {
                    val = val.substring(0, val.lastIndexOf(".") == -1 ? val.length() : val.lastIndexOf("."));
                    method.invoke(o, Integer.valueOf(val));
                } else if (type == float.class || type == java.lang.Float.class) {
                    method.invoke(o, Float.valueOf(val));
                } else if (type == double.class || type == java.lang.Double.class) {
                    method.invoke(o, Double.valueOf(val));
                } else if (type == byte.class || type == java.lang.Byte.class) {
                    method.invoke(o, Byte.valueOf(val));
                } else if (type == boolean.class || type == java.lang.Boolean.class) {
                    method.invoke(o, Boolean.valueOf(val));
                } else if (type == java.math.BigDecimal.class) {
                    method.invoke(o, new BigDecimal(val));
                } else {
                    method.invoke(o, type.cast(val));
                }
            }
        } catch (Exception e) {
            throw new RetException("参数转换错 " + name + "=" + val);
        }
    }

    /**
     * -获取单元格数据
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    return DATE_FORMAT.format(date).trim();
                } else {
                    return NumberToTextConverter.toText(cell.getNumericCellValue()).trim();
                }
            case BOOLEAN:
                Object retBool = cell.getBooleanCellValue();
                return retBool.toString().trim();
            case FORMULA:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    return DATE_FORMAT.format(date).trim();
                }
                return cell.getCellFormula().trim();
            case ERROR:
                Object retError = cell.getErrorCellValue();
                return retError.toString().trim();
            case BLANK:
                return null;
            default:
                return cell.toString().trim();
        }
    }

}
