はじめに

こんにちは、SHIFT ITソリューション部の水谷です。

前回はExcelファイルを読み込むAnsibleフィルタープラグインを作ってみましたが、期待する動作を実現するものができたものの、以下のような課題が見つかりました。

• シート全体を配列に取り込む場合は問題ないが、範囲を指定できるようにするとパラメーターの数が多くなり、やや使いにくい（パラメーターの順を間違えやすいし、読みにくい）

• ヘッダー行（項目名が並んでいる先頭行）があるかないかを指定できるようにすると、さらにパラメーターが増えてしまう

• ヘッダーがある場合用に別名のフィルターを作ったが、今度はフィルター数が多くなり、フィルター名も長くなってしまい、Playbook上で使いにくい

フィルターはそもそも大がかりな作業を行うものではなく、シンプルな変数の操作を行うことが本来の目的なのかな、とも思いますので、Excelシートをいろいろな条件で読み込むような作業は、フィルターで実装するのではなく、モジュールで実装する方が良いのかな、と感じました。

ということで、今回はExcelシートを読み込む機能をモジュールとして実装してみたいと思います。また、せっかくなので、読み込みだけでなく、書き込み用のモジュールも合わせて作ってみたいと思います。

モジュール開発の準備

モジュールは前回作成したコレクションに追加する形で実装することにします。

振り返りになりますが、空のコレクションを作成する方法を書いておきますと、ansible-galaxy コマンドを以下のように実行します。

$ ansible-galaxy collection init {コレクション名（名前空間付き）} --init-path {コレクションを作成するディレクトリ}

今回は、コレクション名を test.excelとしています。

モジュールは、コレクションの"plugins"ディレクトリの下に"modules"ディレクトリを作成して、そこに任意のPythonファイル（.py）を作成して実装します。

あと、Excelファイルの読み書きのために"OpenPyXL"というPython用ライブラリを使用しますので、事前にpipコマンドでインストールしています。

$ pip install openpyxl

Excelシートを読み込むモジュールの開発

さっそく読み込みのモジュールから作っていきますが、モジュール名は"read_sheet"にします。モジュール名とPythonファイルのファイル名は同じにする必要がありますので、read_sheet.pyというファイルを作成して作業を行います。

このファイル内では、まず ansible.module_utils.basicをインポートします。それから、openpyxlも使用しますので、以下のようにインポートします。

from ansible.module_utils.basic import *
from openpyxl import *

続いて、main()関数を書いていくのですが、その先頭でPlaybookからモジュール変数を受け取ったり、あるいは結果を返すために、AnsibleModuleというクラスのインスタンスを作成します。

    module = AnsibleModule(argument_spec = dict(
             path = dict(required=True, type='str'),
             sheet = dict(required=False, type='str', default=''),
             startrow = dict(required=False, type='int', default=1),
             startcolumn = dict(required=False, type='int', default=1),
             endrow = dict(required=False, type='int', default=0),
             endcolumn = dict(required=False, type='int', default=0),
             header = dict(required=False, type='bool', default=False),
             ),
             add_file_common_args=True)

ここでは、argument_specとして、受け取るモジュール変数を定義しているのですが、今回は以下のような変数を受け取ることにします。

さて、ここからモジュールの本体です。まずは、Excelファイルを開いてシートを読み込みます。

        workbook = load_workbook(filename=module.params["path"], data_only=True)
        sheetname = module.params["sheet"]
        sheet = workbook[sheetname] if sheetname != '' else workbook[workbook.sheetnames[0]]

モジュール変数は module.params["変数名"]で参照できますので、Excelファイルのパスやシート名をここで参照しています。

シート内の読み込む範囲は、それぞれstartrow、startcolumn、endrow、endcolumnという変数で管理します。

