【VBA】EXCEL通过VBA生成SQL，自动生成创建表结构SQL

原文：https://blog.csdn.net/zutsoft/article/details/45441343

编程往往与数据库密不可分，一个项目往往有很多的表，很多时候通过excel来维护表结构，记录表名，字段，类型，注释等等信息，将表结构整理到excel已经是件很累的事了，如果整理好的表结构只是用来查字段当字典用，那就太浪费了，excel提供vba编程，我们可以通过一段小脚本来实现一键创建表结构。使用脚本创建的语句需要标准化每个sheet页的格式，格式根据自己需要来做，这里给出我使用的格式和脚本。

EXCEL模板

VBA编程

打开excel vba编程工具，在ThisWorkbook中添加如下代码：

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'关闭工作簿前，删除新创建的工具条先
  Dim bar_name As String
  bar_name = "HRBSJ"
  Application.CommandBars(bar_name).Delete
  On Error GoTo Exception
  Exit Sub
Exception:
End Sub
Private Sub Workbook_Open()
'打开工作簿时创建工具条
  Dim bar_name As String
  Dim new_bar As Office.CommandBar

  bar_name = "HRBSJ"

  Set new_bar = Application.CommandBars.Add(bar_name)
  new_bar.Visible = True

  new_bar.Position = msoBarLeft


  With new_bar.Controls.Add(Type:=msoControlButton, before:=1)
    .BeginGroup = True
    .Caption = "生成建表脚本"
    .TooltipText = "生成建表脚本"
    .Style = msoButtonCaption
    .OnAction = "Create_HR_Table_Script"
  End With



  On Error GoTo Exception
  Exit Sub
Exception:
End Sub

创建模块：

 

Private Sub ShowTable()
  frmTable.Show
