当前位置：首页 > news >正文

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

news 来源：原创 2024/5/20 12:16:37

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

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

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");}
}