ここで、開始行より最終行が小さかったり、開始列より終了列が小さい場合は、この時点で終了させるようにしました。

        startrow = module.params["startrow"]
        startcolumn = module.params["startcolumn"]
        endrow = module.params["endrow"] if module.params["endrow"] != 0 else sheet.max_row
        endcolumn = module.params["endcolumn"] if module.params["endcolumn"] != 0 else sheet.max_column
        if endrow <= startrow:
            module.fail_json(msg="endrow is smaller than startrow")
            return 1
        if endcolumn <= startcolumn:
            module.fail_json(msg="endcolumn is smaller than startcolumn")
            return 1

さて、肝心の読み込み部分ですが、まずはヘッダーがある場合のコードです。

        values = []
        if module.params["header"]:
            header = []
            for c in range(startcolumn, endcolumn + 1):
                header.append(sheet.cell(row=startrow,column=c).value)
            for r in range (startrow + 1, endrow + 1):
                temp = {}
                for c in range(startcolumn, endcolumn + 1):
                    temp[header[c - 1]] = sheet.cell(row=r,column=c).value
                values.append(temp)

基本的に前回のコードと同じです。

最初のfor文で、先頭行のセルをheaderという名前で定義したリスト変数に取り込んでいます。続いて2つ目のfor文で、2行目から最終行までのセルの値を、dict形式で取り込んでいきます。

さて、次にヘッダーがない場合ですが、こちらは単純に2重ループで2次元配列に取り込んでいます。

OpenPyXLには、範囲を指定して一気に読み込む機能もありますが、その際には"A1"形式でセルを指定しないといけないので、今回は簡単な実装にしました（パフォーマンスはちょっと落ちるかもしれないですね）。

        else:
            for r in range (startrow, endrow + 1):
                temp = []
                for c in range(startcolumn, endcolumn + 1):
                    temp.append(sheet.cell(row=r,column=c).value)
                values.append(temp)

あとは、これをあらかじめ用意しておいたdict変数resultのrowsに対する値として、取り込んだリストを入れ、module.exit_jsonをコールすればOKです。

        result["rows"] = values
        module.exit_json(**result)

全体のコードとしては以下のようになりました。もう少しエラーチェックを入れた方が良いのでしょうが、まずは自分で使うモジュールとしてはこれで十分でしょう。

#!/usr/bin/python

from ansible.module_utils.basic import *
from openpyxl import *

def main():
    module = AnsibleModule(argument_spec = dict(
             path = dict(required=True),
             sheet = dict(required=False, default=''),
             startrow = dict(required=False, type='int', default=1),
             startcolumn = dict(required=False, type='int', default=1),
             endrow = dict(required=False, type='int', default=0),
             endcolumn = dict(required=False, type='int', default=0),
             header = dict(required=False, type='bool', default=False),
             ),
             add_file_common_args=True)

    result = {}
    try:
        workbook = load_workbook(filename=module.params["path"], data_only=True)
        sheetname = module.params["sheet"]
        sheet = workbook[sheetname] if sheetname != '' else workbook[workbook.sheetnames[0]]
        
        startrow = module.params["startrow"]
        startcolumn = module.params["startcolumn"]
        endrow = module.params["endrow"] if module.params["endrow"] != 0 else sheet.max_row
        endcolumn = module.params["endcolumn"] if module.params["endcolumn"] != 0 else sheet.max_column
        if endrow < startrow:
            module.fail_json(msg="endrow is smaller than startrow")
            return 1
        if endcolumn < startcolumn:
            module.fail_json(msg="endcolumn is smaller than startcolumn")
            return 1

        values = []
        if module.params["header"]:
            header = []
            for c in range(startcolumn, endcolumn + 1):
                header.append(sheet.cell(row=startrow,column=c).value)
            for r in range (startrow + 1, endrow + 1):
                temp = {}
                for c in range(startcolumn, endcolumn + 1):
                    temp[header[c - 1]] = sheet.cell(row=r,column=c).value
                values.append(temp)
        else:
            for r in range (startrow, endrow + 1):
                temp = []
                for c in range(startcolumn, endcolumn + 1):
                    temp.append(sheet.cell(row=r,column=c).value)
                values.append(temp)
        result["rows"] = values
        module.exit_json(**result)
    except IOError:
        module.fail_json(msg="Error on reading excel file")
        return 1

    return 0

main()

