【VBA】基于EXCEL生成Insert语句工具

工具介绍

基于Excel生成INSERT语句工具是一个辅助工具，用于帮助用户根据Excel数据生成INSERT语句。通常，在数据库中插入大量数据时，手动编写INSERT语句会非常繁琐和耗时。而使用这个工具，可以通过Excel中的数据自动生成相应的INSERT语句，从而简化操作。

本工具会根据字段数量、字段类型、字段长度动态生成随机数据，只使用于生成大量测试数据

该工具的基本概述如下：
excel截图：
在这里插入图片描述

确定表名：在Excel中，A2单元格确定填写表的名字，确定生成数据条数
确定字段信息：第三行将其作为INSERT语句中的字段名、第四行是字段类型、第五行是字段长度。
准备模板：在Excel的另一个工作表中，创建一个INSERT sheet页。可以按照MySQL、SQL Server或其他数据库的语法要求进行模板的设计。
在这里插入图片描述

生成INSERT语句：在模板中，使用Excel的一些函数（如CONCATENATE、TEXT、IF等）将表名、字段名和对应的数据拼接成INSERT语句。使用适当的引号来处理字符串数据。

复制公式：将生成的INSERT语句公式应用到每一行的数据，并自动适应相应的字段和数据。

导出结果：将生成的INSERT语句复制到文本文件或数据库工具中，然后执行插入操作。

操作：A2填写表名称，F2中填写生成insert条数；点击【Data production】生成数据，点击【Insert作成】在Insert sheet中生成insert语句；粘贴到数据库视图工具执行即可。

代码片段

Data production按钮

Private Sub CommandButton2_Click()
    Dim arr() As String
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    'A列最后一个非空单元格所在的行号。
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    '最后一个非空单元格所在的列号。
    lastColumn = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column
    
   '此for是生成多少条数据
    For r = 1 To CInt(ws.Cells(2, 6).Value)
    	'遍历列
        For i = 1 To lastColumn
            '判断是都是decmial类型
            If ws.Cells(4, i).Value = "QUAN" Or ws.Cells(4, i).Value = "DEC" Then
            	'用于将单元格 ws.Cells(r + 5, i) 的格式设置为文本格式的代码。
                ws.Cells(r + 5, i).NumberFormat = "@"
                '判断是否包含逗号
                If InStr(ws.Cells(5, i).Value, ",") > 0 Then
                    '截取
                    arr = Split(ws.Cells(5, i).Value, ",")
                    '赋值，GenerateRandomData()类型处理方法
                    ws.Cells(r + 5, i).Value = CStr(GenerateRandomData(ws.Cells(4, i).Value, CInt(arr(0)) - CInt(arr(1)), CInt(arr(1))))
                Else
                    ws.Cells(r + 5, i).Value = CStr(GenerateRandomData(ws.Cells(4, i).Value, ws.Cells(5, i).Value, 0))
                End If
            Else
                ws.Cells(r + 5, i).Value = GenerateRandomData(ws.Cells(4, i).Value, ws.Cells(5, i).Value, 0)
            End If
        Next i
    Next r
End Sub

GenerateRandomData()类型处理方法

Function GenerateRandomData(dataType As String, length As Integer, decimalPlaces As Integer) As Variant
    Dim result As Variant
    
    Select Case dataType
        Case "INT4"
            result = Int((10 ^ length - 1) * Rnd)
            
        Case "CHAR", "TIMS", "CUKY"
            Dim validChars As String
            validChars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
            
            Dim i As Integer
            For i = 1 To length
                result = result & Mid(validChars, Int((Len(validChars) * Rnd) + 1), 1)
            Next i
            
        Case "DATS"
            Dim todayDate As String
            todayDate = Format(Date, "yyyy-mm-dd")
            result = todayDate
            
        Case "NUMC"
            Dim numcChars As String
            numcChars = "0123456789"
            
            Dim j As Integer
            For j = 1 To length
                result = result & Mid(numcChars, Int((Len(numcChars) * Rnd) + 1), 1)
            Next j
            
        Case "DEC", "QUAN", "CURR"
            Dim minValue As Double
            Dim maxValue As Double
            minValue = 10 ^ (length - decimalPlaces) - 1
            maxValue = 10 ^ length - 10 ^ (length - decimalPlaces)
            result = minValue + Rnd * (maxValue - minValue)
            result = Round(result, decimalPlaces)
            
        Case Else
            result = "Invalid data type."
    End Select
    
    GenerateRandomData = result
End Function

Insert作成按钮

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim lastRow As Integer
    Dim insertSQL As String
    Dim fieldNames As String
    Dim fieldValues As String
    Dim fieldName As String
    Dim fieldValue As String
    Dim i As Integer
    
 	'获取工作薄
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Set Insert = ThisWorkbook.Sheets("Insert")
    
 	'A列最后一个非空单元格所在的行号。
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    '最后一个非空单元格所在的列号。
    lastColumn = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column
    
    '遍历列，拼接列字符串
    For i = 1 To lastColumn
        fieldName = ws.Cells(3, i).Value
        
        
        fieldNames = fieldNames & """" & fieldName & """, "
    Next i
        
        
    fieldNames = Left(fieldNames, Len(fieldNames) - 2)
    Debug.Print fieldNames
    '遍历行    
    For i = 6 To lastRow
        fieldValues = ""
         '遍历列   
        For j = 1 To lastColumn
        	'获取并拼接insert数据
            If ws.Cells(4, j).Value = "DATS" Then
                fieldValue = "TO_DATE('" & ws.Cells(i, j).Value & "', 'YYYY-MM-DD')"
                
            Else
                fieldValue = ws.Cells(i, j).Value
                fieldValue = "'" & Replace(fieldValue, "'", "''") & "'"
            End If
            
            
            fieldValues = fieldValues & fieldValue & ", "
        Next j
        
   
        fieldValues = Left(fieldValues, Len(fieldValues) - 2)
        
		'生成并输出insert 语句
        insertSQL = "INSERT INTO " & ws.Range("A2").Value & "(" & fieldNames & ") VALUES (" & fieldValues & ");"
        
        Insert.Range("A" & i - 5).Value = insertSQL
       
        Debug.Print insertSQL
    Next i
End Sub