PythonのOpenPyXLでExcel操る7つの技【読み書き・装飾・グラフ】

はじめに

データ分析や機械学習の分野で何かと便利なPython言語。

エンジニアは何かのデータを加工する時にもプログラミングを行いますが、
実際に結果を誰かに見せる時にはExcelなどを使って整形・グラフ化し、顧客や上司に報告します。

SANACHAN

Pythonでデータ加工するついでに、レポート用の Excel を作成できたら便利ですね。

本記事では、Python の OpenPyXL を使って Excel を操作する基礎的な7つの技を説明していきます。

こんな方におすすめ

Pythonを使ってExcelを操作したいけど、やり方が分からない
PythonのOpenPyXLを使ってどんなことが出来るのかを知りたい

語句説明

OpenPyXL とは？

OpenPyXL の公式ページでは、以下のように記載されています。

openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.
It was born from lack of existing library to read/write natively from Python the Office Open XML format.
All kudos to the PHPExcel team as openpyxl was initially based on PHPExcel.

1行目の英文がそのままの説明で、Pythonで Excel 2010（.xlsx）の読み書きをするためのライブラリです。

SANACHAN

Excel 作業の自動化や、データ分析などをPythonで行った結果を Excel に出力することなどが容易にできるライブラリです。

準備：開発環境とデータ

Python開発環境

今回はWindows上で、VS Code を使ったPython実行環境を使っています。ターミナル（プロンプト）から Python を実行できる環境がある方は、それでも問題ありません。

OS	Windows10 Pro [20H2]
エディター・ターミナル	Visual Studio Code v1.52.1
Python	3.7.4
numpy	1.19.4
pandas	1.2.0
openpyxl	3.0.5

SANACHAN

Pythonを実行した際に「ModuleNotFoundError: No module named 'openpyxl'」というエラーが出た場合は、「pip install openpyxl」を実行してインストールしましょう。

解析用データ

「国税調査結果 - 男女別人口」からデータをダウンロードして加工しています。
必要な方は、加工後のデータを以下からダウンロードください。

c02.csv (2000年代の近畿地方の実行推移データ)

本記事では、解析用データと実行するPythonスクリプトを以下のような配置で説明しています。
必要に応じて、適宜読み替えてください。

 ファイルの配置
(任意のディレクトリ)/
|- c02.csv
|- (実行するPythonスクリプト)

技①：Excelの書き込み

まずは、Pythonのopenpyxlを使ってExcelを出力できるのかを確認しましょう。

Pythonスクリプト

import openpyxl
wb = openpyxl.Workbook()
ws = wb['Sheet']
ws.cell(1,1).value = '私はSANACHANです。'
wb.save('test.xlsx')
wb.close()

import openpyxl

wb = openpyxl.Workbook()

ws = wb['Sheet']

ws.cell(1,1).value = '私はSANACHANです。'

wb.save('test.xlsx')

wb.close()

Pythonスクリプトの解説

1行目は、Pythonライブラリの openpyxl を読み込んでいます。
2行目は、openpyxl の新規のワークブックを作成する Workbook() を使用し、
変数 wb へ格納しています。
3行目は、シートを作成し、変数 ws へ格納しています。
ハッシュテーブルに任意の文字でアクセスするだけで空のシートが作成されます。
4行目は、ワークシートのセル（1, 1）、つまりA1セルに文字列をセットしています。
セルを openpyxl のcell()で指定し、値の設定は、value プロパティを使って行います。
5行目は、openpyxl のsave() を使って、作成したワークブックを指定したファイル名で保存しています。
6行目は、openpyxl のclose() を使って、ワークブックを閉じています。

SANACHAN

openpyxl のインターフェイスは、Excel VBA とよく似ていますね。

Pythonスクリプト実行結果

Excelなので画像の貼り付けになります。
Excelファイルが作成され、Sheet の A1セルに指定文字「私はSANACHANです。」が入っていますね。

write_test.pyで生成されたExcelファイル

技②：Excelの読み込み

次は、Excelファイルを読み込んで、セルの内容を表示する例です。

Pythonスクリプト

