fj-dc/.svn/pristine/44/449f70103bc3ce8e9c872e8d717119a3d620cc71.svn-base

package cn.com.goldenwater.dcproj.utils.impexcel;

import cn.com.goldenwater.dcproj.socket.WebSocketServer;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static cn.com.goldenwater.dcproj.utils.export.FieldFormatDispatch.getFormatValue;
import static cn.com.goldenwater.dcproj.utils.impexcel.ImpUtil.XLS;
import static cn.com.goldenwater.dcproj.utils.impexcel.ImpUtil.checkIsDate;

/**
 * <p>
 * 导入工具类
 *
 * </p>
 *
 * @author liyz
 * @date 2019/4/10 19:43
 */
public class ImportExcel {

    private static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");
    private static final String EMPTY = "";

    public static void main(String[] args) {
        String filePath = "C:\\Users\\a\\Desktop\\小水库督查情况表1554796096158.xls";
        int startRow = 4;
        int endRow = 0;

        try {
            List<HashMap> bookList = (List<HashMap>) ImportExcel.importExcel(filePath, startRow, endRow, HashMap.class, "", "sk");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 正则表达式 用于匹配属性的第一个字母
     */
    private static final String REGEX = "[a-zA-Z]";

    /**
     * 功能: Excel数据导入到数据库
     * 参数: originUrl[Excel表的所在路径]
     * 参数: startRow[从第几行开始]
     * 参数: endRow[到第几行结束
     * (0表示所有行;
     * 正数表示到第几行结束;
     * 负数表示到倒数第几行结束)]
     * 参数: clazz[要返回的对象集合的类型]
     */
    public static List<?> importExcel(String originUrl, int startRow, int endRow, Class<?> clazz, String sid, String type) throws IOException {
        //是否打印提示信息
        boolean showInfo = true;
        return doImportExcel(originUrl, startRow, endRow, showInfo, clazz, sid, type);
    }


    /**
     * 功能：判断文件是否存在
     */
    public static File isExist(String originUrl) throws IOException {
        // 判断文件是否存在
        File file = new File(originUrl);
        if (!file.exists()) {
            throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
        }
        return file;
    }


    /**
     * 功能:真正实现导入
     */
    private static List<Object> doImportExcel(String originUrl, int startRow, int endRow, boolean showInfo, Class<?> clazz, String sid, String type) throws IOException {
        // 判断文件是否存在
        File file = isExist(originUrl);

        HSSFWorkbook wb = null;
        FileInputStream fis = null;
        List<Row> rowList = new ArrayList<Row>();
        try {
            fis = new FileInputStream(file);
            // 去读Excel
            wb = new HSSFWorkbook(fis);
            Sheet sheet = wb.getSheetAt(0);
            // 获取最后行号
            int lastRowNum = sheet.getLastRowNum();
            // 如果>0，表示有数据
            if (lastRowNum > 0) {
                out("\n开始读取并校验名为【" + sheet.getSheetName() + "】的内容：", showInfo, sid);
                WebSocketServer.sendInfo(SIMPLE_DATE_FORMAT.format(new Date()) + "-②开始读取并校验名为【" + sheet.getSheetName() + "】的内容", sid);
            }
            Row row = null;
            // 循环读取
            for (int i = startRow; i <= lastRowNum + endRow; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    rowList.add(row);
                    out("第" + (i + 1) + "行：", showInfo, false, sid);
                    // 获取每一单元格的值
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        String value = getCellValue(row.getCell(j));
//                        if (!value.equals("")) {
                        out(value + " | ", showInfo, false, sid);
//                        }
                    }
                    out("", showInfo, sid);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            wb.close();
        }
        return returnObjectList(rowList, type, sid);
    }


    /**
     * 功能:获取单元格的值
     */
    private static String getCellValue(Cell cell) {
        Object result = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case STRING:
                    result = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    result = cell.getNumericCellValue();
                    break;
                case BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case FORMULA:
                    result = cell.getCellFormula();
                    break;
                case ERROR:
                    result = cell.getErrorCellValue();
                    break;
                case BLANK:
                    break;
                default:
                    break;
            }
        }
        return result.toString();
    }

