Excel PowerDesigner转化为Sql

Excel批量转化为sql语句

1.PowerDesigner软件安装

链接：https://pan.baidu.com/s/1G16ol08hs-OX3AVjnHUXrg

提取码：c26p

2.软件初始化配置

点击数据库->Edit Current DBMS->进入script/sql/Format

在弹出的对话框中将 ORA11GR1::Script\Sql\Format\UpperCaseOnly 设为Yes

在弹出的对话框中将 ORA11GR1::Script\Sql\Format\CaseSensitivityUsingQuote 设为No

->进入script/Object/Table/Drop 去除里面的drop语句

3.打开工作空间

点击右键->新建->Physical Data Mode->选择DBMS版本

4.把语雀里面的设计的Excel sql下载下来,排版如图2

sheet命名一定要是表结构,两个表中间间隔两行

5.生成sql

点击PowerDesigner工具->Execute Commands->Edit/Run Script->复制vbr脚本

->修改本地excel文件地址(红色字样)->点击run

'******************************************************************************'开始

Option Explicit

Dim tab_name,tab_code,tab_comment

Dim b_r, e_r, s_r, j, m, n

Dim mdl ' the current model

dim count

Dim HaveExcel

Dim RQ

Dim file_name,WScript

Set mdl = ActiveModel

If (mdl Is Nothing) Then

  MsgBox "There is no Active Model"

End If

RQ = vbYes 'MsgBox("Is Excel Installed on your machine ?", vbYesNo + vbInformation, "Confirmation")

If RQ = vbYes Then

  HaveExcel = True

  ' Open & Create Excel Document

Else

  HaveExcel = False

End If

file_name = selectFile()

if file_name <> "" then

  Dim x1  '

  Set x1 = CreateObject("Excel.Application")

  x1.Workbooks.Open "C:\Users\EDZ\Desktop\pd4.xlsx"    '指定excel文档路径

  x1.Workbooks(1).Worksheets("表结构").Activate  '指定要打开的sheet名称

j = 1

do while n < 11

if x1.Workbooks(1).Worksheets("表结构").cells(j,1).value <> "" then

call a(x1, mdl, j, getRow(x1, j))

'msgbox j & "--" & getRow(x1, j)

count = count + 1

j = getRow(x1, j)

n = 0

end if

j = j + 1

n = n + 1

loop

'MsgBox "生成数据表结构共计 " + CStr(count), vbOK + vbInformation, "表 导入完毕!"

MsgBox "生成数据表结构共计 " & CStr(count) & " 表导入完毕!"

x1.Workbooks(1).close

x1.quit

else

msgbox "没有选择文件!"

end if

sub a(x1, mdl, r_0,r_9)

dim rwIndex 

dim tableName

dim colname

dim table

dim col

tab_name = x1.Workbooks(1).Worksheets("表结构").cells(r_0,4).value    '指定表名，如果在Excel文档里有，也可以 .Cells(rwIndex, 3).Value 这样指定

tab_code = x1.Workbooks(1).Worksheets("表结构").cells(r_0,2).value  '指定表名

tab_comment = x1.Workbooks(1).Worksheets("表结构").cells(r_0,4).value

on error Resume Next

set table = mdl.Tables.CreateNew '创建一个表实体

table.Name = tab_name

table.Code = tab_code

table.Comment = tab_comment

For rwIndex = r_0 + 3 To r_9  '指定要遍历的Excel行标  由于第1行是表头，从第2行开始

        With x1.Workbooks(1).Worksheets("表结构")

            If .Cells(rwIndex, 2).Value = "" Then

              Exit For

            End If

              set col = table.Columns.CreateNew  '创建一列/字段

              'MsgBox .Cells(rwIndex, 2).Value, vbOK + vbInformation, "列"

              If .Cells(rwIndex, 2).Value = "" Then

                  col.Name = .Cells(rwIndex, 1).Value  '指定列名

              Else

                  col.Name = .Cells(rwIndex, 2).Value

              End If

              'MsgBox col.Name, vbOK + vbInformation, "列"

              col.Code = .Cells(rwIndex, 1).Value  '指定列名

              col.DataType = .Cells(rwIndex, 3).Value & "(" & .Cells(rwIndex, 4).Value & ")"  '指定列数据类型

              col.Comment = .Cells(rwIndex, 10).Value  '指定列说明

                If ucase(.Cells(rwIndex, 5).Value) = "Y" Then

                  col.Primary = true    '指定主键

                  col.defaultvalue = .Cells(rwIndex,7).value

              End If

              If ucase(.Cells(rwIndex,6).Value) = "N" then

                  col.defaultvalue = .Cells(rwIndex,7).value

                  col.Mandatory = true  '指定列是否可空,true为不可空             

              End If

        End With

Next

End sub

Function getRow(x1, s_r)

dim i, k

k = s_r

do while x1.Workbooks(1).Worksheets("表结构").cells(k,1).value <> ""

k = k + 1

if x1.Workbooks(1).Worksheets("表结构").cells(k,1).value = "" then

getRow = k - 1

exit function

end if

loop

End Function

Function SelectFile()

    Dim shell : Set shell = CreateObject("WScript.Shell")

    Dim fso : Set fso = CreateObject("Scripting.FileSystemObject")

    Dim tempFolder : Set tempFolder = fso.GetSpecialFolder(2)

    Dim tempName : tempName = fso.GetTempName()

    Dim tempFile : Set tempFile = tempFolder.CreateTextFile(tempName & ".hta")

    tempFile.Write _

    "<html>" & _

    "<head>" & _

    "<title>Browse</title>" & _

    "</head>" & _

    "<body>" & _

    "<input type='file' id='f' />" & _

    "<script type='text/javascript'>" & _

    "var f = document.getElementById('f');" & _

    "f.click();" & _

    "var shell = new ActiveXObject('WScript.Shell');" & _

    "shell.RegWrite('HKEY_CURRENT_USER\\Volatile Environment\\MsgResp', f.value);" & _

    "window.close();" & _

    "</script>" & _

    "</body>" & _

    "</html>"

    tempFile.Close

    shell.Run tempFolder & "\" & tempName & ".hta", 0, True

    SelectFile = shell.RegRead("HKEY_CURRENT_USER\Volatile Environment\MsgResp")

    shell.RegDelete "HKEY_CURRENT_USER\Volatile Environment\MsgResp"

End Function

6.拷贝sql并执行

7.excel文件格式