PythonでEXCELを操る基本のキ

日常業務と最も結びつきの強いアプリケーションの一つは間違いなくEXCELだと思います。日々のEXCEL作業を自動化することができればどれくらいの効率化につながるか計り知れません。本屋を覗いてもここ数年Pythonで効率化という書籍を目にすることが増えたと思います。まずは本文書を通してPythonを使ったEXCELファイルの操作方法の基本を学んでいきましょう。

2020年9月、本屋にはPythonとExcelを使った効率化本が新たに2冊加わっていました。Pythonと名前のついた新刊の数を見てもPythonは今最もあついプログラミング言語といっても間違いないでしょう。2023年に入りChatGPTとPythonの関連本も続々登場しています。

openpyxlライブラリ

Python単体ではEXCELファイルを操作することができないので追加のライブラリが必要となります。本文書ではopenpyxlライブラリを利用してEXCELの操作を行います。

openpyxlはEXCELファイルを操作するためのPythonライブラリです。

openpyxlライブラリのインストール

パッケージ管理システムpipを使ってopenpyxlライブラリのインストールを行います。

% pip install openpyxl
Collecting openpyxl
  Downloading openpyxl-3.1.2-py2.py3-none-any.whl (249 kB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 250.0/250.0 kB 853.2 kB/s eta 0:00:00
Collecting et-xmlfile (from openpyxl)
  Downloading et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)
Installing collected packages: et-xmlfile, openpyxl
Successfully installed et-xmlfile-1.1.0 openpyxl-3.1.2

pip listコマンドで追加されたライブラリとバージョンを確認しておきます。

% pip list
Package    Version
---------- -------
et-xmlfile 1.1.0
openpyxl   3.1.2
pip        23.3.1
//その他

EXCELファイルの操作

ワークブックとワークシート

説明の中でワークブックとワークシートいう名前が何度も出てきます。ワークブックはEXCELファイルそのもののことを言います。

ワークシートはEXCELファイルを開いたときに表示される画面です。1つのワークブックに複数のワークシートを作成することができ、ワークシートは下部にあるタブで切り替えることができます。

はじめてのEXCEL操作

公式ホームページにサンプルが掲載されているので、サンプルをコピー&ペーストして動作確認を行いましょう。

from openpyxl import Workbook
#1 create a new workbook(新規のワークブック作成)
wb = Workbook()

#2 grab the active worksheet(開いているワークシートを選択)
ws = wb.active

#3 Data can be assigned directly to cells(セルに直接データを挿入)
ws['A1'] = 42

#4 Rows can also be appended(行の追記)
ws.append([1, 2, 3])

#5 Python types will automatically be converted(Pythonタイプは自動で変換される)
import datetime
ws['A2'] = datetime.datetime.now()

#6 Save the file(ファイルを保存する)
wb.save("sample.xlsx")

上記のコードを記述したpyファイルを保存して実行すると同じフォルダ内にsample.xlsxファイルが作成されます。ファイルを開くと下記のような内容で作成されています。

A2セルについては現在時刻で上書き(1が入っていた)されており、記述したコード通りの内容で作成されていることが確認できます。

ファイルの作成

EXCELファイルを作成するだけであれば、下記の3行で行うことができます。

from openpyxl import Workbook

wb = Workbook()

wb.save("test.xlsx")

実行すると空のtest.xlsxファイルが作成されます。

ワークシート名の付与

デフォルトではワークシート名はSheetとなりますが、ワークシートに任意の名前をつけることができます。

from openpyxl import Workbook

wb = Workbook()

ws = wb.active

ws.title = "テスト"

wb.save("test.xlsx")

作成されたtest.xlsxファイルを開くとワークシートの名前がテストになっていることが確認できます。

複数のワークシートの作成

複数のワークシートを作成したい場合はcreate_sheetメソッドを使用します。

from openpyxl import Workbook

wb = Workbook()

ws1 = wb.create_sheet('ワークシート1')

ws2 = wb.create_sheet('ワークシート2')

wb.save("test.xlsx")

実行するとデフォルトのSheetと追加作成したワークシート1、ワークシート2の3つのシートを作成することができます。ワークシート1, ワークシート2の2つのシートではありません。

ワークシートを下部のタブの指定した場所に追加作成したい場合は、ワークシート名の次の引数に番号を入れることでその場所に追加することができます。

