Openpyxlチートシート!PythonでExcel業務を自動化する

「日本人は方眼紙が好きだね」と揶揄されるように、私達の身の回りの仕事は現在のところExcelに囲まれています。

その是非は置いておいても、Excelを使った定型業務を自動化して、クリエイティブな作業に時間を使うことには一定の価値があります。

https://datascientist-toolbox.com/wp-content/uploads/2019/09/man.png
りーぐる

私のようなVBA嫌いはこのチートシートを活用してね!

Excel業務を自動化する3つの方法

この記事はopenpyxlのチートシートですが、PythonによるExcel業務自動化の全体について軽く触れておきたいと思います。

見た目問題

自動化の際に、必ず問題になるのは「見た目問題」です。

セルの色・フォント・文字の折り返し・罫線・結合単位・行幅/列幅などを自動化で扱うことは、想像以上にコード量を増やしメインロジックと関係ないコードが紛れ込みがちになります。

この問題に対処するために自分がよく使うアプローチは、出力したいExcelのテンプレートシートを予め作っておくことです。

この方法の問題点は、テンプレートの必要が生じた時にPythonコードの出力位置のコードも連動して変更する必要があることです。

しかしその問題に関しては、出力ロジックをまとめるなどコーディングの際の工夫である程度は対処できます。

それによって「見た目問題」の大部分を回避できることにメリットがあります。

https://datascientist-toolbox.com/wp-content/uploads/2019/09/man.png
りーぐる

必ずしも自動化のアウトプットがExcelである必要はないことも頭にいれておこう!

pandas

入力データが比較的きれいなテーブルの形をしているのであれば、pandasを使うのが最も理想的かもしれません。

本記事はpandasの記事ではないので、詳しい処理はPandasのチートシートを作成する機会にしてpandasを使えば「何が簡単にできるか」の一例をここでは挙げます。

pandasでできること例

  • 幅広い集計作業
  • 別テーブルとの結合
  • 汚れたデータに対する前処理
  • フィルタリング

そしてこれらが多くの場合、自作のコードよりもきわめて高速に動作します。

matplotlib、seabornなどの描画ライブラリと組み合わせて、Excelより美しい見た目のグラフを書くこともできます。

但し、pandasのExcel出力(to_excel)はテーブル形式での出力となるため、柔軟なフォーマットを扱うために出力の際に後述のopenpyxlやxlwingsを使う場合があります。

openpyxl

openpyxlを使えば、任意のセル範囲の情報をPythonに取り込んで適切なデータ構造で持つことができます。

入力がテーブルの形状でなかったり、複数のシートにまたがっていたり、1つのシートに大量のテーブルが含まれているような場合はopenpyxlの出番かもしれません。

openpyxlに関しては、チートシートで機能を紹介するのでここでは割愛します。

xlwings

xlwingsは、簡潔に言うとPythonでExcelマクロを実装しているような使用感です。

openpyxlよりも動作が遅く、可能な処理もAPIもVBAと酷似しているため、既にVBAに慣れ親しんでいる人は新たに使うメリットを感じないかもしれません。

Pythonのデータ構造を使って、よりExcelライクな処理をしたいといった場合に候補となるでしょう。

Qiitaにopenpyxlとxlwingsの分かりやすい比較をしてくださっているポストがあるので、どちらを使うかの判断はそちらが参考になると思います。

参考 openpyxl と xlwings の比較Qiita

openpyxlチートシート

ここからは、openpyxlの基本的な使用方法についてまとめます。詳細については公式ドキュメントが参考になります。

参考 openpyxl公式ドキュメントopenpyxl doc
https://datascientist-toolbox.com/wp-content/uploads/2019/09/man.png
りーぐる

目次を活用して、行いたい処理を調べよう!

パッケージのインストール

$ pip install openpyxl

ワークブック操作

ワークブックの新規作成・読み込み

ワークブックの新規作成

import openpyxl

wb = openpyxl.Workbook()