End Sub
Private Sub Create_HR_Table_Script()
  Dim line_tablename As Integer, len_col_id As Integer, len_str_type As Integer, col_num As Integer
  Dim do_column As Boolean, column_end As Boolean
  Dim table_name As String, str_col_id As String, str_space As String
  Dim primary_col As String, index_col As String, str_primary As String

  Dim str_temp As String, str_type As String, str_null As String, str_column As String


  max_line = 1000
  no_data = 0
  do_column = False
  column_end = False
  str_column = ""
  str_index = ""
  line_tablename = 6

  Set fs = CreateObject("Scripting.FileSystemObject")
  sFilePath = ActiveWorkbook.Path & "Script"
  If Dir(sFilePath, vbDirectory) = "" Then
    MkDir sFilePath
  End If

  sFileName = sFilePath & "Create_HR_Table_Script.sql"
  Set fhandle = fs.CreateTextFile(sFileName, True)

  fhandle.WriteLine ("--华融表结构创建脚本，对应数据库Oracle")
  fhandle.WriteLine ("--建表脚本创建开始:" & Date & " " & Time)

  fhandle.WriteLine ("")
  fhandle.WriteLine ("DECLARE")
  fhandle.WriteLine ("  --判断表是否存在")
  fhandle.WriteLine ("  FUNCTION fc_IsTabExists(sTableName IN VARCHAR2)")
  fhandle.WriteLine ("    RETURN BOOLEAN AS")
  fhandle.WriteLine ("   iExists PLS_INTEGER;")
  fhandle.WriteLine ("  BEGIN")
  fhandle.WriteLine ("    SELECT COUNT(*) INTO iExists FROM user_tables ut WHERE ut.table_name  = UPPER(sTableName);")
  fhandle.WriteLine ("    RETURN CASE WHEN iExists > 0 THEN TRUE ELSE FALSE END;")
  fhandle.WriteLine ("  END;")
  fhandle.WriteLine ("")
  fhandle.WriteLine ("BEGIN")

  For i_index = 2 To Sheets.Count  '第一页是目录这里的参数控制开始的页数到截止的sheet页
      Sheets(i_index).Select  '从第二页开始，循环sheet页


      For i_line = 3 To max_line
        first_col = Trim(Sheets(i_index).Cells(i_line, 2))


        Select Case first_col
          Case "目标表说明"
            table_name = Trim(Sheets(i_index).Cells(3, 4))

            primary_col = Trim(Sheets(i_index).Cells(5, 4))

            index_col = Trim(Sheets(i_index).Cells(8, 4))


            If Len(primary_col) > 0 Then
                primary_col = Replace(primary_col, "，", ",")
                str_primary = "alter table " & table_name & " " & "add constraint pk_" & table_name & " primary key (" & primary_col & ")"
            Else
                str_primary = ""
            End If

            If Len(index_col) > 0 Then
                index_col = Replace(index_col, "，", ",")
            Else
                index_col = ""
            End If

          Case "序号"
            fhandle.WriteLine ("")
            fhandle.WriteLine ("/* Table：" & table_name & "  " & Trim(Sheets(i_index).Cells(2, 2)) & "  */")
            fhandle.WriteLine ("IF fc_IsTabExists('" & table_name & "') THEN")
            fhandle.WriteLine ("  execute immediate 'drop table " & table_name & "';")
            fhandle.WriteLine ("END IF;")
            fhandle.WriteLine ("")
            fhandle.WriteLine ("execute immediate '")
            fhandle.WriteLine ("create table " & table_name)
            fhandle.WriteLine ("(")

           Case 1
            do_column = True
           Case ""
            do_column = False
        End Select

        If Trim(Sheets(i_index).Cells(i_line, 2)) = "" Then
          do_column = False
        End If

        str_temp = ""
        str_column = ""

        If do_column = True Then

         '标识最后一个字段列
         If Trim(Sheets(i_index).Cells(i_line + 1, 2)) = "" Or Trim(Sheets(i_index).Cells(i_line + 1, 3)) = "" Then
             column_end = True
         Else
             column_end = False
         End If

         '字段处理，及与数据类型的空格数处理
          str_col_id = Trim(Sheets(i_index).Cells(i_line, 3))
          len_col_id = Len(str_col_id)
          For i = len_col_id To 30
             str_space = str_space & " "
          Next
          str_column = str_col_id & str_space

          '数据类型的处理
          str_space = ""
          str_type = Trim(Sheets(i_index).Cells(i_line, 4))

          len_str_type = Len(str_type)
          For i = len_str_type To 16
             str_space = str_space & " "
          Next

          str_column = str_column & str_type & str_space

          '是否为空的处理
          str_space = ""
          str_temp = Trim(Sheets(i_index).Cells(i_line, 5))
          Select Case str_temp
            Case "N"
               str_null = "not null"
            Case Else
               str_null = ""
          End Select

          str_column = str_column & str_null

          '加一列
          If column_end = False Then
            str_column = str_column & ","
            fhandle.WriteLine ("  " & str_column)
          Else
            fhandle.WriteLine ("  " & str_column)
            fhandle.WriteLine (") tablespace TS_TDC';")
          End If

        End If

      Next ' 结束工作表的循环

       '--加注释
       If Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then
       fhandle.WriteLine (" -- Add comments to the table")
       fhandle.WriteLine ("execute immediate 'comment on table  " & Trim(Sheets(i_index).Cells(3, 4)) & " is ''" & Trim(Sheets(i_index).Cells(2, 2)) & "''';")
       fhandle.WriteLine (" -- Add comments to the columns")
       For i_line = 15 To max_line
       If Trim(Sheets(i_index).Cells(i_line, 2)) <> "" And Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then
       fhandle.WriteLine ("execute immediate 'comment on column " & Trim(Sheets(i_index).Cells(3, 4)) & "." & Trim(Sheets(i_index).Cells(i_line, 3)) & " is ''" & Trim(Sheets(i_index).Cells(i_line, 7)) & "''';")
       End If
       Next ' 结束工作表的循环
       End If

       '--加主键
       If Len(str_primary) > 0 And Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then
        fhandle.WriteLine ("")
        fhandle.WriteLine ("execute immediate '" & str_primary & " using index tablespace TS_TDC';")
       End If

       '--加索引
       If Len(index_col) > 0 And Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then
       fhandle.WriteLine ("")
       fhandle.WriteLine ("execute immediate 'create index i_" & table_name & " on " & table_name & " (" & index_col & " )  tablespace TS_TDC';")
       End If



  Next '结束工作簿的循环
  fhandle.WriteLine ("")
  fhandle.WriteLine ("END;")
  fhandle.WriteLine ("/")

  fhandle.Close

  Sheets(1).Select

  MsgBox "表结构创建脚本成功！文件名" & sFileName
End Sub

保存后可在excel上看到 ‘生成建表脚本’ 按钮

最终的生产结果示例

--表结构创建脚本，对应数据库Oracle
--建表脚本创建开始:2015/5/2 18:35:26

DECLARE
  --判断表是否存在
  FUNCTION fc_IsTabExists(sTableName IN VARCHAR2)
    RETURN BOOLEAN AS
   iExists PLS_INTEGER;
  BEGIN
    SELECT COUNT(*) INTO iExists FROM user_tables ut WHERE ut.table_name  = UPPER(sTableName);
    RETURN CASE WHEN iExists > 0 THEN TRUE ELSE FALSE END;
  END;

BEGIN

/* Table：TEST  测试表  */
IF fc_IsTabExists('TEST') THEN
  execute immediate 'drop table TEST';
END IF;

execute immediate '
create table TEST
(
  c_unique_no                    VARCHAR2(32)     not null,
  c_proj_no                      VARCHAR2(40)     not null,
  c_busi_type                    VARCHAR2(40)     not null,
  c_cust_ID                      VARCHAR2(32)
) tablespace TEST';
 -- Add comments to the table
execute immediate 'comment on table  TEST is ''测试表''';
 -- Add comments to the columns
execute immediate 'comment on column TEST.c_unique_no is ''PK主键''';
execute immediate 'comment on column TEST.c_proj_no is ''项目编号''';
execute immediate 'comment on column TEST.c_busi_type is ''业务类型''';
execute immediate 'comment on column TEST.c_cust_ID is ''客户ID''';


execute immediate 'alter table TEST add constraint pk_TEST primary key (c_unique_no) using index tablespace TEST';

END;
/