excel的宏与VBA实践——建表语句

一、建表语句

  不带分区版本:V1.0:

Sub createTableDDL()
'自动创建建表语句
'定义换行和TAB
Ln = Chr() + Chr()
TB = Chr()
'定义脚本目录
Dim dir AS String
dir = "C:\CREATE_TABLE_DDL"
Set FSOE = CreateObject("Scripting.FileSystemObject")
If FSOE.folderexists(dir) = False Then
MkDir dir
End If '调用脚本定义
Set SqlFileDDL = FSOE.CreateTextFile("C:\CREATE_TABLE_DDL\create_table_ddl.sql", True)
'获得表名
tableName = Trim(Cells(, ).Value)
'获得表注释
tableComment = Trim(Cells(, ).Value)
'获得创建者
createBy = Trim(Cells(, ).Value)
Dim dt As Date
dt = Format(Date, "yyyy-mm-dd")
'获得当前日期
createDate = dt
'获得A列已使用的行数
count_row_k = [A65536].End(xlUp).Row
'定义SQL
SQL = "--创建者:" & createBy & Ln
SQL = SQL & "--创建时间:" & createDate & Ln
SQL = SQL & "DROP TABLE IF EXISTS " & tableName & " ;" & Ln
SQL = SQL & "CREATE TABLE " & tableName & "("
'写入文件
SqlFileDDL.WriteLine (SQL)
For i = To count_row_k
If i = count_row_k Then
col_name = TB & LCase(Cells(i, )) & " " & UCase(Cells(i, )) & " COMMENT '" & Trim(Cells(i, )) & "'" & Ln & ")"
SqlFileDDL.WriteLine(col_name)
Exit For
End If
col_name = TB & LCase(Cells(i, )) & " " & UCase(Cells(i, )) & " COMMENT '" & Trim(Cells(i, )) & "',"
SqlFileDDL.WriteLine(col_name)
Next
SqlFileDDL.WriteLine("COMMENT '" & tableComment & "'")
MsgBox("生成成功!生成路径为:" & dir)
End Sub

   带分区与生命周期版本V2.0:

Sub create_ddl()
'自动创建建表语句
'定义换行和TAB
Ln = Chr() + Chr()
TB = Chr()
'定义脚本目录
Dim dir As String
dir = "C:\CREATE_TABLE_DDL"
Set FSOE = CreateObject("Scripting.FileSystemObject")
If FSOE.folderexists(dir) = False Then
MkDir dir
End If '调用脚本定义
Set SqlFileDDL = FSOE.CreateTextFile("C:\CREATE_TABLE_DDL\create_table_ddl.sql", True)
'获得表名
TableName = Trim(Cells(, ).Value)
'获得表注释
tableComment = Trim(Cells(, ).Value)
'获得生命周期
lifecycle = Trim(Cells(, ).Value)
'获得创建者
createBy = Trim(Cells(, ).Value)
Dim dt As Date
dt = Format(Date, "yyyy-mm-dd")
'获得当前日期
createDate = dt
'获得A列已使用的行数
count_row_k = [A65536].End(xlUp).Row
'获得E列已使用的行数
part_row_k = [E65536].End(xlUp).Row
'定义SQL
Sql = "--创建者:" & createBy & Ln
Sql = Sql & "--创建时间:" & createDate & Ln
Sql = Sql & "DROP TABLE IF EXISTS " & TableName & " ;" & Ln
Sql = Sql & "CREATE TABLE " & TableName & "("
'写入文件
SqlFileDDL.WriteLine (Sql)
For i = To count_row_k
If i = count_row_k Then
col_name = TB & LCase(Cells(i, )) & " " & UCase(Cells(i, )) & " COMMENT '" & Trim(Cells(i, )) & "'" & Ln & ")"
SqlFileDDL.WriteLine (col_name)
Exit For
End If
col_name = TB & LCase(Cells(i, )) & " " & UCase(Cells(i, )) & " COMMENT '" & Trim(Cells(i, )) & "',"
SqlFileDDL.WriteLine (col_name)
Next
SqlFileDDL.WriteLine ("COMMENT '" & tableComment & "'")
'加上分区列
If part_row_k > Then
part_col_name = "PARTITIONED BY ("
SqlFileDDL.WriteLine (part_col_name)
For j = To part_row_k
If j = part_row_k Then
part_col_name = TB & LCase(Cells(j, )) & " " & UCase(Cells(j, )) & " COMMENT '" & Trim(Cells(j, )) & "'"
SqlFileDDL.WriteLine (part_col_name)
Exit For
End If
part_col_name = TB & LCase(Cells(j, )) & " " & UCase(Cells(j, )) & " COMMENT '" & Trim(Cells(j, )) & "'" & ","
SqlFileDDL.WriteLine (part_col_name)
Next
End If
'加上生命周期
SqlFileDDL.WriteLine ("LIFECYCLE " & lifecycle & ";")
MsgBox ("生成成功!生成路径为:" & dir)
End Sub

  模板:

