Powerdesigner_数据结构_导出excel

'******************************************************************************
Option Explicit
Dim rowsNum
rowsNum = 0
'-----------------------------------------------------------------------------
' Main function
'-----------------------------------------------------------------------------
' Get the current active model
Dim Model
Set Model = ActiveModel
If (Model Is Nothing) Or (Not Model.IsKindOf(PdPDM.cls_Model)) Then
    MsgBox "The current model is not an PDM model."
Else
    Dim tableCount, maxTableNum, tableNames
    tableCount = Model.tables.count
    maxTableNum = tableCount - 1

    tableNames = SortTableList(Model, tableCount, maxTableNum)

    '创建EXCEL APP
    dim beginrow
    DIM EXCEL, colsSheet
    set EXCEL = CREATEOBJECT("Excel.Application")
    EXCEL.workbooks.add(-4167)'添加工作表
    EXCEL.workbooks(1).sheets(1).name ="表结构"
    set colsSheet = EXCEL.workbooks(1).sheets("表结构")
    ' tables properties list
    ShowProperties Model, colsSheet, tableNames, maxTableNum
    EXCEL.workbooks(1).Sheets(1).Select
    EXCEL.visible = true
    colsSheet.Columns(1).ColumnWidth = 20
    colsSheet.Columns(2).ColumnWidth = 20
    colsSheet.Columns(3).ColumnWidth = 40
    colsSheet.Columns(4).ColumnWidth = 10
    colsSheet.Columns(5).ColumnWidth = 10
    '不显示网格线
    EXCEL.ActiveWindow.DisplayGridlines = False

    '*  MsgBox "hello world."
End If

'-----------------------------------------------------------------------------
' Show properties of tables
'-----------------------------------------------------------------------------
Sub ShowProperties(mdl, colsSheet, tableNames, maxTableNum)
    ' Show tables of the current model/package
    rowsNum = 0
    beginrow = rowsNum + 1
    Dim rowIndex
    rowIndex = 3
    ' For each table
    output "show table properties begin"
    'Dim tab
    'For Each tab In mdl.tables
    '   ShowTable tab, colsSheet, rowIndex, tablesSheet
    '   rowIndex = rowIndex +1
    'Next
    'if mdl.tables.count > 0 then
    '    colsSheet.Range("A" & beginrow + 1 & ":A" & rowsNum).Rows.Group
    'end if
    Dim i, pos
    For i = 0 To maxTableNum
        pos = tableNames(1, i)
        ShowTable mdl.tables.Item(pos), colsSheet, rowIndex
        rowIndex = rowIndex +1
    Next

    output "end"
End Sub

'-----------------------------------------------------------------------------
' Show table properties
'-----------------------------------------------------------------------------
Sub ShowTable(tab, colsSheet, rowIndex)
    If IsObject(tab) Then
        Dim rangFlag
        rowsNum = rowsNum + 1
        ' Show properties
        Output "================================"
        colsSheet.cells(rowsNum, 1) =tab.name
        colsSheet.cells(rowsNum, 1).HorizontalAlignment=3
        colsSheet.Range(colsSheet.cells(rowsNum, 1),colsSheet.cells(rowsNum, 5)).Merge

        rowsNum = rowsNum + 1

        colsSheet.cells(rowsNum, 1) = "字段名"
        colsSheet.cells(rowsNum, 2) = "字段类型"
        colsSheet.cells(rowsNum, 3) = "注释"
        colsSheet.cells(rowsNum, 4) = "是否主键"
        colsSheet.cells(rowsNum, 5) = "是否非空"
        '设置边框 字体为10号 加粗
        colsSheet.Range(colsSheet.cells(rowsNum-1, 1), colsSheet.cells(rowsNum, 5)).Borders.LineStyle = "1"
        colsSheet.Range(colsSheet.cells(rowsNum-1, 1), colsSheet.cells(rowsNum, 5)).Font.Size = 10
        colsSheet.Range(colsSheet.cells(rowsNum-1, 1), colsSheet.cells(rowsNum, 5)).Font.Bold = True
        
        Dim col ' running column
        Dim colsNum
        colsNum = 0
        for each col in tab.columns
            rowsNum = rowsNum + 1
            colsNum = colsNum + 1
            colsSheet.cells(rowsNum, 1) = col.code
            colsSheet.cells(rowsNum, 2) = col.datatype
            colsSheet.cells(rowsNum, 3) = col.name
            If col.Primary = true Then
                colsSheet.cells(rowsNum, 4) = "Y"
            Else
                colsSheet.cells(rowsNum, 4) = " "
            End If
            If col.Mandatory = true Then
                colsSheet.cells(rowsNum, 5) = "Y"
            Else
                colsSheet.cells(rowsNum, 5) = " "
            End If
        next
        colsSheet.Range(colsSheet.cells(rowsNum - colsNum + 1, 1), colsSheet.cells(rowsNum, 5)).Borders.LineStyle = "3"
        colsSheet.Range(colsSheet.cells(rowsNum - colsNum + 1, 1), colsSheet.cells(rowsNum, 5)).Font.Size = 10
        rowsNum = rowsNum + 2

        Output "FullDescription: "       + tab.Name
    End If
End Sub

'-----------------------------------------------------------------------------
' Get Table List Sort By Name
'-----------------------------------------------------------------------------
Function SortTableList(mdl, count, maxNum)
    Dim tabs()
    ReDim tabs(2, count)
    Dim tab, i, j, temp

    'output "get table names"
    For i = 0 To maxNum
        temp = mdl.tables.Item(i).number
        tabs(0, i) = temp
        'output temp
    Next

    'output "sort table names"
    For i = 0 To maxNum
        For j = i + 1 To maxNum
            If tabs(0, j) < tabs(0, i) Then
                temp = tabs(0, i)
                tabs(0, i) = tabs(0, j)
                tabs(0, j) = temp
            End If
        Next
    Next

    'output "put table index to sorted names"
    i = 0
    For Each tab In Model.tables
        temp = tab.number
        'output tab.name
        For j = 0 To maxNum
            If (tabs(0, j)) = temp Then
                tabs(1, j) = i
            End If
        Next
        i = i + 1
    Next
    'For i = 0 To maxNum
    '    output tabs(0, i) + "-" & tabs(1, i)
    'Next
    SortTableList = tabs
End Function

保存为.vbs格式即可使用