- Excel表结构模板
- Java 代码
- 生成的MySql建表语句
依赖EasyExcel
com.alibaba easyexcel2.2.8
@PostMapping("/importExcel")
public String importExcel(@RequestBody MultipartFile file) {
FileOutputStream fos = null;
OutputStreamWriter osw = null;
try {
// 读取文件流
InputStream inputStream = file.getInputStream();
List list = EasyExcel.read(inputStream)
.head(importExcel.class)
.sheet()
.doReadSync();
StringBuilder sb = new StringBuilder("DROP TABLE IF EXISTS ``;n" +
"CREATE TABLE `` (n");
for (int i = 0; i < list.size(); i++) {
// 字段名
sb.append("`").append(list.get(i).getFiledName()).append("` ");
// 字段类型和长度
if (list.get(i).getDataType().contains("CHAR")) {
sb.append(list.get(i).getDataType()).append("(").append(list.get(i).getLength()).append(") ");
sb.append("CHARACTER SET utf8 COLLATE utf8_general_ci ");
} else {
sb.append(list.get(i).getDataType()).append(" ");
}
// 非空与否
if (list.get(i).getNotNull().equals("Y")) {
sb.append("NOT NULL ");
}
// 是否自增
if (StringUtils.equals("fd_id", list.get(i).getFiledName())) {
sb.append("AUTO_INCREMENT ");
}
// 默认值
if (!StringUtils.equals(list.get(i).getDefaultValue(), "自增序列")) {
if (list.get(i).getDataType().contains("CHAR")) {
sb.append("DEFAULT '").append(list.get(i).getDefaultValue() == null ? "" : list.get(i).getDefaultValue()).append("' ");
} else if (list.get(i).getDataType().contains("INT") && list.get(i).getDefaultValue() != null) {
sb.append("DEFAULT ").append(list.get(i).getDefaultValue()).append(" ");
}
}
// 注释
String remark = list.get(i).getRemark();
String replace = remark.replace("n", " ");
sb.append("COMMENT '").append(replace).append("'");
sb.append(",n");
}
// 索引
List keyFd = list.stream().filter(data -> StringUtils.equals("UNIQUE", data.getKey())).collect(Collectors.toList());
StringBuilder keySb = new StringBuilder("PRIMARY KEY (`fd_id`)");
if (!CollectionUtils.isEmpty(keyFd)) {
keySb.append(",n");
for (int i = 0; i < keyFd.size(); i++) {
keySb.append("UNIQUE KEY `").append(keyFd.get(i).getFiledName()).append("` (`").append(keyFd.get(i).getFiledName()).append("`) USING BTREE");
if (i != keyFd.size() - 1) {
keySb.append(",");
}
keySb.append("n");
}
}
sb.append(keySb);
sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='';");
System.out.println(sb);
// 写入SQL脚本文件
String saveFile = "C:\Users\Administrator\Desktop\ibuilding.sql";
File sqlFile = new File(saveFile);
if (!sqlFile.exists()) {
fos = new FileOutputStream(sqlFile);
} else {
fos = new FileOutputStream(sqlFile, true);
}
osw = new OutputStreamWriter(fos, StandardCharsets.UTF_8);
osw.write(sb.toString());
osw.write("rnn");
} catch (IOException e) {
return "文件读取失败";
} finally {
try {
if (osw != null) {
osw.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if (fos != null) {
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return "Bingo!";
}
行数据对应实体类
public class importExcel implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty(index = 1)
private String filedName;
@ExcelProperty(index = 2)
private String dataType;
@ExcelProperty(index = 3)
private String length;
@ExcelProperty(index = 4)
private String notNull;
@ExcelProperty(index = 5)
private String key;
@ExcelProperty(index = 6)
private String defaultValue;
@ExcelProperty(index = 7)
private String remark;
}
生成的MySql建表语句
加上表名，表注释即可执行
DROP TABLE IF EXISTS ``; CREATE TABLE `` ( `fd_id` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AUTO_INCREMENT DEFAULT '' COMMENT '唯一标识码', `fd_mobile` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '手机号', `fd_account` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '账号', `fd_password` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '密码', `fd_name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'default' COMMENT '名称', `fd_english_name` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '英文名', `fd_head_img` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '头像', `fd_gender` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'secrecy' COMMENT '性别 male：男； female：女； secrecy：保密；', `fd_creator` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '创建人', `fd_create_time` DATETIME NOT NULL COMMENT '创建时间', `fd_updater` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '更新人', `fd_update_time` DATETIME COMMENT '更新时间', `fd_delete_status` CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '删除状态 1：已删除； 0：未删除；', `fd_remarks` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '备注', PRIMARY KEY (`fd_id`), UNIQUE KEY `fd_id` (`fd_id`) USING BTREE, UNIQUE KEY `fd_mobile` (`fd_mobile`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='';