既存のワークブックの読み込み

wb = openpyxl.load_workbook("<ファイルパス>")

ワークブックの保存・クローズ

ワークブックの保存

wb.save("<ファイルパス>")

ワークブックのクローズ

wb.close()

ワークシート操作

ワークシートの新規作成

新規作成するワークシートの挿入位置を、引数で指定できる

ws = wb.create_sheet() # 左端にシートを挿入
ws = wb.create_sheet(0) # 右端にシートを挿入

シート名を指定して新規作成

ws = wb.create_sheet(title="<シート名>")

作成後にリネームを行うことも可能

ws.title = "<シート名>"

ワークシートの選択

アクティブシートの選択

ws = wb.active

シート名を指定して選択

ws = wb.get_sheet_by_name("<シート名>")

シート名の一覧を取得

シート名の一覧がリストで取得できます

sheetNames = wb.get_sheet_names()

ワークシートのコピー

openpyxlでは、同一ブックにのみワークシートのみコピーが可能です。

どうしてもブックをまたいだコピーが必要であれば、xlwings等の手段を使う必要があります。

ws = wb.get_sheet_by_name("<コピーシート名>")
wb.copy_worksheet(ws) # 末尾にシートをコピー

ワークシートの削除

remove()メソッドには、worksheetオブジェクトを渡します。1ページしか存在しないワークシートを削除した場合、実行時エラーが出るので注意が必要です。

wb.remove(ws)

セル値の取得・代入

openpyxlでは、セルの基本操作もきわめて直感的に行なうことができます。

単一セル値の取得

“D8″のようなセル番地と、8行4列目のように行番号・列番号でアクセスする方法の2通りが用意されています。

val = ws["D8"].value
val = ws.cell(row=8, column=4).value

単一セルへの代入

代入も同様に行うことができます。

ws["D8"].value = val
ws.cell(row=8, column=4).value = val

セルの走査方法

Excel自動化を行う際は、範囲に含まれるセルをループで走査して処理することがほとんどです。openpyxlには様々な走査の方法が容易されています。

行によるイテレーション

worksheetオブジェクトのiter_rowsメソッドを使用すると、指定した範囲内で行によるイテレーションを行うことができます。

for row in ws.iter_rows(min_row=1, min_col=1, max_row=8, max_col=4):
    for cell in row:
        cell.value = i  # 走査順に値を代入
        i += 1

ws.rowsを指定すれば、空でないセルを含む最大の行全体でのイテレーションとります。

for row in rows:
    for cell in row:
        print(cell.value)
        print()

列によるイテレーション

worksheetオブジェクトのiter_colsメソッドを使用すると、指定した範囲内で列によるイテレーションを行うことができます。

for col in ws.iter_cols(min_row=1, min_col=1, max_row=8, max_col=4):
    for cell in col:
        cell.value = i  # 走査順に値を代入
        i += 1

ws.columnsを指定すれば、空でないセルを含む列全体でのイテレーションとります。colsとcolumnsが混じってて名前を間違えやすいので注意!

for col in columns:
    for cell in col:
        print(cell.value)
        print()

空でないセルの最大行・最大列の取得

rows、columnsで得られたイテレータを使うと、思わぬ範囲までループしているといったことがあるかもしれません。max_row、max_columnを使えば、空でないセルの最大行・最大列が取得できます。

ws.max_row
ws.max_column

単一行・列の取得

iter_rowsやiter_columnsを使っても、行うことができますが単一行・列を扱いたい場合はより直感的な方法があります。

ws.row_dimensions[8] # 行の取得
ws.column_dimensions['D'] # 列の取得

rowsやcolumnsの発展的な使い方として、外側のイテレータをlistに変換すれば単一行のイテレータが得られます。ただしlistのインデックスは0からはじまることに注意が必要です。

list(ws.rows)[7]
list(ws.columns)[3]

セルの書式設定

セルの書式設定も大部分はopenpyxlで調整が可能です。ただしやりすぎはコーディングに時間がかかる上に、出力形式変更時の修正コストが上がるので注意しよう。

フォントの設定

書式設定の機能はopenpyxl.stylesモジュールに含まれます。

フォントの設定ではopenpyxl.styles.Fontをインポートします。

from openpyxl.styles import Font

# メイリオ、文字色赤、下線付きを指定
font = Font(name='メイリオ',
            size=11,
            bold=False,
            italic=False,
            vertAlign=None,
            underline='single',
            strike=False,
            color='FF0000')

cell.font = font

文字フォント名、文字サイズ、文字色など様々な設定が可能です。文字色だけを変更したいときはもちろん、font = Font(color=’FF0000′)のように他のオプションは省略可能です。

塗りつぶしパターンの設定

塗りつぶしパターンの設定には、openpyxl.styles.PatternFillをインポートします。

from openpyxl.styles import PatternFill

fill = PatternFill(patternType='solid', fgColor='FFFF00') # 黄色でセルを塗りつぶし

cell.fill = fill

罫線の設定

罫線の設定には、openpyxl.styles.Borderおよびopenpyxl.styles.Sideをインポートします。

from openpyxl.styles import Border, Side

border = Border(top=Side(style='thin', color='000000'), 
                bottom=Side(style='medium', color='000000'), 
                left=Side(style='dashed', color='000000'),
                right=Side(style='dotted', color='000000')

cell.border = border

アラインメントの設定

アラインメントの設定には、openpyxl.styles.Alignmentをインポートします。

from openpyxl.styles import Alignment

alignment = Alignment(wrap_text=True,  # セル内文章の折り返しを行う 
                      horizontal='general',
                      vertical='bottom')

cell.alignment = alignment

リンクの設定

cell.hyperlink = "<ハイパーリンクの文字列>"

その他の機能

行幅・列幅の変更

row_dimensionsおよびcolumn_dimensionsにより、幅を変更する対象の行・列を取得し、それぞれheightプロパティ、widthプロパティに値を代入します。

ws.row_dimensions[8].height = 20
ws.column_dimensions["D"].width = 30

ウィンドウの固定

worksheetオブジェクトのfreeze_panes属性を使うことにより、「ウィンドウ枠の固定」を実現できます。

ws.freeze_panes = 'D8'

上記であれば、行は7行目まで列は3(C)列目までが固定されます。片方しか固定したくない場合は、”A8″や”D1″のように指定します。

openpyxlの課題

このようにopenpyxlを使うと、基本的なユースケースは満たせるのですがopenpyxlパッケージは1つ大きな問題点を抱えています。

それは結合セルにまつわる不具合です。最後にこの不具合の内容と応急処置的な対策について触れておきます。

結合セルの不具合

現時点のopenpyxlでは、「結合セルの罫線が表示されない」という不具合があります。

このためにわざわざセルの結合と罫線の再描画を行うのは非効率的なので、極力はセルの結合を避けるのが得策です。

また、より直接的にExcelを操作するxlwingsでは問題を回避できるため、出力ロジックを分離して出力部分をxlwingsで実装するというのも手です。

最後に、Excel自動化の際には下記のようなことが障壁になることがあります。

Excel自動化の課題

  • セル内情報に一貫性がなくダーティーなデータであること
  • 必要以上に見た目にこだわる必要があること

特に1番目について、プログラミングはデータ構造が非常に重要です。脆いデータ構造の上に自動化のコードを書いても、何度もアプリケーション側を書き直さなくてはなりません。

何がやりたいかが見えてきたら、「データをどのようにして持つか」を強く意識して書くと自動化の恩恵に近づけるはずです。

https://datascientist-toolbox.com/wp-content/uploads/2019/09/man.png
りーぐる

他にも必要な機能があったら、随時チートシートに追加していきます!

おすすめ記事

Tkinterを使ってファイル選択GUIを構築しよう