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

        编程往往与数据库密不可分，一个项目往往有很多的表，很多时候通过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 SubException:End SubPrivate 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 SubException:End Sub

创建模块：

Private Sub ShowTable()  frmTable.ShowEnd SubPrivate 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 "表结构创建脚本成功！文件名" & sFileNameEnd Sub

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

最终的生产结果示例

--表结构创建脚本，对应数据库Oracle--建表脚本创建开始:2015/5/2 18:35:26DECLARE  --判断表是否存在  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 tableexecute immediate 'comment on table  TEST is ''测试表'''; -- Add comments to the columnsexecute 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;/