Zenn
⌛

製造業のDXで良く使うopenpyxl入門

2023/06/07に公開
Python
tech
自動化
openpyxl
tech

概要

  • openpyxlを用いてExcelの転記を行い転記作業の自動化を行うことは良くある。そこでopenpyxlの基本操作をまとめていく。

対象者

  • Windowsユーザー
  • Pythonユーザー
  • 業務や日常生活の中でExcelを自動化したい人

目次

  1. openpyxlとは
  2. インストール方法
  3. ワークブックの作成と保存
  4. データの読み書き（基礎編）
  5. データの読み書き（応用編）
  6. Excelの装飾
  7. 便利機能
  8. 参考文献

1. openpyxlとは

openpyxlは、Pythonを用いてExcel 2010 xlsx/xlsm/xltx/xltmファイルを操作するためのライブラリである。このライブラリを用いることで、PythonのプログラムからExcelファイルに対する操作が可能となり、データ分析、変換、自動化などが容易に実施できる。
　openpyxlはExcelがインストールされていない環境でも機能し、多様なプラットフォームで利用できるという利点がある。これにより、サーバー上でExcelデータを取り扱うスクリプトを作成する場面でも活用できる。
　しかし、openpyxlが対応しているのは.xlsx形式のExcelファイルのみであり、古い.xls形式のファイルには対応していない。.xlsファイルを操作したい場合は、他のライブラリを使用する必要がある。

2. インストール方法

扱う拡張子が（.xlsx, .xlsm, .xltx, .xltm）のファイルを扱うだけならば以下のコマンドを実行する。

cmd or terminal
py -m pip install openpyxl

.xlsファイルを扱いたい場合は、以下の２つのコマンドを実行する。xlrdで読み込みができ、xlwtで書き込みができる。rdはreadの略で、wtはwriteの略である。

cmd or terminal
py -m pip install xlrd
cmd or terminal
py -m pip install xlwt

3. ワークブックの作成と保存

まず、Excelファイルを作成する前にopenpyxlで扱うオブジェクトがExcelファイルのどの部分に対応しているのか（どの部分にアクセスするのか）について整理する。

◯Excelのファイルとopenpyxlで生成されるオブジェクトには以下の表のような関連がある。表で整理すると以下のようになる。

Excel openpyxl
ブック Workbookオブジェクト
ワークシート Worksheetオブジェクト
セル Cellオブジェクト

◯イメージで整理すると以下のようになる。

Excelのbook　⇛　Workbookオブジェクト

Excelのsheet1等　⇛　Worksheetオブジェクト

Excelのセル１つ１つ　⇛　Cellオブジェクト

さっそくA1セルに"Hello"、B2セルに"World"、C3セルに"こんにちは"、C4セルに"世界"を保存したsample.xlsxという名前のExcelファイルを作成する。以下に全体コードを示す。

pythonコード
# openpyxlをインポートする
from openpyxl import Workbook

# Workbookオブジェクトを作成する
wb = Workbook()

# デフォルトで作成されるシートを取得する
ws = wb.active

# セルに値を設定する
ws['A1'] = 'Hello'
ws['B2'] = 'World'
ws.cell(row=3, column=3).value = "こんにちは"
ws.cell(row=4, column=4).value = "世界"

# ファイルを保存する
wb.save('sample.xlsx')

上記のコードの手順は以下のように分けられる。
1. ライブラリのインポートとWorkbookオブジェクトの作成
2. Workbookオブジェクト⇛Worksheetオブジェクトを作成⇛任意のセルに値を代入
3. Workbookオブジェクトの保存

1. ライブラリのインポートとWorkbookオブジェクトの作成
以下の部分はopenpyxlライブラリをインポートしWorkbookクラスをインポートし、wb変数にWorkbookクラスから生成したWorkbookオブジェクトを代入している。

インポートとworkbookオブジェクトの作成
# openpyxlをインポートする
from openpyxl import Workbook

# Workbookオブジェクトを作成する
wb = Workbook()

