会社でRuby使用禁止になったのなら、VBAでテンプレートエンジンを使えばいいじゃない
顛末
Rubyで有名なテンプレートエンジンにERBがあります。これとExcelいじるコードを組み合わせていろいろハッピーなスクリプトを書いていたのですが、保守環境では（オトナの事情で）Rubyを使えないので困る、という話もあり、Excelの話はVBAで片付けようという気持ちにもなってきました。
VBAで使えるテンプレートエンジンはあるのかな？
そういう訳でVBAでも使えるテンプレートエンジンはないかな？とググってみたところ、どうやらMini Templetorというエンジンがあることが分かりました。HTML用とか書いてありますが、別にそんなの関係ねえはずです。
GIGAZINEでは世界のPHPテンプレートエンジンとか言って紹介されていますが、JavaとPHPとVBAで使えます。超優秀です。
そんな訳でMini Templatorを使って簡単なサンプルを作ってみた
上図のようなレイアウトで、SQL作成ボタンを押すとCREATE TABLE文を生成してくれるようなVBAマクロを作成してみます。
SQL文のテンプレート
以下のようなものを用意しました。
CREATE TABLE ${schema}${tableName}
(<!-- $BeginBlock columns -->
${columnName} ${columnType} ${constraint}<!-- $EndBlock columns -->
)
/
comment on table ${schema}${tableName} is '${tableComment}'
/
<!-- $BeginBlock comments -->comment on column ${schema}${tableName}.${columnName} is '${columnComment}'
/
<!-- $EndBlock comments -->ちょっと改行がおかしいのはオトナの事情です。これにcreateTemplate.tblという適当な名前をつけて保存しました。
VBAマクロのコード
まずはMiniTemplator（MiniTemplator.cls）をクラスモジュールにインポートします。すると下図のようにクラスモジュールにMiniTemplatorが表示されます。
今回はデータ構造を保存するためにcolumnInfo*1とTableInfoというクラスを作成しています。
' columnInfo Public columnName As String Public columnType As String Public constraint As String Public comment As String
' TableInfo Public schema As String Public tableName As String Public comment As String
そして以下のようなコードでSQLを生成します。
' テンプレートファイル名 Private Const templateFileName = "createTemplate.tbl" Private Const startRow = 5 Private Const startCol = 2 Private Sub SQL生成_Click() ' 必要な情報があるかをチェック ' テーブルの名前は必須 If Range("tableName") = "" Then MsgBox "テーブル名は必須です。テーブル名を入力して下さい。" Exit Sub End If ' カラム情報は一つ以上必要 For Each v In Range("B5:C5") If v = "" Then MsgBox "カラムは一つ以上必須です。カラム情報を入力してください。" Exit Sub End If Next v ' テーブル情報を格納する変数 Dim table As New TableInfo ' テーブル情報を格納する変数 Dim column As columnInfo ' カラム情報のコレクション Dim columnArray As New Collection ' テーブル情報を格納 If Range("schema").Value <> "" Then table.schema = Range("schema").Value & "." Else table.schema = "" End If table.tableName = Range("tableName").Value table.comment = Range("tableComment").Value 'カラム情報を格納 Dim currentRow As Integer currentRow = startRow Do While Cells(currentRow, 2) <> "" Set column = New columnInfo column.columnName = Cells(currentRow, 2) column.columnType = Cells(currentRow, 3) column.constraint = Cells(currentRow, 4) column.comment = Cells(currentRow, 5) columnArray.Add column currentRow = currentRow + 1 Loop ' テンプレートエンジンに値を渡しはじめる Dim t As New MiniTemplator ' テンプレートファイル t.ReadTemplateFromFile ThisWorkbook.Path & "\" & templateFileName ' テーブル情報 t.SetVariable "schema", table.schema t.SetVariable "tableName", table.tableName t.SetVariable "tableComment", table.comment ' カラム情報 Dim columns As Integer For columns = 1 To columnArray.Count t.SetVariable "columnName", columnArray.Item(columns).columnName t.SetVariable "columnType", columnArray.Item(columns).columnType ' 最後のカラムにはカンマをつけない If columns = columnArray.Count Then t.SetVariable "constraint", columnArray.Item(columns).constraint Else t.SetVariable "constraint", columnArray.Item(columns).constraint & "," End If t.AddBlock "columns" Next 'カラムのコメント情報 For columns = 1 To columnArray.Count t.SetVariable "tableName", table.tableName t.SetVariable "columnName", columnArray.Item(columns).columnName t.SetVariable "columnComment", columnArray.Item(columns).comment t.AddBlock "comments" Next t.GenerateOutputToFile ThisWorkbook.Path & "\" & table.tableName & ".tbl" MsgBox "テーブル情報が次のファイルに書き込まれました : " & table.tableName & ".tbl" End Sub
解説と言い訳
Private Const startRow = 5 Private Const startCol = 2
上記の二行はカラムの定義がはじまる場所を示しているのですが、どう考えても美しくないです。
' テーブル情報を格納 If Range("schema").Value <> "" Then table.schema = Range("schema").Value & "." Else table.schema = "" End If
Rangeの中に名前らしきものが入ってて「？」と思われた方もいるかも知れませんが、セルには名前をつけることができます。Range("C1")みたいな指定も良いのですが、後から見たとき何だかなぁという気持ちになるので、できるだけ名前をつけたいところです。
'カラム情報を格納 Dim currentRow As Integer currentRow = startRow Do While Cells(currentRow, 2) <> "" Set column = New columnInfo column.columnName = Cells(currentRow, 2) column.columnType = Cells(currentRow, 3) column.constraint = Cells(currentRow, 4) column.comment = Cells(currentRow, 5) columnArray.Add column currentRow = currentRow + 1 Loop
インスタンスを作って必要な値を取得＆格納し、コレクションクラスにぶち込むというループです。カラム名がないカラム定義はないと思うので、カラム名の記入がないと判断したところでループをストップさせます。
' テンプレートエンジンに値を渡しはじめる Dim t As New MiniTemplator
テンプレートエンジンでファイル生成するためには、まずエンジンのインスタンスを生成します。
' テンプレートファイル t.ReadTemplateFromFile ThisWorkbook.Path & "\" & templateFileName
テンプレートファイルを読み込みます。もちろんフルパスで指定。今回はExcelのカレントフォルダにファイルを配置しているので、ThisWorkbook.Pathでカレントフォルダのパスを取得しています。
この際、テンプレート定義ファイルの構文がおかしかったりする場合はこの場でエラーが出ます。
' テーブル情報 t.SetVariable "schema", table.schema t.SetVariable "tableName", table.tableName t.SetVariable "tableComment", table.comment
SetVariableメソッドでどのタグにどんな値を割り当てるかを設定します。上記の例では${schema}にtable.schemaの内容を挿入したりしています。
' カラム情報 Dim columns As Integer For columns = 1 To columnArray.Count t.SetVariable "columnName", columnArray.Item(columns).columnName t.SetVariable "columnType", columnArray.Item(columns).columnType ' 最後のカラムにはカンマをつけない If columns = columnArray.Count Then t.SetVariable "constraint", columnArray.Item(columns).constraint Else t.SetVariable "constraint", columnArray.Item(columns).constraint & "," End If t.AddBlock "columns" Next
ブロックに対して値を割り当てています。一通り値を突っ込んだあと、AddBlockメソッドでどこのブロックに値を突っ込んでいるのかをエンジンに教えてあげます。
t.GenerateOutputToFile ThisWorkbook.Path & "\" & table.tableName & ".tbl" MsgBox "テーブル情報が次のファイルに書き込まれました : " & table.tableName & ".tbl"
そんな感じで値の挿入が終わったあとは、GenerateOutputToFileメソッドでファイルを出力してあげます。今回はテーブル名.tblというファイルを出力することにしました。
出力結果
以下のような形で出力されます。
CREATE TABLE mah.cat_management ( cat_id NUMBER PRIMARY KEY, cat_name VARCHAR2(50) , cat_type VARCHAR2(50) , cat_condition NUMBER(2) ) / comment on table mah.cat_management is '猫の管理表' / comment on column mah.cat_management.cat_id is '猫のID' / comment on column mah.cat_management.cat_name is '猫の名前' / comment on column mah.cat_management.cat_type is '猫の種類' / comment on column mah.cat_management.cat_condition is '猫の状態' /
実行エビデンス
