数据库表字段以表格形式写入Word

在项目的开发中，难免会有编写概要设计、详细设计文档的要求，而在这些文档中，不可避免的就是要把数据库表的字段信息以表格的形式体现出来。例如下面这种格式

表数量少点还可以一点点粘贴，多了的话真的会疯，所以自己编写了一套可以自动读取数据表字段来批量写入文档的代码。

maven项目需要先引入依赖

        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>27.0-hbase</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version> <!-- 请检查是否有更新的版本 -->
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version> <!-- 请检查是否有更新的版本 -->
        </dependency>

        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>25.1-jre</version>
        </dependency>

代码如下： 

import avro.shaded.com.google.common.collect.Lists;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;

import java.sql.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigInteger;
import java.util.List;

public class DatabaseToWordExample{
    public static void main(String[] args) throws SQLException, IOException {
        // 创建Word文档
        XWPFDocument doc = new XWPFDocument();
        // 创建1级标题
        setDocTitle(doc,1,"一、数据库表设计");

        // 连接数据库
        Connection conn = DriverManager.getConnection("jdbc:mysql://{IP}:{port}/{数据库名}", 用户名, 密码);
        List<String> dbList = Lists.newArrayList("user");
        // 获取元数据
        DatabaseMetaData metaData = conn.getMetaData();
        int dbNum = 1;
        for (String database : dbList) {
            // 2级数据库标题名
            String databaseTitle = new StringBuilder(String.format("%d.%d",1,dbNum))
                    .append(" ")
                    .append(database)
                    .append("库")
                    .toString();
            // 创建2级标题
            setDocTitle(doc,2,databaseTitle);

            int tableNum = 1;
            // 查询表列表
            ResultSet tables = metaData.getTables(database, null, null, null);
            while (tables.next()){
                String tableName = tables.getString("TABLE_NAME");
                // 查询表注释
                String sql = "SELECT table_comment FROM information_schema.tables WHERE table_schema = ? AND table_name = ?";
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1, database);
                ps.setString(2, tableName);
                ResultSet rs = ps.executeQuery();
                String tableComment = "";
                if (rs.next()) {
                    tableComment = rs.getString("table_comment");
                }
                // 3级表标题名
                String tableTitle = new StringBuilder(String.format("%d.%d.%d",1,dbNum,tableNum))
                        .append(" ")
                        .append(tableName)
                        .append(" ")
                        .append(tableComment)
                        .toString();
                // 创建3级标题
                setDocTitle(doc,3,tableTitle);

                // 创建表格
                XWPFTable table = createTable(doc);
                ResultSet columns = metaData.getColumns(database, null, tableName, null);

                // 添加数据行
                while (columns.next()) {
                    XWPFTableRow dataRow = table.createRow();
                    setRowHeight(dataRow, "490");

                    dataRow.getCell(0).setText(columns.getString("COLUMN_NAME"));
                    dataRow.getCell(1).setText(columns.getString("TYPE_NAME"));
                    dataRow.getCell(2).setText(Integer.toString(columns.getInt("COLUMN_SIZE")));
                    dataRow.getCell(3).setText(columns.getString("REMARKS"));

                    // 设置数据行单元格边框
                    for (XWPFTableCell cell : dataRow.getTableCells()) {
                        setCellBorders(cell);
                    }
                }

                tableNum++;
            }
            dbNum++;
        }

        // 保存Word文档
        try (FileOutputStream out = new FileOutputStream("output.docx")) {
            doc.write(out);
        }

