【AlteryxTips】複数のExcelファイルにテンプレートを適用する方法

2021年11月16日 2021年11月16日 11分35秒

AkimasaKajitani

複数のExcelファイルを出力する際に、それぞれ同じテンプレートを適用する方法をご紹介します

Alteryx Designerの最近のバージョンで、フォーマットされたExcelファイルにデータを範囲上書きできるようになりました（2021年のバージョンだとほぼ問題なく動作すると思います）。

しかし、このやり方ですと、元のファイルをそのまま上書いてしまうという欠点があります（もちろん元ファイルは別のところに取っておいて、都度入れ替えることはできますが、面倒ですよね）。

コマンドラインツールなどを使えばファイルをコピーするコマンドを使ってテンプレートをコピーし、その上にデータを上書きという方法も可能ですが、今回は別の方法でやってみたいと思います。

準備

まず、以下のようなファイルを準備しました。このファイルは、「DetailData」と「Pivot」というシートで構成されています。処理をするデータは「DetailData」シートのデータを使います。

処理の内容としては、「DetailData」シートのデータを読み込んで、各地区ごとに別々のファイルに保存します。その際、DetailDataシートのデータは各地区ごとのデータのみにしつつ、このフォーマットは崩さないようにする、というものです。

最終的に得られるものは以下のような形となります。

【入力】

売上_全データ.xlsx

【出力】

売上_全データ_東日本.xlsx
売上_全データ_中日本.xlsx
売上_全データ_西日本.xlsx

※全データといいつつ、中身はそれぞれ東日本、中日本、西日本のデータとなります（なお、Pivotの中身の更新はされません）

ワークフロー解説

ワークフローを作る方針としては以下のとおりです。

データ準備
ファイルパス作成
テンプレートのコピー
ファイル上書き

実際のワークフローがこちらです。

１．データ準備

データ準備部分は、、、今回のメインのお題のテンプレートに書き込む手法にはあまり関係ないですが、今回は地区リスト（県名とそれぞれ３つの地区のどれに該当するかというリストです）をテキスト入力ツールにもっており、これを売上データの県名にあてて３つの地区がわかるようにしています。

２．ファイルパス作成

テンプレートを使って上書きする手法としては、元ファイルのパスをテンプレートのパスとして取得し、そのファイルを保存先にコピーし、そこにデータを上書きするという方法をとります。よって今回必要になるファイルのパスは、３つです。

テンプレートの元となるファイルのパス（TemplatePath）
テンプレートのコピー先となるファイルのパス（SaveFilePath）
上書きするファイルのパス（SaveFilePathDetail）

テンプレートの元となるファイルのパス（TemplatePath）

テンプレートの元となるファイルのパスは、今回最初に読み込んだファイルになるため、データ入力ツールで「ファイル名をフィールドとして出力する」オプションを使えば取得が可能です（今回は、フルパスを指定）。しかしながらこのオプションを使うと、Excelファイルは「売上_全データ.xlsx|||`DetailData$`」のような形でパスが取得されてしまいます。

今回はファイルのコピーを行うため、シート名など余計なものがついているとコピーで失敗してしまいます。ここでは正規表現ツールで必要な部分だけ抜き出しています。

正規表現ツールで「解析」を選択し、正規表現内の式で条件に一致する部分にカッコをつければその部分だけ抜き出せます。今回のようなケースでは、ファイル名の部分だけ抜き出せれば良いので、

(.*.xlsx)|||`DetailData$`

という式でオッケーです。これで、「売上_全データ.xlsx|||`DetailData$`」が「売上_全データ.xlsx」という形で抜き出せます（このブログ上ではフォルダのパスは省略して表現しています）。

テンプレートのコピー先となるファイルのパス（SaveFilePath）

テンプレートをコピーしてそこに上書きするためには、まずテンプレートをコピーする必要があります。コピー元のファイルパスは先程作りましたので（TemplatePathというフィールド名にしています）、ここではコピー先のパスを作っていきましょう。コピー先のパスは、地区名を使ったものになるので、「地区名」フィールドを入れます。フォーミュラツールの設定は以下のような計算式になります。

FileAddPaths(FileGetDir([TemplatePath]), FileGetFileName([TemplatePath]))+"_"+[地区名]+".xlsx"