import openpyxl
wb = openpyxl.load_workbook('test.xlsx', read_only=True)
ws = wb['Sheet']
print(ws.cell(1, 1).value)
wb.close()

import openpyxl

wb = openpyxl.load_workbook('test.xlsx', read_only=True)

ws = wb['Sheet']

print(ws.cell(1, 1).value)

wb.close()

Pythonスクリプトの解説

1行目は、Pythonライブラリの openpyxl を読み込んでいます。
2行目は、openpyxl の load_workbook() を使って Excel ファイルを読み込みます。
今回は書き込みを行わないため、read_only のオプションを付与。
3行目は、Excel シート「Sheet」のオブジェクトを ws に格納しています。
4行目は、openpyxl の cell() を使って、(1, 1) セル、つまり A1 セルの値 value を表示しています。
5行目は、使用したワークブックを close() で閉じています。

Pythonスクリプト実行結果

$ python read_test.py
私はSANACHANです。

$ python read_test.py

私はSANACHANです。

SANACHAN

日本語も問題なく読み出せていますね。

技③：データ分析した結果をExcelに出力

Pythonから openpyxl を使って Excel ファイルに読み書きできることが分かりました。
せっかくですので、以下の基礎⑤で集計したデータを使ってExcelに出力してみましょう。

【データ分析基礎5選】PythonのPandasで解析してみる

Pythonスクリプト

import openpyxl
import pandas as pd
# 出力データの作成
c02 = pd.read_csv('c02.csv')
c02['total'] = c02['man'] + c02['woman']
data = pd.pivot_table(c02, index='prefectures_name', columns='year', values = 'total', aggfunc='sum')
# 出力ワークブックの準備
wb = openpyxl.Workbook()
ws = wb['Sheet']
# 行名の出力
for i in range(len(data.index)):
    ws.cell(1+1+i, 1).value = data.index[i]
# 列名の出力
for j in range(len(data.columns)):
    ws.cell(1, 1+1+j).value = data.columns[j]
# 表の値出力
for i in range(len(data.index)):
    for j in range(len(data.columns)):
        value = data.at[data.index[i], data.columns[j]]
        ws.cell(1+1+i, 1+1+j).value = value
# 保存して終了
wb.save('test.xlsx')
wb.close()
print(data)

import openpyxl

import pandas as pd

# 出力データの作成

c02 = pd.read_csv('c02.csv')

c02['total'] = c02['man'] + c02['woman']

data = pd.pivot_table(c02, index='prefectures_name', columns='year', values = 'total', aggfunc='sum')

# 出力ワークブックの準備

wb = openpyxl.Workbook()

ws = wb['Sheet']

# 行名の出力

for i in range(len(data.index)):

ws.cell(1+1+i, 1).value = data.index[i]

# 列名の出力

for j in range(len(data.columns)):

ws.cell(1, 1+1+j).value = data.columns[j]

# 表の値出力

for i in range(len(data.index)):

for j in range(len(data.columns)):

value = data.at[data.index[i], data.columns[j]]

ws.cell(1+1+i, 1+1+j).value = value

# 保存して終了

wb.save('test.xlsx')

wb.close()

print(data)

Pythonスクリプトの解説

1～9行目は、これまでに出てきた内容ですので省略します。
10～15行目で、出力するデータの行名、列名を出力しています。
行名は index、列名は columns にリストで pandas データフレームに保存されています。
16～20行目で、集計したデータを出力しています。
pandas データフレームの at() を使って値を取得し、セルに書き込んでいます。
21～23行目は、Excel ワークブックの終了処理です。
出力された Excel ファイルの内容と、集計したデータを比較するために画面に表示します。

ポイント

Python 配列の要素番号は 0 から始まります。対して、Excel のセル番号は 1 から始まります。
また、Excel の A1 セルを空白にするため、(1+1)を加算して cell() で場所を指定します。

Pythonスクリプトの実行結果

$ python output_data.py
year                 2000     2005     2010     2015
prefectures_name
三重県               1857339  1866963  1854724  1815865
京都府               2644391  2647660  2636092  2610353
兵庫県               5550574  5590601  5588133  5534800
和歌山県             1069912  1035969  1002198   963579
大阪府               8805081  8817166  8865245  8839469
奈良県               1442795  1421310  1400728  1364316
滋賀県               1342832  1380361  1410777  1412916

