ExcelVBA函数生成SQL.xlsm

ExcelVBA函数生成SQL.xlsm

注意：一定要保存xlsm，2007之后的版本不能用xlsx保存，这种宏是无法保存的。

需要在Excel宏中添加模块1如下：

'---生成SQL----
Public Sub btnSql_Click()
    Dim sqls As String
    Dim sqlHead As String

    For si = 1 To Workbooks(1).Sheets.Count '从第一张表开始,遍历每一张表

        Set mysheet = Workbooks(1).Sheets(si) '表
        'tableName = mysheet.Range("E1").Value '英文表名


        '如果数据库中已存在表，则删除表
        'if exists drop table tableName
        sqlHead = "INSERT INTO USER(ID,NAME,AGE)  VALUES("


        For i = 2 To mysheet.UsedRange.Rows.Count '遍历所有的列,字段从第4行开始
            Dim inserSql As String
            Dim deleteSql As String
            Dim userId As String
            Dim userName As String
            Dim userAge As String

            userId = mysheet.Range("A" & i).Value 'ID
            userName = mysheet.Range("B" & i).Value 'NAME
            userAge = mysheet.Range("C" & i).Value 'AGE

            inserSql = sqlHead & "'" & userId & "','" & userName & "','" & userAge & "');" '插入SQL
            deleteSql = "DELETE FROM USER WHERE ID='" & userId & "';" '插入SQL

             sqls = sqls & deleteSql & vbCrLf & inserSql & vbCrLf

        Next i

        '建表完成

    Next si
    '将得到的所有的sql语句放入sqlForDatabase.txt文件中
    Open "D:\sqlForDatabase.txt" For Output As #1
    Print #1, sqls
    MsgBox "D:\sqlForDatabase.txt 保存成功！"
    Close #1
End Sub

需要在Excel宏中添加模块2如下：

'--- MD5函数 ---
Option Explicit
Option Base 0

Public Type MD5_CTX
    i(1) As Long
    buf(3) As Long
    inc(63) As Byte
    digest(15) As Byte
End Type

Public Declare Sub MD5Init Lib "Cryptdll.dll" (ByVal pContex As Long)
Public Declare Sub MD5Final Lib "Cryptdll.dll" (ByVal pContex As Long)
Public Declare Sub MD5Update Lib "Cryptdll.dll" (ByVal pContex As Long, ByVal lPtr As Long, ByVal nSize As Long)

Public Function ConvBytesToBinaryString(bytesIn() As Byte) As String
    Dim i As Long
    Dim nSize As Long
    Dim strRet As String

    nSize = UBound(bytesIn)
    For i = 0 To nSize
         strRet = strRet & Right$("0" & Hex(bytesIn(i)), 2)
    Next
    ConvBytesToBinaryString = strRet
End Function

Public Function GetMD5Hash(bytesIn() As Byte) As Byte()
    Dim ctx As MD5_CTX
    Dim nSize As Long

    nSize = UBound(bytesIn) + 1

    MD5Init VarPtr(ctx)
    MD5Update ByVal VarPtr(ctx), ByVal VarPtr(bytesIn(0)), nSize
    MD5Final VarPtr(ctx)

    GetMD5Hash = ctx.digest
End Function

Public Function GetMD5Hash_Bytes(bytesIn() As Byte) As String
    GetMD5Hash_Bytes = ConvBytesToBinaryString(GetMD5Hash(bytesIn))
End Function

Public Function GetMD5Hash_String(ByVal strIn As String) As String
    GetMD5Hash_String = GetMD5Hash_Bytes(StrConv(strIn, vbFromUnicode))
End Function

Public Function GetMD5Hash_File(ByVal strFile As String) As String
    Dim lFile As Long
    Dim bytes() As Byte
    Dim lSize As Long

    lSize = FileLen(strFile)
    If (lSize) Then
        lFile = FreeFile
        ReDim bytes(lSize - 1)
        Open strFile For Binary As lFile
        Get lFile, , bytes
        Close lFile
        GetMD5Hash_File = GetMD5Hash_Bytes(bytes)
    End If
End Function

产生的SQL如下：

DELETE FROM USER WHERE ID='CB25999EA16CF93ADFD0DA0244E42453';
INSERT INTO USER(ID,NAME,AGE)  VALUES('CB25999EA16CF93ADFD0DA0244E42453','张三','19');
DELETE FROM USER WHERE ID='4A885791BCDADED0F8640D030071D78C';
INSERT INTO USER(ID,NAME,AGE)  VALUES('4A885791BCDADED0F8640D030071D78C','李四','21');
DELETE FROM USER WHERE ID='EB80B15113B7F73B4AF326C5DAB1BF8F';
INSERT INTO USER(ID,NAME,AGE)  VALUES('EB80B15113B7F73B4AF326C5DAB1BF8F','王五','23');
DELETE FROM USER WHERE ID='C2C2D9B9732420B3894EBAA9A5974447';
INSERT INTO USER(ID,NAME,AGE)  VALUES('C2C2D9B9732420B3894EBAA9A5974447','赵六','25');
DELETE FROM USER WHERE ID='FB7F540AFB9937A41323D7566E1F980C';
INSERT INTO USER(ID,NAME,AGE)  VALUES('FB7F540AFB9937A41323D7566E1F980C','赵六1','26');

---