VBA生成SQL语句

dinggela

　　'
　　'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