目的
以下の様な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 -> 13.4.1 (22F82)
Java 1.8.0_181 -> 1.8.0_311
tika 1.22 -> 2.8.0
Ruby 2.6.4p104 -> 2.6.10p210 (オプション)
yq 2.9.2 -> 4.34.1 (オプション)
jq jq-1.6 (オプション。yqの前提)
取り出し
Apache tika
Apache tikaを利用
以下よりtika-app-N.NN.jarをダウンロードし、適当なディレクトリーに配置
ここではtika-app-1.22.jarを/usr/local/binに配置する -> 2.8.0
$ java -jar /usr/local/bin/tika-app-1.22.jar -t <EXCELファイル名>
で、EXCELファイルの内容をテキストで書き出すことが可能。
シート名は1行で、シートの内容は列をタブで区切った行単位で出力される。
シート取り出し (2023/07改定)
シート名は出力YAMLにおけるキーとする。
YAML整形
ansibleで使用するだけならfrom_yamlフィルターで読み込むだけなので不要。
テキストで使用するならば、ここでは整形にRubyを使用(オプション)
yqなど他の整形ツールが入っているなら、| yq -y '.' などと置き換えも可。
JSONに変換して出力するならば、以下を使用
ruby -r json -r yaml -e "puts JSON.pretty_generate(YAML.load(STDIN.read))"
もしくは
yq '.'
シェルスクリプト例 (2023/07改定)
スクリプトといっても実態は1行。
#! /bin/bash
BOOK="$1"
java -jar /usr/local/bin/tika-app-2.8.0.jar -t "${BOOK}" 2>/dev/null \
| sed \
-e '/^$/d' \
-e '/^\t/! s/$/:/' \
-e 's/\t\t/\t \t/g' -e 's/\t\t/\t \t/g' \
-e 's/\t/ /g'
sedによる処理内容
- 空白行を除去
- タブで始まらない行はシート名なので行末に:を付与してキーとする。
- シート名には:など特殊文字は含まれない
- タブが連続する場合、間にスペースを入れ、"-"だけのセル(文字幅1)に対応するインデントを確保
- 1回のみの変換では偶数番目の隙間が処理されないため、2回実施
- 最後にタブもスペースに変換
実行例 (2023/07改定)
% sheet2text.sh Book.xlsx
Sheet1:
---
- foo1:
- { key1: val11 , key2: val12 }
- { key1: val21 , key2: val22 }
- foo2:
row1: { key1: val11 , key2: val12 }
row2: { key1: val21 , key2: val22 }
#
- 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: 10/21/19 # 書式込み日付
bar13: 10/21/2019 # 書式込み日付
bar11: 43759.0 # aaa書式を使用して日付を曜日表示。出力は、日付の数値となる
bar14: 愛 あい # ふりがな付き文字列。出力にはふりがなも追加される
% sheet2text.sh Book.xlsx | ruby -r yaml -e "puts YAML.dump_stream(YAML.safe_load(STDIN.read))"
---
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: 10/21/19
bar13: 10/21/2019
bar11: 43759.0
bar14: 愛 あい
% sheet2text.sh Book.xlsx | yq .
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: 10/21/19 # 書式込み日付
bar13: 10/21/2019 # 書式込み日付
bar11: 43759.0 # aaa書式を使用して日付を曜日表示。出力は、日付の数値となる
bar14: 愛 あい # ふりがな付き文字列。出力にはふりがなも追加される
制約,考慮事項
- セルに改行が含まれないこと
- tikaのテキストモードを使用している為、行末と区別がつかなくなる
- 図形のテキストは問題となる可能性がある
- []{}などを文字列として使用するためには、'や"などで囲われている必要がある。EXCELのセル上では、例えば文字列フォーマットのセルとして、先頭に'',最後に'を追加するなどの対応が必要。
- YAMLそのものの仕様として、TRUE,yes,YES,on,ONなどはtrueと、FALSE,no,NO,off,OFFなどはfalseとされる
- onなどを明示的に文字列とするには"on"などとする
- 日付はそのままの書式で解釈される様子
- 書式を用いた曜日表示は元となる日付が数値で表示される
- 特にEXCEL上で表示書式指定を行っている場合、取得される値がどうなるのか要注意
- もし"火"など曜日として取り込むには、=TEXT(TODAY(),"aaa")などとTEXT関数を用いて文字列として設定
- ふりがながあると出力に含まれる
- EXCEL側で除いておく事を推奨
- rubyのYAML.safe_loadなどではコメントは除去されるが、yqではコメントは残る
YAMLフロースタイルを組み合わせて表を表現する例
EXCEL
Book.xlsx, Sheet1
| --- | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| - | foo1: | ||||||||
| - | { | key1: | val11 | , | key2: | val12 | } | ||
| - | { | key1: | val21 | , | key2: | val22 | } | ||
| - | foo2: | ||||||||
| row1: | { | key1: | val11 | , | key2: | val12 | } | ||
| row2: | { | key1: | val21 | , | key2: | val22 | } |
出力
$ sheet2text.sh Book.xlsx
Sheet1:
- foo1:
- { key1: val11 , key2: val12 }
- { key1: val21 , key2: val22 }
- foo2:
row1: { key1: val11 , key2: val12 }
row2: { key1: val21 , key2: val22 }
YAMLのブロックスタイルにフロースタイルを挿入することは可能だが、逆にフロースタイルにブロックスタイルを挿入できないことに注意。
例えば、ブロックスタイルの-で表わしたLISTを、フロースタイル中で使用したいといった場合、直接は記載できない。YAMLのアンカー、エイリアスを用いて、先にアンカー付きでブロックスタイルLISTを定義し、そのエイリアスをフロースタイル中で使うことで、回避可能とは思われる。