pdm和excel互转

pdm 获取sql

• 打开powedesigner, 转成想要的数据库

• 查看sql

• 保存sql文件

pdm 转 excel

excel为每个表格一个sheet.

• 打开powerdesigner 软件：

• 点击 工具(Tools) -> 执行命令(Execute Commands) -> 编辑/执行脚本(Edit/Run Script)
  

• 在编辑框内粘贴以下代码，可根据需求自行修改 注意修改excel地址

Option Explicit   
   Dim globelrowsNum   
   globelrowsNum = 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, BOOK, SHEET  
 Set EXCEL = CreateObject("Excel.Application")  
 EXCEL.Visible = True  
 Set BOOK = EXCEL.Workbooks.Add(-4167) '新建工作簿  
  
 '首页名称
 BOOK.Sheets(1).Name = "数据库表结构"  
 Set SHEET = EXCEL.workbooks(1).sheets("数据库表结构")  
  
 ShowProperties Model, SHEET  
 EXCEL.visible = true   
 '设置列宽和自动换行   
 SHEET.Columns(1).ColumnWidth = 10     
 SHEET.Columns(2).ColumnWidth = 30     
 SHEET.Columns(3).ColumnWidth = 20     
  
 SHEET.Columns(1).WrapText =true   
 SHEET.Columns(2).WrapText =true   
 SHEET.Columns(3).WrapText =true   
  
End If  
  
'-----------------------------------------------------------------------------   
' Show properties of tables   
'-----------------------------------------------------------------------------   
Sub ShowProperties(mdl, sheet)   
   ' Show tables of the current model/package   
   globelrowsNum=0   
   beginrow = globelrowsNum+1   
   ' For each table   
   output "begin"   
   Dim tab   
   For Each tab In mdl.tables   
      ShowTable tab,sheet   
   Next   
   if mdl.tables.count > 0 then   
        sheet.Range("A" & beginrow + 1 & ":A" & globelrowsNum).Rows.Group   
   end if   
   output "end"   
End Sub  
  
'-----------------------------------------------------------------------------   
' 数据表查询   
'-----------------------------------------------------------------------------  
Sub ShowTable(tab, sheet)     
   If IsObject(tab) Then   
     Dim rangFlag  
      sheet.cells(1, 1) = "序号"   
      sheet.cells(1, 2) = "表名"  
      sheet.cells(1, 3) = "实体名"  
      '设置边框   
      sheet.Range(sheet.cells(1, 1),sheet.cells(1, 3)).Borders.LineStyle = "1"  
      '设置背景颜色  
      sheet.Range(sheet.cells(1, 1),sheet.cells(1, 3)).Interior.ColorIndex = "16"  
  
      globelrowsNum = globelrowsNum + 1  
      sheet.cells(globelrowsNum+1, 1) = globelrowsNum   
      sheet.cells(globelrowsNum+1, 2) = tab.code  
      sheet.cells(globelrowsNum+1, 3) = tab.name  
      '设置边框  
      sheet.Range(sheet.cells(globelrowsNum+1,1),sheet.cells(globelrowsNum+1,3)).Borders.LineStyle = "1"  
  
      '增加Sheet  
      BOOK.Sheets.Add , BOOK.Sheets(BOOK.Sheets.count)  
      BOOK.Sheets(globelrowsNum+1).Name = tab.name   
  
      Dim shtn  
      Set shtn = EXCEL.workbooks(1).sheets(tab.name)  
		Dim rowsNum 
		rowsNum = 1
      	' Show properties  
      	Output "================================"  
      	shtn.cells(rowsNum, 1) = "数据表中文名"  
	'设置背景颜色
        shtn.Range(shtn.cells(rowsNum, 1),shtn.cells(rowsNum, 2)).Interior.ColorIndex = "15"  
      	shtn.Range(shtn.cells(rowsNum, 1),shtn.cells(rowsNum, 2)).Merge  
      	shtn.cells(rowsNum, 3) =tab.name  
      	shtn.Range(shtn.cells(rowsNum, 3),shtn.cells(rowsNum, 6)).Merge      	
		shtn.Range(shtn.cells(rowsNum, 1),shtn.cells(rowsNum, 6)).Borders.LineStyle = "1"  
		
      	rowsNum = rowsNum + 1  
      	shtn.cells(rowsNum, 1) = "数据表英文名"  
		shtn.Range(shtn.cells(rowsNum, 1),shtn.cells(rowsNum, 2)).Interior.ColorIndex = "15"
      	shtn.Range(shtn.cells(rowsNum, 1),shtn.cells(rowsNum, 2)).Merge  
      	shtn.cells(rowsNum, 3) = tab.code  
      	shtn.Range(shtn.cells(rowsNum, 3),shtn.cells(rowsNum, 6)).Merge  
		shtn.Range(shtn.cells(rowsNum, 1),shtn.cells(rowsNum, 6)).Borders.LineStyle = "1"  

	rowsNum = rowsNum + 1  
      	shtn.cells(rowsNum, 1) = "表格描述"  
		shtn.Range(shtn.cells(rowsNum, 1),shtn.cells(rowsNum, 2)).Interior.ColorIndex = "15"
      	shtn.Range(shtn.cells(rowsNum, 1),shtn.cells(rowsNum, 2)).Merge  
      	shtn.cells(rowsNum, 3) = tab.comment  
      	shtn.Range(shtn.cells(rowsNum, 3),shtn.cells(rowsNum, 6)).Merge  
		shtn.Range(shtn.cells(rowsNum, 1),shtn.cells(rowsNum, 6)).Borders.LineStyle = "1"  

	rowsNum = rowsNum + 2  
      '设置列宽和换行  
       shtn.Columns(1).ColumnWidth = 10     
       shtn.Columns(2).ColumnWidth = 30     
       shtn.Columns(3).ColumnWidth = 20     
       shtn.Columns(4).ColumnWidth = 20  
       shtn.Columns(5).ColumnWidth = 30     
       shtn.Columns(6).ColumnWidth = 20     
       'shtn.Columns(7).ColumnWidth = 20     
       'shtn.Columns(8).ColumnWidth = 20     
  
       shtn.Columns(1).WrapText =true   
       shtn.Columns(2).WrapText =true   
       shtn.Columns(3).WrapText =true  
       shtn.Columns(4).WrapText =true  
       shtn.Columns(5).WrapText =true   
       shtn.Columns(6).WrapText =true  
       'shtn.Columns(7).WrapText =true  
       'shtn.Columns(8).WrapText =true  
  
       '设置列标题  
       shtn.cells(rowsNum, 1) = "序号"   
       shtn.cells(rowsNum, 2) = "字段中文名"   
       shtn.cells(rowsNum, 3) = "字段名"  
       shtn.cells(rowsNum, 4) = "数据类型" 
       shtn.cells(rowsNum, 5) = "注释"  
       shtn.cells(rowsNum, 6) = "默认值"
       'shtn.cells(rowsNum, 7) = "空值"  
       'shtn.cells(rowsNum, 8) = "约束"  
       'shtn.cells(rowsNum, 5) = tab.code  
       'shtn.cells(rowsNum, 6) = tab.Name  
       '设置边框   
       shtn.Range(shtn.cells(rowsNum, 1),shtn.cells(rowsNum, 6)).Borders.LineStyle = "1"  
       'shtn.Range(shtn.cells(rowsNum, 5),shtn.cells(1, 6)).Borders.LineStyle = "1"  
       '设置背景颜色  
       shtn.Range(shtn.cells(rowsNum, 1),shtn.cells(rowsNum, 6)).Interior.ColorIndex = "15"  
       'shtn.Range(shtn.cells(1, 5),shtn.cells(1, 6)).Interior.ColorIndex = "19"  
  
      Dim col ' running column   
      Dim colsNum  
      Dim rNum   
      colsNum = 0  
      rNum = 4   
            for each col in tab.columns   
              rNum = rNum + 1   
              colsNum = colsNum + 1   
  
            shtn.cells(rNum+1, 1) = colsNum   
            shtn.cells(rNum+1, 2) = col.name   
            shtn.cells(rNum+1, 3) = col.code   
            shtn.cells(rNum+1, 4) = col.datatype  
            shtn.cells(rNum+1, 5) = col.comment  
            shtn.cells(rNum+1, 6) = col.defaultvalue  
            'shtn.cells(rNum+1, 7) = col.mandatory  
            'shtn.cells(rNum+1, 8) = col.primary  
            next   
            shtn.Range(shtn.cells(rNum-colsNum+2,1),shtn.cells(rNum+1,6)).Borders.LineStyle = "1"           
            rNum = rNum + 1   
  
            Output "FullDescription: "       + tab.Name  
  
   End If     
