PowerDesigner导入Excel生成数据表

1、需要导入的Exce表结构数据，格式如下：

2、打开PowerDesigner，创建一个Physical Data Model文件,填写文件名称和数据库类型

3、按下快捷键Ctrl+Shift+X打开脚本窗口（或点击菜单Tools->Execute Commands->Edit/Run Script），输入示例VBScript脚本，修改其中的Excel模板路径及sheet名称，点Run按钮执行即可

VBScript脚本代码如下：

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
Dim sheetName
    sheetName = "Sheet1" ' 指定要操作的Sheet名称
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 "C:\Users\seven\Downloads\表结构.xlsx"  '指定excel文档路径
    x1.Workbooks(1).Worksheets(sheetName).Activate   '指定要打开的sheet名称
Else
    HaveExcel = False
    MsgBox "Excel is not installed or not selected."
End If


a x1, mdl  
Sub a(x1, mdl)
    Dim rwIndex
    Dim table
    Dim tableCode  '表名称
    Dim tableComment  '表中文名称
    Dim col
    

    On Error Resume Next
    
    '读取excel，添加表实体属性
    For rwIndex = 1 To 200  '指定要遍历的Excel行标，从第一行开始，有多少行就读取多少行
        With x1.Workbooks(1).Worksheets(sheetName) '需要循环的sheet名称
            
            If .Cells(rwIndex, 1).Value = "" And .Cells(rwIndex, 2).Value <> "" Then
                tableCode = .Cells(rwIndex, 2).Value '表名（表标识）
                tableComment = .Cells(rwIndex, 3).Value '表中文名称
                If tableComment = "" Then tableComment = tableCode End If
                '判断table是否已存在，存在删除
                Dim tb
                For Each tb In mdl.Tables
                  If tb.Code = tableCode Then
                     output "删除："&tableCode
                     mdl.Tables.Remove tb,True
                     Exit For
                  End If
                Next

                '创建一个表实体
                Set table = mdl.Tables.CreateNew
                table.Code = tableCode
                table.Name = tableComment
                table.Comment = tableComment
                output "创建："& table.Code & "【" & table.Name & "】"
            End If

            If .Cells(rwIndex, 1).Value <> "" And .Cells(rwIndex, 1).Value <> "字段名称" And .Cells(rwIndex, 2).Value <> "" Then
                Set col = table.Columns.CreateNew '创建一列/字段
                col.Code = .Cells(rwIndex, 1).Value '指定列code
                If .Cells(rwIndex, 5).Value <> "" Then
                    col.Name = .Cells(rwIndex, 5).Value '指定列name
                Else
                    col.Name = .Cells(rwIndex, 1).Value '指定列name
                End If
                col.Comment = .Cells(rwIndex, 5).Value '指定列说明
                col.DataType = .Cells(rwIndex, 2).Value '指定列数据类型
                If .Cells(rwIndex, 3).Value = "Y" Then '指定主键
                    col.Primary = True
                End If
                If .Cells(rwIndex, 4).Value = "Y" Then '指定列是否可空 true 为不可空
                    col.Mandatory = True
                End If
            End If
        End With
    Next

    Exit Sub
End Sub