ExcelVBA函数生成SQL.xlsm
注意:一定要保存xlsm,2007之后的版本不能用xlsx保存,这种宏是无法保存的。
需要在Excel宏中添加模块1如下:
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)
sqlHead = "INSERT INTO USER(ID,NAME,AGE) VALUES("
For i = 2 To mysheet.UsedRange.Rows.Count
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
userName = mysheet.Range("B" & i).Value
userAge = mysheet.Range("C" & i).Value
inserSql = sqlHead & "'" & userId & "','" & userName & "','" & userAge & "');"
deleteSql = "DELETE FROM USER WHERE ID='" & userId & "';"
sqls = sqls & deleteSql & vbCrLf & inserSql & vbCrLf
Next i
Next si
Open "D:\sqlForDatabase.txt" For Output As #1
Print #1, sqls
MsgBox "D:\sqlForDatabase.txt 保存成功!"
Close #1
End Sub
需要在Excel宏中添加模块2如下:
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');