编程往往与数据库密不可分，一个项目往往有很多的表，很多时候通过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 = "<span style="font-family: Arial, Helvetica, sans-serif;">生成</span><span style="font-family: Arial, Helvetica, sans-serif;">建表脚本"</span> .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 = 20 To Sheets.Count 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
最终的生产结果示例
--表结构创建脚本，对应数据库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;/