Java读取excel模板生成对应数据库表的ddl建表语句
业务功能、应该场景
一般会通过excle里，写入表的字段信息，如果新建的表、字段信息较多，自己手动写成SQL非常不方便，因此推出两个excle模板 放入对应的内容信息即可快速生成建表语句。
我这里推出两个模板，代码实现也比较简单，你也可以自定义自己工作中的模板，改写下代码即可。为了方便我这里把代码跟文件模板放在这里，需要的点我下载即可
代码部分
Maven 依赖
<!--核心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>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.7</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
模板1的代码
package org.example.demo.onetest.excletable;
import org.apache.commons.lang3.StringUtils;
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 ExcelSheet1Utils {
/**
* 读取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();
Cell modelRow = row.getCell(0);
//获取表英文名
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() != 7) {
System.out.println("第2行应该只有7个单元格!");
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 (modelRow.getStringCellValue().contains("模板备注")) {
break;
}
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();
if (StringUtils.isBlank(fieldLength)) {
fieldLength = "0";
}
// 是否为主键
Cell cell4 = row.getCell(3);
// 是否自增
Cell cell5 = row.getCell(4);
//字段是否为空
// String ifnullValue = row.getCell(5).getStringCellValue();
Cell cell6 = row.getCell(5);
// 字段注释
String fieldComment = row.getCell(6).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 " : "")
+ (cell6 != null && "N".equals(cell6.getStringCellValue()) ? " NOT NULL " : " DEFAULT NULL")
+ " 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("C:\\Users\\91845\\Desktop\\ddl.txt");
if (!file.exists()) {
file.createNewFile();
}
FileWriter fileWriter = new FileWriter(file.getName(), true);
fileWriter.write(message);
fileWriter.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
getDataFromExcel("C:\\Users\\91845\\Desktop\\ExcelSheet1.xlsx");
}
}
模板二的代码
package org.example.demo.onetest.excletable;
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 ExcelSheet2Utils {
/**
* 读取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();
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();
Cell modelRow = row.getCell(0);
//获取表英文名
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 (modelRow.getStringCellValue().contains("模板备注")) {
break;
}
if (!row.cellIterator().hasNext()) {
break;
}
// 字段名
String fieldName = row.getCell(0).getStringCellValue();
if (fieldName == null | "".equals(fieldName)) {
break;
}
// 字段类型
String fieldType = row.getCell(1).getStringCellValue();
// 是否为主键
Cell cell4 = row.getCell(2);
// 是否自增
Cell cell5 = row.getCell(3);
//字段是否为空
// String ifnullValue = row.getCell(5).getStringCellValue();
Cell cell6 = 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 " : "")
+ (cell6 != null && "N".equals(cell6.getStringCellValue()) ? " NOT NULL " : " DEFAULT NULL")
+ " 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("C:\\Users\\91845\\Desktop\\ddl.txt");
if (!file.exists()) {
file.createNewFile();
}
FileWriter fileWriter = new FileWriter(file.getName(), true);
fileWriter.write(message);
fileWriter.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
getDataFromExcel("C:\\Users\\91845\\Desktop\\ExcelSheet2.xlsx");
}
}