これで、「売上_全データ.xlsx」が、「売上_全データ_地区名.xlsx」といった形でコピーできるようになりました。

上書きするファイルのパス（SaveFilePathDetail）

ファイルの上書きをする際、単なるシートの上書きではなく、範囲指定で上書きをしたいと思います。これは、装飾された部分に対しての上書きする場合は必ず範囲指定での上書きを要求されるためです。範囲としては、AからN列までで、スタートは3行目からですので、A3:N65535という範囲としています。ちなみに、65535という数字は広めに範囲を取っているだけです（しっかり上書きで消せるように）。理想としては、Excelの上限の1,048,576行の指定でしょうか・・・（しかし、Alteryx内部でデータを作る際にメモリを消費するのであまりおすすめしません）。元々のファイルのレコード数はわかっているので、それを使うこともできます。

[SaveFilePath]+"|||DetailData$A3:N65535"

これで、「売上_全データ_地区名.xlsx|||DetailData$A3:N65535」というファイルパスが得られます。

３．テンプレートのコピー

いよいよテンプレートのファイルをコピーしていきます。ファイルのコピーには、Blob入力・Blob出力ツールを使います。つまり、Blob入力で読み込んだファイルをBlob出力でそのまま保存するという手法を取っています。

なお、Blob入力ツールの前にまず集計ツールでコピー元とコピー先のパスを一意のものにしています（これをしないと無駄に読み込みと保存を繰り返します）。

あとは、テンプレート元のファイルをBlob入力ツールで読み込みます。設定としては以下の通り「パス全体をフィールドに置換」オプションを使用します。

その後、Blob出力ツールで何も手を加えずすぐに保存します。設定としては以下の通りで「パス全体をフィールドに置換」を使用します。

これで、今回のデータでは、地区名が「東日本」「中日本」「西日本」となっているため、「売上_全データ.xlsx」を「売上_全データ_東日本.xlsx」「売上_全データ_中日本.xlsx」「売上_全データ_西日本.xlsx」と3つのファイルにコピーされます。

４．ファイル上書き

まず、ここで行う必要があるのは、結合ツールの設定です。テンプレートのコピー先となるファイルのパス、つまり２で作成したSaveFilePathという項目で結合します。

なお、右入力（R入力）の内容は使わないためチェックを外します。ここでわざわざ右入力に入っているデータを元データに結合しているのは、単にテンプレートのコピーをファイル保存の前に終わらせる、という意図があります（若干高度な話になりますが、結合ツールがブロッキングツールであるという特性を利用しています）。

最後に、テンプレートが３つのファイルにコピーされているので、それぞれのファイルに上書きを行います。設定としては以下の通りです。

フィールド名をスキップする：チェックを入れる
上書き時に書式設定を保持する（範囲が必要）：チェックを入れる
出力オプション：シートまたは範囲を上書きする
フィールドからファイル/テーブル名を取得する：チェックを入れ、「ファイルパス全体を変更する」を選択
ファイル名またはファイル名の一部を含むフィールド：SaveFilePathDetail
出力でフィールドを保持する：チェックをはずす

これにより、売上_全データ.xlsxから各地区ごとにデータを抜き出し、元のファイルと同じフォーマットに上書きする、ということができました。アウトプットとしては、売上_全データ_東日本.xlsx、売上_全データ_中日本.xlsx、売上_全データ_西日本.xlsxの３ファイルが作成されます。

終わりに

Excelファイルに装飾して出力する場合、Alteryxのレポーティングツールを使用し、レンダリングツールで出力するというのが一番スタンダードでスマートな解決方法です。

しかしながら、どうしてもExcelファイルを活用したい、という際は、データを出力した後、VBA（マクロ）でフォーマットとなるExcelファイルに書き込む、もしくは今回のように元のファイルをコピーし、そこにデータ部分だけ更新する、というパターンが考えられます（旧バージョンのDesignerだとフォーマットを残したまま上書きというのができなかったため、相変わらずVBAを利用する方法しかありませんが、最近のバージョンをお使いであれば今回のような方法が使えます）。

通常であればコマンドラインツールを使い、コピーを行うバッチファイルを起動する、というのが一般的な方法かと思いますが、普段使う用途を思いつきにくいBlobツールがこんなところで役に立つとは！という感じでした。