Excel数据转数据库建表脚本

这个项目是将特定结构的excel表结构数据转换成数据库建表语句

源码

 /**
     * 输出sql
     * @param start 起始行
     * @param end  结束行
     * @param sheetAt sheet页
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static  void  toSql(Integer start, Integer end, Integer sheetAt) throws IOException, InvalidFormatException {
        Workbook workbook = WorkbookFactory.create(new File("数据库表结构V1.0.xlsx"));
        Sheet sheet = workbook.getSheetAt(sheetAt);
        int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
        if (rowCount < end) {
            throw new RuntimeException("总行数小于你的输入");
        }
        // 遍历每一行
        String var = ""; //这是建表语句
        String comment = ""; //这是注解
        for (int r = start; r < end; r++) {
            Row row = sheet.getRow(r);

            if (r == start) {
                var = "--" + row.getCell(2) + "\n Create table " + row.getCell(0) + "." + row.getCell(1) + "(\n";
                comment = "Comment on Table " + row.getCell(0) + "." + row.getCell(1) + " is '" + row.getCell(2) + "';\n";
            }
            Cell cell = row.getCell(0);
            if (cell != null) {
                Cell cell1 = row.getCell(7);
                if (cell1.getStringCellValue().equals("N")) {
                    Cell cell2 = row.getCell(4);
                    if (cell2.getStringCellValue().equals("VARCHAR")) {
                        DecimalFormat df = new DecimalFormat("0");
                        var += row.getCell(3).getStringCellValue()
                                + " " + cell2.getStringCellValue()
                                + "(" + df.format(row.getCell(5).getNumericCellValue()) + ") NOT NULL    ,\n ";
                    } else {
                        var += row.getCell(3).getStringCellValue()
                                + " " + cell2.getStringCellValue() + "  NOT NULL    ,\n ";
                    }

                } else {
                    Cell cell3 = row.getCell(4);
                    if (cell3.getStringCellValue().equals("VARCHAR")) {
                        DecimalFormat df = new DecimalFormat("0");
                        var += row.getCell(3).getStringCellValue()
                                + " " + cell3.getStringCellValue()
                                + "(" + (row.getCell(5).toString().endsWith(".0") 
                                ? row.getCell(5).toString().substring(0, row.getCell(5).toString().length() - 2) 
                                : row.getCell(5).toString())  + ")    ,\n";
                    } else if (cell3.getStringCellValue().equals("DECIMAL")) {
                        DecimalFormat df = new DecimalFormat("0");
                        var += row.getCell(3).getStringCellValue()
                                + " " + cell3.getStringCellValue()
                                + "(" + df.format(row.getCell(5).getNumericCellValue()) + ","
                                +  df.format(row.getCell(6).getNumericCellValue()) + ")    ,\n";
                    } else {
                        var += row.getCell(3).getStringCellValue()
                                + " " + cell3.getStringCellValue() + "    ,\n";
                    }

                }
                comment +=  "Comment on Column "
                        + row.getCell(0) + "."
                        + row.getCell(1) + "."
                        + row.getCell(3) + " is '"
                        + row.getCell(8) + "';\n";
            }
            if (r == end - 1) {
                var = var.substring(0, var.length() - 2);
                var += "\n )in TS_DWMM   ;";
                comment += "alter table " + row.getCell(0) + "."
                        + row.getCell(1) + " ADD PRIMARY KEY (ID);";
            }
        }
        System.out.println(var);
        System.out.println(comment);
    }

Excel格式

示例数据

结果

--角色表
 Create table YTFINST.SYS_ROLE(
ID VARCHAR(64) NOT NULL    ,
 ROLECODE VARCHAR(20) NOT NULL    ,
 ROLENAME VARCHAR(100)    ,
ROLEDESC VARCHAR(200)    ,
STATE VARCHAR(2)    ,
CREATEUSERNAME VARCHAR(50)    ,
CREATETIME TIMESTAMP    ,
UPDATEUSERNAME VARCHAR(50)    ,
UPDATETIME TIMESTAMP    
 )in TS_DWMM   ;
Comment on Table YTFINST.SYS_ROLE is '角色表';
Comment on Column YTFINST.SYS_ROLE.ID is '主键';
Comment on Column YTFINST.SYS_ROLE.ROLECODE is '角色编码';
Comment on Column YTFINST.SYS_ROLE.ROLENAME is '角色名称';
Comment on Column YTFINST.SYS_ROLE.ROLEDESC is '角色描述';
Comment on Column YTFINST.SYS_ROLE.STATE is '角色状态';
Comment on Column YTFINST.SYS_ROLE.CREATEUSERNAME is '创建人';
Comment on Column YTFINST.SYS_ROLE.CREATETIME is '创建时间';
Comment on Column YTFINST.SYS_ROLE.UPDATEUSERNAME is '修改人';
Comment on Column YTFINST.SYS_ROLE.UPDATETIME is '修改时间';
alter table YTFINST.SYS_ROLE ADD PRIMARY KEY (ID);