Excel定義書からmigrationファイル書き出し

gistfile1.vb

Public Const COLUMN_NAME_X = 10
Public Const COLUMN_TYPE_X = 18
Public Const COLUMN_DIGIT_X = 24
Public Const COLUMN_NULL_X = 28
Public Const COLUMN_UNIQUE_X = 30
Public Const COLUMN_TYPE_START = 18
Public Const COLUMN_LIMIT_START = 45
Public Const COLUMN_NULL_START = 60

Public Type ColumnData
    name As String
    type As String
    digit As Integer
    null As Boolean
    unique As Boolean
End Type
'テーブル定義書の定義を読み取る
Function ReadColumnData(sheet As Worksheet) As ColumnData()
    Dim column() As ColumnData
    Dim idx As Long
    
    column_count = ColumnCount()
    ReDim column(column_count)
    
    idx = 0
    Do While idx <= column_count
        column(idx).name = sheet.Cells(15 + idx, COLUMN_NAME_X).Value
        column(idx).type = sheet.Cells(15 + idx, COLUMN_TYPE_X).Value
        column(idx).digit = sheet.Cells(15 + idx, COLUMN_DIGIT_X).Value
        
        str_null = sheet.Cells(15 + idx, COLUMN_NULL_X).Value

        If str_null = "不可" Then
            column(idx).null = False
        Else
            column(idx).null = True
        End If
        
        str_unique = sheet.Cells(15 + idx, COLUMN_UNIQUE_X).Value

        If str_null = "一意" Then
            column(idx).unique = True
        Else
            column(idx).unique = False
        End If
        
        idx = idx + 1
    Loop
    
    ReadColumnData = column
    
End Function
'マイグレーションファイルを生成する
Function GenerateMigrationFile(sheet As Worksheet, idx As Integer) As String

    Dim str As String
    Dim table_name As String
    Dim column_data() As ColumnData
    
    table_name = sheet.Cells(5, 8).Value
    column_data = ReadColumnData(sheet)
    
    'テンプレート
    str = "class %classname% < ActiveRecord::Migration" & vbCrLf
    str = str + "  def change" & vbCrLf
    str = str + "    create_table :%tablename% do |t|" & vbCrLf
    str = str + "%columns%" & vbCrLf
    str = str + "      t.timestamps" & vbCrLf
    str = str + "    end" & vbCrLf
    str = str + "  end" & vbCrLf
    str = str + "end" & vbCrLf
    
    'プレースホルダ置換
    str = Replace(str, "%classname%", GetClassName(table_name))
    str = Replace(str, "%tablename%", table_name)
    str = Replace(str, "%columns%", GetMigrationColumns(column_data))
    
    'Debug.Print (str)
    
    ' FreeFile値の取得(以降この値で入出力する)
    intFF = FreeFile
    ' 指定ファイルをOPEN(出力モード)
    Open str_dir & Format(Date, "yyyymmdd") & Format(idx, "000000") & "_create_" & table_name & ".rb" For Output As #intFF
    Print #intFF, str
    ' 指定ファイルをCLOSE
    Close #intFF

End Function
'マイグレーションファイルに出力するクラス名を取得する
Function GetClassName(table_name As String)
    GetClassName = "Create" & Application.Substitute(Application.Proper(table_name), "_", "")
End Function
'マイグレーションファイルに出力するカラム情報を取得する
Function GetMigrationColumns(column_datas() As ColumnData) As String
    Dim str_column As String
    Dim idx As Long

    'カラム数分マイグレーション情報を生成する
    idx = 0
    Do While idx <= UBound(column_datas)
        str_column = str_column & GenarateMigrationColumn(column_datas(idx)) & vbCrLf
        idx = idx + 1
    Loop
    
    GetMigrationColumns = str_column
    
End Function
'マイグレーションファイルに出力するカラム情報（1行）を生成する
Function GenarateMigrationColumn(column_data As ColumnData)
    Dim str_column As String
    
    '属性とカラム名
    str_column = "      t." & column_data.type
    str_column = str_column & String(COLUMN_TYPE_START - Len(str_column), " ") & ":" & column_data.name
    
    '桁数
    If column_data.digit > 0 Then
        str_column = str_column & "," & String(COLUMN_LIMIT_START - Len(str_column), " ") & ":limit => " & column_data.digit
    End If
    
    'null
    If column_data.null = False Then
        str_column = str_column & "," & String(COLUMN_NULL_START - Len(str_column), " ") & ":null => false"
    End If
    
    GenarateMigrationColumn = str_column
    
End Function
'カラム数カウント
Function ColumnCount() As Integer
    Dim r As Variant
    r = Range("J14").End(xlDown).Row
    ColumnCount = r - 14 - 3
End Function

Sub migrationファイル生成マクロ()
    Dim str_dir As String
    Dim str_migration As String
    Dim intFF As Integer            ' FreeFile値
    Dim sheet As Worksheet
    Dim idx As Integer
    
    '出力フォルダ指定
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = True Then
            str_dir = .SelectedItems(1)
            Debug.Print (str_dir)
        End If
    End With
    
    idx = 0
    While idx < Worksheets.Count - 4
        Set sheet = Worksheets(4 + idx)
        Call GenerateMigrationFile(sheet, idx)
        idx = idx + 1
    Wend
End Sub