2. Workbookオブジェクト⇛Worksheetオブジェクトを作成⇛任意のセルに値を代入
ws = wb.activeの部分では、現在アクティブなシートを取得している。アクティブが何を指しているのかというと、Excelファイルが保存された段階でカーソルがのっていたSheetを指している。

複数シートのExcelファイル
↑の状態で保存された場合、print(ws.title)とすると"Sheet"が出力されるので確認してみると理解が進む。

複数のシートが存在するExcelファイルの場合、アクティブなシートを毎回確認するのは面倒くさい。
そこで、Workbookオブジェクト["シート名"]という指定の仕方もある。今回の例でいうと、wb["Sheet"]でも同じようにWorksheetオブジェクトを取得できる。

セルの代入については後で詳しく説明する。

worksheetオブジェクトの作成とセルへ値を代入
# デフォルトで作成されるシートを取得する
ws = wb.active

# セルに値を設定する
ws['A1'] = 'Hello'
ws['B2'] = 'World'
ws.cell(row=3, column=3).value = "こんにちは"

3. Workbookオブジェクトの保存
Workbookオブジェクトを保存することでopenpyxlで作成したExcelファイルがコード実行ファイルと同じディレクトリに作成される。wb.save(ファイルのパス)と記述することでファイルの保存ができ、ファイルのパスを変更することで任意の場所にExcelファイルを保存できる。

workbookオブジェクトを保存
# ファイルを保存する
wb.save('sample.xlsx')

実行ディレクトリにsample.xlsxファイルが作成され中身に以下のようになっていれば完成。

4. データの読み書き（基礎編）

まずは、Excelファイルからデータを読み、同じExcelファイル
に書きこむ操作を行っていく。

1. データの読み込み

2章でsample.xlsxを作成したが、作成したコードをコメントアウトするか、別のファイルを２章で記述したコードファイルと同じディレクトリに作成し、以下のコードを入力する。

データの読み込み
# 必要ライブラリのインポート
from openpyxl import load_workbook
# sample.xlsxをWorkbookオブジェクトとして読み込む
sample_book = load_workbook('sample.xlsx')
# WorkbookオブジェクトからWorksheetオブジェクトに変換
sample_sheet = sample_book.active
# Worksheetオブジェクトから指定したセルの値を取得
print(sample_sheet["A1"].value)
print(sample_sheet.cell(row=1, column=1).value)

出力結果は以下のようになるはずである。

Hello
Hello

まず、Excelファイルの呼び出しにはload_workbookメソッドを利用する。（メソッドについては説明しない）記述方法は、
load_workbook["Excelファイルのパス"]である。
この記述で、ExcelファイルからWorkbookオブジェクトを取得することができる。
次に、値の取り出し方を説明する。値の取り出し方は主に２つある。

  1. Worksheetオブジェクト[セルの位置].value
    (セルの位置は例えば、"A1", "B2", "C1"等である。)

  2. Worksheetオブジェクト.cell(row=1, column=1).value

上記のコードを見れば分かるように、どちらとも同じセルの値を指していることが分かる。
ここで気をつけることは、1ではセルの位置は文字列型が入り、2ではrow, columnにはそれぞれint型が入る。

2. データの書き込み

データの書き込みはデータの読み込みができれば簡単に行うことができる。データの読み込みで利用したコードに以下のコードを追加する。

データの書き込み要素を追加後のコード
# 必要ライブラリのインポート
from openpyxl import load_workbook

# sample.xlsxをWorkbookオブジェクトとして読み込む
sample_book = load_workbook('sample.xlsx')

# WorkbookオブジェクトからWorksheetオブジェクトに変換
sample_sheet = sample_book.active

# Worksheetオブジェクトから指定したセルの値を取得
print(sample_sheet["A1"].value)
print(sample_sheet.cell(row=1, column=1).value)

##############追加部分##############
sample_sheet["A1"].value = "change!!"

# セルの値を確認する
print(sample_sheet["A1"].value)
print(sample_sheet.cell(row=1, column=1).value)

# sample.xlsxをafter_sample.xlsxに名称を変更して保存する
sample_book.save("after_sample.xlsx")
####################################

