Python で複数の Excel ブックを 1 つにまとめる：シート丸ごとと明細行の 2 通り

月次や半期の集計は、最後の一段でつまずきやすい。各拠点がそれぞれの様式で作ったブックが同じフォルダに N 件並んでいて、必要なのは保管も突き合わせもできる 1 つのブック、という状況である。原本の体裁は崩せない——拠点ごとに見出し行があり、所見欄があり、合計行がある。かといってファイルが分かれたままでは横並びの比較ができない。

Spire.XLS for Python では、この作業を 2 つの成果物に分けて片づける。1 つは各原本の体裁をそのまま残したブック、もう 1 つは各シートの明細行を 1 枚に連結した集計シートで、並べ替えやピボットの材料になる。題材は 3 拠点分の半期業績表、いずれも 5 列で構造は同じものを使う。

pip install spire-xls

まず Excel の階層を押さえる

ブックをまたいでデータを移す前に、登場する 3 つのオブジェクトの位置関係を整理しておく。

階層	クラス	本記事で使うもの
ブック	Workbook	LoadFromFile()、SaveToFile()、Worksheets
シート	Worksheet	Name、LastRow、LastColumn、HasMergedCells
セル範囲	CellRange	Copy()、NumberValue、Formula、NumberFormat、Style

ブックをまたぐコピーとは、ある Workbook の Worksheet または CellRange を別の Workbook に移す操作である。Worksheet は特定のブックに属し、シート名はそのブック内で一意でなければならない。だからシートを移すときは名前の重複を自分で処理する必要がある。CellRange は座標のビューにすぎないので、移した先のどの位置に置くかは呼び出し側が決める。

この違いが、後半の 2 通りの使い分けにつながる。

各原本は 1 シート構成で、1 行目が横結合のブロック見出し、2 行目が列見出し、明細行は 3 行目から、最終行がそのシートの合計、営業利益の列は数式という作りにしてある。

2 通りの取り込み方

シート丸ごとの取り込みは Worksheets.AddCopy() を使う。元シートの中身——データ、数式、表示形式、列幅、結合セル、条件付き書式——をまとめて取り込む。原本の見た目を保ったまま 1 冊にまとめたい場合に向く。

明細行の連結は CellRange.Copy() を使う。元シートのデータ部分を矩形のまま、連結先シートの指定位置にコピーする。全件を 1 枚の表にまとめたい場合に向くが、レイアウトは引き継がない。

両者は競合しない。同じ元ファイルから 2 つの成果物を作ればよい。片方は保管と目視確認のために、もう片方は並べ替えと集計のために残す。

シート丸ごと取り込む

新しいブックは空にしてから使う

Workbook() が作るブックには Sheet1、Sheet2、Sheet3 の 3 枚が最初から入っている。そのまま使うと、中身と関係のない空シートが成果物に残る。

from spire.xls import Workbook, WorksheetCopyType, ExcelVersion

merged = Workbook()
merged.Version = ExcelVersion.Version2016
merged.Worksheets.Clear()          # 既定の 3 枚を落とす

Version は保存時の形式の基準で、指定しないときの既定値は Version97to2003。.xlsx として保存されることに変わりはなく、どちらでも開けるが、互換レベルの差が出る。新しい形式の機能を使う可能性があるなら Version2016 を明示しておく。

シートを移して名前を付ける

AddCopy() は取り込んだ新しいシートを返すので、そのまま Name を書き換えられる。名前に「元ファイル名 + 元シート名」を使えば、成果物のどのシートがどこから来たのかが一目で分かる。

import glob
import os

used = set()
for path in sorted(glob.glob("samples/*.xlsx")):
    source = Workbook()
    source.LoadFromFile(path)
    prefix = os.path.splitext(os.path.basename(path))[0]
    for sheet in source.Worksheets:
        if sheet.Name.startswith("Evaluation"):
            continue
        base = "{0}_{1}".format(prefix, sheet.Name)
        name, i = base, 2
        while name in used:        # それでも重複する場合の保険
            name = "{0}_{1}".format(base, i)
            i += 1
        used.add(name)
        copied = merged.Worksheets.AddCopy(sheet, WorksheetCopyType.CopyAll)
        copied.Name = name
    source.Dispose()

merged.ActiveSheetIndex = 0
merged.SaveToFile("output/統合_拠点業績集計.xlsx", ExcelVersion.Version2016)
merged.Dispose()

3 拠点の元ブックはどれもシート名が「業績表」である。実際にできた成果物のシート名は 業績_名古屋_業績表、業績_大阪_業績表、業績_東京_業績表 になった。接頭辞の時点で重複が解消されるので、while はあくまで保険である。接頭辞を付けずに 2 冊目を AddCopy() すると 業績表_1 になり、出所の情報が消える。

WorksheetCopyType.CopyAll はコピー範囲が最も広い指定で、この列挙体の他の値を見ると、ライブラリがシートをどの単位に分解しているかが分かる。

列挙値	コピーされる内容
CopyAll	すべて
CopyCells	セルのデータ
CopyColumnHeight / CopyRowHeight	列幅 / 行の高さ
CopyMerges	結合セル
CopyConditionlFormats	条件付き書式
CopyDataValidations	データの入力規則
CopyShapes	図形
CopyTables / CopyPivotTables	テーブル / ピボットテーブル
CopyPageSetup	ページ設定

「データだけ欲しい、体裁は要らない」場合はこの中から必要な項目を選べばよく、セルを 1 つずつ代入するループに戻る必要はない。

読み戻して確かめる

体裁が本当に移ったかは、目で見るより読み戻して数えたほうが確かである。以下は実行して得た出力そのままである。

workbook = Workbook()
workbook.LoadFromFile("output/統合_拠点業績集計.xlsx")
for sheet in workbook.Worksheets:
    if sheet.Name.startswith("Evaluation"):
        continue
    merged_cells = sheet.MergedCells.Count if sheet.HasMergedCells else 0
    print("{0} {1}行×{2}列 利益列の数式 {3} 合計の数式 {4} 結合 {5} 表示形式 {6}".format(
        sheet.Name, sheet.LastRow, sheet.LastColumn,
        sheet.Range[3, 4].Formula, sheet.Range[sheet.LastRow, 2].Formula,
        merged_cells, sheet.Range[3, 4].NumberFormat))
print("シート総数 {0}、現在のアクティブシート番号 {1}".format(
    workbook.Worksheets.Count, workbook.ActiveSheetIndex))
workbook.Dispose()

業績_名古屋_業績表     5行×5列 利益列の数式 =B3-C3  合計の数式 =SUM(B3:B4)  結合 1  表示形式 #,##0
業績_大阪_業績表       5行×5列 利益列の数式 =B3-C3  合計の数式 =SUM(B3:B4)  結合 1  表示形式 #,##0
業績_東京_業績表       6行×5列 利益列の数式 =B3-C3  合計の数式 =SUM(B3:B5)  結合 1  表示形式 #,##0
シート総数 4、現在のアクティブシート番号 3

Range[3, 4].Formula に計算結果ではなく数式そのものが入っているので、数式は数式として移っている。結合 1 は 1 行目の横結合、#,##0 は明細部分の表示形式である。この 3 つは、手で入力し直すと真っ先に落ちる要素でもある。

シート総数が 3 ではなく 4 なのは、体験版が保存時に末尾へ Evaluation Warning シートを追加するためで、ActiveSheetIndex もその最後のシートを指す。名前の接頭辞で読み飛ばせば、中身は 3 シートである。

LastRow と LastColumn は実際に内容のある範囲の境界を返す。東京のシートは明細が 1 行多いので 6 行になる。この差は明細行の連結で効いてくる。

統合後のブックを開くと次のようになる。3 つの原本それぞれの見出し・列幅・所見がそのまま残り、シート見出しが「元ファイル名_元シート名」になっている。図の中で黄色く塗られているのは数式セルである。体験版では保存時に末尾へ Evaluation Warning シートが追加され、開いた時点ではそのシートが選択された状態になる。

明細行を連結する

シート丸ごとの取り込みでは、独立した N 枚のシートができるだけである。横並びで比較するには、明細行を 1 枚に集める。

FIRST_DATA_ROW = 3          # 元シート：1 行目が見出し、2 行目が列見出し
LAST_COL = 5
SOURCE_COL = 6              # 集計シートは右に 1 列増やしてデータ元を書く

result = Workbook()
result.Version = ExcelVersion.Version2016
result.Worksheets.Clear()
dst = result.Worksheets.Add("業績表")

# 集計シート自身の注記が 1 行目、列見出しが 2 行目、データは 3 行目から
cursor = 3
for path in sorted(glob.glob("samples/*.xlsx")):
    source = Workbook()
    source.LoadFromFile(path)
    sheet = source.Worksheets[0]
    data_end = sheet.LastRow - 1               # 最終行は合計行なので移さない
    data_rows = data_end - FIRST_DATA_ROW + 1
    block = sheet.Range[FIRST_DATA_ROW, 1, data_end, LAST_COL]
    block.Copy(dst.Range[cursor, 1], True, True)   # 第 1 引数で書式、第 2 引数で参照の更新
    for offset in range(data_rows):
        row = cursor + offset
        dst.Range[row, 4].NumberValue = float(
            sheet.Range[FIRST_DATA_ROW + offset, 2].NumberValue
            - sheet.Range[FIRST_DATA_ROW + offset, 3].NumberValue)
        dst.Range[row, SOURCE_COL].Text = os.path.basename(path)
    cursor += data_rows
    source.Dispose()

total_row = cursor
dst.Range[total_row, 1].Text = "合計"
for col in (2, 3, 4):
    letter = chr(ord("A") + col - 1)
    dst.Range[total_row, col].Formula = "=SUM({0}3:{0}{1})".format(letter, total_row - 1)
result.SaveToFile("output/集計_明細連結.xlsx", ExcelVersion.Version2016)
result.Dispose()

列見出しと注記行のフォント・背景色はこの中には出てこない。元シートの見出しと同じ書き方で組んである。

ポイントは 3 つある。

