PowerDesigner一鍵導(dǎo)出數(shù)據(jù)庫設(shè)計表結(jié)構(gòu)

1、背景

幫朋友弄下數(shù)據(jù)庫設(shè)計，又用到PowerDesigner，因為還要數(shù)據(jù)庫設(shè)計文檔（word），還想著怎么把數(shù)據(jù)庫結(jié)構(gòu)導(dǎo)出呢，發(fā)現(xiàn)以前弄過，一鍵導(dǎo)出，很方便，分享下，祝好運！

2、解決方案

2.1 執(zhí)行窗口

依次點擊，Tools->Execute Commands ->Edit/Run Script

2.2 執(zhí)行腳本

第一次執(zhí)行，復(fù)制腳本到空白編輯處，點擊三角執(zhí)行按鈕就可以了，執(zhí)行完成后可以保存腳本，以便下次用，下次直接打開文件執(zhí)行就可以了。

2.3 ?執(zhí)行效果

（1）點擊執(zhí)行后，會首先生成臨時excel文件。

（2）打開excel后，有兩個sheet，一個sheet是表目錄，內(nèi)容為全部表名稱及鏈接；一個sheet所有表結(jié)構(gòu)信息；

2.4 腳本

'******************************************************************************
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
??????'?Get?the?tables?collection
??????'創(chuàng)建EXCEL?APP
??????dim?beginrow
??????DIM?EXCEL,?SHEET,?SHEETLIST
??????set?EXCEL?=?CREATEOBJECT("Excel.Application")
??????EXCEL.workbooks.add(-4167)'添加工作表
??????EXCEL.workbooks(1).sheets(1).name?="表結(jié)構(gòu)"
??????set?SHEET?=?EXCEL.workbooks(1).sheets("表結(jié)構(gòu)")
??????
??????EXCEL.workbooks(1).sheets.add
??????EXCEL.workbooks(1).sheets(1).name?="目錄"
??????set?SHEETLIST?=?EXCEL.workbooks(1).sheets("目錄")
??????ShowTableList?Model,SHEETLIST

