PowerDesigner 技巧性操作

作者: 甯缺毋滥 | 来源:发表于2021-01-22 15:27 被阅读0次

PowerDesigner 技巧性操作
PowerDesigner01 基础概念
【转】PowerDesigner导入SQL注释丢失问题解决
PowerDesigner运行自定义VBS脚本，复制Name到C
使用powerdesigner 导入sql文件查看er模型图和生
powerdesigner name设置成comment
PowerDesigner的下载及安装
201027:PowerDesigner使用-in,exists
免费的国产数据库模型建模工具，PowerDesigner替代方案
Powerdesigner 表没注释和字段没注释的设置

一.pdm 导出 xlsx

ctrl+shift+x 弹出命令框,输入

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
      '创建EXCEL APP
      dim beginrow
      DIM EXCEL, SHEET, SHEETLIST
      set EXCEL = CREATEOBJECT("Excel.Application")
      EXCEL.workbooks.add(-4167)'添加工作表
      EXCEL.workbooks(1).sheets(1).name ="表结构"
      set SHEET = EXCEL.workbooks(1).sheets("表结构")
      
      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
      '设置列宽和自动换行
      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
      '不显示网格线
      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
      '设置超链接，从目录点击表名去查看表结构
      '字段中文名    字段英文名    字段类型    注释    是否主键    是否非空    默认值
      sheetList.Hyperlinks.Add sheetList.cells(rowIndex,2), "","表结构"&"!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) = "默认值"
      '设置边框
      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

二.pdm表的一些操作

2.1.table中的把comment 覆盖name 方法如下

Option   Explicit 
ValidationMode   =   True 
InteractiveMode   =   im_Batch
Dim   mdl   '   the   current   model
'   get   the   current   active   model 
Set   mdl   =   ActiveModel 
If   (mdl   Is   Nothing)   Then 
      MsgBox   "There   is   no   current   Model " 
ElseIf   Not   mdl.IsKindOf(PdPDM.cls_Model)   Then 
      MsgBox   "The   current   model   is   not   an   Physical   Data   model. " 
Else 
      ProcessFolder   mdl 
End   If
Private   sub   ProcessFolder(folder) 
On Error Resume Next
      Dim   Tab   'running     table 
      for   each   Tab   in   folder.tables 
            if   not   tab.isShortcut   then 
                  tab.name   =   tab.comment
                  Dim   col   '   running   column 
                  for   each   col   in   tab.columns 
                  if col.comment="" then
                  else
                        col.name=   col.comment 
                  end if
                  next 
            end   if 
      next
      Dim   view   'running   view 
      for   each   view   in   folder.Views 
            if   not   view.isShortcut   then 
                  view.name   =   view.comment 
            end   if 
      next
      '   go   into   the   sub-packages 
      Dim   f   '   running   folder 
      For   Each   f   In   folder.Packages 
            if   not   f.IsShortcut   then 
                  ProcessFolder   f 
            end   if 
      Next 
end   sub

2.2.将name覆盖comment 方法如下

Option   Explicit 
ValidationMode   =   True 
InteractiveMode   =   im_Batch
Dim   mdl   '   the   current   model
'   get   the   current   active   model 
Set   mdl   =   ActiveModel 
If   (mdl   Is   Nothing)   Then 
      MsgBox   "There   is   no   current   Model " 
ElseIf   Not   mdl.IsKindOf(PdPDM.cls_Model)   Then 
      MsgBox   "The   current   model   is   not   an   Physical   Data   model. " 
Else 
      ProcessFolder   mdl 
End   If
'   This   routine   copy   name   into   comment   for   each   table,   each   column   and   each   view 
'   of   the   current   folder 
Private   sub   ProcessFolder(folder) 
      Dim   Tab   'running     table 
      for   each   Tab   in   folder.tables 
            if   not   tab.isShortcut   then 
                  tab.comment   =   tab.name 
                  Dim   col   '   running   column 
                  for   each   col   in   tab.columns 
                        col.comment=   col.name 
                  next 
            end   if 
      next
      Dim   view   'running   view 
      for   each   view   in   folder.Views 
            if   not   view.isShortcut   then 
                  view.comment   =   view.name 
            end   if 
      next
      '   go   into   the   sub-packages 
      Dim   f   '   running   folder 
      For   Each   f   In   folder.Packages 
            if   not   f.IsShortcut   then 
                  ProcessFolder   f 
            end   if 
      Next 
