mysql,oracle导出数据字典excel文件

先上效果图：

汇总sheet页

 点击图中标注的表英文名，可跳转对应表详情的sheet页。

表详情的sheet页

 点击返回首页，返回汇总sheet页。

核心代码就4个SQL

mapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dictionaries.mapper.DictionariesMapper">

    <select id="getTableInfoListMySQL" resultType="com.example.demo.dictionaries.entity.XSSTableInfo">
        select lower(TABLE_NAME) as tableName, TABLE_COMMENT as tableComment
        from information_schema.`TABLES` b
        where b.TABLE_SCHEMA = (select database())
        order by b.TABLE_NAME asc
    </select>

    <select id="getTableColumnListMySQL" resultType="com.example.demo.dictionaries.entity.XSSTableColumnInfo">
        select lower(COLUMN_NAME)                                  as columnName,
               COLUMN_COMMENT                                      as columnComment,
               COLUMN_TYPE                                         as columnType,
               (CASE WHEN IS_NULLABLE = 'NO' THEN '是' ELSE '' END) as required,
               COLUMN_KEY                                          as columnKey
        from information_schema.`COLUMNS`
        where TABLE_SCHEMA = (select database())
          and TABLE_NAME = #{tableName}
        order by ORDINAL_POSITION asc
    </select>

    <select id="getTableInfoListOracle" resultType="com.example.demo.dictionaries.entity.XSSTableInfo">
        select a.TABLE_NAME as tableName,
               b.COMMENTS   as tableComment
        from user_tables a
                 LEFT JOIN user_tab_comments b ON a.TABLE_NAME = b.TABLE_NAME
        ORDER BY a.TABLE_NAME ASC
    </select>

    <select id="getTableColumnListOracle" resultType="com.example.demo.dictionaries.entity.XSSTableColumnInfo">
        select s.COLUMN_NAME AS                columnName,
               s.COMMENTS    AS                columnComment,
               s.DATA_TYPE   AS                columnType,
               s.REQUIRED    AS                required,
               listagg(s.CONSTRAINT_TYPE, '，') within group(order by s.CONSTRAINT_TYPE desc) as columnKey
        FROM (
            select
            a.COLUMN_NAME,
            d.COMMENTS,
            a.DATA_TYPE||'('||a.DATA_LENGTH||')' AS DATA_TYPE,
            (CASE WHEN a.NULLABLE = 'N' THEN '是' ELSE '' END ) AS REQUIRED,
            c.CONSTRAINT_TYPE
            from USER_TAB_COLS a
            LEFT JOIN user_cons_columns b ON a.COLUMN_NAME = b.COLUMN_NAME AND a.TABLE_NAME=b.TABLE_NAME
            LEFT JOIN user_constraints c ON b.CONSTRAINT_NAME = c.CONSTRAINT_NAME
            LEFT JOIN USER_COL_COMMENTS d ON d.COLUMN_NAME = a.COLUMN_NAME AND a.TABLE_NAME=d.TABLE_NAME
            WHERE a.TABLE_NAME =#{tableName}
            GROUP BY a.COLUMN_ID,
            a.COLUMN_NAME,
            d.COMMENTS,
            a.DATA_TYPE||'('||a.DATA_LENGTH||')',
            (CASE WHEN a.NULLABLE = 'N' THEN '是' ELSE '' END ),
            c.CONSTRAINT_TYPE
            ORDER BY a.COLUMN_ID
            ) s
        GROUP BY
            s.COLUMN_NAME,
            s.COMMENTS,
            s.DATA_TYPE,
            s.REQUIRED
    </select>

</mapper>

操作excel提供一些公共函数，其他的应该很容易的，不懂就问

    /**
     * 设置超链接
     * @param cell 单元格
     * @param address 地址
     * @param type 地址类型
     */
    public void setCellLink(XSSFCell cell, String address, HyperlinkType type) {
        XSSFHyperlink link = cell.getSheet().getWorkbook().getCreationHelper().createHyperlink(type);
        link.setAddress(address);
        cell.setHyperlink(link);
    }

    /**
     * 设置超链接
     * @param cell 单元格
     * @param sheetName sheet名称
     */
    public void setCellLinkSheet(XSSFCell cell, String sheetName) {
        setCellLink(cell,"'"+sheetName+"'!A1",HyperlinkType.DOCUMENT);
    }
    /**
     * 设置超链接
     * @param cell 单元格
     * @param sheetName sheet名称
     * @param position 类型 A3 或者 H5
     */
    public void setCellLinkSheet(XSSFCell cell, String sheetName,String position) {
        setCellLink(cell,"#"+sheetName+"!"+position,HyperlinkType.DOCUMENT);
    }


    /**
     * 获取单元格，没有就创建
     * @param sheet sheet 页面
     * @param x  横
     * @param y  竖
     * @return
     */
    public XSSFCell getCell(XSSFSheet sheet, int x, int y){
        XSSFRow row = sheet.getRow(y);
        if(row==null)row=sheet.createRow(y);
        XSSFCell cell = row.getCell(x);
        if(cell==null)cell=row.createCell(x);
        return cell;
    }

    /**
     * 为了避免代码冗余，提供同时设置样式和内容的方法
     */
    public void setCellValueAndStyle(XSSFCell cell,String value,XSSFCellStyle style){
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }


    /**
     * 设置合并单元格的样式
     */
    public void setRangeCellStyle(XSSFSheet sheet,CellRangeAddress range , XSSFCellStyle style){
        setRangeCellStyle(sheet,style,range.getFirstRow(),range.getLastRow(),range.getFirstColumn(),range.getLastColumn());
    }
    /**
     * 设置指定区域的单元格样式
     */
    public void setRangeCellStyle(XSSFSheet sheet, XSSFCellStyle style,int firstRow,int lastRow,int  firstColumn, int lastColumn ){
        for(int i=firstRow;i<=lastRow;i++){
            for(int j=firstColumn;j<=lastColumn;j++){
                getCell(sheet,j,i).setCellStyle(style);
            }

        }
    }