目的

以下の様な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 '.'

コマンドラインからRubyを用いた JSON, YAML, XML の整形および JSON <-> YAMLの変換の例

シェルスクリプト例 (2023/07改定)

スクリプトといっても実態は1行。

sheet2text.sh

#! /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を定義し、そのエイリアスをフロースタイル中で使うことで、回避可能とは思われる。

目的