我们经常会在excel中设计整理表结构，整理完需要导入到Powerdesigner中，可以通过以下脚本来实现快速，具体操作方法：
打开PowerDesigner，新建模型，点击Tools|Execute Commands|Edit/Run Script菜单或按下快捷键Ctrl + Shift + X打开脚本窗口，输入示例VBScript脚本，修改其中的Excel模板路径，点Run按钮执行即可
1 Option Explicit 2 Dim mdl ' the current model 3 Set mdl = ActiveModel 4 If (mdl Is Nothing) Then 5 MsgBox "There is no Active Model" 6 End If 7 8 Dim HaveExcel 9 Dim RQ 10 RQ = vbYes 'MsgBox("Is Excel Installed on your machine ?", vbYesNo + vbInformation, "Confirmation") 11 Dim tName 12 tName="Sheet1" 13 If RQ = vbYes Then 14 HaveExcel = True 15 ' Open & Create Excel Document 16 Dim x1 ' 17 Set x1 = CreateObject("Excel.Application") 18 x1.Workbooks.Open "D:\XXX\XXX.xls" 19 x1.Workbooks(1).Worksheets(tName).Activate 20 Else 21 HaveExcel = False 22 End If 23 a x1, mdl 24 sub a(x1,mdl) 25 dim rwIndex 26 dim tableName 27 dim colname 28 dim table 29 dim col 30 dim count 31 32 'on error Resume Next 33 34 dim shtIdx 35 for shtIdx=2 to x1.Workbooks(1).Worksheets.Count '第二个sheet页开始 36 For rwIndex = 1 To 120 step 1 37 With x1.Workbooks(1).Worksheets(shtIdx) 38 'With x1.Workbooks(1).Worksheets(tName) 39 'MsgBox "生成数据表结构共计1 ="+CStr(.Cells(2,2).Value ), vbOK + vbInformation, "表" 40 If .Cells(rwIndex, 1).Value = "" Then 41 Exit For 42 End If 43 if rwIndex=1 Then 44 '创建一个表实体 45 set table = mdl.Tables.CreateNew 46 '指定表名 47 table.Name = .Cells(2 , 2).Value 48 '指定表Code 49 table.Code = .Cells(2 , 1).Value 50 'Comment 51 table.Comment = .Cells(2 , 2).Value 52 count = count + 1 53 End If 54 if rwIndex>1 Then '忽略表头 55 colName = .Cells(rwIndex, 1).Value 56 set col = table.Columns.CreateNew '创建列 57 'MsgBox .Cells(rwIndex, 1).Value, vbOK + vbInformation, "列" 58 col.Name = .Cells(rwIndex, 3).Value '指定列名 59 'MsgBox col.Name, vbOK + vbInformation, "列" 60 col.Code = .Cells(rwIndex, 3).Value '指定列code 61 col.DataType = .Cells(rwIndex, 4).Value '指定列数据类型 62 col.Comment = .Cells(rwIndex,5).Value '指定列Comment 63 If .Cells(rwIndex, 6).Value = "Y" Then'指定主键 64 col.Primary = true 65 End If 66 '指定列是否可空 67 'If .Cells(rwIndex, 7).Value = "Y" Then 68 ' col.Mandatory = true'指定列是否可空,true为不可空 69 'End If 70 End If 71 End With 72 Next 73 Next 74 MsgBox "生成数据表结构共计" + CStr(count), vbOK + vbInformation, "表" 75 Exit Sub 76 End sub
Excel模板如下：