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

2022-09-06 11:34:08

一、建表语句

　　不带分区版本：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 倒计时效果