Customized Excel table data structure import and export to PowerDesigner
- 1. Excel table style.
- 2. Open PowerDesigner and create a physical model (Physical Data Model).
- 3. In the PowerDesigner menu bar, click “Tools ->Excute Commands->Edit/Run Script..” in turn.
-
- Function shortcut key
- Import script:
- Insert links and images
- 4. Export the table structure from PowerDesigner to Excel, which is the same as step 3.
1. Excel table style.
2. Open PowerDesigner and create a physical model (Physical Data Model).
3. In the PowerDesigner menu bar, click “Tools -> Excute Commands -> Edit/Run Script…”.
Function shortcut keys
Run Script: Ctrl/Shift + X
Import Script:
Option Explicit Dim mdl ' the current model Set mdl = ActiveModel If (mdl Is Nothing) Then MsgBox "There is no Active Model" End If Dim Have Excel 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:\sheet1.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 "Total data table structure 1 =" + CStr(.Cells(2,2).Value ), vbOK + vbInformation, "table" If .Cells(rwIndex, 1).Value = "" Then rwIndex = rwIndex + 1 IF .Cells(rwIndex, 1).Value = "" then Exit For end if End If If .Cells(rwIndex, 3).Value = "" Then set table = mdl.Tables.CreateNew table.Name = .Cells(rwIndex , 1).Value table.Code = .Cells(rwIndex, 2).Value rwIndex = rwIndex + 1 count = count + 1 Else colName = .Cells(rwIndex, 1).Value set col = table.Columns.CreateNew 'MsgBox .Cells(rwIndex, 1).Value, vbOK + vbInformation, "Columns" col.Name = .Cells(rwIndex, 1).Value 'MsgBox col.Name, vbOK + vbInformation, "column" col.Code = .Cells(rwIndex, 2).Value col.Comment = .Cells(rwIndex,4).Value col.DataType = .Cells(rwIndex, 3).Value End If End With Next MsgBox "Generate the total data table structure" + CStr(count), vbOK + vbInformation, "Table" Exit Sub End sub
Among them, d:\sheet1.xlsx is the path of your Excel data sheet, which can be adjusted according to your own directory structure;
Insert links and images
4. Export the table structure from PowerDesigner to Excel, which is the same as step 3.
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 'Create EXCEL APP dim beginrow DIM EXCEL, SHEET set EXCEL = CREATE OBJECT("Excel.Application") EXCEL.workbooks.add(-4167)'Add worksheet EXCEL.workbooks(1).sheets(1).name="sheet1" set sheet = EXCEL.workbooks(1).sheets("sheet1") ShowProperties Model, SHEET EXCEL.visible = true 'Set column width and word wrap sheet.Columns(1).ColumnWidth = 20 sheet.Columns(2).ColumnWidth = 40 sheet.Columns(3).ColumnWidth = 30 sheet.Columns(4).ColumnWidth = 50 'sheet.Columns(5).ColumnWidth = 20 'sheet.Columns(6).ColumnWidth = 15 sheet.Columns(1).WrapText =true sheet.Columns(2).WrapText =true sheet.Columns(4).WrapText =true End If '------------------------------------------------ ----------------------------- ' Show properties of tables '------------------------------------------------ ----------------------------- Sub ShowProperties(mdl, sheet) ' Show tables of the current model/package rowsNum=0 beginrow = rowsNum + 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" & amp; beginrow + 1 & amp; ":A" & amp; rowsNum).Rows.Group end if output "end" End Sub '------------------------------------------------ ----------------------------- ' Show table properties '------------------------------------------------ ----------------------------- Sub ShowTable(tab, sheet) If IsObject(tab) Then Dim rang Flag rowsNum = rowsNum + 1 'Show properties Output "===================================" sheet.cells(rowsNum, 1) = tab.name sheet. cells(rowsNum, 2) = tab. code sheet.cells(rowsNum, 3) = "" 'sheet.cells(rowsNum, 4) = "table name" 'sheet.cells(rowsNum, 5) = tab.code 'sheet.Range(sheet.cells(rowsNum, 5),sheet.cells(rowsNum, 6)).Merge rowsNum = rowsNum + 1 'sheet.cells(rowsNum, 1) = "property name" 'sheet.cells(rowsNum, 2) = "Description" 'sheet.cells(rowsNum, 3) = "" sheet.cells(rowsNum, 1) = "Field Chinese name" sheet.cells(rowsNum, 2) = "field name" sheet.cells(rowsNum, 3) = "field type" sheet.cells(rowsNum, 4) = "Comment" 'set border sheet.Range(sheet.cells(rowsNum-1, 1),sheet.cells(rowsNum, 4)).Borders.LineStyle = "1" 'sheet.Range(sheet.cells(rowsNum-1, 4),sheet.cells(rowsNum, 6)).Borders.LineStyle = "1" 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, 2) = col.comment 'sheet.cells(rowsNum, 3) = "" sheet.cells(rowsNum, 1) = col.name sheet.cells(rowsNum, 2) = col.code sheet.cells(rowsNum, 3) = col.datatype sheet.cells(rowsNum, 4) = col.comment next sheet.Range(sheet.cells(rowsNum-colsNum + 1,1),sheet.cells(rowsNum,4)).Borders.LineStyle = "2" 'sheet.Range(sheet.cells(rowsNum-colsNum + 1,4),sheet.cells(rowsNum,6)).Borders.LineStyle = "2" rowsNum = rowsNum + 1 Output "FullDescription: " + tab.Name End If End Sub
Go to https://blog.csdn.net/lin521lh/article/details/78282774