はじめに
業務で複数シートに分かれたExcelのテストケースを横断的に検索したい場面がありました。シートを1枚ずつ開いて探すのは手間なので、Pythonのopenpyxlで必要な情報だけを抜き出し、テキストファイルに出力するスクリプトを作りました。テキストにしてしまえば、エディタやgrepで全シートをまとめて検索できます。
処理の流れはざっくり次のとおりです。
- Excelを読み込み、シートごとに処理する
- 各シートのデータ範囲を把握する
- テストケースを1行ずつ処理し、ステータスや必要情報の有無で出力対象を判定する
- 対象行の値をテキストに出力する
作る中で詰まったポイントを1つずつ紹介していきます。今回はシートごとに異なるデータ範囲と結合セルの2つです。
記事中のExcelとコードは、説明のために作成した再現用の実装例です。シート名やテストケースは架空のもので、実際の業務で使ったファイル構成やコードとは異なります。
サンプルのExcel
どちらのシートも1行目がタイトル、2行目が見出し、3行目からがテストケースです。
ログイン画面（A2:E30に罫線を設定。データは6行目まで）
| 行 | A: No | B: 大項目 | C: テスト内容 | D: 期待結果 | E: ステータス |
|---|---|---|---|---|---|
| 3 | 1 | 正常系（B3:B4を結合） | 正しいID/パスワードでログイン | トップ画面に遷移する | 済 |
| 4 | 2 | （結合範囲内） | ログイン状態を保持してログイン | 再訪問時に自動ログインされる | 未 |
| 5 | 3 | 異常系（B5:B6を結合） | 誤ったパスワードでログイン | エラーメッセージを表示する | 未 |
| 6 | 4 | （結合範囲内） | 未入力でログイン | 入力エラーを表示する | 未 |
| 7〜30 | （空欄。罫線のみ） |
検索画面（列が1つ多く、行数も違う）
| 行 | A: No | B: 大項目 | C: テスト内容 | D: 期待結果 | E: ステータス | F: 備考 |
|---|---|---|---|---|---|---|
| 3 | 1 | 正常系 | キーワードで検索 | 一致する結果を一覧表示する | 済 | |
| 4 | 2 | 異常系（B4:B5を結合） | 記号のみで検索 | 入力エラーを表示する | 未 | 仕様確認中のため保留（F4:F5を結合） |
| 5 | 3 | （結合範囲内） | 256文字以上で検索 | 入力エラーを表示する | 未 | （結合範囲内） |
Tips 1：シートごとに異なるデータ範囲の取得
困ったこと
最初は「3〜30行目、A〜E列」のように範囲を固定して読み取っていました。しかしこの方法には次の問題があります。
- シートごとに行数・列数が違うため、範囲外のテストケースや列（検索画面のF列など）が漏れる
- テストケースを追加するたびにコードを直す必要がある
- データが少ないシートでは、空行まで出力される
シートの範囲を取得するプロパティ
openpyxlのワークシートには、範囲を表すプロパティが用意されています。
| プロパティ | 内容 |
|---|---|
min_row / max_row
|
セルが存在する最初の行 / 最後の行 |
min_column / max_column
|
セルが存在する最初の列 / 最後の列（数値） |
dimensions |
上記を A1:E30 のような文字列で表したもの |
from openpyxl import load_workbook
wb = load_workbook("testcases.xlsx")
for ws in wb.worksheets:
print(ws.title, ws.dimensions, ws.min_row, ws.max_row, ws.min_column, ws.max_column)
ログイン画面 A1:E30 1 30 1 5
検索画面 A1:F5 1 5 1 6
この値を使えば、シートごとに範囲を変えて読み取れます。iter_rows() に範囲を渡し、values_only=True を指定すると、セルではなく値のタプルが返ります。
HEADER_ROW = 2 # 見出し行（サンプルの想定）
with open("testcases.txt", "w", encoding="utf-8") as f:
for ws in wb.worksheets:
for row in ws.iter_rows(min_row=HEADER_ROW + 1, max_row=ws.max_row,
max_col=ws.max_column, values_only=True):
values = ["" if v is None else str(v) for v in row]
f.write(f"[{ws.title}] " + " | ".join(values) + "\n")
[ログイン画面] 1 | 正常系 | 正しいID/パスワードでログイン | トップ画面に遷移する | 済
[ログイン画面] 2 | | ログイン状態を保持してログイン | 再訪問時に自動ログインされる | 未
[ログイン画面] 3 | 異常系 | 誤ったパスワードでログイン | エラーメッセージを表示する | 未
[ログイン画面] 4 | | 未入力でログイン | 入力エラーを表示する | 未
[ログイン画面] | | | |
[ログイン画面] | | | |
（30行目まで空行が続く）
[検索画面] 1 | 正常系 | キーワードで検索 | 一致する結果を一覧表示する | 済 |
[検索画面] 2 | 異常系 | 記号のみで検索 | 入力エラーを表示する | 未 | 仕様確認中のため保留
[検索画面] 3 | | 256文字以上で検索 | 入力エラーを表示する | 未 |
列数の違いには対応できましたが、ログイン画面では空行が大量に出力されてしまいました。
なぜ空行が出るのか
max_row などは「値が入っている範囲」ではなく、ワークシート上にセルが存在する範囲を返します。罫線や背景色などの書式だけが設定された空のセルも「存在するセル」として数えられるため、サンプルでは罫線を引いた30行目までが範囲になりました。値を消しても書式が残っているセルも同様です。
つまり、次の2つは別物として考える必要があります。
-
データ範囲：シート上にセルが存在する範囲（
max_rowなどで取得） - テストケースの範囲：実際にテストケースが入っている範囲（自分で判定する）
対処法：キー列と見出し行から実データの範囲を判定する
テストケースには必ずNoがある、見出し行には必ず項目名がある、という前提を置き、max_row / max_column から逆向きに走査して最後の値を探します。
def has_value(v):
return v is not None and str(v).strip() != ""
def get_last_row(ws, key_col=1):
"""キー列（No列）に値がある最後の行"""
for r in range(ws.max_row, HEADER_ROW, -1):
if has_value(ws.cell(row=r, column=key_col).value):
return r
return HEADER_ROW # テストケースなし
def get_last_col(ws):
"""見出し行に値がある最後の列"""
for c in range(ws.max_column, 0, -1):
if has_value(ws.cell(row=HEADER_ROW, column=c).value):
return c
return 0
max_row は「ここより下にはセルがない」という上限として使い、テストケースの終わりはデータの中身で判定する、という役割分担です。
通常モードの ws.cell() は、存在しないセルを指定すると新しく作成します。範囲外を参照すると max_row などが変わってしまうため、走査は max_row / max_column の内側に収めています。
また、load_workbook(..., read_only=True) で開いた場合、範囲はファイルに記録された情報をもとに返されるため、実際とずれることがあります。次のTipsで使う結合セル情報も読み取り専用モードでは参照できないため、今回は通常モードで読み込んでいます。
このあと range(HEADER_ROW + 1, get_last_row(ws) + 1) で行を回せば、空行は出力されなくなります（出力コードはTips 2でまとめて掲載します）。
Tips 2：結合セルからの値の取得
困ったこと
Tips 1の出力をよく見ると、ログイン画面のNo.2・4や検索画面のNo.3で「大項目」が空になっています。Excel上では「正常系」「異常系」と表示されているのに、です。これでは「正常系」でgrepしても、No.2がヒットしません。
原因
Excelのファイル上では、結合セルの値は結合範囲の左上のセルにだけ保存されています。それ以外のセルは「結合されている」という情報があるだけで、値を持っていません。
openpyxlでは、左上以外のセルは MergedCell という特殊なオブジェクトとして読み込まれ、value は常に None です。
from openpyxl.cell.cell import MergedCell
ws = wb["ログイン画面"]
print(ws["B3"].value) # 正常系
print(ws["B4"].value) # None
print(isinstance(ws["B4"], MergedCell)) # True
MergedCell で「結合範囲の左上以外であること」は分かりますが、どこが左上なのかは分かりません。そこで結合範囲の一覧を使います。
ws.merged_cells.ranges で結合範囲を調べる
ws.merged_cells.ranges には、シート内の結合範囲が CellRange オブジェクトとして入っています。各範囲は行・列の最小値と最大値を持っています。
for rng in ws.merged_cells.ranges:
print(rng.coord, rng.min_row, rng.max_row, rng.min_col, rng.max_col)
B3:B4 3 4 2 2
B5:B6 5 6 2 2
対象セルの行・列がこの範囲に収まっていれば結合範囲内であり、左上セルは (min_row, min_col) です。なお "B4" in rng のように座標文字列での判定もできますが、今回は行番号・列番号で処理しているので数値で比較します。
def get_merged_value(ws, row, col):
"""結合範囲内なら左上セルの値、そうでなければセル自身の値を返す"""
for rng in ws.merged_cells.ranges:
if rng.min_row <= row <= rng.max_row and rng.min_col <= col <= rng.max_col:
return ws.cell(row=rng.min_row, column=rng.min_col).value
return ws.cell(row=row, column=col).value
セルごとに全結合範囲をループするので、結合が非常に多いシートでは、シートごとに {(行, 列): 値} の辞書を一度作っておく方が効率的です。
値を書き込まず、読み取り時に参照する
MergedCell の value は読み取り専用で、代入すると AttributeError になります。unmerge_cells() で結合を解除してから値を埋める方法もありますが、元のファイルの構造を変えることになり、保存先を間違えると元データを壊すリスクもあります。今回の目的は読み取りだけなので、Excelには手を加えず、読み取り時に左上の値を参照する方式にしました。
すべての行に同じ値を出すべきとは限らない
結合セルの値を全行に補完するかどうかは、その列の意味によって変わります。
- 大項目のような分類：各行に補完すると、1行だけで内容が分かり検索しやすい
- 備考のような補足：複数ケースへのまとめ書きを各行に複製すると、ケースごとに個別の備考があるように見え、後で件数を数えるときに重複する
- No列など1件のテストケースを複数行で書いている場合：補完すると同じテストケースが複数件あるように見えてしまう
そこで、補完する列を明示的に指定することにしました。
FILL_COLS = {2} # 結合セルを各行に補完する列（B列＝大項目）
with open("testcases.txt", "w", encoding="utf-8") as f:
for ws in wb.worksheets:
last_row, last_col = get_last_row(ws), get_last_col(ws)
for r in range(HEADER_ROW + 1, last_row + 1):
values = []
for c in range(1, last_col + 1):
if c in FILL_COLS:
v = get_merged_value(ws, r, c)
else:
v = ws.cell(row=r, column=c).value
values.append("" if v is None else str(v))
f.write(f"[{ws.title}] " + " | ".join(values) + "\n")
[ログイン画面] 1 | 正常系 | 正しいID/パスワードでログイン | トップ画面に遷移する | 済
[ログイン画面] 2 | 正常系 | ログイン状態を保持してログイン | 再訪問時に自動ログインされる | 未
[ログイン画面] 3 | 異常系 | 誤ったパスワードでログイン | エラーメッセージを表示する | 未
[ログイン画面] 4 | 異常系 | 未入力でログイン | 入力エラーを表示する | 未
[検索画面] 1 | 正常系 | キーワードで検索 | 一致する結果を一覧表示する | 済 |
[検索画面] 2 | 異常系 | 記号のみで検索 | 入力エラーを表示する | 未 | 仕様確認中のため保留
[検索画面] 3 | 異常系 | 256文字以上で検索 | 入力エラーを表示する | 未 |
空行がなくなり、大項目はすべての行に出力されました。一方、備考は補完対象外なので、元のExcelと同じく最初の行にだけ出ています。
まとめ
-
max_row/max_columnは「セルが存在する範囲」であり、書式だけのセルも含まれる。テストケースの範囲は、キー列や見出し行の値をもとに別途判定する - 結合セルの値は左上のセルにしかなく、それ以外は
MergedCell（値はNone）として読み込まれる -
ws.merged_cells.rangesで結合範囲を調べ、読み取り時に左上セルの値を参照すれば、Excelを変更せずに対応できる - 結合セルを全行に補完するかは列の意味次第。補完する列は明示的に決めておくと安全