openpyxl で検収レポート・判定表を作ってみた

Posted at 2026-08-30

はじめに

上下水道業界で管路台帳システム(GIS)の運用に携わっている実務者です。

前回までの分析の話はいったん休んで、今回は箸休めです。このシリーズのリポジトリで人に渡す報告物を出す 4 本のツール(台帳の検収(納品物の受け入れ検査)・属性の記入率レポート・台帳と調査の突合・緊急度判定)は、どれも Excel ファイルで出します(分析用に CSV を併せて出すツールもあります)。なぜそうしているのか、そして「開いた瞬間に納品物に見える」Excel を Python(Excel ファイルを読み書きするライブラリ openpyxl)で作るときの勘所を、まとめておきます。

私はもともと Excel VBA で帳票(請求書のフォーマットを自動生成し、ボタン一つで印刷プレビューまで出すもの)を作っていました。Python 側に移っても、帳票に求められることは変わりません。変わったのは「Excel の中から作る」か「外から作る」かだけです。

お断り: 本記事の画像は、ツールが出力した xlsx を LibreOffice で PDF 化して描画したものです(撮影のために印刷範囲と縮尺だけ調整しています。Excel 本体の画面ではありません)。緊急度判定表は第 6 回の架空の調査記録による判定で、スパン(マンホール間の区間)の ID・諸元だけが半田市オープンデータ由来で、実際の管渠の状態ではありません。検収レポートの NG 一覧は第 8 回で公開した結果の再掲で、入力疑義の検出であり、半田市の台帳品質の評価ではありません。画像の下の出典・注記の 1 行は画像化のときに付けたもので、xlsx には含まれていません。

なぜ CSV ではなく Excel なのか

技術者の感覚では、CSV の方が素直です。軽いし、差分も取れるし、どの言語でも読める。システムに取り込むための納品なら CSV や GeoPackage(GIS データを 1 ファイルに収める形式)が正解です。それでも人に渡す報告物を Excel にするのは、受け取る側の行動がだいたい決まっているからです。

受け取った人はファイルをダブルクリックして開き、そのまま画面を見ながら協議し、印刷して回覧し、決裁の添付にします。その間、データをプログラムで読み込む人はほとんどいません。中身の正しさが品質なのは当然として、開いた瞬間の見た目が、納品物の第一印象を決めます。 CSV をダブルクリックした結果は、だいたいこうなります。

文字コードが合わずに文字化けする(先頭のバイト順マーク(BOM)が無い UTF-8 は、日本語版 Windows の Excel が Shift_JIS として開いてしまう典型例)
先頭のゼロが消える(施設番号 0012 が 12 になる)
長い番号が指数表記になる、日付に見える文字列が勝手に日付に変わる
列幅が揃わず、判定の色も付かず、複数の表を 1 ファイルに同梱できない

第 8 回で書いたとおり、検収の協議は発注者と受注者が同じ「NG 一覧」を見ながら 1 件ずつ潰していきます。そのテーブルに載せる紙が、開くたびに化けるわけにはいきません。このシリーズでは、分析の中間成果は GeoPackage や CSV、人に渡す報告は Excel と使い分けています。

帳票の 3 層構造 — サマリ・明細・根拠

ツールが出す Excel は、この 3 層を基本形にしています(いちばん揃っているのは緊急度判定ツールで、以下はそれを例にします。検収ツールは根拠シートを持たず、代わりに閾値のある検査項目については明細の備考列に閾値と理由を書いています)。

シート	役割	読む人
サマリ	件数と実行条件。報告書の頭に貼れる 1 枚	決裁者・発注担当
明細	1 行 1 件の判定結果。協議のテーブルに載せる	担当者
根拠	判定基準と出典(検収ツールは明細の備考列で代替)	質問してきた人

サマリには件数だけでなく実行日を必ず入れています(4 ツール共通)。緊急度判定ツールはさらに基準プロファイル名(どの判定基準ファイルで判定したか)と末尾の「注意」行を持ちます。半年後に「この表はどの基準で出したのか」と聞かれたとき、ファイルを開けば分かる状態にしておくためで、免責を別紙にすると別紙だけが外れて表が独り歩きするからです。

明細では、判定(緊急度 Ⅰ/Ⅱ/Ⅲ)のセルをランクごとの色で塗り、右端に「判定根拠」の列を置いています。どの不良がどのランクに効いて、国交省の手引き(案)(第 6 回で実装した判定基準の出典)のどの規定(表の備考)でこの緊急度になったかを 1 行の文章にしたものです。長い文になるので、列幅の上限に当たったら折り返して印刷されるようにしてあります(後述)。協議で「なぜこれがⅡなのか」と聞かれたとき、この列を読み上げれば済みます。

検収レポートでは「NG 一覧」が協議用の明細で、別シートの「全検査結果」には OK の行も残しています。「検査して問題なかった」と「検査していない」を区別できるのは、OK の行だけです。

緊急度ツールの「判定基準と出典」シートは根拠の層です。判定に使った閾値を 1 行ずつ並べ、出典(手引きの表番号とページ)に加えて資料名と URL の列を持たせています。URL はハイパーリンクにしてあるので、質問されたとき、ブックの中から出典を開けます。

勘所をコードで — style_sheet 関数

見た目の処理は、関数 1 つにまとめています(同じ関数を各ツールに置いていて、共通モジュール化はしていません。列幅の上限 max_width だけツールごとに 40〜60 で変えています)。pandas の to_excel で書いた直後に、シートごとに掛けるだけです。

import unicodedata

from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.properties import PageSetupProperties

HEADER_FILL = PatternFill("solid", fgColor="1F4E79")
HEADER_FONT = Font(color="FFFFFF", bold=True)


def display_width(text: str) -> int:
    """Width in Excel character units: CJK (and ambiguous) glyphs count double."""
    return sum(2 if unicodedata.east_asian_width(ch) in "FWA" else 1 for ch in text)


def style_sheet(ws, max_width: int = 50) -> None:
    for cell in ws[1]:
        cell.fill = HEADER_FILL
        cell.font = HEADER_FONT
        cell.alignment = Alignment(horizontal="center")
    for idx, col in enumerate(ws.columns, start=1):
        width = max(display_width(str(c.value)) for c in col if c.value is not None)
        ws.column_dimensions[get_column_letter(idx)].width = min(width + 2, max_width)
        if width + 2 > max_width:            # long text: wrap instead of clipping
            for c in col[1:]:
                c.alignment = Alignment(wrap_text=True, vertical="top")
    ws.freeze_panes = "A2"
    ws.page_setup.orientation = "landscape"
    ws.page_setup.paperSize = ws.PAPERSIZE_A4
    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = 0
    ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)
    ws.print_title_rows = "1:1"
    ws.print_options.gridLines = True   # screen gridlines are NOT printed by default

上から順に、何をしているかと、なぜそうするかです。

ヘッダー行: 濃紺の塗り+白の太字+中央揃え。Office のテーマ色由来の濃紺で、「ここが見出しだ」と一目で分かります。

列幅 — len() の罠: 列幅はいちばん長い値に合わせて自動で決めますが、Python の len() は全角文字も 1 と数えます。Excel の列幅の単位は既定フォントの数字 1 文字分の幅(ほぼ半角 1 文字)なので、日本語の見出しは len() で測るとおおむね半分の幅にしかなりません。unicodedata.east_asian_width で全角(F/W)と曖昧幅(A)の文字を 2 と数えるのが実用的です。上限(max_width)に当たった列は、切り捨てる代わりに折り返し(wrap_text)にします — 判定根拠のような長文列が印刷で途中で切れないためです。白状すると、この記事で挙げた勘所の多く — 全角を数える列幅、折り返し、次の印刷設定(枠線を含む)、サマリの実行日、根拠シートの資料名と URL の列 — は、この記事を書くにあたって 4 ツールの出力をまとめて見直して足したものです。列幅はそれまで len() + 4 という雑な余白で誤魔化していました。

ウィンドウ枠の固定: freeze_panes = "A2" で 1 行目を固定。数百行の明細をスクロールしても見出しが消えません。

印刷設定: A4 横、幅を 1 ページに収め(縦は成り行き)、見出し行を全ページに繰り返す。帳票は印刷される前提なので、ここを省くと「2 ページ目以降に見出しが無い紙」が回覧されます。もう 1 つ忘れやすいのが枠線で、画面に見えている枠線は既定では印刷されません。print_options.gridLines = True を入れないと、行が宙に浮いた紙になります。

判定セルの色: 緊急度の列は、条件付き書式ではなく値に応じてセルを直接塗っています。

URGENCY_COLORS = {"Ⅰ": "C00000", "Ⅱ": "ED7D31", "Ⅲ": "FFC000"}

for row in range(2, len(results) + 2):
    cell = ws.cell(row=row, column=urgency_col)
    color = URGENCY_COLORS.get(str(cell.value))
    if color:
        cell.fill = PatternFill("solid", fgColor=color)
        cell.font = Font(color="FFFFFF", bold=True)

直接塗る理由は、色を「ルール」ではなく「書式そのもの」として持たせたいからです。条件付き書式はルールとして持ち回られるため、受け取った側の貼り付けや行の追加でルールが増えたり範囲がずれたりしがちです。検収物の色は「データの一部」として固定した方が安全です。

