【可直接运行代码】Sql文件生成Doc文档

效果图：

本来想直接通过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;


/**
 * @author YuLF
 * @date 2021/9/29 11:20
 */
@Slf4j
public class SqlParser {
    /**
     * word 表格 列
     */
    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 <https://www.e-iceblue.com/Introduce/doc-for-java.html>
        writeWord(readSqlFile(sqlPath), toWordPath);
        System.out.println("生成完成!");
    }

    private static void writeWord(List<DatabaseTable> 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<DatabaseTableRow> 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);
            }
        }
    }

    /**
     * 将文件中的sql语句以；为单位读取到列表中
     *
     * @param sqlPath /
     * @return /
     * @throws Exception e
     */
    private static List<DatabaseTable> readSqlFile(String sqlPath) throws Exception {
        File file = new File(sqlPath);
        List<DatabaseTable> 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;
    }

    /**
     * 解析每一行 字段
     *
     * @param databaseTable 表对象
     * @param tmp           当前行
     */
    private static List<DatabaseTableRow> resolveRow(DatabaseTable databaseTable, String tmp) {
        if (databaseTable != null) {
            //获取类型
            List<DatabaseTableRow> 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<DatabaseTableRow> checkTableRow(List<DatabaseTableRow> tableRows) {
        if (tableRows == null) {
            tableRows = new ArrayList<>();
        }
        return tableRows;
    }


    /**
     * 从行中获取表英文名
     *
     * @param tmp CREATE TABLE `tableName`  (
     * @return 返回tableName
     */
    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<DatabaseTableRow> tableRow;
}

@Data
class DatabaseTableRow {
    /**
     * 字段名
     */
    private String columnName;
    /**
     * 数据类型
     */
    private String dataType;
    /**
     * 数据长度
     */
    private String dataLen = "0";
    /**
     * 是否必填
     */
    private String must = "否";
    /**
     * 字段注释
     */
    private String desc;
}