$ python output_data.py

year 2000 2005 2010 2015

prefectures_name

三重県 1857339 1866963 1854724 1815865

京都府 2644391 2647660 2636092 2610353

兵庫県 5550574 5590601 5588133 5534800

和歌山県 1069912 1035969 1002198 963579

大阪府 8805081 8817166 8865245 8839469

奈良県 1442795 1421310 1400728 1364316

滋賀県 1342832 1380361 1410777 1412916

output_data.pyが生成したExcelの内容

SANACHAN

実質10行に満たないPythonのソースコードで、集計したデータをExcelに出力できました。

技④：Excel表の罫線を設定

さて、ここからは少しExcelのスタイル・整形に関する処理をみていきます。
Python の openpyxl で表計算ソフト Excel の罫線を引く方法です。

Pythonスクリプト

import openpyxl
# スタイルに関するクラスをインポート
from openpyxl.styles import PatternFill, Border, Side, Font
wb = openpyxl.load_workbook('test.xlsx')
ws = wb['Sheet']
# スタイルに関するオブジェクトを生成
side = Side(style='thin', color='FF0000')
border = Border(top=side, bottom=side, left=side, right=side)
# スタイルの反映
for row in ws:
    for cell in row:
        if ws[cell.coordinate].value:
            ws[cell.coordinate].border = border
# 上書き保存
wb.save('test.xlsx')
wb.close()

import openpyxl

# スタイルに関するクラスをインポート

from openpyxl.styles import PatternFill, Border, Side, Font

wb = openpyxl.load_workbook('test.xlsx')

ws = wb['Sheet']

# スタイルに関するオブジェクトを生成

side = Side(style='thin', color='FF0000')

border = Border(top=side, bottom=side, left=side, right=side)

# スタイルの反映

for row in ws:

for cell in row:

if ws[cell.coordinate].value:

ws[cell.coordinate].border = border

# 上書き保存

wb.save('test.xlsx')

wb.close()

Pythonスクリプトの解説

1～5行目は、先に解説した内容ですので割愛します。
3行目では、スタイル設定に必要なクラスを openpyxl からインポートしています。
技③で生成したExcelファイルを読み込みます。
6~8行目で、罫線の幅と色、そして上下左右のどこに罫線を引くかを定義しています。
Side（罫線の幅、色）と Border（罫線を引く）クラスを使ってオブジェクトを生成します。
9～12行目で、値が設定されている各セルに罫線を引いています。
今回は、イテレーティブな記述をして、coordicate を使ってセルを指定しています。
13～15行目は、Excel の上書き保存、終了処理です。

Pythonスクリプトの実行結果

Excelなので画像の貼り付けになります。
値の入っているセルの罫線が Side で指定した赤色（RGB=FF0000）で反映されていますね。

set_border.pyの実行結果

技⑤：セルの文字をボールド・色を設定

Excelを使用しているときも、表の行列名を太字にしたり文字色を変えて目立たせます。
Python の openpyxl を使っても、同じことができます。

Pythonスクリプト

import openpyxl
from openpyxl.styles import PatternFill, Border, Side, Font
wb = openpyxl.load_workbook('test.xlsx')
ws = wb['Sheet']

for row in ws:
    for cell in row:
        if ws[cell.coordinate].value:
            ws[cell.coordinate].font = Font(bold=True, color='FF0000')

wb.save('test.xlsx')
wb.close()

import openpyxl

from openpyxl.styles import PatternFill, Border, Side, Font

wb = openpyxl.load_workbook('test.xlsx')

ws = wb['Sheet']

for row in ws:

for cell in row:

if ws[cell.coordinate].value:

ws[cell.coordinate].font = Font(bold=True, color='FF0000')

wb.save('test.xlsx')

wb.close()

Pythonのスクリプト解説

このスクリプトの肝は、9行目です。
Font クラスを使って、文字の装飾を指定します。
引数の bold=True で太字、color='RGB' で色のRGB値を指定します。