追加コードを見てみると、sample_sheet["A1"].value = "change!!"と記述し、print文で中身を確認し、Workbookオブジェクトを保存するという処理になっている。書き込みの部分はsample_sheet["A1"].value = "change!!"とsample_book.save("after_sample.xlsx")である。まず、sample_sheet["A1"].value = "change!!"この部分で新たな"change!!"という文字列を代入している。これは変数に代入するのと同じように考えれば良い。そして、これだけだと新たなExcelもできていないし、既存Excel（＝sample.xlsx）が変更されているわけでもない。肝心なのは、sample_book.save("after_sample.xlsx")この記述によってafter_sample.xlsxというExcelファイルがコード実行ディレクトリに作成され、sample.xlsxの"A1"セルの"Hello"⇛"change!!"に書き換わって保存されていることである。ちなみに、sample_book.save("sample.xlsx")と記述すれば、既存のsample.xlsxが上書き保存されて"Hello"⇛"change!!"に変更される。

　　　　　　　　　　　　　　　　　　　↓ 変更後

5. データの読み書き（応用編）

4章では値の読み込み、書き込みの簡単な操作を扱った。ここからは、複数の値を読み込み、別のExcelファイル（４章のようにWorkbookオブジェクト.save(ファイルのパス名)で作成されたものではなく既存のファイルだと想定）に一気に転記していくにはどうすれば良いかを扱う。
以下のディレクトリ構成でできたディレクトリを作成する。
make_excel.pyを利用してサンプルデータと転記先のExcelファイルを作成する。

ディレクトリ構成
--
 |- practice.py
 |- make_excel.py
make_excel.py
from openpyxl import Workbook
import random

# 新規ワークブックを作成
sample_data_wb = Workbook()

# デフォルトのシートを選択
sample_data_ws = sample_data_wb.active

# ヘッダー行を追加
sample_data_ws.append(["ID", "Name", "City", "Score"])

# 10000行のランダムなデータを追加
for i in range(1, 101):
    id = i
    name = f"Name_{i}"
    city = f"City_{i}"
    score = random.randint(50, 100)
    sample_data_ws.append([id, name, city, score])

# ファイルを保存
sample_data_wb.save("sample_data.xlsx")

# 新規ワークブックを作成
empty_format_wb = Workbook()

# デフォルトのシートを選択
empty_format_ws = empty_format_wb.active

# ヘッダー行を追加
empty_format_ws.append(["ID", "Name", "City", "Score"])

# ファイルを保存
empty_format_wb.save("empty_format.xlsx")

make_excel.pyを使用すると以下のようなディレクトリ構成になるはずである。

 -
 |- practice.py
 |- make_excel.py
 |- sample_data.xlsx 　⇚作成されたファイル
 |- empty_format.xlsx　⇚作成されたファイル

sample_data.xlsxは以下のように作成されていたらOK

empty_format.xlsxは以下のように作成されていたらOK

今回満たしたい要件

sample_data.xlsxの中でName_1, Name_10, Name_20, Name_30, Name_40, Name_50, Name_60, Name_70, Name_80, Name_90, Name_100の行の要素をすべて（ID, Name, City, Score）を取得しempty_format.xlsxの中の該当の行（例えば、Name_1ならempty_format.xlsxの２行目に、Name_100ならempty_format.xlsxの101行目）に転記し、format.xlsxという新しいファイルとして保存する。

上記の要件を満たそうと考えた場合、セルすべての情報を取得し、該当のセルを見つけてその要素が存在する行の番号を見つけ、最後に任意の場所に要素を転記するという操作が必要である。セルすべてを取得し検索する方法を説明する。
ここで紹介するのは、sheetオブジェクト.iter_cols()とsheetオブジェクト.iter_rows()を用いる方法である。以下に詳細を説明した図を記載する。


この概念を理解すると以下のコードで何をしているのかが理解しやすい。今回はiter_colsで実装してみた。まず最初のfor文でiter_colsによって作成された列が入ったタプルのジェネレータオブジェクトが1つ1つ抽出され、column変数に、列のタプルが順次代入される。そして、次のfor文により列のタプルが1つ1つ抽出される。このときenumerateで囲むことでタプルの中身とタプルのindex番号両方を取得している。列のタプルから1つ1つ取り出す場合、ここのindex番号は行の番号になる。タプルのindex番号は0からスタートし、openpyxlでExcelのセルを指定する場合、sheetオブジェクト.cell(row=??, column=??)のrowもcolumnも1行1列からスタートする。そのためindex番号+1をしている。

