AlteryxのTipsを不定期にお届けしている週刊（？）AlteryxTipsです。
今回は、レンダリングツールで出力したExcelファイルのセルの幅がAlteryxでどうにもならない問題を、Pythonツールを使って解決する方法と、Pythonツールを使わずに解決する方法をご紹介します。
レンダリングツールのExcel出力について
Alteryxのレンダリングツールは、Alteryx内で作成したレポートを出力するためのツールですが、色々と癖があります。特に、Excel形式に出力する場合、セルの幅がどうしても言うことを聞いてくれなくて苦労している人は多いかと思います。結果的に、Excelに出力する際は、Excelであらかじめフォーマットを作っておき、そこに上書きする、という方法が主流となっています。
しかしながら、やはりすべてAlteryxで完結できると楽です。フォーマット化されたExcelに書き込む場合も色々と課題があったりするので、できればテーブルツール＋レンダリングツールで解決できると非常に楽です。
Excelのセル幅を指定するためには、結局Pythonツールに頼るか、あとは、Excelのxmlを無理やり書き換えるという二択になりますが、基本的にはPythonツールで解決するのが主流かと思います。
openpyxlでセル幅をコントロールする
Excelを操作するライブラリは数多くありますが、今回はopenpyxlを使いましょう。
Pythonツールを使う場合、インストールしていないライブラリを使う場合は必ず事前に管理者権限でDesignerを起動し、Package.installPackagesを使ってインストールする必要があるのでご注意ください。
今回の全体のワークフローとしては以下のとおりとなりますが、テーブルツールでテーブル形式にし、その後レイアウトツールでシート名を指定しながらレンダリングツールで保存し、保存したファイルをPythonツールでそのまま列幅を設定する、というワークフローとしています。
これは、保存したファイルと列幅を指定するPythonツールを連動させるためにファイルパス、シート名を外出しにしています。すべてアドホックに個別に指定する場合は、ここまで凝ったワークフローにする必要はありません。
入力として、出力するExcelファイルのパスとシート名をコントロールするために、以下のようにしています。
また、コントロールする列幅の設定もテキスト入力ツールに入れています。
Cellカラムの方にカラムの名称、sizeカラムに列幅を指定します（単位は、ピクセルではありません）。Excelのカラム変更の際に出てくる数値がその列幅です。
Pythonのコードとしては、以下のようになります。意外と単純なコードになっています。
from ayx import Alteryx
import openpyxl
# 処理するExcelファイルのパスとシート名を取得
df = Alteryx.read("#1")
excelpath = df.iloc[0]['OutputPath']
sheetname = df.iloc[0]['sheetname']
# カラムの幅情報を取得
df_col = Alteryx.read("#2")
columnsetting = df_col.iloc[0].to_dict()
# openpyxlで処理を実行
# 処理対象のExcelファイルを開く
wb = openpyxl.load_workbook(excelpath)
ws = wb[sheetname]
# 各列を個別に設定できるように辞書型を作成する。
column_width = columnsetting #{"A":3, "B":8, "C":6}
# 各列を処理する
for column , width in column_width.items():
ws.column_dimensions[column].width = width
# Excelファイルを保存
wb.save(excelpath)一旦できてしまえば、あとはこれをマクロにするなりなんなりできるかと思います。今回はサンプルワークフロー内に、カラムセッティングとファイルパス、シート名を指定すれば、Excelのセルの横幅のみ調整するようなマクロを作成してみました。
ちなみに、Pythonツール内でファイルを読み込む場合、フルパス指定しないといけません。というより、Pythonツールのカレントフォルダはワークフローのあるフォルダではないため、入力ツールなどと同じような感覚で対応するとうまくいかない、ということになります。
ExcelのXMLを無理やり書き換えて対応する
以前の記事で、ExcelファイルをZIPファイルと見立てて情報の取得を行いました。
これを応用すると、ExcelファイルをZIPとして解凍し、XMLとして編集した後、またExcelファイルに圧縮しなおすことでExcelファイルをXMLとして編集することができます。
正直これは、ZIPファイルに名前を変えつつコピーし、解凍し、編集してから再度ZIPファイルに圧縮し、元の名前に戻す、といった感じで４回バッチファイルを叩かないといけないので結構面倒です。
まぁ、一度形を作ってしまえばそのまま使えてしまうのでちょっとチャレンジしたいと思います。
ExcelのXML構造
Excelファイルは、最終的にxl\worksheets内にある「sheet1.xml」（シートが増えると「1」の部分は増えていきます）に実データが格納されています。体裁もある程度はここで設定されているように見えます（間違っていたらごめんなさい）。ただ、ファイル名はシート名ではなく、シートのIDをベースに作られています。最初のシートであればsheet1.xml決め打ちで問題ないのですが、シート名決め打ちはできないので厄介です。シート名とIDの対応を管理しているのは、xl/workbook.xmlです。
今回見なければならないのは、その中でもsheetsという部分です。例えば、シートが3つある時は以下のようになっています。
<sheets>
<sheet name="Sheet1" sheetId="1" r:id="rId1"/>
<sheet name="Sheet2" sheetId="2" r:id="rId2"/>
<sheet name="Sheet3" sheetId="3" r:id="rId3"/>
</sheets>ここで取得できるsheetIdとnameを比較し、実際に変更すべきファイルを特定します。
さらに、実際のシートのXMLはどのようになっているのでしょうか？実際にセルの幅を決めているのはsheet1.xmlの以下のタグの部分です。
<cols>
<col min="1" max="1" width="3.875" customWidth="1"/>
<col min="2" max="2" width="17.875" customWidth="1"/>
</cols>ここを書き換えれば良いわけです。
カラムの位置は、minとmaxで一度に複数指定できるようになっていますが、一から作る場合には便利かもしれませんが、既存のタグがある場合に、min=1、max=3などとなっているとこれをバラさなければならないのでめんどくさいです。Alteryxでは、行生成ツールであらかじめ分解してしまった方が楽かと思います。
ちなみに、このcolsタグはカラムのサイズ変更をまったく行っていない場合、そもそもタグが存在しないので、そのあたりの場合分けなどもする必要があります。
ということで、思った以上にめんどくさい処理になるので、正直なところPythonのライブラリでやった方が100倍楽ですね・・・（まぁ、Excelを解凍してまた戻す、というテンプレート的なものが作れたということでいえば、いい勉強になりました）。
ちなみに、workbook.xmlを読み込み、sheet.xmlのアップデートをするパートだけで以下のようなワークフローとなりました（結構長いですね・・・）。
さらに、この前にファイルをzipとして解凍して、この後にファイルを圧縮する、というパートをつける必要がありますが、Alteryx内部だけで完結できました。
まとめ
- レンダリングツールで出力したExcelファイルの列の幅を変更する方法をご紹介しました
- Python版のマクロ、Base A版のマクロ、両方作成しています
- 思ったよりBaseA版が作業量重かったですが、出来栄えはそれなりに満足しています
コメント