では、テストしてみます。

まずはヘッダー無しで、シート全体を読み込むパターンです。

テスト用のExcelシートは前回と同じで、下のような「テストシート」で試してみます。

Playbookは、シート全体を読み込んでexcel_dataという変数に格納し、次のdebugタスクで表示させるだけのものです。

---
- hosts: localhost
  gather_facts: false
  tasks:
    - name: read excel sheet
      test.excel.read_sheet:
        path: test.xlsx
        sheet: テストシート
      register: excel_data

    - name: show data
      ansible.builtin.debug:
        var: excel_data

実行結果は、以下のようになりました。

$ ansible-playbook site.yml -v
Using /etc/ansible/ansible.cfg as config file
[WARNING]: provided hosts list is empty, only localhost is available. Note that the implicit localhost does not match
'all'

PLAY [localhost] *******************************************************************************************************

TASK [read excel sheet] ************************************************************************************************
ok: [localhost] => {"changed": false, "rows": [[1, 2, 3], ["A", "B", "C"], ["dog", "cat", "monkey"], ["日本語", "読める か", "な？"]]}

TASK [show data] *******************************************************************************************************
ok: [localhost] => {
    "excel_data": {
        "changed": false,
        "failed": false,
        "rows": [
            [
                1,
                2,
                3
            ],
            [
                "A",
                "B",
                "C"
            ],
            [
                "dog",
                "cat",
                "monkey"
            ],
            [
                "日本語",
                "読めるか",
                "な？"
            ]
        ]
    }
}

PLAY RECAP *************************************************************************************************************
localhost                  : ok=2    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

問題なさそうですね。

続いてヘッダーありのシートを読んでみたいと思います。

テスト用のシートはこちらです。

Playbookは先ほどとほとんど同じですが、"header: true" の行が追加されています。

---
- hosts: localhost
  gather_facts: false
  tasks:
    - name: read excel sheet
      test.excel.read_sheet:
        path: test.xlsx
        sheet: ヘッダー付きシート
        header: true
      register: excel_data

    - name: show data
      ansible.builtin.debug:
        var: excel_data

以下が実行結果です。

$ ansible-playbook site.yml -v
Using /etc/ansible/ansible.cfg as config file
[WARNING]: provided hosts list is empty, only localhost is available. Note that the implicit localhost does not match
'all'

PLAY [localhost] *******************************************************************************************************

TASK [read excel sheet] ************************************************************************************************
ok: [localhost] => {"changed": false, "rows": [{"価格": 750, "商品名": "小ラーメン"}, {"価格": 850, "商品名": "大ラーメ ン"}, {"価格": 900, "商品名": "小豚"}, {"価格": 1100, "商品名": "大豚"}, {"価格": 50, "商品名": "生たまご"}]}

TASK [show data] *******************************************************************************************************
ok: [localhost] => {
    "excel_data": {
        "changed": false,
        "failed": false,
        "rows": [
            {
                "価格": 750,
                "商品名": "小ラーメン"
            },
            {
                "価格": 850,
                "商品名": "大ラーメン"
            },
            {
                "価格": 900,
                "商品名": "小豚"
            },
            {
                "価格": 1100,
                "商品名": "大豚"
            },
            {
                "価格": 50,
                "商品名": "生たまご"
            }
        ]
    }
}

PLAY RECAP *************************************************************************************************************
localhost                  : ok=2    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

期待通りにシートの内容がmap形式で返ってきました。

Excelファイルの書き込みモジュールも作る

「Excelファイルを読み込むモジュールを作る」という目的は達成できましたが、せっかくなのでExcelファイルに書き込むモジュールも作ってみます。

こちらについては詳しくは説明しませんが、思ったよりもやることが多かったです。

考慮すべきポイントは以下のようなものです。

• パラメーターで指定されたExcelファイルがすでに存在していれば、それを開いてそこに書き込み、存在していなければ新規に作成するロジックが必要

• できるだけ冪等性を保つために、既存のシートから変更があるかどうかを確認し、ファイルを上書きするかどうかを判断する必要がある