ws2 = wb.create_sheet('ワークシート2',0)

ワークシート2は0を指定したのでタブの先頭に追加作成されます。

作成したEXCELファイルを開いた時に表示させておきたいワークシートはactiveで設定を行います。

wb.active=1を指定すると2番目のワークシートを選択した状態で開きます。

wb.active = 1

ワークシートの番号は一番左が0になるため1を指定すると2番目のワークシートになります。プログラムを実行後にはデフォルトでは”Sheet”のワークシートが開きますが”ワークシート1″で開くことになります。

from openpyxl import Workbook

wb = Workbook()

ws1 = wb.create_sheet('ワークシート1')

ws2 = wb.create_sheet('ワークシート2')

wb.active = 1

wb.save("test.xlsx")

ワークシート名の取得

作成したワークシートの名前はWorkbook.sheetnamesで取得することができます。

print(wb.sheetnames)

先ほどのコードに以下を追加して再度実行します。

from openpyxl import Workbook

wb = Workbook()

ws1 = wb.create_sheet('ワークシート1')

ws2 = wb.create_sheet('ワークシート2')

wb.active = 1

wb.save("test.xlsx")

print(wb.sheetnames)

Workbook.sheetnamesを利用すれば、ワークシートの名前を取得することができます。

>py test.py
['Sheet', 'ワークシート1', 'ワークシート2']

また、名前の取得はfor文を利用しても取得することができます。

for sheet in wb:
	print(sheet.title)

実行すると配列ではなくワークシートの名前を1つずつ取得することができます。

>py test.py
Sheet
ワークシート1
ワークシート2

シートへの書き込み(セル指定)

3つの方法でセルの場所を指定してデータの書き込みを行います。

from openpyxl import Workbook

wb = Workbook()

ws = wb.active

# A1のセルを指定
ws['A1'] = 42

# 行番号1、列番号2でB2を指定
ws.cell(row=1, column=2).value = 44

# 行番号3、列番号3でC3を指定
ws.cell(row=3, column=3, value=59)

wb.save("test.xlsx")

cellメソッドを使用する場合は、rowとcolumnの番号を指定する必要があります。

作成したpyファイル実行後にtest.xlsxを開くと指定したセルのA1, B1, C3セルにデータが挿入されます。指定したセルにデータが書き込まれていることが確認できました。

複数のワークシートを作成した後に特定のワークシートのセルにデータを挿入したい場合は以下のように行うことができます。

from openpyxl import Workbook

wb = Workbook()

ws1 = wb.create_sheet('ワークシート1')

ws2 = wb.create_sheet('ワークシート2')

ws1['A1'] = 100

ws2['A1'] = 500

wb.save("test.xlsx")

ワークシート1という名前のシートのA1セルに100とワークシート2という名前のシートのA1セルに500と書き込まれたファイルが作成されます。

シートへの書き込み(リスト利用)

リストデータをシートに書き込みたい場合はappendメソッドを利用します。

from openpyxl import Workbook

wb = Workbook()

ws = wb.active

rows = [['商品ID', '部門ID', '商品コード', '商品名', '商品単価'],
['1', '1', '1001', '商品A', '1000'],
['2', '1', '1002', '商品B', '1000'],
['3', '1', '1003', '商品C', '1000'],
['4', '1', '1004', '商品D', '2000'],
['5', '1', '1005', '商品E', '2000'],
['6', '1', '1006', '商品F', '2000']]

for row in rows:
	ws.append(row)

wb.save("test.xlsx")

作成したpyファイルを実行するとリストデータがEXCELファイルに書き込まれていることが確認できます。

上記のリストデータはすべてのデータを文字列として指定している(数値に”で囲んでいる)ためEXCELを開いてセルのメッセージを見ると”数値が文字列として保存されている”と表示されます。数値を数値として保存したい場合には下記のように記述することで数値として保存されます。

from openpyxl import Workbook

wb = Workbook()

ws = wb.active

rows = [['商品ID', '部門ID', '商品コード', '商品名', '商品単価'],
[1, 1, '1001', '商品A', 1000],
[2, 1, '1002', '商品B', 1000],
[3, 1, '1003', '商品C', 1000],
[4, 1, '1004', '商品D', 2000],
[5, 1, '1005', '商品E', 2000],
[6, 1, '1006', '商品F', 2000]]

