大批量的刷生成sql脚本工具

1. 读取Excel 表格中的数据

        List<List<Object>> inList = new ArrayList<>();
        try{
            // 读取Excel文档
            File finalXlsxFile = new File(finalXlsxPath);
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页
            Sheet sheet = workBook.getSheetAt(0);
            int rowNumber = sheet.getLastRowNum();    // 第一行从0开始算
            System.out.println("原始数据总行数，除属性列：" + rowNumber);
            for (int i = 1; i <= rowNumber; i++) {
                List<Object> list = new ArrayList<>();
                Row row = sheet.getRow(i);
                int cellNumber = row.getLastCellNum();
                for(int j = 0; j < cellNumber; j++){
                    Cell cell = row.getCell(j);
                    System.out.println(getCellValue(cell));
                    if(j==3){
                        //Object cityObj =  getCellValue(cell);
                        //String cityStr = String.valueOf(((Double)cityObj).intValue());
                        String cityStr = (String) getCellValue(cell);
                        if(cityStr.length()==2){
                            cityStr = "0"+cityStr;
                        }
                        System.out.println(cityStr);
                        list.add(cityStr);
                    }else {
                        list.add(getCellValue(cell));
                    }

                }
                inList.add(list);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return inList;
    }

 /**
     * 判断Excel的版本,获取Workbook
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbok(File file) throws IOException{
        Workbook wb = null;
        FileInputStream in = new FileInputStream(file);
        if(file.getName().endsWith(EXCEL_XLS)){     //Excel2003
            wb = new HSSFWorkbook(in);
        }else if(file.getName().endsWith(EXCEL_XLSX)){    // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

public static Object getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        Object obj = null;
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case FORMULA:
                try {
                    obj = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    String valueOf = String.valueOf(cell.getNumericCellValue());
                    BigDecimal bd = new BigDecimal(Double.valueOf(valueOf));
                    bd = bd.setScale(2, RoundingMode.HALF_UP);
                    obj = bd;
                }
                break;
            case NUMERIC:
                //obj = cell.getNumericCellValue();
                obj = String.valueOf(cell.getNumericCellValue());
                if(obj.toString().endsWith(".0")){
                    int index = obj.toString().indexOf(".0");
                    obj = obj.toString().substring(0,index);
                }
                break;
            case STRING:
                String value = String.valueOf(cell.getStringCellValue());
                value = value.replace(" ", "");
                value = value.replace("\n", "");
                value = value.replace("\t", "");
                obj = value;
                break;
            default:
                break;
        }
        return obj;
    }

1. 生成sql文本

private static void writeTXT(List<List<Object>> asd, String s) {
        List<String> list1 = new ArrayList<>();
        List<String> list2 = new ArrayList<>();
        try {
            for (List<Object> list :asd) {
                //UPDATE tbl_name SET col_name1=value1, col_name2=value2, … WHERE conditions
                if (list.size()>5 ){
                    String txt = "UPDATE store_base SET B_STORE_CODE = '"+list.get(5)+"',B_STORE_NAME = '"+list.get(6)+"' WHERE store_code = '"+list.get(0)+"';";
                    list1.add(txt);
                }else{
                    list2.add((String) list.get(0));
                }
            }
            System.out.println(JSON.toJSONString(list1));
            System.out.println("有大B的个数："+list1.size()+",没有大B的个数"+list2.size());
            WriteStringToFile2(s,JSON.toJSONString(list1));
            System.out.println(JSON.toJSONString(list2));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

public static void WriteStringToFile2(String filePath,String str) {
        try {
            FileWriter fw = new FileWriter(filePath, true);

            BufferedWriter bw = new BufferedWriter(fw);

//            bw.append("在已有的基础上添加字符串");

            bw.write(str);// 往已有的文件上添加字符串

            bw.close();

            fw.close();
          
        } catch (Exception e) {
            e.printStackTrace();

        }
    }

1. 修改notepad++中生成的数据

1. 先去掉所有的双引号
2. 用图中替换功能替换掉