Pythonスクリプトの実行結果

Excelなので画像の貼り付けになります。
値の入っているセルの文字装飾が Font で指定した太字、赤色（RGB=FF0000）が反映されていますね。

set_font_style.pyの実行結果

SANACHAN

ちょっと赤々して目が痛いですが、簡単に文字装飾を設定できました。

技⑥：セルに数式を設定

Excel でよく使用する数式。もちろん、openpyxl でもセルに数式を設定することが可能です。
Excel と違って、入力補完や関数の説明は出ませんので、関数を事前に調べておく必要はあります。

Pythonスクリプト

import openpyxl
wb = openpyxl.load_workbook('test.xlsx')
ws = wb['Sheet']

cell = ws.cell(9, 2).value = f'=SUM(B2:B{ws.max_row})'

wb.save('test.xlsx')
wb.close()

import openpyxl

wb = openpyxl.load_workbook('test.xlsx')

ws = wb['Sheet']

cell = ws.cell(9, 2).value = f'=SUM(B2:B{ws.max_row})'

wb.save('test.xlsx')

wb.close()

Pythonスクリプトの解説

今回の肝は5行目です。近畿における2000年の人口合計を計算します。
設定方法は簡単で、値を設定してい value に数式の文字列を設定します。

SANACHAN

今回は、max_row を使って最終行を取得する小技を使っています。

Pythonスクリプトの実行結果

Excelなので画像の貼り付けになります。
指定したセルに数式が入っており、2000年の総人口が値として見えていますね。

use_function.pyの実行結果

技⑦：折れ線グラフの作成と挿入

最後はExcelにグラフの挿入する方法です。
数値を表形式で入力・集計し、最後にグラフにして可視化する。Excelで最も一般的な使い方ですね。

Pythonスクリプト

import openpyxl
from openpyxl.chart import Reference, BarChart, PieChart, LineChart, ScatterChart, Series
wb = openpyxl.load_workbook('test.xlsx')
ws = wb['Sheet']

ref = Reference(ws, min_col=2, min_row=6, max_col=5, max_row=6)
series = Series(ref, title='人口推移')

chart = LineChart()
chart.title = '大阪の人口推移'
chart.x_axis.title = 'データ数'
chart.y_axis.title = '人口'
chart.height = 10
chart.width = 20
chart.series.append(series)
ws.add_chart(chart, 'D10')

wb.save('test.xlsx')
wb.close()

import openpyxl

from openpyxl.chart import Reference, BarChart, PieChart, LineChart, ScatterChart, Series

wb = openpyxl.load_workbook('test.xlsx')

ws = wb['Sheet']

ref = Reference(ws, min_col=2, min_row=6, max_col=5, max_row=6)

series = Series(ref, title='人口推移')

chart = LineChart()

chart.title = '大阪の人口推移'

chart.x_axis.title = 'データ数'

chart.y_axis.title = '人口'

chart.height = 10

chart.width = 20

chart.series.append(series)

ws.add_chart(chart, 'D10')

wb.save('test.xlsx')

wb.close()

Pythonスクリプトの解説

2行目で、グラフを挿入するためのクラスを openpyxl からインポートしています。
6～7行目で、折れ線グラフにするデータの範囲、折れ線のタイトルを指定しています。
9～16行目で、openpyxl の LineChart() を使って折れ線グラフの設定を行っています。
グラフタイトル、X軸・Y軸のタイトル、高さ・幅、グラフを挿入するセル番号などです。

SANACHAN

折れ線グラフの他に、棒グラフ（BarChart）などもあります。

Pythonスクリプトの実行結果

Excelなので画像の貼り付けになります。
設定したデータ範囲が選択され、D10セルに折れ線グラフが挿入されていることが分かります。

line_chart.pyの実行結果

おわりに

いかがでしたでしょうか。

Python と Excel を使う作業や業務の方は、openpyxl を使って Excel を使用する作業を自動化し、
生産性を上げる工夫ができそうです。

以上、「PythonのOpenPyXLでExcel操る7つの技【グラフ出力まで】」でした。