[简单]excel表格数据初始化为insert sql 语句

 excel 表格数据初始化为 insert sql 语句 ：

    @ApiOperation(value = "excel 数据初始化 sql ")
    @PostMapping("/excelInit")
    public void dataMigration(@RequestParam("file") MultipartFile file) throws Exception {
        //获取sheet页的数据:
        ExcelReader reader = ExcelUtil.getReader(file.getInputStream(),"t_bank");
        //类似封装：org.apache.poi.ss.usermodel.WorkbookFactory.create(IoUtil.toMarkSupportStream(in), password(null)).getSheet(sheetName);


        List<Map<String,Object>> bankList = reader.readAll();
        //表头字段：bank_shield	account_name	bank_name	open_bank_name	bank_account	currency	bank_register_date warning_interval	frequency	statues	remark
        //字段含义 ：银行顿号     账户名称	        银行名称	开户行名称/联行号	银行账号	币别	注册日期	调度时间	是否高频	预警状态	备注

        //最终导出的SQL List
        List<String> sQLList = new ArrayList<>();

        //遍历准入数据
        for (Map<String,Object> item: bankList){

            //获取日期类数据，处理成想要的字符串格式
            Object bank_register_date = item.get("bank_register_date");
            java.time.LocalDate localDate = null;
            if (U.isNotBlank(bank_register_date)) {
                LocalDateTime createDate = LocalDateTimeUtil.parse(bank_register_date.toString(), DateFormatType.YYYY_MM_DD_HH_MM_SS.getValue());
                 localDate = createDate.toLocalDate();
            }

            Map bankDataMap = new LinkedHashMap();
            //普通字段直接获取 put
            bankDataMap.put("bank_shield",item.get("bank_shield").toString());
            bankDataMap.put("account_name", item.get("account_name"));
            bankDataMap.put("bank_name", item.get("bank_name"));
            bankDataMap.put("open_bank_name", item.get("open_bank_name"));
            bankDataMap.put("bank_account", item.get("bank_account"));
            bankDataMap.put("currency", item.get("currency"));
            bankDataMap.put("bank_register_date", U.isBlank(localDate)?"": LocalDateTimeUtil.format(localDate,DateFormatType.YYYY_MM_DD.getValue()));
            bankDataMap.put("warning_interval", item.get("warning_interval"));
            bankDataMap.put("frequency", "否".equals(item.get("frequency"))?"LOW":"HIGH");
            bankDataMap.put("statues", "开启".equals(item.get("statues"))?"ENABLE":"DISABLE");
            bankDataMap.put("remark", item.get("remark"));

            bankDataMap.put("create_time", LocalDateTime.now());
            bankDataMap.put("create_user", "系统初始化");
            String bank_code = "-";
            if (U.isNotBlank(item.get("bank_name"))) {
                String bank_name = item.get("bank_name").toString();
                //枚举类处理：根据名称获取编码
                BankEnum anEnum = BankEnum.getCodeByDesc(bank_name);
                if (U.isNotBlank(anEnum)) {
                     bank_code = anEnum.getCode();
                }

            }
            bankDataMap.put("bank_code", bank_code);
            //最终组装 sQLList
            sQLList.add(getInsertSql("`dwh_fund`.t_bank_warning_config", bankDataMap));
        }

        for (String s:sQLList) {
            //输出insert sql
            System.out.println(s);
        }
    }



    public static String getInsertSql(String tableName, Map<String, Object> data) {
        String cols = null;
        String marks = null;

        for (Map.Entry<String, Object> entry : data.entrySet()) {
            String col = entry.getKey();
            Object val = entry.getValue();
            if (U.isNotBlank(val)) {
                cols = StringUtils.strcat(cols, ",", col);
                marks = StringUtils.strcat(marks, ",", sqlConstant(val));
            }
        }
        String sql = "INSERT INTO " + tableName + "(" + cols + ") VALUES (" + marks + ");";
        return sql;
    }

    static public String sqlConstant(Object value) {
        if (value == null) {
            return null;
        } else if (value instanceof java.util.Date) {
            return sqlDateConstant((java.util.Date) value);
        } else if (value instanceof LocalDate) {
            return sqlDateConstant((LocalDate) value);
        } else if (value instanceof LocalDateTime) {
            return sqlDateConstant((LocalDateTime) value);
        } else if (value instanceof String) {
            return sqlStringConstant((String) value);
        } else {
            return value.toString();
        }
    }

    static public String sqlStringConstant(String value)
    {
        //      if( sb!=null )
        return sqlStringConstantTo(value,null).toString();//return "'"+sb.toString() + value+"'";
//        return "'"+value+"'";
    }
    static private StringBuffer sqlStringConstantTo(String value,StringBuffer sb)
    {
        if( sb==null ) sb = new StringBuffer();
        sb.append('\'');
        for(;;) {
            int p = value.indexOf('\'');
            if( p<0 ) break;
//            if( sb==null )
//                sb = new StringBuffer(value.substring(0,p+1));
//            else
            sb.append(value.substring(0,p+1));
            sb.append('\'');
            value = value.substring(p+1);
        }
        sb.append(value);
        sb.append('\'');
        return sb;
    }

    static public String sqlDateConstant(LocalDateTime date) {
        int year = date.getYear(), month = date.getMonthValue(), day = date.getDayOfMonth();
        int h=date.getHour(),m=date.getMinute(),s=date.getSecond();
        return sqlDateConstant(year, month, day,h,m,s);
    }

    static public String sqlDateConstant(LocalDate date) {
        int year = date.getYear(), month = date.getMonthOfYear(), day = date.getDayOfMonth();
        return sqlDateConstant(year, month, day);
    }

    static public String sqlDateConstant(java.util.Date date) {
        if (date == null) return null;
        final int dateE[] = getDateElements(date);
        if ((dateE[3] != 0 || dateE[4] != 0 || dateE[5] != 0)) {
            return sqlDateConstant(dateE[0], dateE[1], dateE[2], dateE[3], dateE[4], dateE[5]);
        } else {
            return sqlDateConstant(dateE[0], dateE[1], dateE[2]);
        }
    }

    final static private String toStr2(int x) {
        return x < 10 ? "0" + x : "" + x;
    }

    static public String sqlDateConstant(int year, int month, int day, int hours, int min, int sec) {
        String d = "'";
        return d + year + '-' + toStr2(month) + '-' + toStr2(day) + ' ' + toStr2(hours) + ':' + toStr2(min) + ':' + toStr2(sec) + d;
    }
    static public String sqlDateConstant(int year, int month, int day) {
        String d = "'";
        return d + year + '-' + toStr2(month) + '-' + toStr2(day) + d;
    }

    public static int[]  getDateElements(java.util.Date date)
    {
        if( date==null )
            return null;
        final int hOffset = TimeZone.getDefault().getRawOffset()/(60*60*1000);
        Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"+(hOffset>0?"+"+hOffset:""+hOffset)+":00"));
        cal.setTime(date);
        int ymd[] = new int[7];
        ymd[0] = cal.get(Calendar.YEAR);
        ymd[1] = cal.get(Calendar.MONTH)+1;
        ymd[2] = cal.get(Calendar.DATE);
        ymd[3] = cal.get(Calendar.HOUR_OF_DAY);
        ymd[4] = cal.get(Calendar.MINUTE);
        ymd[5] = cal.get(Calendar.SECOND);
        ymd[6] = cal.get(Calendar.MILLISECOND);
        return ymd;

    }