VBA Excel表をHTMLのtableタグに変換するマクロ

先日メインサイトにXlRgbColor定数の一覧を掲載した。

今回はこれを作る時に使用したExcelマクロを題材に、VBAでExcel表からtableタグを出力する方法について記す。

まずHTML化する前のExcel表を用意しておく。

今回使用したのはこちら。
f:id:t-hom:20170306221642p:plain
※7色で画面から切れてるが、実際は137色ある。

これを、冒頭で紹介したようにWebページで表示させるには、HTML形式に変化しなければならない。
具体的には、Excel表を以下のようなHTMLに変換する必要がある。

<table>
<tr>
<th>No</th>
<th>色</th>
<th>名前</th>
<th>RGB値</th>
<th>説明</th>
</tr>

<tr>
<td class="col0">1</td>
<td class="col1" style="background-color:#800000; width:30px;">　</td>
<td class="col2">rgbMaroon</td>
<td class="col3">128, 0, 0</td>
<td class="col4">栗色</td>
</tr>
<tr>
<td class="col0">2</td>
<td class="col1" style="background-color:#8b0000; width:30px;">　</td>
<td class="col2">rgbDarkRed</td>
<td class="col3">139, 0, 0</td>
<td class="col4">濃い赤</td>
</tr>

<!--以下略 -->
</table>

Excel表をhtml化するWebツールもあったが、セルの色までは再現してくれなかったので自作方法を覚えておくと役に立つかもしれない。

さて、タイトルでtableタグに変換するマクロと書いたが、実際にマクロで出力するのはtableの中身である、tdタグ
tableタグとthタグ(テーブルのヘッダー)もマクロで作ることはできるが、それほど手間でもないので今回は手入力とした。

マクロと手入力の分担は以下の通り。

<!-- ここから手入力 -->
<table>
<tr>
<th>No</th>
<th>色</th>
<th>名前</th>
<th>RGB値</th>
<th>説明</th>
</tr>
<!-- ここまで手入力 -->

<!-- ここからマクロ -->
<tr>
<td class="col0">1</td>
<td class="col1" style="background-color:#800000; width:30px;">　</td>
<td class="col2">rgbMaroon</td>
<td class="col3">128, 0, 0</td>
<td class="col4">栗色</td>
</tr>
<tr>
<td class="col0">2</td>
<td class="col1" style="background-color:#8b0000; width:30px;">　</td>
<td class="col2">rgbDarkRed</td>
<td class="col3">139, 0, 0</td>
<td class="col4">濃い赤</td>
</tr>
<!-- ここまでマクロ -->

<!-- 以下略 -->
<!-- tableの閉じタグも手入力 -->
</table>

つまり実際にマクロでは、以下テンプレートの[No]、[色値]、[色名]、[RGB値]、[説明]をループで次々と変更しながら出力すれば良いということ。

<tr>
<td class="col0">[No]</td>
<td class="col1" style="background-color:#[色値]; width:30px;">　</td>
<td class="col2">[色名]</td>
<td class="col3">[RGB値]</td>
<td class="col4">[説明]</td>
</tr>

※tdタグにそれぞれclassを付けたのは、列ごとに後からフォントサイズを調整しやすくするため。

さて、ここから実際にマクロを作成していく。

検証段階ではとりあえずDebug.PrintでHTMLを出力させるが、イミディエイトウインドウへは200行までしか出力できず、切れてしまう。
後で簡単にテキストファイルへの書き出しに切り替えられるよう一旦WriteLineというプロシージャを作っておこう。

Sub WriteLine(message)
    Debug.Print message
End Sub

こうすればマクロが完成した後にWriteLineの中身を書き変えることで出力先をテキストファイルに切り替えられる。
※こうした保守性の観点からのプロシージャ分割も重要。

次に今回はXlRgbColorの色見本を扱うので、テーブルのセルの背景色をそれぞれ個別に指定する必要がある。
出力するのはHTMLなので16進数の色表記を用いる必要があるが、Excel表では単なるRGBのLong値なので、これを16進数の文字列に変換するための関数もあらかじめ作っておく。

以下がその関数。RGB値を渡すと、HTMLで使用する16進数に変換してくれる。

Function RGBToHTMLColor(color_rgb)
    Dim r: r = color_rgb \ 256 ^ 0 Mod 256
    Dim g: g = color_rgb \ 256 ^ 1 Mod 256
    Dim b: b = color_rgb \ 256 ^ 2 Mod 256
    Dim hexR: hexR = Right("0" & Hex(r), 2)
    Dim hexG: hexG = Right("0" & Hex(g), 2)
    Dim hexB: hexB = Right("0" & Hex(b), 2)
    RGBToHTMLColor = "#" & hexR & hexG & hexB
End Function

そして、出力したい表のあるエクセルシートのシートモジュールに以下を張り付けて実行すると、イミディエイトウインドウにHTMLが出力される。

Sub 表をHTML化()
    For 行 = 2 To 138
        WriteLine "<tr>"
        For 列 = 1 To 5
            If 列 = 2 Then
                Dim backGroundColor As String
                backGroundColor = RGBToHTMLColor(Cells(行, 4).Value)
                WriteLine "<td class=""col1"" style=""background-color:" _
                    & backGroundColor & "; width:30px;"">　</td>"
            Else
                WriteLine "<td class=""col" & 列 - 1 & """>" _
                    & Cells(行, 列).Value & "</td>"
            End If
        Next
        WriteLine "</tr>"
    Next
End Sub

注意：(私にとっては)単発ものなので、変数宣言もしてないしループ回数もリテラルべた打ち。良いコードとは言えないので、こちらを参考に汎用的なマクロを作る場合は適宜きちんと書いて。

さて、イミディエイトウインドウでは200行以上出力すると古いものから順に消えてしまうのでテキストファイルへの出力に変更したい。
ここでFileSystemObjectを使用するので、Microsoft Scripting Runtimeを参照設定しておく。

そしてWriteLineを以下のように書き換える。

Sub WriteLine(message)
    With New FileSystemObject
        Dim ts As TextStream
        Set ts = .OpenTextFile("c:\work\writeline.txt", ForAppending, True)
        ts.WriteLine message
        ts.Close
    End With
End Sub