• 与えられたデータが2次元配列なのか、mapの配列なのかで処理を変える必要がある

• mapの場合、すべての列が同じkeyを持つという保証がないので、keyがバラバラだとしても動作するような対策が必要

• 既存のシート内のセルを上書き更新したい場合と、新たな行を挿入してそこに値を書き込みたい場合があるので、どちらもできるようにする必要がある

これらに対して行ったことは以下になります。

• 変更の有無を管理するためにchangedという名前のbool変数を追加して管理した

• 行挿入を行うかどうかを指定できるようinsertというbool変数を追加して管理した

• 既存のファイルを上書きする場合は、ファイルが存在するかを確認した上で、存在すればそれをロードする。そうでない場合はメモリ上に空のワークブックを作成して作業を進める

• mapの場合、列によってはkeyが多かったり少なかったりする場合に対応するため、最初にすべての行のkeyを列挙する

  • その上で"項目名:それを書き込む列の番号"という形でdictを作っておき、各行の各要素を書き込む位置が分かるようにする

• 行挿入がある場合、冪等性を保つのが難しいのでこの場合は冪等性を諦めるが、単純な2次元配列の書き込みや行挿入を行わないmapの書き込みは冪等性を持たせるようにする

また、セルに値を入れるためには、"A1"形式でセルの位置を指定しなければいけないので、行と列の値から"A1"形式の文字列を作る関数を作りました。

ということで、下のようなコードになりました（ちょっと長いですね……）。

#!/usr/bin/python

from ansible.module_utils.basic import *
from openpyxl import *

