VBA生成SQL语句
''Date: 2012/05/04
'Author: xi wei cheng
'
'Option Explicit
Public Sub SqlInsert()
Dim flg As Boolean
flg = Worksheets("SQL-Tool").CheckBox1.value
Dim template, t, t1, t2 As String
template = "insert into {tScame}.{tName} ({colNameArr}) VALUES ({colValArr})"
t = "String sqlInsert{index} = " & Chr(34) & "{template}" & Chr(34) & ";"
Dim tScame, tName, colNameArr, colValArr As String
tScame = Range("D5").value
tName = Range("D3").value
colNameArr = ""
colValArr = ""
If tScame = "" Then
MsgBox "[ƒXƒL[ƒ}] can't be empty!"
Exit Sub
End If
If tName = "" Then
MsgBox "[ƒe[ƒuƒ‹–¼i·¨—–¼j] can't be empty!"
Exit Sub
End If
'11
Dim i, j, lastCol As Integer
lastCol = Rows(11).Worksheet.Range("IV11").End(xlToLeft).Column
Dim colName, colVal, colType As String
colName = ""
colVal = ""
For i = 2 To lastCol
colName = Cells(11, i).value
colNameArr = colNameArr & colName & ", "
Next i
colNameArr = Strings.Left(colNameArr, Len(colNameArr) - 2)
t1 = Strings.Replace(template, "{tScame}", tScame)
t1 = Strings.Replace(t1, "{tName}", tName)
t1 = Strings.Replace(t1, "{colNameArr}", colNameArr)
'MsgBox t1
Dim lastRow As Integer
lastRow = MaxRowIndex(ActiveSheet)
If lastRow[" & colType & "]."
End Select
Next j
colValArr = Strings.Left(rowData, Len(rowData) - 2)
t2 = Strings.Replace(t1, "{colValArr}", colValArr)
If flg Then
t2 = Strings.Replace(t, "{template}", t2)
t2 = Strings.Replace(t2, "{index}", i - 16)
sqlIdArr = sqlIdArr & "sqlInsert" & (i - 16) & ", "
End If
sqlArr = sqlArr & t2 & vbCrLf
Next i
If flg Then
sqlArr = sqlArr & vbCrLf _
& "TestUtil tu = new TestUtil();" & vbCrLf _
& "String[] sqlArr = {" & Strings.Left(sqlIdArr, Len(sqlIdArr) - 2) & "};" & vbCrLf _
& "for(String sql : sqlArr){" & vbCrLf _
& " tu.runBySql(sql);" & vbCrLf _
& "}"
End If
Dim dataObj As DataObject
Set dataObj = New DataObject
dataObj.SetText sqlArr
dataObj.PutInClipboard
MsgBox "The insert Sql has been put into the clipboard."
End Sub
Function MaxRowIndex(ws As Worksheet)
Dim i, index, tempIndex As Integer
index = 0
For i = 1 To 100
tempIndex = ws.Cells(65536, i).End(xlUp).row
If tempIndex > index Then index = tempIndex
Next
MaxRowIndex = index
End Function
Public Sub SqlDelete()
Dim flg As Boolean
flg = Worksheets("SQL-Tool").CheckBox1.value
Dim template, t, t1, t2 As String
template = "delete from {tScame}.{tName} where {condition}"
t = "String sqlDelete{index} = " & Chr(34) & "{template}" & Chr(34) & ";"
Dim tScame, tName, condition As String
tScame = Range("D5").value
tName = Range("D3").value
condition = ""
If tScame = "" Then
MsgBox "[ƒXƒL[ƒ}] can't be empty!"
Exit Sub
End If
If tName = "" Then
MsgBox "[ƒe[ƒuƒ‹–¼i·¨—–¼j] can't be empty!"
Exit Sub
End If
Dim i, j, lastCol As Integer
lastCol = Rows(11).Worksheet.Range("IV11").End(xlToLeft).Column
Dim colName, colVal, colType, colKey As String
colName = ""
colVal = ""
colKey = ""
t1 = Strings.Replace(template, "{tScame}", tScame)
t1 = Strings.Replace(t1, "{tName}", tName)
'MsgBox t1
Dim lastRow As Integer
lastRow = MaxRowIndex(ActiveSheet)
If lastRow[" & colType & "]."
Case Else
MsgBox "Can't parse Column type-> [" & colType & "]."
End Select
End If
Next j
condition = Strings.Left(rowData, Len(rowData) - 5)
t2 = Strings.Replace(t1, "{condition}", condition)
If flg Then
t2 = Strings.Replace(t, "{template}", t2)
t2 = Strings.Replace(t2, "{index}", i - 16)
sqlIdArr = sqlIdArr & "sqlDelete" & (i - 16) & ", "
End If
sqlArr = sqlArr & t2 & vbCrLf
Next i
If flg Then
sqlArr = sqlArr & vbCrLf _
& "TestUtil tu = new TestUtil();" & vbCrLf _
& "String[] sqlArr = {" & Strings.Left(sqlIdArr, Len(sqlIdArr) - 2) & "};" & vbCrLf _
& "for(String sql : sqlArr){" & vbCrLf _
& " tu.runBySql(sql);" & vbCrLf _
& "}"
End If
Dim dataObj As DataObject
Set dataObj = New DataObject
dataObj.SetText sqlArr
dataObj.PutInClipboard
MsgBox "The delete Sql has been put into the clipboard."
End Sub
页:
[1]