    /**
     * 功能:返回指定的对象集合
     */
    private static List<Object> returnObjectList(List<Row> rowList, String type, String sid) {
        List<Object> objectList = null;
        List<String> keys = new ArrayList<>();
        Row keyRow = rowList.get(0);

        for (int i = 0; i < keyRow.getPhysicalNumberOfCells(); i++) {
            keys.add(keyRow.getCell(i).toString());
        }
        try {
            objectList = new ArrayList<>();
            for (int i = 1; i < rowList.size(); i++) {
                Row row = rowList.get(i);
                HashMap<String, String> map = new LinkedHashMap<>();
                int check = 0;
                for (int j = 0; j < keys.size(); j++) {
                    // 此单元格的值
                    String cellValue = getCellValue(row.getCell(j));
                    // 格式化后的值
                    String formatValue = getFormatValue(type, keys.get(j), cellValue, true).toString();
                    map.put(keys.get(j), formatValue);
                    // 先不格式化
//                    map.put(keys.get(j), getCellValue(row.getCell(j)));
                    // 错误的话输出一下
                    if (formatValue.contains("$E$")) {
                        check++;
                        WebSocketServer.sendInfo(SIMPLE_DATE_FORMAT.format(new Date()) + "-第" + (i + 5) + "行[" + cellValue + "]填写有误,请查看弹出信息,并重新上传模板！", sid);
                    }
                }
                if (check == 0 && row.getCell(2) != null && !"".equals(row.getCell(2).toString())) {
                    WebSocketServer.sendInfo(SIMPLE_DATE_FORMAT.format(new Date()) + "-第" + (i + 5) + "行验证完毕，数据无误。", sid);
                }
                objectList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return objectList;
    }

    /**
     * 功能:返回指定的对象集合,（有单表实体类时可用）
     */
    private static List<Object> returnObjectList(List<Row> rowList, Class<?> clazz) {
        List<Object> objectList = null;
        Object obj = null;
        String attribute = null;
        String value = null;
        int j = 0;
        try {
            objectList = new ArrayList<Object>();
            Field[] declaredFields = clazz.getDeclaredFields();
            for (Row row : rowList) {
                j = 0;
                obj = clazz.newInstance();
                for (Field field : declaredFields) {
                    attribute = field.getName().toString();
                    value = getCellValue(row.getCell(j));
                    setAttrributeValue(obj, attribute, value);
                    j++;
                }
                objectList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return objectList;
    }

    /**
     * 功能:给指定对象的指定属性赋值
     */
    public static void setAttrributeValue(Object obj, String attribute, String value) {
        //得到该属性的set方法名
        String methodName = convertToMethodName(attribute, obj.getClass(), true);
        Method[] methods = obj.getClass().getMethods();
        for (Method method : methods) {
            /*
             因为这里只是调用bean中属性的set方法，属性名称不能重复
             所以set方法也不会重复，所以就直接用方法名称去锁定一个方法
             (注：在java中，锁定一个方法的条件是方法名及参数）
             */
            if (method.getName().equals(methodName)) {
                Class<?>[] parameterC = method.getParameterTypes();
                try {
                    /*
                     如果是(整型,浮点型,布尔型,字节型,时间类型,Long类型),
                     按照各自的规则把value值转换成各自的类型
                     否则一律按类型强制转换(比如:String类型)
                     */
                    if (parameterC[0] == int.class || parameterC[0] == Integer.class) {
                        value = value.substring(0, value.lastIndexOf("."));
                        method.invoke(obj, Integer.valueOf(value));
                        break;
                    } else if (parameterC[0] == float.class || parameterC[0] == Float.class) {
                        method.invoke(obj, Float.valueOf(value));
                        break;
                    } else if (parameterC[0] == double.class || parameterC[0] == Double.class) {
                        if (value != null && !"".equals(value)) {
                            method.invoke(obj, Double.valueOf(value));
                        }
                        break;
                    } else if (parameterC[0] == long.class || parameterC[0] == Long.class) {
                        if (value != null && !"".equals(value)) {
                            method.invoke(obj, Long.valueOf(value));
                        }
                        break;
                    } else if (parameterC[0] == byte.class || parameterC[0] == Byte.class) {
                        method.invoke(obj, Byte.valueOf(value));
                        break;
                    } else if (parameterC[0] == boolean.class || parameterC[0] == Boolean.class) {
                        method.invoke(obj, Boolean.valueOf(value));
                        break;
                    } else if (parameterC[0] == Date.class) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = null;
                        try {
                            if (StringUtils.isNotBlank(value)) {
                                // 可能是年月日格式的，处理一下
                                value = value.replace("年","-").replace("月","-").replace("日","");
                                if(checkIsDate(value)){
                                    date = sdf.parse(value.replace("/","-"));
                                }
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        method.invoke(obj, date);
                        break;
                    } else {
                        method.invoke(obj, parameterC[0].cast(value));
                        break;
                    }
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                } catch (SecurityException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 功能:根据属性生成对应的set/get方法
     */
    private static String convertToMethodName(String attribute, Class<?> objClass, boolean isSet) {
        // 通过正则表达式来匹配第一个字符
        Pattern p = Pattern.compile(REGEX);
        Matcher m = p.matcher(attribute);
        StringBuilder sb = new StringBuilder();
        // 如果是set方法名称
        if (isSet) {
            sb.append("set");
        } else {
            // get方法名称
            try {
                Field attributeField = objClass.getDeclaredField(attribute);
                // 如果类型为boolean
                if (attributeField.getType() == boolean.class || attributeField.getType() == Boolean.class) {
                    sb.append("is");
                } else {
                    sb.append("get");
                }
            } catch (SecurityException e) {
                e.printStackTrace();
            } catch (NoSuchFieldException e) {
                e.printStackTrace();
            }
        }
        // 针对以下划线开头的属性
        if (attribute.charAt(0) != '_' && m.find()) {
            sb.append(m.replaceFirst(m.group().toUpperCase()));
        } else {
            sb.append(attribute);
        }
        return sb.toString();
    }

    /**
     * 功能:输出提示信息(普通信息打印)
     */
    private static void out(String info, boolean showInfo, String sid) {
        if (showInfo) {
//            System.out.print(info + (showInfo ? "\n" : ""));
        }
    }

    /**
     * 功能:输出提示信息(同一行的不同单元格信息打印)
     */
    private static void out(String info, boolean showInfo, boolean nextLine, String sid) {
        if (showInfo) {
            if (nextLine) {
//                System.out.print(info + (showInfo ? "\n" : ""));
                WebSocketServer.sendInfo(info + (showInfo ? "\n" : ""), sid);
            } else {
//                System.out.print(info);
            }
        }
    }


    // 新方法


    /**
     * 功能:获取Excel数据行集合(可单独使用)
     *
     * @param originUrl Excel文件路径
     * @param startRow  起始读取行
     * @param endRow    结束读取行
     */
    public static List<Row> getRowList(String originUrl, int startRow, int endRow) throws IOException {
        // 判断文件是否存在
        File file = isExist(originUrl);
        // 开始读取Excel的内容
        Workbook wb = null;
        FileInputStream fis = null;
        List<Row> rowList = new ArrayList<Row>();
        try {
            fis = new FileInputStream(file);
            if (originUrl.substring(originUrl.lastIndexOf(".") + 1).equals(XLS)) {
                wb = new HSSFWorkbook(fis);
            } else {
                wb = new XSSFWorkbook(fis);
            }

            Sheet sheet = wb.getSheetAt(0);
//            int lastRowNum = sheet.getLastRowNum();
            int lastRowNum = sheet.getPhysicalNumberOfRows();
            Row row;
            for (int i = startRow; i <= lastRowNum + endRow; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    rowList.add(row);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (wb != null) {
                wb.close();
            }
            if (fis != null) {
                fis.close();
            }
        }
        return rowList;
    }

    /**
     * 功能:把List<Row>转为List<Map>并格式化字段(可单独使用)
     */
    public static List<LinkedHashMap<String, Object>> getDataList(List<Row> rowList, Class<?> clazz) {
        List<LinkedHashMap<String, Object>> objectList = null;
        List<String> nameKeys = new ArrayList<>();
        Row keyRow = rowList.get(0);

        for (int i = 0; i < keyRow.getPhysicalNumberOfCells(); i++) {
            nameKeys.add(keyRow.getCell(i).toString().trim());
        }
        try {
            objectList = new ArrayList<>();
            for (int i = 1; i < rowList.size(); i++) {
                Row row = rowList.get(i);
                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                int check = 0;
                for (int j = 0; j < nameKeys.size(); j++) {
                    // 此单元格的值
                    String cellValue = getCellValue(row.getCell(j));
                    // 格式化后的值
                    String name = nameKeys.get(j);
                    map.put(name, formatValue(name, cellValue, clazz));
                }
                objectList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return objectList;
    }

    /**
     * 功能:格式化字段(可单独使用,具体格式化方式由claszz决定，通常也检查字段是否合法，并注上错误标识)
     */
    public static Object formatValue(String name, Object value, Class<?> formatClazz) {
        Method[] methods = formatClazz.getMethods();
        for (Method method : methods) {
            if (("format" + name).toLowerCase().equals(method.getName().toLowerCase())) {
                try {
                    Class<?>[] parameterC = method.getParameterTypes();
                    if (parameterC[0] == Date.class) {
                        if (value instanceof Date) {
                            return method.invoke(formatClazz, (Date) value);
                        } else {
                            return value;
                        }
                    } else {
                        // 找到对应的方法，并把原始值传进去，返回格式化后的值
                        return method.invoke(formatClazz, value);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return value;
    }

    /**
     * 功能:直接调用此方法获取解析后的数据
     */
    public static List<LinkedHashMap<String, Object>> getParseData(String originUrl, int startRow, int endRow
            , Class<?> clazz) throws IOException {
        return getDataList(getRowList(originUrl, startRow, endRow), clazz);
    }

    /**
     * 功能：把解析数据转为展示数据，例如：字典项 1 转为 是
     */
    public static List<LinkedHashMap<String, Object>> getShowData(List<LinkedHashMap<String, Object>> parseData, Class<?> clazz) {
        List<LinkedHashMap<String, Object>> listShow = new ArrayList<>();
        for (LinkedHashMap lhm : parseData) {
            LinkedHashMap<String, Object> map = new LinkedHashMap<>();
            for (Object o : lhm.keySet()) {
                String key = o.toString();
                String value = lhm.get(key).toString();
                Object formatValue = ImportExcel.formatValue(key, value, clazz);
                map.put(key, formatValue);
            }
            listShow.add(map);
        }
        return listShow;
    }

    /**
     * 功能：把Map转成Bean (主要用于把从Excel解析出来的Map对象还原成实体类对象，方便入库)
     */
    public static Object convertMapToBean(Class<?> clazz, Map lhp) throws IllegalAccessException, InstantiationException {
        Field[] fields = clazz.getDeclaredFields();
        Object obj = clazz.newInstance();
        for (Field field : fields) {
            setAttrributeValue(obj, field.getName(), MapUtils.getString(lhp, field.getName()));
        }
        return obj;
    }

}