def row_column_to_cell_name(row, column):
    cell = ""
    if column > 26:
        cell = chr(0x40 + ((column - 1) // 25) + 1)
    cell += chr(0x40 + ((column - 1) % 26) + 1) + str(row)
    return cell

def main():
    module = AnsibleModule(argument_spec = dict(
             path = dict(required=True, type='str'),
             sheet = dict(required=False, type='str', default=''),
             startrow = dict(required=False, type='int', default=1),
             startcolumn = dict(required=False, type='int', default=1),
             data = dict(required=True, type='list'),
             create = dict(required=False, type='bool', default=False),
             insert = dict(required=False, type='bool', default=False),
             ),
             add_file_common_args=True)

    result = {}
    changed = False
    try:
        create = False
        if os.path.isfile(module.params["path"]) == False:
            create = True
            changed = True
            workbook = Workbook()
        else:
            workbook = load_workbook(filename=module.params["path"])
        sheet = workbook.active
        sheetname = module.params["sheet"]
        if sheetname != '':
            if sheetname not in workbook.sheetnames:
                workbook.create_sheet(sheetname)
                if create:
                    workbook.remove(workbook.active)
            sheet = workbook[sheetname]

        row = module.params["startrow"]
        data = module.params["data"]

        if type(data[0]) == list:
            if module.params["insert"]:
                sheet.insert_rows(row, len(data))
            for rowdata in data:
                column = module.params["startcolumn"]
                for value in rowdata:
                    if sheet[row_column_to_cell_name(row, column)] != value:
                        sheet[row_column_to_cell_name(row, column)] = value
                        changed = True
                    column = column + 1
                row = row + 1
        else:
            if module.params["insert"]:
                sheet.insert_rows(row, len(data) + 1)
            header = {}
            column = module.params["startcolumn"]
            for rowdata in data:
                for k in rowdata.keys():
                    if k not in header.keys():
                        header[k] = column
                        if create == False and sheet[row_column_to_cell_name(row, column)].value != k:
                            sheet[row_column_to_cell_name(row, column)] = k
                            changed = True
                        column = column + 1
            row = row + 1
            for rowdata in data:
                for k in rowdata.keys():
                    if create == False and sheet[row_column_to_cell_name(row, header[k])].value != rowdata[k]:
                        sheet[row_column_to_cell_name(row, header[k])] = rowdata[k]
                        changed = True
                row = row + 1

        if changed:
            workbook.save(module.params["path"])
        result["changed"] = changed
        module.exit_json(**result)
    except IOError:
        module.fail_json(msg="Error on writing excel file")
        return 1

    return 0

main()

簡単にテストしてみます。

テストデータとしては、単純な2次元配列とmapの配列の２つ用意し、"テストシート１"に前者を、"テストシート２"に後者を書き込みます。なお、Excelファイルのファイル名は"test2.xlsx"ですが、このファイルが存在しない状態からPlaybookを実行してみます。

Playbookはこちらです。

---
- hosts: localhost
  gather_facts: false
  vars:
    testdata:
      - [1,2,3]
      - [4,5,6]
      - [7,8,9]
    testdata2:
      - 商品名: 小ラーメン
        価格: 750
      - 商品名: 大ラーメン
        価格: 850
      - 商品名: 小豚
        価格: 900
      - 商品名: 大豚
        価格: 1100
        おすすめ: 〇
      - 商品名: 生たまご
        価格: 50
  tasks:
    - name: Write 2D array to excel file
      test.excel.write_sheet:
        path: test2.xlsx
        sheet: テストシート１
        data: "{{ testdata }}"
        startrow: 2
        startcolumn: 2

    - name: Write map to the excel file in diffrent sheet
      test.excel.write_sheet:
        path: test2.xlsx
        sheet: テストシート２
        data: "{{ testdata2 }}"
        startrow: 2
        startcolumn: 2
        insert: true

では、実行してみます。

$ ansible-playbook site.yml -v
Using /etc/ansible/ansible.cfg as config file
[WARNING]: provided hosts list is empty, only localhost is available. Note that the implicit localhost does not match
'all'

PLAY [localhost] *******************************************************************************************************

TASK [Write 2D array to excel file] ************************************************************************************
changed: [localhost] => {"changed": true}

TASK [Write map to the excel file in diffrent sheet] *******************************************************************
changed: [localhost] => {"changed": true}

PLAY RECAP *************************************************************************************************************
localhost                  : ok=2    changed=2    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

無事実行は完了しましたので、作成されたシートを開いてみます。

どちらも大丈夫そうですね。

冪等性のチェックのため、もう一度実行してみます。

$ ansible-playbook site.yml -v
Using /etc/ansible/ansible.cfg as config file
[WARNING]: provided hosts list is empty, only localhost is available. Note that the implicit localhost does not match
'all'

PLAY [localhost] *******************************************************************************************************

TASK [Write 2D array to excel file] ************************************************************************************
ok: [localhost] => {"changed": false}

TASK [Write map to the excel file in diffrent sheet] *******************************************************************
ok: [localhost] => {"changed": false}

PLAY RECAP *************************************************************************************************************
localhost                  : ok=2    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

どちらのタスクもchangedがfalseになり、ファイルは更新されませんでした。こちらも期待通りの結果です。

既存のファイルへの書き込みと、合わせて行挿入も試してみます。

該当タスクだけ書いておきます。

    - name: Insert the same map to the excel file in different position
      test.excel.write_sheet:
        path: test2.xlsx
        sheet: テストシート２
        data: "{{ testdata2 }}"
        startrow: 6
        startcolumn: 5
        insert: true

アップデートされたExcelファイルを見てみると、指定した位置に挿入されてますね。

１つ問題なのは、（上でも書いたように）この行挿入のアクションには冪等性がないことです。2回目のPlaybook実行を行うと、さらに行が挿入されて、指定したデータが書き込まれてしまいます。

解決方法は考えられますがちょっと長くなりそうなで今回はここまでにしたいと思います。

（注意）上記コードはあくまで自分で使用するために作成したもので、動作を保証するものではありません。また、エラーチェックはあまり入れておりませんので、もし使用されるのであればその点にもご注意ください。

IaC支援サービスのご紹介

SHIFTではTerraformやCDKを使ったクラウドインフラ構築の自動化や、Ansibleを使ったサーバOSの設定自動化や構成管理のご支援も行っております。ご依頼・ご相談は下記リンクからお願いします。

お問合せはお気軽に

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら

PHOTO：UnsplashのBram Van Oost