Excel批量导入表结构到PowerDesigner

来源：互联网发布：性能测试书籍推荐知乎编辑：程序博客网时间：2024/05/17 23:19

编写测试EXCEL，格式如图所示：
打开PowerDesigner，创建物理模型(Physical Data Model)
在PowerDesigner菜单栏中，依次点击“Tools ->Excute Commands->Edit/Run Script..”
修改脚本，指定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 .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

阅读全文

0 0