java-基于excle生成建表语句

1、jar：https://repo1.maven.org/maven2/   网上查询jxl-2.6.jar属于哪个包下的jar 去链接中下载

2、excle格式

 

 

3、执行代码

import java.io.File;
import java.io.PrintWriter;
import java.util.Date;

import jxl.Sheet;
import jxl.Workbook;


public class Main {

    public static void main(String[] args) throws Exception {
        File file = new File("D:\\aaa.xls");
        Workbook wb = Workbook.getWorkbook(file);

        Sheet[] sheets = wb.getSheets();
        System.out.println("当前文件夹的sheet数量" + sheets.length);
        Date dateStart = new Date();
        PrintWriter writer = new PrintWriter("D:\\result.sql") ;
        for (int i = 1; i < sheets.length; i++) {
            Sheet sheet = wb.getSheet(i);
            String createstr = createstatement(sheet);
            writer.write(createstr);

        }
        writer.close();
        Date dateEnd = new Date();
        System.out.println("生成完毕，一共用时："+(dateEnd.getTime()-dateStart.getTime())/1000+"秒");
    }

    // 生成建表语句的方法
    public static String createstatement(Sheet sheet) {
        //                CREATE TABLE `NewTable` (
        //                        `id`  int NULL DEFAULT NULL COMMENT '中文id' ,
        //                        `name`  varchar(10) NULL DEFAULT 'wjw' COMMENT '名字' ,
        //                        `age`  decimal(5,2) NOT NULL DEFAULT 5.12 COMMENT '年龄'
        //                )
        //                COMMENT='测试';
        StringBuffer bodysb = new StringBuffer(); // 建表语句的表头和结尾注释不分
        StringBuffer fldsb = new StringBuffer(); // 字段部分
        StringBuffer commentsb = new StringBuffer(); // 表中文名
        StringBuffer partitionsb = new StringBuffer(); // 分区字段部分
        bodysb.append("-- 建表语句"+sheet.getCell(2, 0).getContents()+"： \r\n create table ");
        bodysb.append(sheet.getCell(1, 0).getContents());
        // 表头部分
        bodysb.append(" (\r\n");
        // 表中文名
        commentsb.append("\r\ncomment '" + sheet.getCell(2, 0).getContents() + "';\r\n\n\n\n");
        // 字段部分

        for (int i = 2; i < sheet.getRows(); i++) {
            // 首行处理
            if (i == 2) {
                fldsb.append(" " + sheet.getCell(0, 2).getContents() + " " + sheet.getCell(1, 2).getContents());
                String isNullVal = sheet.getCell(2, 2).getContents();
                String defaultVal = sheet.getCell(3, 2).getContents();
                if ("N".equals(isNullVal.toUpperCase())) { //不允许为null
                    fldsb.append(" NOT NULL");
                    if (!"".equals(defaultVal)) { //存在默认
                        fldsb.append(" DEFAULT '" + defaultVal + "'");
                    }
                } else {
                    fldsb.append(" NULL");
                }
                if (!"".equals(sheet.getCell(4, 2).getContents())) {
                    fldsb.append(" comment '" + sheet.getCell(4, 2).getContents() + "',\r\n");
                }else{
                    fldsb.append(",\r\n");
                }

            } else if (i > 2 && i < sheet.getRows() - 1) {
                for (int j = 0; j <= 4; j++) {
                    switch (j) {
                        case 0:
                            fldsb.append(" " + sheet.getCell(j, i).getContents());
                            break;
                        case 1:
                            fldsb.append(" " + sheet.getCell(j, i).getContents());
                            break;
                        case 2:
                            String isNullVal = sheet.getCell(j, i).getContents();
                            int addj = j + 1;//将默认值一并处理
                            String defaultVal = sheet.getCell(addj, i).getContents();
                            if ("N".equals(isNullVal.toUpperCase())) { //不允许为null
                                fldsb.append(" NOT NULL");
                                if (!"".equals(defaultVal)) { //存在默认
                                    fldsb.append(" DEFAULT '" + defaultVal + "'");
                                }
                            } else {
                                fldsb.append(" NULL");
                            }
                            break;
                        case 3:
                            break;
                        case 4:
                            if (!"".equals(sheet.getCell(j, i).getContents())) {
                                fldsb.append(" comment '" + sheet.getCell(j, i).getContents() + "',\r\n");
                            }else{
                                fldsb.append(",\r\n");
                            }
                            break;
                        default:
                            break;
                    }
                }
            } else {
                fldsb.append(" " + sheet.getCell(0, sheet.getRows() - 1).getContents() + " " + sheet.getCell(1, sheet.getRows() - 1).getContents());
                String isNullVal = sheet.getCell(2, sheet.getRows() - 1).getContents();
                String defaultVal = sheet.getCell(3, sheet.getRows() - 1).getContents();
                if ("N".equals(isNullVal.toUpperCase())) { //不允许为null
                    fldsb.append(" NOT NULL");
                    if (!"".equals(defaultVal)) { //存在默认
                        fldsb.append(" DEFAULT '" + defaultVal + "'");
                    }
                } else {
                    fldsb.append(" NULL");

                }
                fldsb.append(" comment '" + sheet.getCell(4, sheet.getRows() - 1).getContents() + "'\r\n)");
            }

        }

        // 合并结果集
        StringBuffer result = new StringBuffer();
        result = bodysb.append(fldsb).append(commentsb).append(partitionsb);
        return result.toString();
    }



}

 生成结果: