はじめに
定期レポートの集計を行うDatabricks JOBは動いているのに、最後のExcel更新とPPT作成だけは人がやっていました。そこで、案件の識別子を渡すと完成したExcelとPPTがストレージに出るところまで、Databricks JOBに移しました。難しかったのはファイル生成より、元のレポートと同じものを納品できると確かめることでした。
対象読者: ExcelとPPTで定期レポートを作成しているデータアナリスト。特に、集計の自動化はできているが、納品ファイルへの仕上げが手作業で残っている人
以下は複数の定型レポートで行ったことを、架空の1案件にまとめ直したものです。実際の顧客、レポート名、データ、ファイル構成は載せていません。
参考文献
- Databricks Jobsのパラメータ
- Microsoft: SpreadsheetML文書の構造
- Microsoft: PresentationML文書の構造
- Python: zipfile
- openpyxl: 既存ブックの保存時に失われる要素について
CSVまで自動でも、レポートは完成していなかった
元の流れは、Databricks JOBが集計結果をCSVに出し、担当者がExcelを開いてPower Queryを更新し、数式とグラフを確認してからPPTを仕上げるというものです。CSVは正しくても、納品ファイルはまだありません。
この工程は単純な「CSVをExcelに変換する」作業でもありませんでした。Power Queryが列を整え、Excel側の数式や書式が表を作り、PPTにはExcelから作った図を配置します。処理を移すなら、この役割をそれぞれ把握する必要があります。
最初に対象を1つの定型レポートに絞りました。すべての帳票を同時に移すと、集計値が違うのか、Excelの書式が違うのか、PPTの配置が違うのかを切り分けにくくなります。
Excelは新規作成せず、テンプレートの対象XMLを更新した
一番慎重になったのはExcelです。既存テンプレートにはグラフ、図形、条件付き書式、数式が入っていました。はじめはopenpyxlで開いて値を入れ、保存する素直な方法を試しましたが、このテンプレートでは一部の図形などが失われました。コードは正常終了するので、開くまで気づきません。
そこで、.xlsxをZIPとして開き、値を入れるワークシートなど変更が必要なパーツだけを更新しました。残りのパーツは、読み出した内容をそのまま新しいZIPへ書き戻します。
.xlsxの構造は以前の「Excelの実体って結局何なのか？」で、既存テンプレートを壊さない編集と検証は「なぜopenpyxlではなくXML直接編集が良いのか？」で扱いました。ここでは、XML編集をレポート生成JOBの一工程としてどう使ったかを書きます。
from pathlib import Path
from zipfile import ZipFile
def replace_parts(template: Path, output: Path, patches: dict[str, bytes]) -> None:
"""既存のOOXMLパーツを差し替える。新しいパーツの追加は扱わない。"""
with ZipFile(template, "r") as source, ZipFile(output, "w") as target:
members = source.infolist()
names = {member.filename for member in members}
missing = set(patches) - names
if missing:
raise ValueError(f"テンプレートにないパーツ: {sorted(missing)}")
for member in members:
data = patches.get(member.filename)
if data is None:
data = source.read(member.filename)
target.writestr(member, data)
これはZIPのパーツを差し替える部分だけのコードです。実際のセル更新では、対象セルが想定どおり1件見つかることを確認し、文字列はinlineStr、数値は数値セルとして書きました。数式の結果は古いキャッシュが残るため、Excelを開いたときの再計算も設定します。XML編集の具体的なコードと落とし穴は、上にリンクした記事に譲ります。
「他のパーツはそのまま」といっても、ZIPファイル全体のバイト列が元と同一になるわけではありません。比較するのは、解凍した各パーツの内容です。また、シートや画像を新たに追加する場合は、XML本体だけでなく.relsと[Content_Types].xmlの参照関係も更新する必要があります。
PPTは画像だけでなく、画像への参照も追った
PPTも既存テンプレートを活かしました。集計結果から図を描画して差し替える際、スライド番号から画像ファイル名を決め打ちすると、テンプレートの版が変わったときに別の画像を置換しかねません。
.pptx内のスライドと画像の関係は、ppt/slides/_rels/slideN.xml.relsに記録されています。差し替え対象の画像をこの参照から特定し、必要なスライドXMLと画像パーツを更新しました。元画像と新画像の形式が違うときは、画像の参照先やコンテンツタイプも合わせて直します。Excelと同じく、テンプレートにあるものをどこまで残すかが設計の中心でした。
図を入れ替えた後は、ファイルを開けることと、文字・凡例・余白が見本どおりに見えることを別々に確認します。PPTXが正常なZIPでも、図の縦横比が変わって枠からはみ出せば納品物としては失敗です。
移行判定は「数値」と「開いた画面」を分けた
検証には、すでに提出済みの案件と同じ条件を使いました。旧Power Query版とJOB生成版を並べ、まず表の人数や合計値を突き合わせます。次にExcelとPPTを実際に開き、表示を確認します。
| 確認するもの | 見る理由 |
|---|---|
| 集計値・合計値 | データの絞り込み、結合、分母が旧版と合うか |
| Excel内の部品とXML | グラフや図形が落ちていないか、参照が壊れていないか |
| Excelの再計算後のセル | 数式エラーや古いキャッシュ値が残らないか |
| PPTを画像化した各ページ | 図の位置、凡例、桁数、余白が見本と合うか |
実際に、構成比の値は0.213なのに、セルの表示形式が整数になっていて画面では「0」と出たことがありました。元データとグラフは正しく、Excelを開いて初めて気づく種類の不具合です。「数値が一致したからOK」で止めていたら見落としていました。
移行直後は旧手順も並行して動かし、案件ごとに結果を突き合わせました。検証期間を置いたことで、生成処理のバグと、もともとのテンプレート側の問題も分けて見られました。
自動化後も、例外の確認は残した
定型の案件なら、JOBの入力からExcel・PPTの出力までをつなげられました。一方、案件ごとに変わる注釈や、元の依頼と納品物が一致しているかは、完成ファイルを開いて確認しています。自動生成できることと、内容を確認せず渡せることは別です。
今回いちばん時間を使ったのは、Pythonでファイルを作るコードそのものより、旧版のどの値・どの見た目を正解とするか決める作業でした。レポートの自動化は、最後のファイルまで作って、開いた画面で旧版と比べて初めて終わる。CSVで止めていた頃には、その境界が見えていませんでした。