Luo Hao

word转sql表结构

rehoni / 2020-04-15

word转sql表结构：

工作流如下：

  1. 已经在office word编辑好的数据库说明文档

  2. 通过按照特定的表结构，录入到excel中，前两行为中文表名和英文表名，下边为字段，下方的sheet名也为中文表名，并且注意”名称“列，是不可以有重名的，否则执行脚本的时候会报错，并且一直占用该excel导致此excel只读，无法编辑（如果修改完excel没有退出则继续修改就行）只能创建excel副本。

    word2Sql

  3. 打开power designer->create model->physical data，创建物理模型，DBMS选择对应的数据库，版本低一点没有关系，商用库为12a，选择最高的Oracle version 11g，创建之后按ctrl+shift+x打开脚本执行界面，执行以下VB脚本，生成表的物理模型。可以根据数据库文档中每一列的含义，在脚本中进行调整。执行完之后可以保存到power designer的脚本库中，方便之后重用。

    '
' @Author: 罗皓
' @Date: 2020-03-25 14:41:57
' @LastEditTime: 2020-03-26 10:11:31
' @Description:
' @LastEditors: 罗皓
'
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 '
 Dim wb
 Dim Sht
 Dim count
 Dim newTableName  'sheet中的表名
 Dim singleTable   'powerdesigner中的表名
 Dim existsFlag
  Set x1 = CreateObject("Excel.Application")
  '打开excel文件
  Set wb = x1.Workbooks.Open("C:\Users\Res0l\Documents\珠海项目归档\8.数据库\气象数据.xlsx")
    '遍历每个sheet
  For Each Sht In wb.Sheets
        '打印sheet名
         'msgbox( Sht.Name)
         'msgbox( mdl.Tables.count)
         '获取到当前需要创建的表名，查看它是否在powerdesigner中已经存在
         newTableName = Sht.Cells(1, 2).Value
         'msgbox( newTableName)
         For Each singleTable In mdl.Tables

            'msgbox(singleTable.Name)
             If singleTable.Name = newTableName Then
               existsFlag = True
                End If
         Next
      If existsFlag Then
         MsgBox (newTableName + "已经存在")
         '重置flag
         existsFlag = False
      Else

         '调用子模块
         immigrate_function Sht, mdl
         count = count + 1
      End If
   Next
  MsgBox "生成数据表结构共计" + CStr(count), vbOK + vbInformation, "表"

'关闭流
Set Sht = Nothing
wb.Close
Set wb = Nothing
x1.Quit
Set x1 = Nothing

Else
   HaveExcel = False
End If
 '子程序模块
Sub immigrate_function(Sht, mdl)
Dim rwIndex
Dim tableName
Dim colname
Dim table
Dim col
'on error Resume Next
For rwIndex = 1 To 1000 Step 1'指定要遍历的Excel行标  从第1行开始
With Sht
   If .Cells(rwIndex, 1).Value = "" Then
       rwIndex = rwIndex + 1
       If .Cells(rwIndex, 1).Value = "" Then '连续第一列的两行为空，则退出for
       Exit For
       End If
   End If
  If rwIndex = 1 Then
    Set table = mdl.Tables.CreateNew '创建表
        table.Name = .Cells(rwIndex, 2).Value '表名
        table.Comment = .Cells(rwIndex , 2).Value '指定表注释，以表名
           ElseIf rwIndex = 2 Then
        table.Code = .Cells(rwIndex, 2).Value '表英文名
       rwIndex = rwIndex + 1
      Else
    colname = .Cells(rwIndex, 1).Value
    Set col = table.Columns.CreateNew '创建一列/字段
    col.Name = Replace(.Cells(rwIndex, 1).Value," ","") '指定列名——中文
    col.Code = Replace(.Cells(rwIndex, 2).Value," ","") '指定列名——英文字段
    col.DataType = Replace(.Cells(rwIndex, 3).Value," ","")'指定列数据类型
    If Replace(.Cells(rwIndex, 4).Value," ","") = "True" Then '标True的为主键
        col.Primary = True
    End If
    If Replace(.Cells(rwIndex, 5).Value," ","") = "Default0" Then 'Default0，指默认值为0
        col.DefaultValue = 0
        ElseIf Replace(.Cells(rwIndex, 5).Value," ","") = "Default''" Then 'Default''，指默认值为''
            col.DefaultValue = ""
    End If
    If Replace(.Cells(rwIndex, 6).Value," ","") = "" or Replace(.Cells(rwIndex, 6).Value," ","") = Replace(.Cells(rwIndex, 1).Value," ","") Then
        col.Comment = Replace(.Cells(rwIndex, 1).Value," ","") '列说明放入中文列名值
        Else
            col.Comment = Replace(.Cells(rwIndex, 1).Value," ","") & "：" & Replace(.Cells(rwIndex, 6).Value," ","")'指定列说明
    End If
   End If
  End With
  Next
 End Sub

  4. 脚本执行成功后，将会在power designer左侧边栏生成Tables，右键对应表名->SQL previews，即可生成建表语句，看情况决定是否注释第一行的drop table 语句。