ExcelからYAMLとして内容を取り出して利用する例 (2023/07改定)

# 目的

以下の様な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をダウンロードし、適当なディレクトリーに配置

- [Apache Tika - a content analysis toolkit](http://tika.apache.org)
- [Apache Tika Download](https://tika.apache.org/download.html)

ここでは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の変換の例](https://qiita.com/hiroyuki_onodera/items/effd236d89a1a757155b)

## シェルスクリプト例 (2023/07改定)
スクリプトといっても実態は1行。

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



# 参考

- [Apache Tika - a content analysis toolkit](http://tika.apache.org)
- [様々なファイルからテキストとメタデータを抽出できるライブラリ「Apache Tika」](https://vintage.ne.jp/blog/2014/10/346)
- [フリガナを除去するマクロ](http://mt-soft.sakura.ne.jp/kyozai/excel_vba/300_vba_kiso/20_DeletePhonetic.htm)
- [個人用マクロブックの作成方法と使い方](https://allabout.co.jp/gm/gc/297809/)