java通过excel表模型实现生成建表语句（mysql和pgsql）
一、mysql
<!--核心jar包-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.Iterator;
/**
* @author Jack Li
* @description 读取excel文件内容生成数据库表ddl
* @date 2022/3/27 19:54
*/
public class ExcelUtils {
/**
* 读取excel文件内容生成数据库表ddl
*
* @param filePath excel文件的绝对路径
*/
public static void getDataFromExcel(String filePath) {
if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
System.out.println("文件不是excel类型");
}
InputStream fis = null;
Workbook wookbook = null;
try {
fis = new FileInputStream(filePath);
if (filePath.endsWith(".xls")) {
try {
//2003版本的excel，用.xls结尾
wookbook = new HSSFWorkbook(fis);
} catch (IOException e) {
e.printStackTrace();
}
}
if (filePath.endsWith(".xlsx")) {
try {
//2007版本的excel，用.xlsx结尾
wookbook = new XSSFWorkbook(fis);
} catch (IOException e) {
e.printStackTrace();
}
}
Iterator<Sheet> sheets = wookbook.sheetIterator();
while (sheets.hasNext()) {
StringBuilder ddl = new StringBuilder();
// 是否自增
boolean autoIncrement = false;
Sheet sheet = sheets.next();
System.out.println("--------------------------当前读取的sheet页：" + sheet.getSheetName() + "--------------------------");
// 当前读取行的行号
int rowId = 1;
Iterator<Row> rows = sheet.rowIterator();
String tableEnglishName = "";
String tableChineseName = "";
while (rows.hasNext()) {
Row row = rows.next();
//获取表英文名
if (rowId == 1) {
Cell cell1 = row.getCell(0);
if (!"表英文名".equals(cell1.getStringCellValue())) {
System.out.println("第一行第一格应该为“表英文名”!");
return;
}
Cell cell2 = row.getCell(1);
tableEnglishName = cell2.getStringCellValue();
ddl.append("CREATE TABLE " + "`" + tableEnglishName + "` (" + "\r\n");
rowId++;
continue;
}
//获取表中文名
if (rowId == 2) {
Cell cell1 = row.getCell(0);
if (!"表中文名".equals(cell1.getStringCellValue())) {
System.out.println("第2行第一格应该为“表中文名”!");
return;
}
Cell cell2 = row.getCell(1);
tableChineseName = cell2.getStringCellValue();
rowId++;
continue;
}
//校验属性列名称和顺序
if (rowId == 3) {
if (row.getPhysicalNumberOfCells() != 6) {
System.out.println("第2行应该只有6个单元格!");
return;
}
Iterator<Cell> cells = row.cellIterator();
StringBuilder tableField = new StringBuilder();
while (cells.hasNext()) {
tableField.append(cells.next().getStringCellValue().trim());
}
if (!"字段名类型长度,小数点是否为主键是否自增注释".equals(tableField.toString())) {
System.out.println("第3行应该为 字段名 类型 长度,小数点 是否为主键 是否自增 注释 !");
return;
}
rowId++;
continue;
}
if (!row.cellIterator().hasNext()) {
break;
}
// 字段名
String fieldName = row.getCell(0).getStringCellValue();
if (fieldName == null | "".equals(fieldName)){
break;
}
// 字段类型
String fieldType = row.getCell(1).getStringCellValue();
// 字段长度
Cell cell3 = row.getCell(2);
cell3.setCellType(CellType.STRING);
String fieldLength = cell3.getStringCellValue();
// 是否为主键
Cell cell4 = row.getCell(3);
// 是否自增
Cell cell5 = row.getCell(4);
// 字段注释
String fieldComment = row.getCell(5).getStringCellValue();
ddl.append(
"`" + fieldName + "` "
+ fieldType
+ (!"0".equals(fieldLength) ? "(" + fieldLength + ")" : "")
+ (cell4 != null && "Y".equals(cell4.getStringCellValue()) ? " PRIMARY KEY " : "")
+ (cell5 != null && "Y".equals(cell5.getStringCellValue()) ? " AUTO_INCREMENT " : "")
+ " COMMENT '" + fieldComment + "'"
+ (rows.hasNext() ? ",\r\n" : "\r\n")
);
if (cell4 != null && "Y".equals(cell5.getStringCellValue())) {
autoIncrement = true;
}
rowId++;
}
if (ddl.toString().endsWith(",\r\n")){
ddl = ddl.deleteCharAt(ddl.length()-3);
ddl.append("\r\n");
}
ddl.append(") ENGINE=InnoDB " + (autoIncrement ? "AUTO_INCREMENT=1" : "") + " DEFAULT CHARSET=utf8 "
+ (!"".equals(tableChineseName) ? "COMMENT = '" + tableChineseName + "'" : "") + ";\r\n");
ddl.append("-- --------------------------------------------------------------------------------\r\n");
System.out.println(ddl.toString());
writeMessageToFile(ddl.toString());
}
System.out.println("运行成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void writeMessageToFile(String message) {
try {
File file = new File("ddl.txt");
if (!file.exists()) {
file.createNewFile();
}
FileWriter fileWriter = new FileWriter(file.getName(), true);
fileWriter.write(message);
fileWriter.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
excel模板
效果
二、pgsql
package com.startel.middleware.controller;
import com.startel.middleware.utils.TextUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import java.io.*;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
/**
* @author Jack Li
* @description 读取excel文件内容生成数据库表ddl
* @date 2022/3/27 19:54
*/
@RestController
@RequestMapping("/middleware/text")
public class ExcelUtils {
/**
* 读取excel文件内容生成数据库表ddl
*
* @param filePath excel文件的绝对路径
*/
@PostMapping(value = "/getDataFromExcel")
@ResponseBody
public static void getDataFromExcel(String filePath) {
if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
System.out.println("文件不是excel类型");
}
InputStream fis = null;
Workbook wookbook = null;
try {
fis = new FileInputStream(filePath);
if (filePath.endsWith(".xls")) {
try {
//2003版本的excel，用.xls结尾
wookbook = new HSSFWorkbook(fis);
} catch (IOException e) {
e.printStackTrace();
}
}
if (filePath.endsWith(".xlsx")) {
try {
//2007版本的excel，用.xlsx结尾
wookbook = new XSSFWorkbook(fis);
} catch (IOException e) {
e.printStackTrace();
}
}
Iterator<Sheet> sheets = wookbook.sheetIterator();
while (sheets.hasNext()) {
StringBuilder ddl = new StringBuilder();
// 是否自增
boolean autoIncrement = false;
Sheet sheet = sheets.next();
System.out.println("--------------------------当前读取的sheet页：" + sheet.getSheetName() + "--------------------------");
// 当前读取行的行号
int rowId = 1;
Iterator<Row> rows = sheet.rowIterator();
String tableEnglishName = "";
String tableChineseName = "";
Map<String,Object> map = new LinkedHashMap<>();
while (rows.hasNext()) {
Row row = rows.next();
//获取表英文名
if (rowId == 1) {
Cell cell1 = row.getCell(0);
// if (!"表英文名".equals(cell1.getStringCellValue())) {
// System.out.println("第一行第一格应该为“表英文名”!");
// return;
// }
Cell cell2 = row.getCell(3);
tableEnglishName = cell2.getStringCellValue().toLowerCase();
ddl.append("CREATE TABLE " + "\"" + tableEnglishName + "\" (" + "\r\n");
rowId++;
continue;
}
//获取表中文名
if (rowId == 2) {
Cell cell1 = row.getCell(0);
// if (!"表中文名".equals(cell1.getStringCellValue())) {
// System.out.println("第2行第一格应该为“表中文名”!");
// return;
// }
Cell cell2 = row.getCell(3);
tableChineseName = cell2.getStringCellValue();
rowId++;
continue;
}
//校验属性列名称和顺序
// if (rowId == 3) {
// if (row.getPhysicalNumberOfCells() != 6) {
// System.out.println("第2行应该只有6个单元格!");
// return;
// }
// Iterator<Cell> cells = row.cellIterator();
// StringBuilder tableField = new StringBuilder();
// while (cells.hasNext()) {
// tableField.append(cells.next().getStringCellValue().trim());
// }
if (!"字段名类型长度,小数点是否为主键是否自增注释".equals(tableField.toString())) {
System.out.println("第3行应该为 字段名 类型 长度,小数点 是否为主键 是否自增 注释 !");
return;
}
// rowId++;
// continue;
// }
if (!row.cellIterator().hasNext()) {
break;
}
rowId++;
// 字段名
if(rowId >= 9){
String fieldName = row.getCell(3).getStringCellValue().toLowerCase();
if (fieldName == null | "".equals(fieldName)){
break;
}
// 字段类型
String fieldType = row.getCell(4).getStringCellValue();
switch (fieldType){
case "integer":
fieldType = "int4";
break;
case "long":
fieldType = "int4";
break;
case "double":
fieldType = "numeric(18,2)";
break;
case "string":
fieldType = "varchar(255)";
break;
case "datetosecond":
fieldType = "timestamp(6)";
break;
case "datetoday":
fieldType = "timestamp(6)";
break;
default:
break;
}
// 字段长度
// Cell cell3 = row.getCell(2);
// cell3.setCellType(CellType.STRING);
// String fieldLength = cell3.getStringCellValue();
// 是否为主键
Cell cell4 = row.getCell(6);
//枚举值
String enumeration = row.getCell(7) !=null ? row.getCell(7).getStringCellValue() :"";
// 是否自增
// Cell cell5 = row.getCell(4);
// 字段注释
String fieldComment = row.getCell(2).getStringCellValue();
if(!"varchar(255)".equals(fieldType)){
ddl.append(
"\"" + fieldName + "\" "
+ fieldType
// + (!"0".equals(fieldLength) ? "(" + fieldLength + ")" : "")
// + (cell4 != null && "Y".equals(cell4.getStringCellValue()) ? " PRIMARY KEY " : "")
// + (cell5 != null && "Y".equals(cell5.getStringCellValue()) ? " AUTO_INCREMENT " : "")
// + " COMMENT '" + fieldComment + "'"
+ (cell4 != null && "不可为空".equals(cell4.getStringCellValue()) ? " NOT NULL " : "")
// + (rows.hasNext() ? ",\r\n" : "\r\n")
+",\r\n"
);
}else{
ddl.append(
"\"" + fieldName + "\" "
+ fieldType
// + (!"0".equals(fieldLength) ? "(" + fieldLength + ")" : "")
// + (cell4 != null && "Y".equals(cell4.getStringCellValue()) ? " PRIMARY KEY " : "")
// + (cell5 != null && "Y".equals(cell5.getStringCellValue()) ? " AUTO_INCREMENT " : "")
// + " COMMENT '" + fieldComment + "'"
+ " COLLATE \"pg_catalog\".\"default\""
+ (cell4 != null && "不可为空".equals(cell4.getStringCellValue()) ? " NOT NULL " : "")
// + (rows.hasNext() ? ",\r\n" : "\r\n")
+",\r\n"
);
}
// if (cell4 != null && "数据唯一".equals(cell5.getStringCellValue())) {
// autoIncrement = true;
// }
if(TextUtil.isNotNull(enumeration)){
map.put(fieldName,fieldComment+ "；\r\n" + "枚举值为:" +enumeration);
}else{
map.put(fieldName,fieldComment);
}
rowId++;
}
}
//主键
ddl.append("CONSTRAINT " + "\"" + tableEnglishName + "_pkey" + "\"" + " PRIMARY KEY " + "(\"int_id\")" +"\r\n);");
if (ddl.toString().endsWith(",\r\n")){
ddl = ddl.deleteCharAt(ddl.length()-3);
ddl.append("\r\n");
}
//权限
ddl.append( "\r\n" + "ALTER TABLE \"public\"." +tableEnglishName+ " OWNER TO \"postgres\"" + ";\r\n");
//注释
for(String key:map.keySet()){//keySet获取map集合key的集合 然后在遍历key获取value即可
String value = map.get(key).toString();//
ddl.append( "\r\n" + "COMMENT ON COLUMN \"public\"." +tableEnglishName+ "." +"\"" + key + "\"" +" IS " +"'" + value +"'" + ";\r\n");
}
ddl.append("-- --------------------------------------------------------------------------------\r\n");
System.out.println(ddl.toString());
writeMessageToFile(ddl.toString());
}
System.out.println("运行成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void writeMessageToFile(String message) {
try {
File file = new File("ddl.txt");
if (!file.exists()) {
file.createNewFile();
}
FileWriter fileWriter = new FileWriter(file.getName(), true);
fileWriter.write(message);
fileWriter.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
效果
--------------------------当前读取的sheet页：student--------------------------
CREATE TABLE "student" (
"resid" vachar(11) COLLATE "pg_catalog"."default" NOT NULL ,
"name" vachar(255) COLLATE "pg_catalog"."default",
"age" vachar(11) COLLATE "pg_catalog"."default",
CONSTRAINT "student_pkey" PRIMARY KEY ("resid")
);
ALTER TABLE "public".student OWNER TO "postgres";
COMMENT ON COLUMN "public".student."name" IS '姓名';
COMMENT ON COLUMN "public".student."resid" IS '主键';
COMMENT ON COLUMN "public".student."age" IS '年龄';
-- --------------------------------------------------------------------------------
运行成功
832
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