practice.py
# openpyxlをインポートする
from openpyxl import Workbook, load_workbook

# Workbookオブジェクトを作成する
sample_data_wb = load_workbook("sample_data.xlsx")
format_wb = load_workbook("empty_format.xlsx")

# Worksheetオブジェクトを取得する
sample_data_sheet = sample_data_wb.active
format_sheet = format_wb.active


for column in sample_data_sheet.iter_cols():
    for row_index, cell in enumerate(column):
        if cell.value in ["Name_1","Name_10","Name_20","Name_30","Name_40","Name_50","Name_60","Name_70","Name_80","Name_90","Name_100"]:
            
            # 取得要素を変数に代入
            id_data = sample_data_sheet.cell(row=row_index+1, column=1).value
            name = sample_data_sheet.cell(row=row_index+1, column=2).value
            city = sample_data_sheet.cell(row=row_index+1, column=3).value
            score = sample_data_sheet.cell(row=row_index+1, column=4).value
            
            # 何が取得できているか確認
            print(id_data, name, city, score)
            
            # format_sheetに書き込む
            format_sheet.cell(row=row_index+1, column=1).value = id_data
            format_sheet.cell(row=row_index+1, column=2).value = name
            format_sheet.cell(row=row_index+1, column=3).value = city
            format_sheet.cell(row=row_index+1, column=4).value = score

# format.xlsxに書き込んだ内容を保存
format_wb.save("format.xlsx")
出力(print文の中身)
1 Name_1 City_1 71
10 Name_10 City_10 53
20 Name_20 City_20 93
30 Name_30 City_30 94
40 Name_40 City_40 75
50 Name_50 City_50 61
60 Name_60 City_60 70
70 Name_70 City_70 80
80 Name_80 City_80 56
90 Name_90 City_90 56
100 Name_100 City_100 65

コード実行ディレクトリにformat.xlsxファイルが作成され中身が以下の写真のようになっていれば完成。

iter_rowsを使用した場合も各自で実装してみると勉強になる。

メソッドを使用した行列の指定

上記の転記作業においてrowはタプルのindexを使用し、列は1,2,3,4とそのまま数値で指定した。実はそのように取得しなくてもセルの位置は今から紹介する2つの方法で取得できる。

1. cellオブジェクト.coodinate

coodinateは”A1”,"C2"のように文字列型で取得することができる。

2. cellオブジェクト.row cellオブジェクト.column

.row, .columnはそれぞれ1,1や2,3のようにint型で取得できる。

修正後のpractice.py
# openpyxlをインポートする
from openpyxl import Workbook, load_workbook

# Workbookオブジェクトを作成する
sample_data_wb = load_workbook("sample_data.xlsx")
format_wb = load_workbook("empty_format.xlsx")

# Worksheetオブジェクトを取得する
sample_data_sheet = sample_data_wb.active
format_sheet = format_wb.active

for column in sample_data_sheet.iter_cols():
    for cell in column:
        if cell.value in ["Name_1","Name_10","Name_20","Name_30","Name_40","Name_50","Name_60","Name_70","Name_80","Name_90","Name_100"]:
            
            # 取得要素の行と列を取得
            target_cell_row = cell.row
            target_cell_column = cell.column
            
            # 取得要素を変数に代入
            id_data = sample_data_sheet.cell(row=target_cell_row, column=target_cell_column-1).value
            name = sample_data_sheet.cell(row=target_cell_row, column=target_cell_column).value
            city = sample_data_sheet.cell(row=target_cell_row, column=target_cell_column+1).value
            score = sample_data_sheet.cell(row=target_cell_row, column=target_cell_column+2).value
            
            # format_sheetに書き込む
            format_sheet.cell(row=target_cell_row, column=target_cell_column-1).value = id_data
            format_sheet.cell(row=target_cell_row, column=target_cell_column).value = name
            format_sheet.cell(row=target_cell_row, column=target_cell_column+1).value = city
            format_sheet.cell(row=target_cell_row, column=target_cell_column+2).value = score