End Sub  

excel 转 pdm

excel为每个表格一个sheet.

• 打开powerdesigner 软件：

• 点击 工具(Tools) -> 执行命令(Execute Commands) -> 编辑/执行脚本(Edit/Run Script)
  

• 在编辑框内粘贴以下代码，可根据需求自行修改 注意修改excel地址

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.Workbooks.Open "D:\项目\AIWhale\local\pipeline表.xlsx" 
  x1.Workbooks(1).Worksheets("Sheet1").Activate 
Else
   HaveExcel = False 
End If 
  
a x1, mdl 
  
sub a(x1,mdl) 
dim rwIndex 
dim tableName 
dim colname 
dim table 
dim col 
dim count 
  
'on error Resume Next 
For rwIndex = 1 To 1000 step 1   
    With x1.Workbooks(1).Worksheets("Sheet1")
  'MsgBox "生成数据表结构共计1 ="+CStr(.Cells(2,2).Value ), vbOK + vbInformation, "表" 
   If .Cells(rwIndex, 1).Value = "" Then 
    else   
  If .Cells(rwIndex, 3).Value = "" Then 
    set table = mdl.Tables.CreateNew 
        table.Name = .Cells(rwIndex , 1).Value 
        table.Code = .Cells(rwIndex , 2).Value 
        count = count + 1 
  ELSE          
   If  .Cells(rwIndex, 3).Value = "字段类型" Then
       
   else
    colName = .Cells(rwIndex, 1).Value 
    set col = table.Columns.CreateNew  
   'MsgBox .Cells(rwIndex, 1).Value, vbOK + vbInformation, "列" 
    col.Name = .Cells(rwIndex, 1).Value 
    'MsgBox col.Name, vbOK + vbInformation, "列"
     col.Code = .Cells(rwIndex, 2).Value 
    col.Comment = .Cells(rwIndex,4).Value  
    col.DataType = .Cells(rwIndex, 3).Value 
   End If 
  End If 
  
   End If  
  End With 
Next 
  
MsgBox "生成数据表结构共计" + CStr(count), vbOK + vbInformation, "表" 
 
Exit Sub 
End sub