for row in rows:
	ws.append(row)

wb.save("test.xlsx")

EXCELファイルを開く

これまではすべて新規のEXCELファイルを作成してきましたが、openpyxlのload_workbookを利用して作成済みのファイルを開きます。開いた後先ほど学んだsheetnamesを使ってシート名を取得します。test.xlsxファイルはPythonを実行するファイルと同じディレクトリにすでに存在していることとして進めています。

from openpyxl import load_workbook

file_path = 'test.xlsx'

wb = load_workbook(filename=file_path)

print(wb.sheetnames)

wb.close()

次にワークシートを開く方法を確認します。

load_workbookでファイルパスを指定してワークブック(ファイル)を開き、シート名を指定することでシートを開きます。

from openpyxl import load_workbook

file_path = 'test.xlsx'

wb = load_workbook(filename=file_path)

sheet = wb['Sheet']

#操作を行う

wb.close()

セルから値を取り出す

load_workbookを使用して開いたシートからセルの値を取得します。

from openpyxl import load_workbook

file_path = 'test.xlsx'

wb = load_workbook(filename=file_path)

sheet = wb['Sheet']

print(sheet['C1'].value)

wb.close()

開いたファイルは下記のようにデータが入力されています。C1コードの値を読み取っているので、上記コードを実行すると商品コードが表示されます。

シートの値だけではなく、指定したセルの行番号と列番号を取得することができます。

print(sheet['C1'].column)

print(sheet['C1'].row)

実行すると行番号は3、列番号が1であることがわかります。

シートのセルの全データ取得

シートに登録されているデータをすべて表示します。for文を利用してsheetからrow(行情報)を取り出し、rowからcellの値を取得しています。

from openpyxl import load_workbook

file_path = 'test.xlsx'

wb = load_workbook(filename=file_path)

sheet = wb['Sheet']

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

wb.close()

作成したpyファイルを実行すると下記のようにセルの値が改行されて縦に表示されます。

>py test.py
商品ID
部門ID
商品コード
商品名
商品単価
1
1
1001
商品A
1000
2
1
1002
商品B
1000
 ・
 ・

上記の方法でシート内のすべてのデータを取り出すことに成功していますが、データを読むのが難しいのでforの箇所のみ変更を行います。(内包表記を利用)

for row in sheet:
	values = [cell.value for cell in row]
	print(values)

実行すると行が配列として表示できます。

>py test.py
['商品ID', '部門ID', '商品コード', '商品名', '商品単価']
['1', '1', '1001', '商品A', '1000']
['2', '1', '1002', '商品B', '1000']
['3', '1', '1003', '商品C', '1000']
['4', '1', '1004', '商品D', '2000']
['5', '1', '1005', '商品E', '2000']
['6', '1', '1006', '商品F', '2000']

行、列の削除

シートから行を削除したい場合は、delete_rowsを利用することができます。ワークシートの名前が”Sheet”の3行目の行を削除したい場合は下記のように記述します。

sheet = wb['Sheet']

sheet.delete_rows(3)

下記のコードを実行するとターミナルには削除した情報が表示されますがtest.xlsxファイルを開くと列の削除は行われていません。

from openpyxl import load_workbook

file_path = 'test.xlsx'

wb = load_workbook(filename=file_path)

sheet = wb['Sheet']

sheet.delete_rows(3)

for row in sheet:
	values = [cell.value for cell in row]
	print(values)

wb.close()

削除した状態をファイルに保存させたい場合にはwb.save(‘sample.xlsx”)を実行する必要があります。

複数行を削除することも可能です。3行目の行から3行削除したい場合は、下記のように記述することができます。

sheet = wb['Sheet']

sheet.delete_rows(3,3)

列の削除はdelete_columnsで行うことができます。

sheet = wb['Sheet']

sheet.delete_cols(2)

複数列を削除することも可能です。3列目の行から2列削除したい場合は、下記のように記述することができます。

sheet = wb['Sheet']

sheet.delete_cols(3,2)

Pythonのopenpyxlライブラリを利用してEXCELファイルの基本操作を学ぶことができました。ファイルを開く、保存する、セル、列、行を操作することができたので次はPythonを利用してEXCELを操作してどのような業務の効率化を行いたいか考える必要があります。