前几天博主写了**《Excel VBA(Visual Basic)编程入门》**的博文,今天讲一个实用小案例。将我们excel定义好的表名和字段名等信息,生成sql建表语句。
一、Excel表结构 二、VBA语句 Public Sub class() Rem 声明字段row的开始行号 Const startRow As Integer = 13 Rem 声明字段row的结束行号 Const endRow As Integer = 28 Rem 声明表名 Dim tableName As String tableName = Range("E" & 6) Rem 声明主键 Dim primaryKey As String primaryKey = Range("F" & 13) Rem 声明表名注释 Dim tableComment As String tableComment = Range("E" & 7) Rem 声明字段名对应列 英文序号 Dim filedMetaNo As String filedMetaNo = "F" Rem 声明字段注释对应对应列 英文序号 Dim commentMetaNo As String commentMetaNo = "C" Rem 声明字段备注 对应列的 英文序号 Dim comment2MetaNo As String comment2MetaNo = "U" Rem 声明类型 对应列的 英文序号 Dim typeMetaNo As String typeMetaNo = "G" Rem 声明字长 对应的列的英文序号 Dim lengthMetaNo As String lengthMetaNo = "H" Rem 最终要拼接的sql Dim sqlStr As String sqlStr = "CREATE TABLE " & Range("E" & 6) & Chr(13) sqlStr = sqlStr & "(" & Chr(13) For count = startRow To endRow Rem 拼接 字段名 sqlStr = sqlStr & Replace(Range(filedMetaNo & count).Text, " ", "") Rem 拼接 字段类型(字段长度) sqlStr = sqlStr & " " & Range(typeMetaNo & count) If IsEmpty(Range(lengthMetaNo & count)) = False Then sqlStr = sqlStr & "(" & Range(lengthMetaNo & count) & ") " End If Rem 如果是主键,设置NOT NULL COMMENT If primaryKey = Range(filedMetaNo & count) Then sqlStr = sqlStr & " NOT NULL COMMENT " Else Rem 拼接 DEFAULT NULL COMMENT '字段名称注释(字段备注)' sqlStr = sqlStr & " DEFAULT NULL COMMENT " End If sqlStr = sqlStr & "'" & Range(commentMetaNo & count) If IsEmpty(Range(comment2MetaNo & count)) = False Then sqlStr = sqlStr & "(" & Range(comment2MetaNo & count) & ")" End If sqlStr = sqlStr & "'" sqlStr = sqlStr & "," & Chr(13) Next sqlStr = sqlStr & "PRIMARY KEY (" & primaryKey & ")" & Chr(13) sqlStr = sqlStr & ")ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='" & tableComment & "'" & Chr(13) Debug.Print sqlStr End Sub注意:通常的时候,excel保存不了,关闭excel直接没了,另存为xlsm格式就可以了。