        // 关闭数据库连接
        conn.close();
    }

    /**
     * 设置标题
     *
     * @param doc
     * @param titleLevel 标题等级
     * @param titleName 标题名称
     */
    private static void setDocTitle(XWPFDocument doc, int titleLevel, String titleName){
        switch (titleLevel) {
            case 1:
                // 创建一级标题
                XWPFParagraph title1 = doc.createParagraph();
                XWPFRun run1 = title1.createRun();
                run1.setText(titleName);
                run1.setBold(true);
                run1.setFontSize(16);
                break;
            case 2:
                // 创建二级标题
                XWPFParagraph title2 = doc.createParagraph();
                XWPFRun run2 = title2.createRun();
                run2.setText(titleName);
                run2.setBold(true);
                run2.setFontSize(14);
                break;
            case 3:
                // 创建三级标题
                XWPFParagraph title3 = doc.createParagraph();
                XWPFRun run3 = title3.createRun();
                run3.setText(titleName);
                run3.setBold(true);
                run3.setFontSize(12);
                break;
            default:
                break;
        }
    }

    /**
     * 创建表格
     *
     * @param doc
     * @return
     */
    private static XWPFTable createTable(XWPFDocument doc){
        // 创建表格
        XWPFTable table = doc.createTable();

        // 设置表格宽度
        table.setWidth("9990");

        // 创建表头
        XWPFTableRow headerRow = table.getRow(0);
        setRowHeight(headerRow, "490");  // 设置行高
        headerRow.getCell(0).setText("字段名");
        headerRow.addNewTableCell().setText("字段类型");
        headerRow.addNewTableCell().setText("字段长度");
        headerRow.addNewTableCell().setText("字段备注");

        // 计算每个单元格的宽度
        int totalWidth = 8222;  // 总宽度，例如14.5厘米
        int numColumns = 4;  // 列数，例如4列
        int cellWidth = totalWidth / numColumns;  // 计算每个单元格的宽度

        // 设置表格的列宽
        CTTblWidth tblWidth = table.getCTTbl().addNewTblPr().addNewTblW();
        tblWidth.setType(STTblWidth.DXA);
        tblWidth.setW(BigInteger.valueOf(totalWidth));


        // 设置单元格的宽度
        for (XWPFTableRow row : table.getRows()) {
            for (XWPFTableCell cell : row.getTableCells()) {
                CTTcPr tcPr = cell.getCTTc().addNewTcPr();
                CTTblWidth cellWidth1 = tcPr.addNewTcW();
                cellWidth1.setW(BigInteger.valueOf(cellWidth));
                cellWidth1.setType(STTblWidth.DXA);
            }
        }

        // 设置表头单元格边框
        for (XWPFTableCell cell : headerRow.getTableCells()) {
            setCellBorders(cell);
            cell.setColor("D3D3D3");  // 浅灰色的十六进制颜色代码
            // 设置文字竖向居中
            cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
        }
        return table;
    }

    private static void setRowHeight(XWPFTableRow row, String height) {
        row.setHeight(Integer.parseInt(height));
    }


    /**
     * 设置单元格边框和竖向居中
     * @param cell
     */
    private static void setCellBorders(XWPFTableCell cell) {

        // 确保单元格的CTTcPr对象已经被初始化
        if (!cell.getCTTc().isSetTcPr()) {
            cell.getCTTc().addNewTcPr();
        }
        // 设置文字竖向居中
        cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);

        // 设置单元格上边框
        CTTcBorders borders = cell.getCTTc().getTcPr().addNewTcBorders();
        CTBorder topBorder = borders.addNewTop();
        topBorder.setVal(STBorder.SINGLE);
        topBorder.setSz(BigInteger.valueOf(4));
        topBorder.setColor("000000");

        // 设置单元格下边框
        CTBorder bottomBorder = borders.addNewBottom();
        bottomBorder.setVal(STBorder.SINGLE);
        bottomBorder.setSz(BigInteger.valueOf(4));
        bottomBorder.setColor("000000");

        // 设置单元格左边框
        CTBorder leftBorder = borders.addNewLeft();
        leftBorder.setVal(STBorder.SINGLE);
        leftBorder.setSz(BigInteger.valueOf(4));
        leftBorder.setColor("000000");

        // 设置单元格右边框
        CTBorder rightBorder = borders.addNewRight();
        rightBorder.setVal(STBorder.SINGLE);
        rightBorder.setSz(BigInteger.valueOf(4));
        rightBorder.setColor("000000");
    }
}