??????ShowProperties?Model,?SHEET,SHEETLIST
??????
??????
??????EXCEL.workbooks(1).Sheets(2).Select
??????EXCEL.visible?=?true
??????'設(shè)置列寬和自動換行
??????sheet.Columns(1).ColumnWidth?=?20?
??????sheet.Columns(2).ColumnWidth?=?20?
??????sheet.Columns(3).ColumnWidth?=?20?
??????sheet.Columns(4).ColumnWidth?=?40?
??????sheet.Columns(5).ColumnWidth?=?10?
??????sheet.Columns(6).ColumnWidth?=?10?
??????sheet.Columns(1).WrapText?=true
??????sheet.Columns(2).WrapText?=true
??????sheet.Columns(4).WrapText?=true
??????'不顯示網(wǎng)格線
??????EXCEL.ActiveWindow.DisplayGridlines?=?False
??????
??????
?End?If
'-----------------------------------------------------------------------------
'?Show?properties?of?tables
'-----------------------------------------------------------------------------
Sub?ShowProperties(mdl,?sheet,SheetList)
???'?Show?tables?of?the?current?model/package
???rowsNum=0
???beginrow?=?rowsNum+1
???Dim?rowIndex?
???rowIndex=3
???'?For?each?table
???output?"begin"
???Dim?tab
???For?Each?tab?In?mdl.tables
??????ShowTable?tab,sheet,rowIndex,sheetList
??????rowIndex?=?rowIndex?+1
???Next
???if?mdl.tables.count?>?0?then
????????sheet.Range("A"?&?beginrow?+?1?&?":A"?&?rowsNum).Rows.Group
???end?if
???output?"end"
End?Sub
'-----------------------------------------------------------------------------
'?Show?table?properties
'-----------------------------------------------------------------------------
Sub?ShowTable(tab,?sheet,rowIndex,sheetList)
???If?IsObject(tab)?Then
?????Dim?rangFlag
?????rowsNum?=?rowsNum?+?1
??????'?Show?properties
??????Output?"================================"
??????sheet.cells(rowsNum,?1)?=tab.name
??????sheet.cells(rowsNum,?1).HorizontalAlignment=3
??????sheet.cells(rowsNum,?2)?=?tab.code
??????'sheet.cells(rowsNum,?5).HorizontalAlignment=3
??????'sheet.cells(rowsNum,?6)?=?""
??????'sheet.cells(rowsNum,?7)?=?"表說明"
??????sheet.cells(rowsNum,?3)?=?tab.comment
??????'sheet.cells(rowsNum,?8).HorizontalAlignment=3
??????sheet.Range(sheet.cells(rowsNum,?3),sheet.cells(rowsNum,?7)).Merge
??????'設(shè)置超鏈接，從目錄點擊表名去查看表結(jié)構(gòu)
??????'字段中文名????字段英文名????字段類型????注釋????是否主鍵????是否非空????默認值
??????sheetList.Hyperlinks.Add?sheetList.cells(rowIndex,2),?"","表結(jié)構(gòu)"&"!B"&rowsNum
??????rowsNum?=?rowsNum?+?1
??????sheet.cells(rowsNum,?1)?=?"字段中文名"
??????sheet.cells(rowsNum,?2)?=?"字段英文名"
??????sheet.cells(rowsNum,?3)?=?"字段類型"
??????sheet.cells(rowsNum,?4)?=?"注釋"
??????sheet.cells(rowsNum,?5)?=?"是否主鍵"
??????sheet.cells(rowsNum,?6)?=?"是否非空"
??????sheet.cells(rowsNum,?7)?=?"默認值"
??????'設(shè)置邊框
??????sheet.Range(sheet.cells(rowsNum-1,?1),sheet.cells(rowsNum,?7)).Borders.LineStyle?=?"1"
??????'sheet.Range(sheet.cells(rowsNum-1,?4),sheet.cells(rowsNum,?9)).Borders.LineStyle?=?"1"
??????'字體為10號
??????sheet.Range(sheet.cells(rowsNum-1,?1),sheet.cells(rowsNum,?7)).Font.Size=10
????????????Dim?col?'?running?column
????????????Dim?colsNum
????????????colsNum?=?0
??????for?each?col?in?tab.columns
????????rowsNum?=?rowsNum?+?1
????????colsNum?=?colsNum?+?1
??????????sheet.cells(rowsNum,?1)?=?col.name
????????'sheet.cells(rowsNum,?3)?=?""
??????????'sheet.cells(rowsNum,?4)?=?col.name
??????????sheet.cells(rowsNum,?2)?=?col.code
??????????sheet.cells(rowsNum,?3)?=?col.datatype
????????sheet.cells(rowsNum,?4)?=?col.comment
??????????If?col.Primary?=?true?Then
????????sheet.cells(rowsNum,?5)?=?"Y"?
????????Else
????????sheet.cells(rowsNum,?5)?=?"?"?
????????End?If
????????If?col.Mandatory?=?true?Then
????????sheet.cells(rowsNum,?6)?=?"Y"?
????????Else
????????sheet.cells(rowsNum,?6)?=?"?"?
????????End?If
????????sheet.cells(rowsNum,?7)?=??col.defaultvalue
??????next
??????sheet.Range(sheet.cells(rowsNum-colsNum+1,1),sheet.cells(rowsNum,7)).Borders.LineStyle?=?"3"???????
??????'sheet.Range(sheet.cells(rowsNum-colsNum+1,4),sheet.cells(rowsNum,9)).Borders.LineStyle?=?"3"
??????sheet.Range(sheet.cells(rowsNum-colsNum+1,1),sheet.cells(rowsNum,7)).Font.Size?=?10
??????rowsNum?=?rowsNum?+?2
??????
??????Output?"FullDescription:?"???????+?tab.Name
???End?If
???
End?Sub
'-----------------------------------------------------------------------------
'?Show?List?Of?Table
'-----------------------------------------------------------------------------
Sub?ShowTableList(mdl,?SheetList)
???'?Show?tables?of?the?current?model/package
???Dim?rowsNo
???rowsNo=1
???'?For?each?table
???output?"begin"
???SheetList.cells(rowsNo,?1)?=?"主題"
???SheetList.cells(rowsNo,?2)?=?"表中文名"
???SheetList.cells(rowsNo,?3)?=?"表英文名"
???SheetList.cells(rowsNo,?4)?=?"表說明"
???rowsNo?=?rowsNo?+?1
???SheetList.cells(rowsNo,?1)?=?mdl.name
???Dim?tab
???For?Each?tab?In?mdl.tables
?????If?IsObject(tab)?Then
?????????rowsNo?=?rowsNo?+?1
??????SheetList.cells(rowsNo,?1)?=?""
??????SheetList.cells(rowsNo,?2)?=?tab.name
??????SheetList.cells(rowsNo,?3)?=?tab.code
??????SheetList.cells(rowsNo,?4)?=?tab.comment
?????End?If
???Next
????SheetList.Columns(1).ColumnWidth?=?20?
??????SheetList.Columns(2).ColumnWidth?=?20?
??????SheetList.Columns(3).ColumnWidth?=?30?
?????SheetList.Columns(4).ColumnWidth?=?60?
???output?"end"
End?Sub

更多信息請關(guān)注公眾號：「軟件老王」，關(guān)注不迷路，軟件老王和他的IT朋友們，分享一些他們的技術(shù)見解和生活故事。