excel的宏与VBA实践——建表语句

  更新版本2.1:

Sub createTableDDL()
'定义换行和TAB
Ln = Chr() + Chr()
TB = Chr()
'定义脚本目录
Dim dir AS String
dir = "C:\CREATE_TABLE_DDL"
Set FSOE = CreateObject("Scripting.FileSystemObject")
If FSOE.folderexists(dir) = False Then MkDir dir
'调用脚本定义
Set SqlFileDDL = FSOE.CreateTextFile("C:\CREATE_TABLE_DDL\create_table_ddl.sql", True)
'获得表名
tableName = Trim(Cells(, ).Value)
'获得表注释
tableComment = Trim(Cells(, ).Value)
'获得生命周期
lifecycle = Trim(Cells(, ).Value)
'获得创建者
createBy = Trim(Cells(, ).Value)
Dim dt As Date
dt = Format(Date, "yyyy-mm-dd")
'获得当前日期
createDate = dt
'获得A列已使用的行数
count_row_k = [A65536].End(xlUp).Row
'获得E列已使用的行数
part_row_k = [E65536].End(xlUp).Row
'定义SQL
SQL = "--创建者:" & createBy & Ln & _
"--创建时间:" & createDate & Ln & _
"DROP TABLE IF EXISTS " & tableName & " ;" & Ln & _
"CREATE TABLE " & tableName & "("
'写入文件
SqlFileDDL.WriteLine (SQL)
For i = To count_row_k -
col_name = TB & LCase(Cells(i, )) & " " & UCase(Cells(i, )) & " COMMENT '" & Trim(Cells(i, )) & "',"
SqlFileDDL.WriteLine(col_name)
Next
'最后一列
i = count_row_k
col_name = TB & LCase(Cells(i, )) & " " & UCase(Cells(i, )) & " COMMENT '" & Trim(Cells(i, )) & "'" & Ln & ")"
SqlFileDDL.WriteLine(col_name)
SqlFileDDL.WriteLine("COMMENT '" & tableComment & "'")
'加上分区列
If part_row_k > Then
part_col_name = "PARTITIONED BY ("
SqlFileDDL.WriteLine(part_col_name)
For j = To part_row_k -
part_col_name = TB & LCase(Cells(j, )) & " " & UCase(Cells(j, )) & " COMMENT '" & Trim(Cells(j,)) & "'" & ","
SqlFileDDL.WriteLine(part_col_name)
Next
j = part_row_k
part_col_name = TB & LCase(Cells(j, )) & " " & UCase(Cells(j, )) & " COMMENT '" & Trim(Cells(j,)) & "'"
SqlFileDDL.WriteLine(part_col_name)
End If
'加上生命周期
SqlFileDDL.WriteLine("LIFECYCLE " & lifecycle & ";")
MsgBox("生成成功!生成路径为:" & dir)
End Sub

//模板在微云文件中。

二、反向解析

  通过DDL,反向解析出字段填充到excel中

  反向解析1.0:

REM "获取数组长度"
Public Function ArrayLength(ByVal ary) As Integer
ArrayLength = UBound(ary) - LBound(ary) +
End Function Sub resverse_parse()
'获得表名
tableDDL = Trim(Cells(, ).Value)
'截取语句字段部分(以括号分隔)
index_quote_left_1 = InStr(,tableDDL,"(")
index_quote_right_1 = InStr(,tableDDL,")")
index_quote_left_2 = InStr(index_quote_right_1 + ,tableDDL,"(")
index_quote_right_2 = InStr(index_quote_right_1 + ,tableDDL,")")
table_comment_content = Mid(tableDDL,index_quote_right_1 + ,index_quote_left_2)
table_comment_arr = Split(table_comment_content, "'")
table_comment = table_comment_arr()
Cells(, ).Value = table_comment
table_name = Mid(tableDDL,,index_quote_left_1 - )
Cells(, ).Value = table_name
table_content = Mid(tableDDL,index_quote_left_1,index_quote_right_1 - index_quote_left_1 - )
content_arr = Split(table_content,",")
content_len = ArrayLength(content_arr) For i = to content_len - step
col_arr = Split(content_arr(i)," ")
j = i +
col_name = col_arr()
col_comment = Replace(col_arr(),"'","")
MsgBox col_comment
Cells(j, ).Value = col_name
Cells(j, ).Value = col_comment
Next i
End Sub

  清除重置1.1(1.0为直接清除指定区域版,已废弃):

Sub clear()
'采用先清除后填充的策略
Range("A1:D256").ClearContents
'填充
Cells(, ).Value = "建表DDL"
Cells(, ).Value = "表名"
Cells(, ).Value = "表注释"
Cells(, ).Value = "列名"
Cells(, ).Value = "列注释"
End Sub

  模板文件在百度云中,图示如下:

  excel的宏与VBA实践——建表语句

上一篇:CentOS7 下linux不能上网解决方法​,centos7 eth0 没有ip,IP突然丢失


下一篇:jQuery Countdown Timer 倒计时效果