# format.xlsxに書き込んだ内容を保存
format_wb.save("format.xlsx")

6. Excelの装飾

openpyxlにはセルに色をつける、文字の色を変える等のスタイルの変更が簡単にできる。今回は頻繁に使用するものを紹介する。あくまで良く使うもの限定で紹介するため詳しい情報は公式を読むか以下のサイトが詳しいため読むことをオススメする。装飾のみならずopenpyxlの情報は詳細に書かれているため非常に参考になる。
https://www.shibutan-bloomers.com/python_libraly_openpyxl-4/2586/
まず、今まで記述してきたコードと同じディレクトリに、style.pyを追加する。そこに以下のコードを記述する。style.pyで行っているのは以下の4つの操作である。

1. セルに色やパターンをつける
2. 文字の位置を変更する
3. 文字の色を変更する
4. セルの線を変更する。

style.py
# openpyxlをインポートする
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Alignment, Font, Side, Border

# Workbookオブジェクトを作成する
format_wb = load_workbook("format.xlsx")

# Worksheetオブジェクトを取得する
format_sheet = format_wb.active

#######################セルに色をつける########################
cell = format_sheet.cell(row=2, column=2)
cell.fill = PatternFill(patternType='solid', fgColor='FF0000')
###############################################################

######################テキストの中央寄せ########################
cell = format_sheet.cell(row=2, column=3)
cell.alignment = Alignment(horizontal='center', vertical='center')
###############################################################

###########テキストの色をサイズ, フォント, 色を変更#############
cell = format_sheet.cell(row=2, column=4)
cell.font = Font(name="BIZ UDPゴシック", size=15, color='FF0000')
###############################################################

#######################セルの罫線を変更########################
cell = format_sheet.cell(row=2, column=5)
side = Side(style='thin', color='000000')
border = Border(top=side, bottom=side, left=side, right=side)
cell.border = border
###############################################################

format_wb.save("after_style_format.xlsx")

ここが地味に大切なので要注意である。インポートの部分では今回の装飾に必要なクラスをインポートしている。クラスについて理解はしなくて良いが、インポートしないとExcelの装飾はできないこと、クラスというものを呼び出していろいろ操作を行っていることは覚えておくとコードの理解が進むはずである。

必要なクラスのインポート
from openpyxl.styles import PatternFill, Alignment, Font, Side, Border

1. セルに色やパターンをつける
Name_1のみセルの色を変更してみる。

セルに色を付ける部分
cell = format_sheet.cell(row=2, column=2)
cell.fill = PatternFill(patternType='solid', fgColor='FF0000')

セルに色やパターンをつけるにはPatterFillクラスを使用する。()の中で引数に設定値を入れることで様々な色やパターンをつけることができる。引数には様々なものがあるが、今回はpatternType(塗りつぶしのパターン)="solid", fgColor(前景色)="FF0000(赤)"を利用した。特に疑問に思うのは色だと思うが、色はHTMLカラーコードで指定する。HTMLカラーコードは色を16進数の数値で表現したものである。

HTMLカラーコードの例

以下↓のサイトにHTMLカラーコードと色の対応表がある。
https://www.colordic.org/

after_style_format.pyでは以下のようになっていれば成功。

2. 文字の位置を変更する

cell = format_sheet.cell(row=2, column=3)
cell.alignment = Alignment(horizontal='center', vertical='center')

Alignmentクラスを使用する。引数はそれぞれ、horizontal="横の位置の設定", vertical="縦の位置の設定"を表している。今回の場合、すべて中央揃えである。
以下の写真のように中央揃えになっていたら成功。

3. 文字の色を変更する

文字の色やフォント, サイズを変更する部分
cell = format_sheet.cell(row=2, column=4)
cell.font = Font(name="BIZ UDPゴシック", size=15, color='FF0000')

Fontクラスを使用する。引数によって様々な設定ができるが、今回はフォントの種類, サイズ, フォントの色を設定した。
以下の写真のようなフォントサイズ, フォント, 色になっていたら成功。