Excel 側の落とし穴

openpyxl は素直なライブラリですが、Excel というファイル形式の側に、足を取られやすい制約があります。

シート名は 31 文字まで。: \ / ? * [ ] は使えません。日本語のシート名は短く。
ID は文字列で書く。0012 のような番号を数値で書くと先頭ゼロが消えます。逆に、数値に見える ID(台帳エクスポートの一意 ID である SAUID など)に桁区切りを付けてはいけません。
日付は日付型で書く。文字列で書くと、Excel 側の日付フィルタや期間集計が効きません。pandas の datetime 列や Python の date オブジェクトをそのまま渡せば、Excel の日付になります。サマリの実行日も同じです。
数式は書かない。検収物に欲しいのは「値」です。openpyxl が書く数式には計算結果のキャッシュが無いので、再計算しないビューアや pandas で読むと空欄に見えます。
大きなデータは Excel にしない。xlsx の上限は 1,048,576 行ですが、上限の前に開く側が固まります。20 万レコードの台帳そのものは Excel に載せず、Excel には検査結果(102 行)と NG 一覧(24 行)だけを載せる — これが第 8 回の構成です。
マクロ付き(xlsm)を openpyxl で開いて保存すると VBA が消える。既存の様式ブックに書き込む場合は load_workbook(path, keep_vba=True) が必須です(それでも図形やフォームコントロールなど、保持されない要素はあります)。

VBA との違い — プレビューできない

VBA は Excel の中で動くので、ボタンを押せば印刷プレビューが出ます。openpyxl は外からファイルを書くだけで、書いた結果を見る手段を持ちません。ここがいちばんの違いです。

対策は 2 つです。1 つは、納品前に必ず人間が一度開くこと(当たり前ですが、自動化すると省きたくなります)。もう 1 つは、LibreOffice で PDF 化して目視することです。本記事の画像もこの方法で作りました。

soffice --headless --convert-to pdf --outdir out report.xlsx

Excel が入っていないサーバーや CI でも動くので、「印刷したらどう見えるか」を自動で確認したいときに重宝します。

実務でどう使えるか

検収・判定ツールの出口を Excel にする: サマリ/明細/根拠の 3 層にしておくと、決裁者・担当者・質問者のどの読み手にも 1 ファイルで応えられます。
発注者の様式に列順と列名を寄せる: 様式が指定されている納品なら、ツール側の列名を様式に合わせます。DataFrame の列を並べ替えるだけで済むのが、Python で作る利点です。
「値」で納める: 数式も条件付き書式も使わず、色も含めて値として固定する。受け取った側が行を足したり貼り付けたりしても、色やルールが勝手に動きません。
印刷を前提に設計する: 見出し行の繰り返しと横向き・幅合わせは、最初の 1 回で関数に入れてしまえば二度と忘れません。

まとめ

納品物を Excel にするのは、受け取る側が「開いて・囲んで・印刷して・添付する」からです。開いた瞬間の見た目が、納品物の第一印象を決めます。
帳票はサマリ・明細・根拠の 3 層を基本形に。実行日(と基準名・注意書き)はサマリ本体に埋め込み、明細には判定根拠の列、根拠シートには出典の資料名と URL を置きます。
見た目の勘所は関数 1 つに集約できます: ヘッダー装飾、全角を 2 と数える列幅、枠固定、印刷設定(横・幅合わせ・見出し繰り返し・枠線)、判定セルの直接塗り。
openpyxl はプレビューできないので、納品前に開くか、LibreOffice で PDF 化して確認します。コードはリポジトリの各ツール(style_sheet 関数)にあります。

関連記事: 上下水道×データの記事は一覧からどうぞ。コードは GitHub で公開しています。

免責・出典

本記事の画像は、ツールが出力した xlsx を LibreOffice で PDF に変換して描画したもので、Excel 本体の表示とは細部が異なる場合があります。
データ出典: 半田市「水道管路等データ」(CC BY 4.0)を加工して作成 https://www.city.handa.lg.jp/opendata/1005557/1005561/1010384.html
緊急度判定表は架空の調査記録による判定で、スパンの ID・諸元のみ半田市オープンデータ由来で、実際の管渠の状態ではありません(第 6 回参照)。検収レポートの NG 一覧は半田市「水道管路等データ」(CC BY 4.0)を加工したもので、入力疑義の検出であり半田市の台帳品質の評価ではありません(第 8 回参照)。
コード: pipeline-ledger-tools(MIT License)。openpyxl の API は執筆時点(3.1 系)のものです。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up