假定Excel中存储的表结构是这样：

Excel中的DB表结构

并且存储在c:\a.xlsx
打开PD，创建好物理数据模型，执行ctrl+shirft+x，打开script执行界面，输入以下脚本（VB）执行即可。

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
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:\a.xlsx"
    x1.Workbooks(1).Worksheets("Sheet1").Activate
Else
    HaveExcel = False
End If

a x1, mdl

Sub a(x1,mdl)
    Dim rwIndex
    Dim tableName
    Dim table
    Dim col
    Dim Count

    'on error Resume Next 

    For rwIndex = 2 To 5140 step 1

        With x1.Workbooks(1).Worksheets("Sheet1")
            'MsgBox "生成数据表结构共计1 ="+CStr(.Cells(2,2).Value ), vbOK + vbInformation, "表" 

            If .Cells(rwIndex, 1).Value = "" And .Cells(rwIndex, 3).Value = "" Then
                Exit For
            End If

            If .Cells(rwIndex, 1).Value <> "" Then
                Set table  = mdl.Tables.CreateNew
                table.Name = .Cells(rwIndex, 1).Value
                table.Code = .Cells(rwIndex , 1).Value

                If .Cells(rwIndex , 2).Value <> "" Then
                    table.Comment = .Cells(rwIndex , 2).Value
                End If

                Count    = Count + 1
            Else
                Set col  = table.Columns.CreateNew
                col.Name = .Cells(rwIndex, 3).Value
                col.Code = .Cells(rwIndex, 3).Value

                If .Cells(rwIndex,4).Value <> "" Then
                    col.Comment = .Cells(rwIndex, 4).Value
                End If

                If .Cells(rwIndex,6).Value <> "" Then
                    col.Primary = True
                End If

                If .Cells(rwIndex,7).Value <> "" Then
                    col.Mandatory = True
                End If

                col.DataType = .Cells(rwIndex, 5).Value
            End If

        End With

    Next

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

    Exit Sub
    End Sub

可以修改循环语句内的代码来满足其他excel的表结构。