PowerDesigner generates an Excel version of the database file

By Power Designer

To convert the database design to an excel version, Baidu will publish a script file, which is easy to use and find out, keep a memorial

In pd, shift+ctrl+X, open the script to run, the script is as follows, and a copy of the attachment is also left:

[vb] view plain Copy

< span class="tracking-ad">in View code piece on CODE Derived to my code piece

  1. ‘***** ************************************************** ***********************
  2. ‘*File:pdm2excel.txt
  3. ‘*Title: pdm export to excel
  4. ‘* Purpose: To export the tables and columns to Excel
  5. ‘* Model: Physical Data Model
  6. ‘*Objects: Table, Column, View
  7. ‘*Author: ziyan
  8. ‘* Created: 2012-05-03
  9. ‘* Version: 1.0
  10. ‘************************************************ ******************************
  11. OptionExplicit< /span>
  12. DimrowsNum
  13. < span style="margin:0px; padding:0px; border:none; color:black; background-color:inherit"> rowsNum= 0
  14. ‘—————————- ————————————————-< /span>
  15. ‘Main function
  16. ‘————— ————————————————– ———— li>
  17. ‘Get the current active model
  18. < span style="margin:0px; padding:0px; border:none; color:black; background-color:inherit">DimModel Model< /span>
  19. SetModel= ActiveModel
  20. If(ModelIs< span style="margin:0px; padding:0px; border:none; background-color:inherit">Nothing)​< /span>Or (Not Model.IsKindOf(PdPDM.cls_Model))Then
  21. MsgBox“The current model is not an PDM model.” span>
  22. Else
  23. ‘Get the tables collection
  24. ‘Create EXCEL APP
  25. dimbeginrow
  26. DIM EXCEL, SHEET
  27. set EXCEL= CREATEOBJECT (“Excel.Application”)
  28. EXCEL.workbooks.add(-4167)‘Add worksheet
  29. EXCEL.workbooks(1).sheets(1).name=“test”
  30. set sheet = EXCEL.workbooks( 1).sheets(“test”)
  32. < span style="margin:0px; padding:0px; border:none; color:black; background-color:inherit"> ShowProperties Model, SHEET
  33. EXCEL.visible= true
  34. ‘Set column width and auto Line break
  35. sheet.Columns(1) .ColumnWidth= 20
  36. sheet.Columns (2).ColumnWidth= 40
  37. sheet.Columns(4).ColumnWidth= 20
  38. sheet.Columns(5).ColumnWidth= 20 20 /span>< /li>
  39. sheet.Columns(6).ColumnWidth= 15
  40. < span style="margin:0px; padding:0px; border:none; color:black; background-color:inherit">sheet.Columns(1).WrapText=true
  41. sheet.Columns(2).WrapText=true
  42. sheet.Columns(4).WrapText=true
  43. End< span class="keyword" style="margin:0px; padding:0px; border:none; color:rgb(0,102,153); font-weight:bold; background-color:inherit">If
  44. ‘————————– ————————————————– –
  45. ‘Show properties of tables
  46. ‘—————— ————————————————– ———

    47. < li class="alt" style="border-style:none none none solid; border-left-width:3px; border-left-color:rgb(108,226,108); list-style:decimal-leading-zero outside; color: inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> Sub< span style="margin:0px; padding:0px; border:none; background-color:inherit">ShowProperties(mdl,sheet)

  47. ‘Show tables of the current model/package
  48. rowsNum=0
  49.    beginrow = rowsNum+1  
  50.    ‘ For each table
  51.    output “begin”  
  52.    Dim tab  
  53.    For Each tab In mdl.tables  
  54.       ShowTable tab,sheet  
  55.    Next  
  56.    if mdl.tables.count > 0 then  
  57.         sheet.Range(“A” & beginrow + 1 & “:A” & rowsNum).Rows.Group  
  58.    end if  
  59.    output “end”  
  60. End Sub  
  61. ‘—————————————————————————–  
  62. ‘ Show table properties  
  63. ‘—————————————————————————–  
  64. Sub ShowTable(tab, sheet)  
  65.    If IsObject(tab) Then  
  66.      Dim rangFlag  
  67.      rowsNum = rowsNum + 1  
  68.       ‘ Show properties  
  69.       Output “================================”  
  70.       sheet.cells(rowsNum, 1) = “实体名”  
  71.       sheet.cells(rowsNum, 2) =tab.name  
  72.       sheet.cells(rowsNum, 3) = “”  
  73.       sheet.cells(rowsNum, 4) = “表名”  
  74.       sheet.cells(rowsNum, 5) = tab.code  
  75.       sheet.Range(sheet.cells(rowsNum, 5),sheet.cells(rowsNum, 6)).Merge  
  76.       rowsNum = rowsNum + 1  
  77.       sheet.cells(rowsNum, 1) = “属性名”  
  78.       sheet.cells(rowsNum, 2) = “说明”  
  79.       sheet.cells(rowsNum, 3) = “”  
  80.       sheet.cells(rowsNum, 4) = “字段中文名”  
  81.       sheet.cells(rowsNum, 5) = “字段名”  
  82.       sheet.cells(rowsNum, 6) = “字段类型”  
  83.       ‘设置边框  
  84.       sheet.Range(sheet.cells(rowsNum-1, 1),sheet.cells(rowsNum, 2)).Borders.LineStyle = “1”  
  85.       sheet.Range(sheet.cells(rowsNum-1, 4),sheet.cells(rowsNum, 6)).Borders.LineStyle = “1”  
  86. Dim col ‘ running column  
  87. Dim colsNum  
  88. < span style="margin:0px; padding:0px; border:none; color:black; background-color:inherit">colsNum = 0  
  89.       for each col in tab.columns  
  90.         rowsNum = rowsNum + 1  
  91.         colsNum = colsNum + 1  
  92.       sheet.cells(rowsNum, 1) = col.name  
  93.       sheet.cells(rowsNum, 2) = col.comment  
  94.         sheet.cells(rowsNum, 3) = “”  
  95.       sheet.cells(rowsNum, 4) = col.name  
  96.       sheet.cells(rowsNum, 5) = col.code  
  97.       sheet.cells(rowsNum, 6) = col.datatype  
  98.       next  
  99.       sheet.Range(sheet.cells(rowsNum-colsNum+1,1),sheet.cells(rowsNum,2)).Borders.LineStyle = “2”         
  100.       sheet.Range(sheet.cells(rowsNum-colsNum+1,4),sheet.cells(rowsNum,6)).Borders.LineStyle = “2”  
  101.       rowsNum = rowsNum + 1  
  102.         
  103.       Output “FullDescription: “       + tab.Name  
  104.    End If  
  105. End Sub