4. セルの線を変更する。

セルの線を変更する部分
cell = format_sheet.cell(row=2, column=5)
side = Side(style='thin', color='000000')
border = Border(top=side, bottom=side, left=side, right=side)
cell.border = border

セルの線を描くには、2つのクラスを使用する。SideクラスとBorderクラスである。Sideクラスでは、線の細さや色を設定できる。そして、BorderクラスにSideクラスで設定した設定を反映させる。Borderの引数で指定するのは主に、セルの四方のどの線にSideクラスで設定した設定を反映させるかである。今回はSideクラスで指定した設定を四方すべての線に対して反映させている。
以下の写真のように線が引かれていたら成功。

after_style_format.xlsxは以下のようになっているはず。

7. 便利機能

■連続した行または列の最大個数を知りたい
Worksheetオブジェクト.max_row ⇛ 行の最大値
Worksheetオブジェクト.max_column ⇛ 列の最大値

max_rowとmax_column
from openpyxl import load_workbook

# ワークブックを読み込む
wb = load_workbook('sample.xlsx')
ws = wb.active

# ワークシートの行と列の数を取得
num_rows = ws.max_row
num_cols = ws.max_column

print(f"The worksheet has {num_rows} rows and {num_cols} columns.")

■"A1"⇚⇛(1,1)のように相互変換したい
openpyxl.tuilsモジュールのcolumn_index_from_stringとget_column_letterを使用する。

"A1"⇚⇛(1,1)相互変換
from openpyxl.utils import column_index_from_string, get_column_letter

# "A1"から(1, 1)への変換
cell = "A1"
column, row = cell[0], cell[1:]
column = column_index_from_string(column)  # 列の文字をインデックスに変換
row = int(row)  # 行の数字を整数に変換
print(column, row)  # Output: 1, 1

# (1, 1)から"A1"への変換
column, row = 1, 1
column = get_column_letter(column)  # 列のインデックスを文字に変換
cell = f"{column}{row}"
print(cell)  # Output: A1

■行列の追加、削除
行の追加
追加はinsert_rows(行番号)

行の追加
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 2行目に1行を追加
ws.insert_rows(2)

wb.save('insert_rows_example.xlsx')

行の削除
削除はdelete_rows(行番号)

行の削除
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 2行目の1行を削除
ws.delete_rows(2)

wb.save('delete_rows_example.xlsx')

列の追加
追加はinsert_cols(列番号)

列の追加
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 2列目に1列を追加
ws.insert_cols(2)

wb.save('insert_cols_example.xlsx')

列の削除
削除はdelete_cols(列番号)

列の削除
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 2列目の1列を削除
ws.delete_cols(2)

wb.save('delete_cols_example.xlsx')

■Hyperlinkが貼りたい
例では"A1"セルにhttp://www.example.com
へのハイパーリンクが貼られている。また、.value を使用してハイパーリンクのテキストを設定している。.valueはなくてもリンクは貼られる。リンクに任意の名前をつけたい時だけ.valueでテキストを入れる。

hyperlinkの例
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# A1セルにハイパーリンクを設定
ws['A1'].hyperlink = "http://www.example.com"
ws['A1'].value = "Link to Example.com"

wb.save('hyperlink_example.xlsx')

8. 参考文献

1.openpyxl公式Tutorial
https://openpyxl.readthedocs.io/en/stable/tutorial.html

2.Excelファイルを扱う上でのopenpyxlとpandasの違い
https://gammasoft.jp/blog/openpyxl-vs-pandas/
3.ChatGPT(gpt-4)

4.PythonでExcelファイルを読み込み・書き込みするxlrd, xlwtの使い方
https://note.nkmk.me/python-xlrd-xlwt-usage/

5.いつも忘れる OpenPyXL
https://qiita.com/saiva/items/7083a7661fdab67f4f7b

6.【Python×Excel】openpyxlでセルに書式設定(フォント・塗り潰し・罫線)する方法【前編】
https://www.shibutan-bloomers.com/python_libraly_openpyxl-4/2586/

Discussion