Help us understand the problem. What is going on with this article?

ExcelからYAMLの内容を取り出し、Ansibleでvarsに取り込む例

目的

以下の様なExcelからYAMLテキストを取り出しAnsibleで利用する

Book.xlsx
Sheet1

#
- name: Add a user # タスク名
user: # userモジュール
name: johnd # 名前
comment: John Doe # コメント
uid: 22026 # ユーザーID
group: admin # グループ名
- foo:
bar1: TRUE # true
bar2: yes # true
bar3: YES # true
bar4: on # true
bar5: ON # true
bar6: FALSE # false
bar7: no # false
bar8: off # false
bar9: "on" # 文字列
bar10: "FALSE" # 文字列
bar12: 2019/10/21 # 書式込み日付
bar13: 10/21/2019 # 書式込み日付
bar11: # aaa書式を使用して日付を曜日表示。出力は、日付の数値となる
bar14: # ふりがな付き文字列。出力にはふりがなも追加される

確認環境

MAC macOS 10.14.6
Java 1.8.0_181
tika 1.22
Ruby 2.6.4p104 (オプション)
yq 2.9.2 (オプション)
jq jq-1.6 (オプション。yqの前提)

取り出し

Apache tika

Apache tikaを利用

以下よりtika-app-N.NN.jarをダウンロードし、適当なディレクトリーに配置

ここではtika-app-1.22.jarを/usr/local/binに配置する

$ java -jar /usr/local/bin/tika-app-1.22.jar -t <EXCELファイル名>
で、EXCELファイルの内容をテキストで書き出すことが可能。

シート名は1行で、シートの内容は列をタブで区切った行単位で出力される。

シート取り出し

ここではawkを使用
--htmlや--xmlで書き出させてruby nokogiriなどを使用して論理的に取り出すことも可能と思える

YAML整形

ansibleで使用するだけならfrom_yamlフィルターで読み込むだけなので不要。

テキストで使用するならば、ここでは整形にRubyを使用(オプション)

yqなど他の整形ツールが入っているなら、| yq -y '.' などと置き換えも可。

JSONに変換して出力するならば、以下を使用

ruby -r json -r yaml -e "puts JSON.pretty_generate(YAML.load(STDIN.read))"

もしくは

yq '.'

シェルスクリプト例

sheet2text.sh
#! /bin/bash

BOOK="$1"
SHEET="$2"

java -jar /usr/local/bin/tika-app-1.22.jar -t "${BOOK}" 2>/dev/null \
| awk '
  BEGIN                           { phase = 0 } 
  phase == 0 && /^'"${SHEET}"'$/  { phase = 1; next } 
  phase == 1 && ( /^\t/ || /^$/ ) { print; next }
  phase == 1                      { exit } 
' \
| awk '
  $1 == "---" && $2 == "" { print $1; next }
                          {
                            gsub("\t\t","\t \t"); gsub("\t\t","\t \t"); 
                            gsub("\t"," "); 
                            gsub("^ ",""); 
                            print 
                          }' \
| ruby -r yaml -e "puts YAML.dump_stream(YAML.load(STDIN.read))"

tikaのテキストモード出力では、セルの境はタブとなる。
タブが連続する場合、間にスペースを入れ、"-"だけのセル(文字幅1)に対応するインデントを確保する。最後にタブもスペースに変換。

実行例

$ sheet2text.sh Book.xlsx Sheet1
---
- name: Add a user
  user:
    name: johnd
    comment: John Doe
    uid: 22026
    group: admin
- foo:
    bar1: true
    bar2: true
    bar3: true
    bar4: true
    bar5: true
    bar6: false
    bar7: false
    bar8: false
    bar9: 'on'
    bar10: 'FALSE'
    bar12: 2019/10/21
    bar13: 10/21/2019
    bar11: 43759.0
    bar14: 愛 アイ

制約,考慮事項

  • セルに改行が含まれないこと(tikaのテキストモードを使用している為、行末と区別がつかなくなる)
  • YAMLとして、TRUE,yes,YES,on,ONなどはtrueと、FALSE,no,NO,off,OFFなどはfalseとされる
  • onなどを明示的に文字列とするには"on"などとする
  • 日付はそのままの書式で解釈される様子
  • 書式を用いた曜日表示は元となる日付が数値で表示された
  • ふりがながあると出力に含まれる為、EXCEL側で除いておく事を推奨

AnsibleによるExcel上YAML読み込み例

task

tasks/main.yml
---
- shell: |
    set -ex

    BOOK="Book.xlsx"
    SHEET="Sheet1"

    java -jar /usr/local/bin/tika-app-1.22.jar -t "${BOOK}" 2>/dev/null \
    | awk '
      BEGIN                          { phase = 0 } 
      phase == 0 && /^'"${SHEET}"'$/ { phase = 1; next } 
      phase == 1 && /^\t/            { print; next }
      phase == 1                     { exit } 
    ' \
    | awk '
      $1 == "---" && $2 == "" { print $1; next }
                              {
                                gsub("\t\t","\t \t");
                                gsub("\t\t","\t \t"); 
                                gsub("\t"," "); 
                                gsub("^ ",""); 
                                print 
                              }'
  delegate_to: localhost
  register: r

- set_fact:
    excel_contents: "{{ r.stdout | from_yaml }}"

- debug:
    var: excel_contents

出力

...

PLAY [all] *********************************************************************************************************************

TASK [Gathering Facts] *********************************************************************************************************
ok: [centos-7]

TASK [include_role local role] *************************************************************************************************

TASK [../tika-test : shell] ****************************************************************************************************
changed: [centos-7 -> localhost]

TASK [../tika-test : set_fact] *************************************************************************************************
ok: [centos-7]

TASK [../tika-test : debug] ****************************************************************************************************
ok: [centos-7] => {
    "excel_contents": [
        {
            "name": "Add a user", 
            "user": {
                "comment": "John Doe", 
                "group": "admin", 
                "name": "johnd", 
                "uid": 22026
            }
        }, 
        {
            "foo": {
                "bar1": true, 
                "bar10": "FALSE", 
                "bar11": 43759.0, 
                "bar12": "2019/10/21", 
                "bar13": "10/21/2019", 
                "bar14": "愛 アイ", 
                "bar2": true, 
                "bar3": true, 
                "bar4": true, 
                "bar5": true, 
                "bar6": false, 
                "bar7": false, 
                "bar8": false, 
                "bar9": "on"
            }
        }
    ]
}

PLAY RECAP *********************************************************************************************************************
centos-7                   : ok=4    changed=1    unreachable=0    failed=0   


YAMLフロースタイルを組み合わせて表を表現する例

EXCEL

Book.xlsx, Sheet2

---
- foo1:
- { key1: val11 , key2: val12 }
- { key1: val21 , key2: val22 }
- foo2:
row1: { key1: val11 , key2: val12 }
row2: { key1: val21 , key2: val22 }

出力

$ sheet2text.sh Book.xlsx Sheet2
---
- foo1:
  - key1: val11
    key2: val12
  - key1: val21
    key2: val22
- foo2:
    row1:
      key1: val11
      key2: val12
    row2:
      key1: val21
      key2: val22

参考

hiroyuki_onodera
インフラエンジニア。 Chef等による自動化、クラスターSW(PowerHA,TSA, pacemaker)などをしています。 このサイトにおける掲載内容はあくまで私自身の見解であり、必ずしも私の所属団体・企業における立場、戦略、意見を代表するものではありません。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした