PowershellでExcelの表をオブジェクト化して取得

目的

Excelで作られた表を使って、コンフィグファイルなどの任意の形式のテキストに加工がしたい。
オブジェクトとしてデータを取得できれば色々とできることの幅が広がるはず。

-最終的に出来上がったコードと実行例は以下のGist参照-

https://gist.github.com/sensq/2fe327b8b4cc3afec3c6cba0546634a8

背景

設計書などでこんな感じの表がよくあります。（形式似せただけで中身は適当に自作）
方眼紙なのはともかく、インプットとしては特に悪くなさそうな表です。

ただ、この表から実際のコンフィグファイルなどを作る方法が「一つ一つひたすらコピペしていく」という典型的なバッドノウハウになっていました。

テンプレートを用意してレコード数分ループさせて一括自動生成させる方法にした方が、明らかに工数や操作ミスなどの余計な心配が減って良さそうです。

方法

• ×Excel関数で頑張る

最初はExcelの関数組み合わせれば簡単に作れると思ってたけど、表は1レコード1行なのに対して、出力したいテキストは1レコード複数行になるため、結果的にゴリ押しな方法になってしまって汎用的に使える方法はできなかった。
できたとしても、毎回関数コピペするのが面倒なので却下

• ×VBAで頑張る

Excelと言えばVBA
だけど、VBAはコード管理とか人に使わせるための手順とかパラメータ入力の方法とかが色々と依存性高くて面倒なので却下

• ○Powershellで頑張る

PowershellはWindows使ってる人だったら特に実行環境を用意する必要が無く実行が簡単で、コードもオブジェクト指向で割と直感的に読み書きしやすいので個人的に最近好きな言語
表をオブジェクトとして取得できれば後は通常のオブジェクトと同様に好きなように使いまわすことができるので、インプット部分さえ作れば汎用的に使うことができそうということで採用

実装

以下のことができれば作れそうと判断

1. Excelブック読み込み
2. シート読み込み
3. データテーブル（表）読み込み
4. 読み込んだデータをオブジェクト化

上記を行うために最低限必要になる入力パラメータは以下と想定

• ブックの名前orパス
• シートの名前
• テーブルの範囲（左上と右下のセル番号がわかれば十分）

1. Excelブック読み込み

2通りの方法が考えられる

1. 指定されたパスからスクリプト実行毎にブックを起動する
2. 起動済みのExcelプロセスから指定された名前のブックを取得

1の場合、サイズにもよるが、毎回Excelの起動が行われるので動作が若干重くなる。
また、毎回終了処理も必要になるが、途中でエラー終了や強制終了が発生した等の理由で終了処理が呼ばれなかった場合、ゴミプロセスが残って気持ち悪いことになってしまう。

一方、2は事前にブックを開いておく手間が必要になるが、特に不自然でも面倒でも無い手間であり、一度開いておけばスムーズに動作し、面倒な事前事後処理も不要になることから、こちらの方法で実装する方が良いと判断

実装方法は以下のページを参考にした。

Excelを開いたままでPowerShell

2. シート読み込み

ブックが取得できれば簡単に読み込める。

後から知ったが、Powershell？のバージョンが古いとSheetsメソッドが存在しないようなので注意。

$sheet = $book.Sheets($input.sheetname)
# 上記でSheetsメソッドが無いとエラーが出る場合は以下
$sheet = $book.Worksheets.Item($input.sheetname)

3. データテーブル（表）読み込み

ここまでで目的のシートまで辿り着けているため、後は読み込みたい表の範囲を指定すれば表を読み込むことができる。

範囲の指定方法は色々ありそうだが、最低限左上と右下のセル番号がわかれば一意に特定できる。

ただし、ここでExcelの面倒な仕様として、一般的に列方向はA1形式（アルファベットで列番号指定する形式）で表記されていてプログラム的に扱いにくいという問題がある。
そのため、アルファベットを数字に変換する必要がある。
アルファベットだから26進法（実は微妙に違う）だと考えれば自力でアルゴリズム考えて変換も可能だが、シートオブジェクトに実装されているRangeメソッドを使うと簡単に数字での取得が行える。

$start = $sheet.Range("D6")
echo $start.Row     # ⇒ 6
echo $start.Column  # ⇒ 4

これで表の範囲の特定までできたことになるので、具体的な値の取得が可能になる。

4. 読み込んだデータをオブジェクト化

一番メインの部分

• オブジェクトは以前の記事でも書いたように配列とPSCustomObjectを使用する

• プロパティを参照するためのキー名には、一般的な表であれば普通一番上の行に書かれている項目名を利用することにして全自動化する

• Excel方眼紙対策として、項目名が空の列は無視するようにしておく

ここまで決まれば、後はループと上記の場合のif文を書けば完成

