power Designer 导入 excel 数据 创建表。

1. 原文地址：https://blog.csdn.net/qq_33218394/article/details/76270807
2. 编写测试EXCEL，格式如图所示： 
   
3. 打开PowerDesigner，创建物理模型(Physical Data Model)
   
4. 在PowerDesigner菜单栏中，依次点击“Tools ->Excute Commands->Edit/Run Script..
5. 修改脚本，指定excel所在路径及文件名

    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 x1sApp,xlsWorkBook,xlsSheet 
    RQ = vbYes 'MsgBox("Is Excel Installed on your machine ?", vbYesNo + vbInformation, "Confirmation")  
    If RQ = vbYes Then  
       HaveExcel = True  
       ' Open & Create Excel Document  


       Set x1sApp = CreateObject("Excel.Application")  
       set xlsWorkBook = x1sApp.Workbooks.Open("D:\CMSDB.xls")   '指定excel文档路径  
       set xlsSheet = x1sApp.Workbooks(1).Worksheets("Sheet1")   '指定要打开的sheet名称  
    Else  
       HaveExcel = False  
    End If  

    a x1sApp, mdl,x1sApp,xlsWorkBook,xlsSheet  



    sub a(x1, mdl,x1sApp,xlsWorkBook,xlsSheet)  
    dim rwIndex     
    dim tableName  
    dim colname  
    dim table  
    dim col  
    dim count  
    dim rowCount

     rowCount = xlsSheet.usedRange.Rows.Count

    on error Resume Next  

    For rwIndex = 2 To rowCount   '指定要遍历的Excel行标  由于第1行是表头，从第2行开始  
            With xlsSheet
                If .Cells(rwIndex, 2).Value = "" Then '如果遍历到第二列为空，则退出  
                   Exit For  
                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   
                    table.Comment = .Cells(rwIndex , 1).Value '指定表注释，第一列的值  
                    count = count + 1    
                Else  
                   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.DataType = .Cells(rwIndex, 3).Value '指定列数据类型             
                     'MsgBox col.DataType, vbOK + vbInformation, "列类型"                 
                   col.Comment = .Cells(rwIndex, 6).Value  '指定列说明  

                   if col.Code = "id" Then

col.Identity = true

End If

if col.Code = "ID" Then

col.Identity = true

End If

                    

                    if .Cells(rwIndex, 4).Value = "Primary Key" Then

col.Primary = true

End If

If.Cells(rwIndex, 5).Value = "NOT NULL" Then

col.Mandatory =true

End If

End If

End With

Next

MsgBox "生成数据表结构共计 " + CStr(count), vbOK + vbInformation, "表"

xlsWorkBook.Close

x1sApp.Quit

set x1sApp = nothing

set xlsWorkBook = nothing

Exit Sub

End sub