java 执行建表语句_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();

}

}

生成结果: