VERSION 1.0 CLASS
BEGIN
MultiUse = -1
END
Attribute VB_Name = "cls試験データテーブル"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
Const cnstインデント = " "
Private obj対象シート As Worksheet
Public Enum 型種別
NUMBER系 = 1
TIMESTAMP系 = 2
DATE系 = 3
CHAR系 = 4
End Enum
Private Type 型桁情報
型名 As String
型種別 As 型種別
桁数 As Long
End Type
Private lngテーブル名記載行 As Long
Private lngカラム論理名記載行 As Long
Private lngカラム物理名記載行 As Long
Private lng型桁記載行 As Long
Private lng制約記載行 As Long
Private lngデータ開始行 As Long
Private lngデータ終了行 As Long
Private lngカラム開始列 As Long
Private lngカラム終了列 As Long
Private txtテーブル論理名, txtテーブル物理名 As String
Private isHidden As Boolean
Private lngDBCount結果 As Long
Private obj設定シート As cls設定シート
Public Property Set 対象シート(ByRef arg対象シート)
Set obj対象シート = arg対象シート
End Property
Public Property Get 対象シート() As Worksheet
If obj対象シート Is Nothing Then
Set obj対象シート = ActiveSheet
End If
Set 対象シート = obj対象シート
End Property
Public Property Get カラム論理名記載行()
カラム論理名記載行 = lngカラム論理名記載行
End Property
Public Property Get データ開始行()
データ開始行 = lngデータ開始行
End Property
Public Property Get データ終了行()
データ終了行 = lngデータ終了行
End Property
Public Property Get カラム終了列()
カラム終了列 = lngカラム終了列
End Property
Public Property Get cls設定シート() As cls設定シート
Set cls設定シート = obj設定シート
End Property
Public Property Let cls設定シート(ByRef arg設定シート As cls設定シート)
Set obj設定シート = arg設定シート
End Property
Public Sub setテーブル名記載行(ByVal argテーブル名記載行)
lngテーブル名記載行 = argテーブル名記載行
lngカラム物理名記載行 = argテーブル名記載行 + 1
lngカラム論理名記載行 = argテーブル名記載行 + 2
lng型桁記載行 = argテーブル名記載行 + 3
lng制約記載行 = argテーブル名記載行 + 4
lngデータ開始行 = argテーブル名記載行 + 5
lngカラム開始列 = CAlp2Num(cls設定シート.カラム開始列)
lngカラム終了列 = ActiveSheet.Cells(lngカラム物理名記載行, lngカラム開始列).End(xlToRight).Column
txtテーブル論理名 = ActiveSheet.Range("C" & lngテーブル名記載行).Value
txtテーブル物理名 = ActiveSheet.Range("A" & lngテーブル名記載行).Value
isHidden = ActiveSheet.Cells(lngカラム論理名記載行, 1).EntireRow.Hidden
End Sub
Public Function getテーブル名記載行() As Long
getテーブル名記載行 = lngテーブル名記載行
End Function
Public Sub setデータ終了行(ByVal param As Long)
lngデータ終了行 = param
End Sub
Public Function Hidden()
Hidden = isHidden
End Function
Public Function getテーブル物理名()
getテーブル物理名 = txtテーブル物理名
End Function
Public Function getテーブル論理名()
getテーブル論理名 = txtテーブル論理名
End Function
Public Function getカラム論理名(ByVal arg指定カラム列 As Long) As String
getカラム論理名 = Me.対象シート.Cells(lngカラム論理名記載行, arg指定カラム列)
End Function
Public Function setDBCount結果(ByVal param As Long)
lngDBCount結果 = param
End Function
Public Function getDBCount結果() As Long
getDBCount結果 = lngDBCount結果
End Function
Public Function get件数()
get件数 = Me.対象シート.Cells(lngテーブル名記載行, 6)
End Function
Public Function addOrderBy(ByVal txtQuery As String) As String
Dim Re As Object: Set Re = CreateObject("VBScript.RegExp")
Re.Pattern = "(.+? FROM)"
If txtQuery Like "* UNION *" Then
addOrderBy = Re.Replace(txtQuery, "SELECT * FROM ( $1") & " ) DUMMY "
Else
addOrderBy = txtQuery
End If
Dim var主キー As Variant
var主キー = get主キー()
If Not IsEmpty(var主キー) Then
addOrderBy = addOrderBy & " ORDER BY " & Join(var主キー, ", ")
End If
End Function
Public Function createSELECT文From単行(Optional argデータ行 As Long = -1) As String
With ActiveSheet
Dim j As Long
Dim stbSELECT文 As New clsStringBuilder
Dim stbWHERE句 As New clsStringBuilder
For j = 2 To lngカラム終了列
If stbSELECT文.length = 0 Then
stbSELECT文.append ("SELECT ")
Else
If stbSELECT文.lastLineLength > cls設定シート.折返文字数 Then
Call stbSELECT文.appendLine("")
End If
stbSELECT文.append (", ")
End If
Dim txtカラム値 As String
txtカラム値 = editカラム値(.Cells(lngカラム物理名記載行, j).Value, .Cells(lng型桁記載行, j).Value, True)
If txtカラム値 Like "*(*" Then
stbSELECT文.append (txtカラム値 & " " & .Cells(lngカラム物理名記載行, j).Value)
Else
stbSELECT文.append (txtカラム値)
End If
If argデータ行 = -1 Then
GoTo continue
End If
If .Cells(argデータ行, j).Value <> "" Then
If stbWHERE句.length = 0 Then
stbWHERE句.append (" WHERE ")
Else
If stbWHERE句.lastLineLength > cls設定シート.折返文字数 Then
Call stbWHERE句.appendLine("")
End If
stbWHERE句.append (" AND ")
End If
stbWHERE句.append (.Cells(lngカラム物理名記載行, j).Value)
stbWHERE句.append (" = ")
stbWHERE句.append (editカラム値(.Cells(argデータ行, j).Value, .Cells(lng型桁記載行, j).Value, False))
End If
continue:
Next j
End With
createSELECT文From単行 = stbSELECT文.toString & " FROM " & txtテーブル物理名 & stbWHERE句.toString
End Function
Public Function createSELECT文From複数行() As String
Dim stbQuery As New clsStringBuilder
If getデータ行の入力数() > 0 Then
Dim j As Long
For j = lngデータ開始行 To lngデータ終了行
If getデータ行の入力数(j) > 0 Then
If stbQuery.length <> 0 Then
stbQuery.append (vbCrLf & " UNION ")
End If
stbQuery.append (createSELECT文From単行(j))
End If
Next j
Else
stbQuery.append (createSELECT文From単行())
End If
createSELECT文From複数行 = addOrderBy(stbQuery.toString)
End Function
Public Function createSELECT文From複数行To複数SQL(Optional ByVal is選択行のみ As Boolean = False) As String
Dim txtQuery As String
If getデータ行の入力数() > 0 Then
Dim j As Long
For j = lngデータ開始行 To lngデータ終了行
If is選択行のみ And Not is選択状態(j) Then
GoTo jContinue
End If
If getデータ行の入力数(j) > 0 Then
txtQuery = txtQuery & addOrderBy(createSELECT文From単行(j)) & ";" & vbCrLf
End If
jContinue:
Next j
End If
If txtQuery = "" Then
Dim k As Long
For k = lngデータ開始行 To lngデータ終了行
If is選択行のみ And Not is選択状態(k) Then
Else
txtQuery = addOrderBy(createSELECT文From単行()) & ";" & vbCrLf
Exit For
End If
Next k
End If
If txtQuery <> "" Then
txtQuery = vbCrLf & "-- " & txtテーブル論理名 & " " & txtテーブル論理名 & vbCrLf & txtQuery
createSELECT文From複数行To複数SQL = txtQuery
End If
End Function
Public Function createInsert文(ByRef obj引数対象シート As Worksheet, Optional ByVal is選択行のみ As Boolean = False) As String
Dim txt結果 As New clsStringBuilder
Dim txtInsertInto As New clsStringBuilder
Dim txtInsertValues As New clsStringBuilder
With obj引数対象シート
If .Range("B" & lngデータ開始行).Value = "" Then
Exit Function
End If
Dim j, k As Long
For j = lngデータ開始行 To lngデータ終了行
If .Range("B" & j).Value = "" Then
GoTo jContinue
End If
If is選択行のみ And Not is選択状態(j) Then
GoTo jContinue
End If
Call txtInsertInto.append("INSERT INTO " & txtテーブル物理名 & " (")
Call txtInsertValues.append(" VALUES (")
For k = 2 To lngカラム終了列
If k > 2 Then
If txtInsertInto.lastLineLength > cls設定シート.折返文字数 Then
Call txtInsertInto.appendLine("")
End If
Call txtInsertInto.append(", ")
If txtInsertValues.lastLineLength > cls設定シート.折返文字数 Then
Call txtInsertValues.appendLine("")
End If
Call txtInsertValues.append(", ")
End If
Call txtInsertInto.append(.Cells(lngカラム物理名記載行, k))
Call txtInsertValues.append(editカラム値(.Cells(j, k), .Cells(lng型桁記載行, k)))
Next k
Call txt結果.append(txtInsertInto.toString)
Call txt結果.append(")")
Call txt結果.append(vbCrLf)
Call txt結果.append(" ")
Call txt結果.append(txtInsertValues.toString)
Call txt結果.append(");")
Call txt結果.append(vbCrLf)
txtInsertInto.length = 0
txtInsertValues.length = 0
jContinue:
Next j
End With
If txt結果.length <> 0 Then
Call txt結果.insertHead(vbCrLf & "-- " & txtテーブル論理名 & " " & txtテーブル物理名 & vbCrLf)
createInsert文 = txt結果.toString
End If
Set txt結果 = Nothing
End Function
Public Function createUpdate文(ByRef obj引数対象シート As Worksheet, Optional ByVal is選択行のみ As Boolean) As String
Dim stb結果 As New clsStringBuilder
Dim stbUpdate As New clsStringBuilder
Dim stbWHERE As New clsStringBuilder
With Me.対象シート
If .Range("B" & lngデータ開始行).Value = "" Then
Exit Function
End If
Dim var主キー As Variant
var主キー = Me.get主キー()
Dim j, k As Long
For j = lngデータ開始行 To lngデータ終了行
If .Range("B" & j).Value = "" Then
GoTo jContinue
End If
If is選択行のみ And Not is選択状態(j) Then
GoTo jContinue
End If
For k = 2 To lngカラム終了列
If containArray(var主キー, .Cells(lngカラム物理名記載行, k)) Then
If stbWHERE.length = 0 Then
stbWHERE.append (" WHERE ")
Else
If stbWHERE.lastLineLength > cls設定シート.折返文字数 Then
Call stbWHERE.appendLine("")
End If
stbWHERE.append (" AND ")
End If
stbWHERE.append (.Cells(lngカラム物理名記載行, k))
stbWHERE.append (" = ")
stbWHERE.append (editカラム値(.Cells(j, k), .Cells(lng型桁記載行, k)))
Else
If stbUpdate.length = 0 Then
stbUpdate.append ("UPDATE " & txtテーブル物理名 & " SET ")
Else
If stbUpdate.lastLineLength > cls設定シート.折返文字数 Then
Call stbUpdate.appendLine("")
End If
stbUpdate.append (" , ")
End If
stbUpdate.append (.Cells(lngカラム物理名記載行, k))
stbUpdate.append (" = ")
stbUpdate.append (editカラム値(.Cells(j, k), .Cells(lng型桁記載行, k)))
End If
Next k
stb結果.append (stbUpdate.toString & vbCrLf)
stb結果.append (" ")
stb結果.append (stbWHERE.toString & ";" & vbCrLf)
stbUpdate.length = 0
stbWHERE.length = 0
jContinue:
Next j
End With
If stb結果.length <> 0 Then
stb結果.insertHead (vbCrLf & "-- " & txtテーブル論理名 & " " & txtテーブル物理名 & vbCrLf)
createUpdate文 = stb結果.toString
End If
Set stb結果 = Nothing
Set stbUpdate = Nothing
Set stbWHERE = Nothing
End Function
Public Function createDelete文(ByRef obj引数対象シート As Worksheet, Optional ByVal is選択行のみ As Boolean) As String
Dim Re As Object: Set Re = CreateObject("VBScript.RegExp")
Re.Global = True
Re.Pattern = "UPDATE .+\n "
Dim txt結果 As String
txt結果 = Re.Replace(createUpdate文(obj引数対象シート, is選択行のみ), "DELETE FROM " & txtテーブル物理名)
createDelete文 = txt結果
End Function
Public Function createCount文(ByVal txtQuery As String) As String
Dim Re As Object: Set Re = CreateObject("VBScript.RegExp")
Re.Pattern = "(SELECT .+? FROM)"
createCount文 = Re.Replace(txtQuery, "SELECT COUNT(*) AS COUNT FROM")
End Function
Public Sub copy枠Toクリップボード()
ActiveWorkbook.ActiveSheet.Rows(lngテーブル名記載行 & ":" & lngデータ開始行).Copy
End Sub
Public Sub clearデータ行()
Me.対象シート.Rows(lngデータ開始行 & ";" & lngデータ終了行).ClearContents
Me.対象シート.Rows(lngデータ開始行 & ";" & lngデータ終了行).ClearComments
End Sub
Public Sub set抽出条件(ByVal var自動設定値 As Variant)
With Me.対象シート
Dim i As Long
For i = 2 To lngカラム終了列
Dim j As Long
For j = LBound(var自動設定値) To UBound(var自動設定値)
If .Cells(lngカラム物理名記載行, i) = var自動設定値(j, 1) Then
.Cells(lngカラム物理名記載行, i) = var自動設定値(j, 2)
End If
Next j
Next i
End With
End Sub
Public Sub add空行(ByVal arg追加行番号 As Long)
Me.対象シート.Rows(lngデータ開始行).Copy
Me.対象シート.Rows(arg追加行番号).Insert
Me.対象シート.Rows(arg追加行番号).ClearContents
Me.対象シート.Rows(arg追加行番号).ClearComments
Application.CutCopyMode = False
End Sub
Public Sub edit選択行強調(ByVal arg選択行番号 As Long)
With Me.対象シート
Call Me.edit変更強調色(.Range(.Cells(arg選択行番号, 2), .Cells(arg選択行番号, lngカラム終了列)))
End With
End Sub
Public Sub edit変更強調色(ByRef arg修飾範囲 As Range)
With arg修飾範囲.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 6750207
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Public Sub edit選択行網掛(ByVal arg選択行番号 As Long)
With Me.対象シート
Call Me.edit網掛(.Range(.Cells(arg選択行番号, 2), .Cells(arg選択行番号, lngカラム終了列)))
End With
End Sub
Public Sub edit網掛(ByRef rng修飾範囲 As Range)
With rng修飾範囲.Interior
.Pattern = xlGray16
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Function get主キー項目連結文字列(ByVal arg対象データ行 As Long) As String
get主キー項目連結文字列 = Join(get主キー(arg対象データ行))
End Function
Private Function getデータ行の入力数(Optional arg対象データ行 = -1)
With ActiveSheet
If arg対象データ行 = -1 Then
getデータ行の入力数 = WorksheetFunction.CountA( _
.Range(.Cells(lngデータ開始行, 2), .Cells(lngデータ終了行, lngカラム終了列)))
Else
getデータ行の入力数 = WorksheetFunction.CountA( _
.Range(.Cells(arg対象データ行, 2), .Cells(arg対象データ行, lngカラム終了列)))
End If
End With
End Function
Public Function get主キー(Optional ByVal arg対象データ行 As Long = -1) As Variant
If arg対象データ行 = -1 Then
arg対象データ行 = lngカラム物理名記載行
End If
Dim var主キー As Variant
ReDim var主キー(1 To lngカラム終了列)
Dim i, lng主キー数 As Long
For i = 2 To lngカラム終了列
With Me.対象シート
If IsNumeric(.Cells(lng制約記載行, i).Value) And .Cells(lng制約記載行, i).Value <> "" Then
lng主キー数 = lng主キー数 + 1
var主キー(lng主キー数) = .Cells(arg対象データ行, i).Value
End If
End With
Next
If lng主キー数 = 0 Then
get主キー = Empty
Else
ReDim Preserve var主キー(1 To lng主キー数)
get主キー = var主キー
End If
End Function
Private Function editカラム値( _
ByVal argカラム値 As String, ByVal arg型桁 As String, Optional ByVal is列名 = False) As String
arg型桁 = UCase(arg型桁)
If argカラム値 = "" Or argカラム値 Like "*(NULL)*" Then
editカラム値 = "NULL"
Exit Function
End If
If arg型桁 Like "DATE*" Then
If UCase(argカラム値) = "SYSTIMESTAMP" Or UCase(argカラム値) = "SYSDATE" Then
editカラム値 = argカラム値
Else
If is列名 Then
editカラム値 = obj設定シート.日付関数 & "(" & argカラム値 & ", '" & obj設定シート.日付形式 & "')"
Else
editカラム値 = obj設定シート.日付関数 & "('" & argカラム値 & "', '" & obj設定シート.日付形式 & "')"
End If
End If
ElseIf arg型桁 Like "TIMESTAMP*" Then
If UCase(argカラム値) = "SYSTIMESTAMP" Or UCase(argカラム値) = "SYSDATE" Then
editカラム値 = argカラム値
Else
If is列名 Then
editカラム値 = obj設定シート.日時関数 & "(" & argカラム値 & ", '" & obj設定シート.日時形式 & "')"
Else
editカラム値 = obj設定シート.日時関数 & "('" & argカラム値 & "', '" & obj設定シート.日時形式 & "')"
End If
End If
ElseIf arg型桁 Like "NUMBER*" Or arg型桁 Like "INT*" Or arg型桁 Like "FLOAT*" Then
editカラム値 = argカラム値
ElseIf arg型桁 Like "VARCHAR*" Or arg型桁 Like "CHAR*" Then
If is列名 Then
editカラム値 = argカラム値
Else
editカラム値 = "'" & argカラム値 & "'"
End If
Else
MsgBox "処理できない型:" & arg型桁
End If
End Function