end   sub

三.xlsx转pdm

准备工作,需要把xlsx的格式处理一下(A1:表名,B1:表显示名,C1:表说明)

说明:A1对应的表名需要和下方的脚本中的表名对应

xlsx格式,看第一行.png

Option Explicit
 
Dim mdl ' the current model
Set mdl = ActiveModel
If (mdl Is Nothing) Then
   MsgBox "There is no Active Model"
End If
Dim HaveExcel
Dim RQ
RQ = vbYes 'MsgBox("Is Excel Installed on your machine ?", vbYesNo + vbInformation, "Confirmation")
If RQ = vbYes Then
   HaveExcel = True
   'Open & Create Excel Document
   Dim x1  
   Set x1 = CreateObject("Excel.Application")
   x1.Application.Visible = True
   x1.Workbooks.Open "C:\jsxm.xlsx"'excel所在位置
   x1.Workbooks(1).Worksheets("Sheet1").Activate'活动的sheet
Else
   HaveExcel = False
End If
a x1, mdl
sub a(x1, mdl)
dim rwIndex
dim tableName
dim colname
dim table
dim col
dim count
dim  shtIdx
for shtIdx=1 to x1.Workbooks(1).Worksheets.Count    
    on error Resume Next
    With x1.Workbooks(1).Worksheets(shtIdx)
        If .Cells(1, 1).Value = "if_t_buildProject" Then'判断表
            count = count + 1                   
            set table = mdl.Tables.CreateNew
            table.Code = .Cells(1, 1).Value'表编码（对应excel的位置）
            table.Name = .Cells(1, 2).Value'表名
            table.Comment = .Cells(1, 3).Value'描述
        
            For rwIndex = 3 To 255'从第三列开始                   
                If .Cells(rwIndex, 3).Value <> "" Then'类型不为空的
                    set col = table.Columns.CreateNew
                    col.Name = .Cells(rwIndex, 1).Value'字段名
                    col.Code = .Cells(rwIndex, 2).Value'字段编码
                    col.Comment = .Cells(rwIndex, 8).Value'字段描述所在列
                    col.DataType =  .Cells(rwIndex, 3).Value'数据类型
                                                 
        End If
      Next
    End If
  End With
Next
x1.Application.Quit
Set x1=Nothing
MsgBox "生成数据表结构共计 " + CStr(count), vbOK + vbInformation, "表"
End sub

PowerDesigner 技巧性操作
一.pdm 导出 xlsx ctrl+shift+x 弹出命令框,输入二.pdm表的一些操作 2.1.table...
PowerDesigner01 基础概念
PowerDesigner思维导图 PowerDesigner概述 PowerDesigner模型 PowerDe...
【转】PowerDesigner导入SQL注释丢失问题解决
操作步骤放入的代码：转载链接 PowerDesigner导入mysql文件注释丢失问题解决
PowerDesigner运行自定义VBS脚本，复制Name到C
1.说明 PowerDesigner支持自定义一些命令与操作，通过编写VBS(Visual Basic Scrip...
使用powerdesigner 导入sql文件查看er模型图和生
一、[endif]使用powerdesigner导入sql文件查看er模型图 1、启动PowerDesigner，...
powerdesigner name设置成comment
powerdesigner 把name设置成comment； 1：PowerDesigner->Tools->Ex...
PowerDesigner的下载及安装
一、下载PowerDesigner 1. PowerDesigner的下载路径（1）https://pan.ba...
201027:PowerDesigner使用-in,exists
一. PowerDesigner创建表打开PowerDesigner，然后点击File-->New Model然...
免费的国产数据库模型建模工具，PowerDesigner替代方案
一款开源免费的数据库模型建模工具，支持Windows,Mac,Linux等操作系统，是PowerDesigner免...
Powerdesigner 表没注释和字段没注释的设置
powerdesigner 表没注释参考：powerdesigner 生成的表结构没有注释[https://ww...