[vb]  view plain  copy

  在CODE上查看代码片 派生到我的代码片

  1. ‘******************************************************************************  
  2. ‘* File:     pdm2excel.txt  
  3. ‘* Title:    pdm export to excel  
  4. ‘* Purpose:  To export t he tables and columns to Excel  
  5. ‘* Model:    Physical Data Model  
  6. ‘* Objects:  Table, Column, View  
  7. ‘* Author:   ziyan  
  8. ‘* Created:  2012-05-03  
  9. ‘* Version:  1.0  
  10. ‘******************************************************************************  
  11. Option Explicit  
  12.    Dim rowsNum  
  13.    rowsNum = 0  
  14. ‘—————————————————————————–  
  15. ‘ Main function  
  16. ‘—————————– ————————————————  
  17. ‘ Get the current active model  
  18. Dim Model  
  19. Set Model = ActiveModel  
  20. If (Model Is Nothing) Or (Not Model.IsKindOf(PdPDM.cls_Model)) Then  
  21.   MsgBox “The current model is not an PDM model.”  
  22. Else  
  23.  ‘ Get the tables collection  
  24.  ‘创建EXCEL APP  
  25.  dim beginrow  
  26.  DIM EXCEL, SHEET  
  27.  set EXCEL = CREATEOBJECT(“Excel.Application”)  
  28.  EXCEL.workbooks.add(-4167)‘添加工作表  
  29.  EXCEL.workbooks(1).sheets(1).name =“test”  
  30.  set sheet = EXCEL.workbooks(1 ).sheets(“test”)  
  31.    
  32.  ShowProperties Model, SHEET  
  33.  EXCEL.visible = true  
  34.  ‘设置列宽和自动换行  
  35.  sheet.Columns(1).ColumnWidth = 20   
  36.  sheet.Columns(2).ColumnWidth = 40   
  37.  sheet.Columns(4).ColumnWidth = 20   
  38.  sheet.Columns(5).ColumnWidth = 20   
  39.  sheet.Columns(6).ColumnWidth = 15   
  40.  sheet.Columns(1).WrapText =true  
  41.  sheet.Columns(2).WrapText =true  
  42.  sheet.Columns(4).WrapText =true  
  43.  End If  
  44. ‘—————————————————————————–  
  45. ‘ Show properties of tables  
  46. ‘—————————————————————————–  
  47. Sub ShowProperties(mdl, sheet)  
  48.    ‘ Show tables of the current model/package  
  49.    rowsNum=0  
  50.    beginrow = rowsNum+1  
  51.    ‘ For each table  
  52.    output “begin”  
  53.    Dim tab  
  54.    For Each tab In mdl.tables  
  55.       ShowTable tab,sheet  
  56.    Next  
  57.    if mdl.tables.count > 0 then  
  58.         sheet.Range(“A” & beginrow + 1 & “:A” & rowsNum).Rows.Group  
  59.    end if  
  60.    output “end”  
  61. End Sub  
  62. ‘——————————————- ———————————-  
  63. ‘ Show table properties  
  64. ‘—————————————————————————–  
  65. Sub ShowTable(tab, sheet)  
  66.    If IsObject(tab) Then  
  67.      Dim rangFlag  
  68.      rowsNum = rowsNum + 1  
  69.       ‘ Show properties  
  70.       Output “================================”  
  71.       sheet.cells(rowsNum, 1) = “实体名”  
  72.       sheet.cells(rowsNum, 2) =tab.name  
  73.       sheet.cells(rowsNum, 3) = “”  
  74.       sheet.cells(rowsNum, 4) = “表名”  
  75.       sheet.cells(rowsNum, 5) = tab.code  
  76.       sheet.Range(sheet.cells(rowsNum, 5),sheet.cells(rowsNum, 6)).Merge  
  77.       rowsNum = rowsNum + 1  
  78.       sheet.cells(rowsNum, 1) = “属性名”  
  79.       sheet.cells(rowsNum, 2) = “说明”  
  80.       sheet.cells(rowsNum, 3) = “”  
  81.       sheet.cells(rowsNum, 4) = “字段中文名”  
  82.       sheet.cells(rowsNum, 5) = “字段名”  
  83.       sheet.cells(rowsNum, 6) = “字段类型”  
  84.       ‘设置边框  
  85.       sheet.Range(sheet.cells(rowsNum-1, 1),sheet.cells(rowsNum, 2)).Borders.LineStyle = “1”  
  86.       sheet.Range(sheet.cells(rowsNum-1, 4),sheet.cells(rowsNum, 6)).Borders.LineStyle = “1”  
  87. Dim col ‘ running column  
  88. Dim colsNum  
  89. colsNum = 0  
  90.       for each col in tab.columns  
  91.         rowsNum = rowsNum + 1  
  92.         colsNum = colsNum + 1  
  93.       sheet.cells(rowsNum, 1) = col.name  
  94.       sheet.cells(rowsNum, 2) = col.comment  
  95.         sheet.cells(rowsNum, 3) = “”  
  96.       sheet.cells(rowsNum, 4) = col.name  
  97.       sheet.cells(rowsNum, 5) = col.code  
  98.       sheet.cells(rowsNum, 6) = col.datatype  
  99.       next  
  100.       sheet.Range(sheet.cells(rowsNum-colsNum+1,1),sheet.cells(rowsNum,2)).Borders.LineStyle = “2”         
  101.       sheet.Range(sheet.cells(rowsNum-colsNum+1,4),sheet.cells(rowsNum,6)).Borders.LineStyle = “2”  
  102.       rowsNum = rowsNum + 1  
  103.         
  104.       Output “FullDescription: “       + tab.Name  
  105.    End If  
  106. End Sub

[vb]  view plain  copy

  在CODE上查看代码片 派生到我的代码片

[vb]  view plain  copy

  在CODE上查看代码片 派生到我的代码片

, , , , ,

Leave a Comment

Your email address will not be published.