效果图：
本来想直接通过Navicat导出sql文件（只导出结构）然后使用PowerDesigner生成word的，但是操作一遍比较费时，所以就自己写了个工具类
lombok+Spire.Doc.jar(可以使用maven的方式也可以使用jar的方式)
jar提取链接：https://pan.baidu.com/s/1Em5QHkBbutYsSRouCan93Q
提取码：i3dy
也可以通过https://www.e-iceblue.com/Download/doc-for-java-free.html下载
package com.example.wordtopdf.test;
import com.spire.doc.*;
import com.spire.doc.documents.*;
import com.spire.doc.fields.TextRange;
import lombok.Data;
import lombok.ToString;
import lombok.extern.slf4j.Slf4j;
import java.awt.Color;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Slf4j
public class SqlParser {
static String[] header = {"名称", "字段名", "数据类型", "长度", "必填", "描述"};
private static final Pattern COLUMN_LEN_REGEX = Pattern.compile("\(.*\)");
private static final Pattern COLUMN_START_REGEX = Pattern.compile("\s{2}`*.*`\s");
private static final Pattern DESC_REGEX = Pattern.compile("\s'.*',$");
private static final Pattern TYPE_REGEX = Pattern.compile("`\u0020(tinyint|smallint|mediumint|int|integer|bigint|float|double|decimal|date|time|year|datetime|timestamp|char|varchar|tinyblob|tinytext|blob|text|mediumblob|mediumtext|longblob|longtext)([(\u0020])");
private static final Pattern CHIN_NAME_REGEX = Pattern.compile("'.*'");
private static final Pattern ENG_NAME_REGEX = Pattern.compile("`.*`");
private static final String MUST = "NOT NULL";
public static void main(String[] args) throws Exception {
//填写你Navicat导出的sql结构文件
String sqlPath = "D:\im_bird.sql";
//填写你写出word文件的路径
String toWordPath = "D:\2.docx";
// 写出word文档 非商业版 Spire.Doc 限制25 张表格 所以我建议25张表 25张表生成，然后手动复制到一个word里面合并
// Spire.Doc free version is limited to 25 tables. This limitation is enforced during reading or writing files.
// Upgrade to Commercial Edition of Spire.Doc
writeWord(readSqlFile(sqlPath), toWordPath);
System.out.println("生成完成!");
}
private static void writeWord(List list,String toPath) {
document document = new document();
Section section = document.addSection();
//设置标题样式
ParagraphStyle style1 = new ParagraphStyle(document);
style1.setName("titleStyle");
style1.getCharacterFormat().setBold(true);
style1.getCharacterFormat().setTextColor(Color.BLACK);
style1.getCharacterFormat().setFontName("Arial");
style1.getCharacterFormat().setFontSize(16f);
document.getStyles().add(style1);
for (DatabaseTable databaseTable : list) {
//添加word标题
Paragraph para = section.addParagraph();
para.appendText(getName(databaseTable));
para.applyStyle("titleStyle");
para.getFormat().setHorizontalAlignment(HorizontalAlignment.Center);
para.getFormat().setAfterSpacing(15f);
//对象转为二维数组 以顺序性写入word表格的每一列
String[][] data = convertArr(databaseTable.getTableRow());
addTable(section, data);
}
document.saveToFile(toPath, FileFormat.Docx_2013);
}
private static String getName(DatabaseTable databaseTable) {
StringBuilder builder = new StringBuilder();
if(databaseTable.getChinTableName() != null){
builder.append(databaseTable.getChinTableName());
}
if(databaseTable.getEngTableName() != null){
builder.append(databaseTable.getEngTableName());
}
return builder.toString();
}
private static String[][] convertArr(List tableRow) {
if(tableRow == null){
return null;
}
return tableRow.stream()
.map(r -> new String[]{r.getDesc(), r.getColumnName(), r.getDataType(), r.getDataLen(),r.getMust(), r.getDesc()})
.toArray(String[][]::new);
}
private static void addTable(Section section, String[][] data) {
if(data == null){
return;
}
//Add a table
Table table = section.addTable();
table.resetCells(data.length + 1, header.length);
table.applyStyle(DefaultTableStyle.Colorful_List);
table.getTableFormat().getBorders().setBorderType(BorderStyle.Single);
//将第一行设置为表头并添加数据
TableRow row = table.getRows().get(0);
row.isHeader(true);
row.setHeight(20);
row.setHeightType(TableRowHeightType.Exactly);
row.getRowFormat().setBackColor(Color.gray);
for (int i = 0; i < header.length; i++) {
row.getCells().get(i).getCellFormat().setVerticalAlignment(VerticalAlignment.Middle);
Paragraph p = row.getCells().get(i).addParagraph();
p.getFormat().setHorizontalAlignment(HorizontalAlignment.Center);
TextRange range1 = p.appendText(header[i]);
range1.getCharacterFormat().setFontName("Arial");
range1.getCharacterFormat().setFontSize(12f);
range1.getCharacterFormat().setBold(true);
}
//向其余行添加数据
for (int r = 0; r < data.length; r++) {
TableRow dataRow = table.getRows().get(r + 1);
dataRow.setHeight(25);
dataRow.setHeightType(TableRowHeightType.Exactly);
dataRow.getRowFormat().setBackColor(Color.white);
for (int c = 0; c < data[r].length; c++) {
dataRow.getCells().get(c).getCellFormat().setVerticalAlignment(VerticalAlignment.Middle);
TextRange range2 = dataRow.getCells().get(c).addParagraph().appendText(data[r][c]);
range2.getCharacterFormat().setFontName("宋体");
range2.getCharacterFormat().setFontSize(10f);
}
}
}
private static List readSqlFile(String sqlPath) throws Exception {
File file = new File(sqlPath);
List list = new ArrayList<>();
try (BufferedReader reader = new BufferedReader(new InputStreamReader(
new FileInputStream(file), StandardCharsets.UTF_8))) {
String tmp;
DatabaseTable databaseTable = null;
while ((tmp = reader.readLine()) != null) {
//英文表名
String tableStart = "CREATE TABLE `*.*`";
if (Pattern.compile(tableStart).matcher(tmp).find()) {
databaseTable = new DatabaseTable();
databaseTable.setEngTableName(getTableEngName(tmp));
}
//中间行
String rowRegex = "^[\u0020]{2}.+`[\u0020](tinyint|smallint|mediumint|int|integer|bigint|float|double|decimal|date|time|year|datetime|timestamp|char|varchar|tinyblob|tinytext|blob|text|mediumblob|mediumtext|longblob|longtext).*,$";
if (Pattern.compile(rowRegex).matcher(tmp).find()) {
assert databaseTable != null;
databaseTable.setTableRow(resolveRow(databaseTable, tmp));
}
//中文表名
String tableEnd = "\)\sENGINE\s=";
if (Pattern.compile(tableEnd).matcher(tmp).find()) {
if (databaseTable != null) {
databaseTable.setChinTableName(getTableChinName(tmp));
list.add(databaseTable);
}
}
}
}
return list;
}
private static String getTableChinName(String tmp) {
Matcher matcher = CHIN_NAME_REGEX.matcher(tmp);
if (matcher.find()) {
return matcher.group().replace("'", "");
}
return null;
}
private static List resolveRow(DatabaseTable databaseTable, String tmp) {
if (databaseTable != null) {
//获取类型
List tableRow = databaseTable.getTableRow();
tableRow = checkTableRow(tableRow);
DatabaseTableRow row = new DatabaseTableRow();
//字段名
Matcher matcher = COLUMN_START_REGEX.matcher(tmp);
if (matcher.find()) {
String group = matcher.group();
row.setColumnName(group.replace("`", "").trim());
}
//字段类型
Matcher matcher1 = TYPE_REGEX.matcher(tmp);
if (matcher1.find()) {
row.setDataType(matcher1.group().replace("`", "").replace("(", "").trim());
}
//数据类型长度
Matcher matcher2 = COLUMN_LEN_REGEX.matcher(tmp);
if (matcher2.find()) {
String group = matcher2.group();
row.setDataLen(group.substring(group.indexOf("(") + 1, group.indexOf(")")));
}
//是否必填
if (tmp.contains(MUST)) {
row.setMust("是");
}
//注释
Matcher matcher3 = DESC_REGEX.matcher(tmp);
if (matcher3.find()) {
row.setDesc(matcher3.group().replace("'", "").replace(",", ""));
}
tableRow.add(row);
return tableRow;
}
return null;
}
private static List checkTableRow(List tableRows) {
if (tableRows == null) {
tableRows = new ArrayList<>();
}
return tableRows;
}
private static String getTableEngName(String tmp) {
Matcher matcher = ENG_NAME_REGEX.matcher(tmp);
if (matcher.find()) {
return matcher.group().replace("`", "");
}
return null;
}
}
@Data
@ToString
class DatabaseTable {
private String engTableName;
private String chinTableName;
private List tableRow;
}
@Data
class DatabaseTableRow {
private String columnName;
private String dataType;
private String dataLen = "0";
private String must = "否";
private String desc;
}
如果对你有所帮助的话，点个赞即可