# Excelのテーブルをオブジェクトとして取得する関数
## $sheet: シートオブジェクト
## $start: 表の左上のセル番号（A1形式での文字列）
## $end:   表の右下のセル番号（A1形式での文字列）
Function Get-TableObject($sheet, $start, $end){
  # テーブルオブジェクト作成
  $table = [pscustomobject]@{
    start = $sheet.Range($start)
    end = $sheet.Range($end)
    key = @()
    data = @()
  }
  # 古いPowershellだと以下のような書き方をしないとエラーになる
  #$table = New-Object PSCustomObject
  #$table | Add-Member -MemberType NoteProperty -Name start -Value $sheet.Range($start)
  
  # テーブルをオブジェクト化
  for($row=$table.start.Row; $row -le $table.end.Row; $row++){
    # 最初にデータの無い行は無視
    if($sheet.cells.item($row, $table.start.Column).text -eq "" ){
      continue
    }
    # 1レコード用オブジェクトを準備
    $record = New-Object PSCustomObject
    $key_ref_number = 0
    for($col=$table.start.Column; $col -le $table.end.Column; $col++){
      # 最初にデータの無い列は無視
      if($sheet.cells.item($table.start.Row, $col).text -eq "" ){
        continue
      }
      # 1行目の値からキー名作成
      if($row -eq $table.start.Row){
        $table.key += $sheet.cells.item($row, $col).text
      }
      # 1レコード作成
      else{
        $key_name = ($table.key[$key_ref_number])
        $val = ($sheet.cells.item($row, $col).text)
        $record | Add-Member -MemberType NoteProperty -Name $key_name -Value $val
        $key_ref_number += 1
      }
    }
    # 1レコード追加
    if($row -gt $table.start.Row){
      $table.data += $record
    }
  }
  return $table
}

その他（パラメータの入力方法）

Paramを使って引数で指定させるようにしている。
[Parameter(Mandatory)]と書くと指定必須の引数になる

Param(
  [Parameter(Mandatory)]  # 対象ブック名
  [string]$BookName,
  [Parameter(Mandatory)]  # 対象シート名
  [string]$SheetName,
  [Parameter(Mandatory)]  # 対象テーブルの開始セル（テーブルの一番左上のセル）のアドレス
  [string]$StartCell,
  [Parameter(Mandatory)]  # 対象テーブルの終了セル（テーブルの一番右下のセル）のアドレス
  [string]$EndCell
)

ただし、Powershell2.0以前だと[Parameter(Mandatory)]が使えないようなので、エラーが出る場合は消すこと。

実行例

背景で挙げたExcelの表からシンプルにBindのコンフィグファイル（named.conf）を生成してみる。

※気持ち悪いが、Powershellはプロパティ名などに2バイト文字が使えるので項目名が日本語のままでも問題無く実行できる。

実行コード:

$table = .\Get-ExcelTable.ps1 -BookName "testdata.xlsx" -SheetName "Sheet1" -StartCell "D6" -EndCell "W12"

echo ('#named.conf')
$table.data | %{
  echo ("// $($_.備考)")
  echo ("zone $($_.ドメイン名) in {")
  echo ("    type ""$($_.type)"";")
  echo ("    file ""$($_.ファイル)"";")
  echo ("};")
  echo ("")
}

実行結果:

#named.conf
// テスト1サーバ
zone test1.com in {
    type "master";
    file "test1.zone";
};

// テスト2サーバ
zone test2.com in {
    type "master";
    file "test2.zone";
};

// テスト3サーバ
zone test3.com in {
    type "master";
    file "test3.zone";
};

// テスト4サーバ
zone test4.com in {
    type "master";
    file "test4.zone";
};

// テスト5サーバ
zone test5.com in {
    type "master";
    file "test5.zone";
};

// テスト6サーバ
zone test6.com in {
    type "master";
    file "test6.zone";
};

---

最近だとyamlで出力するのも便利そうですね。

$table = .\Get-ExcelTable.ps1 -BookName "testdata.xlsx" -SheetName "Sheet1" -StartCell "D6" -EndCell "W12"

echo ('#named.conf.yml')
echo ('- named_conf:')
$table.data | %{
  echo ("  - domain_name: '$($_.ドメイン名)'  # $($_.備考)")
  echo ("    type: '$($_.type)'")
  echo ("    file: '$($_.ファイル)'")
}

#named.conf.yml
- named_conf:
  - domain_name: 'test1.com'  # テスト1サーバ
    type: 'master'
    file: 'test1.zone'
  - domain_name: 'test2.com'  # テスト2サーバ
    type: 'master'
    file: 'test2.zone'
  - domain_name: 'test3.com'  # テスト3サーバ
    type: 'master'
    file: 'test3.zone'
  - domain_name: 'test4.com'  # テスト4サーバ
    type: 'master'
    file: 'test4.zone'
  - domain_name: 'test5.com'  # テスト5サーバ
    type: 'master'
    file: 'test5.zone'
  - domain_name: 'test6.com'  # テスト6サーバ
    type: 'master'
    file: 'test6.zone'