java 实现mysql表结构导出到word文档

1.pom引用 

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

2.编写java代码 java接口

 public  List<MyTable> getTables(@Param("database") String database);
    
 public List<MyColumn> getColumns(@Param("database")String database, @Param("tableName")String tableName);

3.mapper.xml

<select id="getTables" resultType="com.pab.data.datasource.entity.MyTable" parameterType="string">
        select `table_name`,`table_comment`,`engine` from information_schema.tables where table_schema= #{database}
    </select>

    <select id="getColumns" resultType="com.pab.data.datasource.entity.MyColumn">
        select `column_name`,`column_comment`,`column_type`,`column_default`,`is_nullable`, `column_key`
        from information_schema.columns
        where  table_schema=#{database} and table_name=#{tableName}  group by column_name
    </select>

4.实体类

@Data
public class MyColumn {
    private String columnName;
    private String columnComment;
    private String columnType;
    private String columnDefault;
    private String  isNullable;
    private String columnKey;
}

@Data
public class MyTable {
    private String tableName;
    private String tableComment;
    private String engine;
    private List<MyColumn> columns;
}

5. 生成报告

   public void report() throws  Exception;

  // 导出数据
    @Override
    public void report() throws  Exception{
        String database= "test";
        String reportPath = "D:\\data\\";
        Map<String, List<MyColumn>> data = getData("test");       // 表名：表体
        List<MyTable> tables = userMapper.getTables("test");         // 表体(列名、类型、注释)
        Map<String, String> tableMap = new HashMap<String, String>();              // 表名:中文名
        JSONObject json = new JSONObject((HashMap) data);
        for (MyTable table : tables) {
            tableMap.put(table.getTableName(),table.getTableComment());
        }
        // 构建表格数据
        XWPFDocument document = new XWPFDocument();
        Integer i = 1;
        for (String tableName : data.keySet()) {

            XWPFParagraph paragraph = document.createParagraph();                // 创建标题对象
            XWPFRun run = paragraph.createRun();                                 // 创建文本对象
            run.setText((i+"、"+tableName+"    "+tableMap.get(tableName)));      // 标题名称
            run.setFontSize(14);                                                 // 字体大小
            run.setBold(true);                                                   // 字体加粗

            int j = 0;
            XWPFTable table = document.createTable(data.get(tableName).size()+1,5);
            // 第一行
            table.setCellMargins(10,50,10,200);
            table.getRow(j).getCell(0).setText("字段名称");
            table.getRow(j).getCell(1).setText("字段类型");
            table.getRow(j).getCell(2).setText("约束");
            table.getRow(j).getCell(3).setText("为空");
            table.getRow(j).getCell(4).setText("字段含义");
            j++;

            for (MyColumn tableColumn : data.get(tableName)) {

                table.getRow(j).getCell(0).setText(tableColumn.getColumnName());
                table.getRow(j).getCell(1).setText(tableColumn.getColumnType());
                table.getRow(j).getCell(2).setText(tableColumn.getColumnKey());
                table.getRow(j).getCell(3).setText(tableColumn.getIsNullable());
                table.getRow(j).getCell(4).setText(tableColumn.getColumnComment());
                j++;

            }
            i++;
        }

        // 文档输出
        FileOutputStream out = new FileOutputStream(reportPath + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString()+"_test.docx");
        document.write(out);
        out.close();
        System.out.println("Word生成完成!!!");
    }

   private Map<String,List<MyColumn>> getData(String database){
        System.out.println("数据生成中，请稍等...");
        Map<String,List<MyColumn>> map = new HashMap<String,List<MyColumn>>();
        List<MyTable> tables = userMapper.getTables(database);

        for (MyTable table : tables) {
            List<MyColumn> columns = userMapper.getColumns(database,table.getTableName());
            map.put(table.getTableName(),columns);
        }
        return map;
    }

6.测试结果

@Slf4j
@SpringBootTest
public class UserServiceTest {
    @Autowired
    private UserService userService;

    @Test
    public void testSelectById(){
        try {
            userService.report();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}