開始行の計算。 元シートの明細は 3 行目から始まり、最終行の合計行は移さない。新しいシートでは意味を持たないうえ、あとで合計を取るときに二重計上になる。data_end = sheet.LastRow - 1 とすると、名古屋が 2 行、大阪が 2 行、東京が 3 行で、明細は合計 7 行。集計シートの 3 行目から 9 行目に入る。

利益列は計算し直す。 元シートの 4 列目は =B3-C3 のような数式である。シート丸ごとの取り込みなら数式のまま持ち出せるが、明細行の連結ではそうはいかない。行番号が変わるので、=B3-C3 が集計シートの 8 行目に来ると参照先がずれる。ここでは NumberValue で計算済みの数値を書き込んでいる。集計シートのこの列は純粋なデータなので、どのソフトで開いても結果が見える。

データ元の列を足す。 集計シートは元シートより 1 列多く、データ元 に元ファイル名が入る。この種の数値は各拠点が個別に申告してくるもので、問い合わせが来たときにまず聞かれるのは「この数字はどこから来たのか」である。出所を表に書いておくほうが、あとから台帳をめくるより速い。

読み戻した成果物は 10 行 × 6 列になる。

本表は拠点別の業績 3 件を統合したもので、数値は原本どおり
拠点        売上高    販売費    営業利益   所見       データ元
名古屋支店   73500    54100     19400    概ね達成   業績_名古屋.xlsx
静岡営業所   32600    25100      7500    要注視     業績_名古屋.xlsx
大阪支店     97400    68800     28600    達成       業績_大阪.xlsx
神戸営業所  112600    79200     33400    達成       業績_大阪.xlsx
東京本社    124500    73000     51500    達成       業績_東京.xlsx
横浜支店     83200    56000     27200    概ね達成   業績_東京.xlsx
埼玉営業所   61800    41500     20300    概ね達成   業績_東京.xlsx
合計

合計は 10 行目で、数式は =SUM(B3:B9)。データ範囲は 3 行目から 9 行目の 7 行で、取り込んだ行数と一致する。

セルに書くときの 2 つの指定

サンプルの中に、直感に反するが避けられない書き方が 2 つある。

Range[i, j].Value は文字列しか受け付けず、数値を入れると型エラーになる。

TypeError: 'int' object cannot be interpreted as ctypes.c_wchar_p

数値は必ず NumberValue を通す。

sheet.Range[row, 2].NumberValue = float(revenue)

もう 1 つは表示形式である。#,##0 はセルごとではなく範囲に対して一度に設定する。

sheet.Range[first_data_row, 2, total_row, 4].NumberFormat = "#,##0"

Range[r1, c1, r2, c2] の 4 引数版は矩形の範囲を返す。Merge()、NumberFormat、Style はいずれも範囲全体に効くので、セル単位で回すより速く、設定漏れも出にくい。

この方法でできること、できないこと

• 数式はシート丸ごとの取り込みでそのまま移るが、統合後のブックが再計算してくれるわけではない。成果物の数式セルには数式だけがあり計算結果はキャッシュされていない。Excel で開けば自動で計算されるが、再計算しないパーサーが下流にある場合は空値になる。その場合は連結の段階で数値を書き込むほうが安全である。
• シート名は、シート丸ごとの取り込みで唯一自分で面倒を見る部分である。シート名はブック内で一意でなければならず、元ファイル名が同じでも元シート名が同じでも衝突する。接頭辞と連番の 2 段構えでこれを吸収している。
• ピボットテーブルやグラフのようにデータソース参照を持つオブジェクトは、CopyAll でオブジェクトごと移る。更新は開いたソフト側の処理になる。
• 明細行の連結は、元シートの列構成が揃っていることを前提にしている。実務用のツールにするなら、先に検証を 1 段入れておきたい。1 冊目の列見出しを読み、各ファイルの列名と列数を突き合わせ、食い違ったファイルを名指しで弾く。連結し終えてからずれを探すより速い。
• 体験版は保存のたびに Evaluation Warning というシートをブックの末尾に追加する。スクリプト側ではシート名の接頭辞で読み飛ばしている。正規ライセンスでは現れない。
• シートの並び順は読み込み順そのままで、ファイル名の順に並ぶだけで提出順とは関係がない。順序を固定したい場合はファイルのリストを明示的に書き、glob の返す順序に任せない。

どちらを使うか

シート丸ごとの取り込みは、保管・監査・1 件ずつの確認に向く。原本の体裁も所見も見出しもそのまま残るので、誰が何を出したのかを開いてすぐ確認できる。明細行の連結は計算に向く。並べ替え、抽出、ピボット、あるいは後続処理への入力として使う。どちらか一方に決める必要はなく、同じ元ファイルから 2 つ作って、1 つは保管用、1 つは計算用にすればよい。

成果物のシート数、各ファイルから取り込んだ行数、合計行が何行目に来たかはスクリプトがコンソールに出す。まずそこを見る。件数がさらに増えるなら、元ファイルのフォルダを引数にして、2 つの成果物をそのまま定期実行に載せる。命名規則も列構成の検証もコード側にあるので、拠点が 1 つ増えてもスクリプトを書き換える必要はない。