PowerDesigner批量导入excel代码

打开powerdesigner,按下ctrl+shift+x,执行如下代码

Option Explicit

'
' get the current active model
'
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 excfile
Dim wrkbok
Dim sht
RQ = MsgBox ("Is Excel Installed on your machine ?", vbYesNo + vbInformation,"Confirmation")
if RQ= VbYes then
    HaveExcel= True
    ' Open & Create Excel Document
    Set excfile = CreateObject("Excel.Application")
    Set wrkbok=excfile.Workbooks.Open("D:\doc\dic.xlsx")
    ' excfile.Visible = True
    Set sht=wrkbok.Sheets("Sheet1")
    'Output sht.Range("B:B").Count '1048576 not max rows 
    ReadExcel2PDM
else
   HaveExcel= false
end if

Private sub ReadExcel2PDM
    Dim i,j,maxRow,maxCols
    maxRow=sht.UsedRange.Rows.Count 'max rows
    maxCols=sht.UsedRange.Columns.Count 'max columns
    Dim tabName

    tabName = ""
    For i = 2 To maxRow Step 1
        Dim tbl,col
        If sht.Cells(i,1).Value <>"" Then
            If tabName<>sht.Cells(i,1).Value Then 
                tabName=sht.Cells(i,1).Value
                set tbl = mdl.Tables.CreateNew
                dim dt_col
                set dt_col = tbl.Columns.CreateNew
                dt_col.code="acct_date"
                dt_col.name="统计日期"
                dt_col.datatype="string"
                'dt_col.PartitionColumn=True
                tbl.PhysicalOptions="row format delimited"+vbCrLf+"fields terminated by '\t'"+vbCrLf+"lines terminated by '\n';"
                tbl.code=tabName
                tbl.name=sht.Cells(i,2).Value
                tbl.comment=sht.Cells(i,2).Value
            End If

            set col = tbl.Columns.CreateNew
            col.code=sht.Cells(i,4).Value
            'if col.code="acct_date" then
             '  col.PartitioningColumn=True
            'end if
            col.datatype=sht.Cells(i,5).Value
            col.name=sht.Cells(i,3).Value
            col.comment=sht.Cells(i,3).Value
            Output "Import PDM from excel ,table:" + tabName + ",Column:" + sht.Cells(i,4).Value
        End If
    Next
    wrkbok.Close()
End Sub
打赏

觉得本站还不错就打赏一下吧!

支付宝扫一扫打赏

微信扫一扫打赏

本站所有内容均来自于互联网自动采集,如无意侵犯了您的权利,请您发送邮件到admin@chinaetl.com.cn联系管理员删除。
ChinaETL » PowerDesigner批量导入excel代码