LoginSignup
95
125

More than 5 years have passed since last update.

@mimitaro

pythonのOpenPyXLでExcelのデータをとことん読み取る

Posted at

はじめに

pythonのOpenPyXLを使ってExcelのデータをとことん読み込んでみました。

世の中にはまだまだExcelを使って、何かをする需要が高くあります。
Excel内で完結すればよいのですが、Excelからデータを取り出し何かをするのはとても大変です。
私もpythonでデータを編集したいのに、元データがExcelにある、というケースが多々あり四苦八苦していました。

そこでOpenPyXLでExcelのデータを読み込む方法や注意点についてまとめてみました。
本情報が同じような境遇の方に役立っていただければ幸いです。

環境とサンプルデータについて

今回は以下の環境でOpenPyXLを使用しました。

環境 バージョン
OS Windows 10
python 3.7.2
OpenPyXL 2.5.12

また分かりやすく説明するために次のようなサンプルExcelファイルを使用します。

openpyxl_excel.PNG

上記のExcelファイルは以下のディレクトリに配置して各pythponを実行しています。

sample.xlsmの配置先
C:\sample\sample.xlsm

単純な使い方

まずはExcelファイルをロードして、セルの情報を取得してみます。

Excelファイルのロード

最初にExcelファイルのロードだけをしてみます。

単純なExcelロード
from openpyxl import load_workbook

# Excelファイルのロード(読み取り専用)
excel_path='C:/sample/sample.xlsm'
workbook = load_workbook(filename=excel_path, read_only=True)

# Excelのシート名一覧を表示
print(workbook.sheetnames)

# ロードしたExcelファイルを閉じる
workbook.close()
実行結果
['フレンズたち']

今回のExcelファイルはシートが1つのため上記のように表示されます。
今回はシートが1つだけなので、「フレンズたち」シートのみが表示されます。
ちなみにデータ型はlist型です。

シートを選択し特定のセルからデータを取り出す

続いて、シートから座標を選択しデータを取り出します。
座標指定の方法は、Excel座標(A3とかD12とかの文字列)と数値座標のどちらかが使えます。
直感的にはExcel座標は分かりやすいですが、細かい処理をする時は数値座標が便利です。
また互いの座標を変換するための便利なUtilisがOpenPyXLに付いています。
詳しくは役に立つ機能を参照してください。

特定のセルからデータ取得(Excel座標による方法)

まずはExcel座標を使う方法です。

セルのデータ取得(Excel座標使用)
from openpyxl import load_workbook

# エクセルファイルのロード
excel_path='C:/sample/sample.xlsm'
workbook = load_workbook(filename=excel_path, read_only=True)

# シートのロード
sheet = workbook['フレンズたち']

# セルの値取得
cell_c6_value = sheet['C6'].value
cell_d6_value = sheet['D6'].value

# 取得した値の表示
print('C6', cell_c6_value)
print('D6', cell_d6_value)

# ロードしたExcelファイルを閉じる
workbook.close()
実行結果
C6 サーバル
D6 None

ワークシートにExcel座標の文字列を指定することで、OpenPyXLのCell型が得られます。
Cell型のvalueから、そのセルが持つ値が取得可能です。

openpyxl_excel_2.PNG

ここで注目して欲しいのはD6には値がNoneであることです。
C6とD6は結合セルですが、値はC6側にのみ入っています。
これはExcelの仕様で結合セルは、結合前の左上端のデータのみが保持されるためです。

特定のセルからデータ取得(数値座標による方法)

続いて数値座標からセルのデータを取得する方法です。
数値座標は(1, 1)からスタートし、横軸はA=1、B=2、C=3…となっています。

セルのデータ取得(数値座標使用)
from openpyxl import load_workbook

# エクセルファイルのロード
excel_path='C:/sample/sample.xlsm'
workbook = load_workbook(filename=excel_path, read_only=True)

# シートのロード
sheet = workbook['フレンズたち']

# セルの値取得
cell_3_6_value = sheet.cell(column=3, row=6).value
cell_4_6_value = sheet.cell(column=4, row=6).value

# 取得した値の表示
print('(3, 6)', cell_3_6_value)
print('(4, 6)', cell_4_6_value)

# ロードしたExcelファイルを閉じる
workbook.close()
実行結果
(3, 6) サーバル
(4, 6) None

worksheetの持つcell関数を使うことで、数値座標からCell型を取得可能です。
値"サーバル"が格納されているC6座標は、数値にすると(横軸, 縦軸) = (3, 6)です。
そのため上記のように(3, 6)座標でサーバルの値が取得可能です。

Cell型のvalueや(4, 6)の値がNoneである点は前項目を参照してください。

Cell型の扱いについて

本章で説明した通り、ExcelのセルデータはOpenPyXLのCell型で対応します。
Cell型の詳細は以下のマニュアルから確認できます。

※ただし今回は読み取り専用なため正確にはReadOnlyCell型を使用しています。

実際にCellからデータを取り扱った際に、色々と注意すべき点があったためここに記載します。

Cell型から取得できる座標と値

ExcelのセルはCell型として扱われ、座標や値を取得できます。
ただし以下のようにインスタンス変数によって取得する座標の種類等が異なります。

Cell型から座標と値を取得
from openpyxl import load_workbook

# エクセルファイルのロード
excel_path='C:/sample/sample.xlsm'
workbook = load_workbook(filename=excel_path, read_only=True)

# シートのロード
sheet = workbook['フレンズたち']

# セル取得
cell_b14 = sheet['B14']

# Cell型から取得できる代表的なデータ
print('Cell.column 横軸の座標:', cell_b14.column)
print('Cell.row 縦軸の座標:', cell_b14.row)
print('Cell.coordinate Excel座標:', cell_b14.coordinate)
print('Cell.value セルの値:', cell_b14.value)

# ロードしたExcelファイルを閉じる
workbook.close()
実行結果
Cell.column 横軸の座標: 2
Cell.row 縦軸の座標: 14
Cell.coordinate Excel座標: B14
Cell.value セルの値: ★けものフレンズ２

まとめると次のようになります。

インスタンス変数 説明
Cell.column 横軸の座標(1から始まる数値)
Cell.row 縦軸の座標(1から始まる数値)
Cell.coordinate Excel座標(A12などの文字列)
Cell.value セルに格納された値

空白のセルの取り扱い

Cell型で特に注意が必要な点は、例え空白のセルを取得してもCell型はNoneではないことです。
Cell.valueがNoneとなります。

空白のセルを取得
# エクセルファイルのロード
excel_path='C:/sample/sample.xlsm'
workbook = load_workbook(filename=excel_path, read_only=True)

# シートのロード
sheet = workbook['フレンズたち']

# セル取得
cell_b14 = sheet['B14']
cell_b15 = sheet['B15']

# ------------------------------------
# 値のあるセル(B14)と空白セル(B15)の比較
# ------------------------------------

# B15は空白セルだが、Noneではない。
print('cell_b14のNone判定:', cell_b14 is None)
print('cell_b15のNone判定:', cell_b15 is None)

# 空白のセルは正確にはEmptyCell型
print('cell_b14のデータ型:', type(cell_b14))
print('cell_b15のデータ型:', type(cell_b15))

# 空白のセルは値がNoneとなる
print('cell_b14のvalueのNone判定:', cell_b14.value is None)
print('cell_b15のvalueのNone判定:', cell_b15.value is None)

# ロードしたExcelファイルを閉じる
workbook.close()
実行結果
cell_b14のNone判定: False
cell_b15のNone判定: False
cell_b14のデータ型: <class 'openpyxl.cell.read_only.ReadOnlyCell'>
cell_b15のデータ型: <class 'openpyxl.cell.read_only.EmptyCell'>
cell_b14のvalueのNone判定: False
cell_b15のvalueのNone判定: True

またマニュアルに記載されており通りEmptyCellからは座標が取得できません。
※column、row、coordinateがないため。

特定範囲のデータを取り扱う

エクセルの特定範囲をまとめて処理してみます。
今回は次のデータ範囲を選択しました。

openpyxl_excel_3.PNG

主な方法は2つありますが、お互いデータ範囲のデータ型が異なるため用途ごとに使い分けが必要です。

範囲データ取得方法 コード例 取り扱い方 説明
Excel座標で指定 sheet['C16':'I19'] tuple型 Excel座標を使う必要はあるが、tuple型なためさらに絞り込んだデータ指定が可能
数値座標で指定 sheet.iter_rows(min_row=...) ジェネレータ 数値を用いるため座標指定は使いやすいが、ジェネレータであることからforなどでデータを一括処理する必要あり

Excel座標で範囲指定

今回指定する範囲はExcel座標でC16～I19です。
この範囲内に存在する値とそのExcel座標を表示させます。

Excel座標で範囲指定
from openpyxl import load_workbook

# エクセルファイルのロード
excel_path='C:/sample/sample.xlsm'
workbook = load_workbook(filename=excel_path, read_only=True)

# シートのロード
sheet = workbook['フレンズたち']

# 範囲データ取得
sheet_range = sheet['C16':'I19']

# 範囲データを順次処理
for row in sheet_range:
    for cell in row:
        # 該当セルの値取得
        cell_value = cell.value
        # 該当セルに値が存在する場合表示
        if cell_value is not None:
            print(cell.coordinate, cell_value)

# ロードしたExcelファイルを閉じる
workbook.close()
実行結果
C16 ふれんずのなまえ
E16 ひとこと
C17 チーター
E17 私は一人が気楽でいいの！
C18 プロングホーン
E18 ともに速さを磨いていこうじゃないか！
C19 G・ロードランナー
E19 飛んじゃいけないなんてルールはなかったぜぇ

空白のセルの取り扱いで説明した通り、セルそのものは値が空白でもNoneとなりません。
そのためセルの値に対して「if ... is not None」を使用します。

範囲データのさらなる絞り込み

sheet_range = sheet['C16':'I19']はtuple型の2次配列です。
そのためsheet_rangeから直接値を取得することが可能です。
ただし注意点として、tuple型の配列の添字は[0][0]から始まり、そこに格納されているデータ=セルは元のExcelファイル上の座標に依存しています。

openpyxl_excel_4.PNG

範囲データのさらなる絞り込み
from openpyxl import load_workbook

# エクセルファイルのロード
excel_path='C:/sample/sample.xlsm'
workbook = load_workbook(filename=excel_path, read_only=True)

# シートのロード
sheet = workbook['フレンズたち']

# 特定範囲の絞り込み
sheet_range = sheet['C16':'I19']

# 範囲データからさらに絞り込む(Cell型)
print(sheet_range[0][0] == sheet['C16'])
print(sheet_range[0][0].coordinate)
print(sheet_range[0][0].value)

# ロードしたExcelファイルを閉じる
workbook.close()
実行結果
True
C16
ふれんずのなまえ

sheet_rangeの配列の添字とExcel上の座標は当然ずれるためややこしくなります。
しかし適切に使えば柔軟性の高い操作が可能です。

数値座標で範囲指定

数値座標による範囲指定も可能です。
ただしこの場合、ジェネレータが返ってくるためtuple型のような柔軟性はありません。
細かいセルの指定は、最初からsheetをそのまま使うことを前提に考える必要があります。

数値座標で範囲指定
from openpyxl import load_workbook

# エクセルファイルのロード
excel_path='C:/sample/sample.xlsm'
workbook = load_workbook(filename=excel_path, read_only=True)

# シートのロード
sheet = workbook['フレンズたち']

# 特定範囲の絞り込み
# 頂点C16座標の数値化
C16_COL = 3
C16_ROW = 16

# 低点I19座標の数値化
I19_COL = 9
I19_ROW = 19

# 範囲データを順次処理
for row in sheet.iter_rows(min_col=C16_COL, min_row=C16_ROW, max_col=I19_COL, max_row=I19_ROW):
    for cell in row:
        # 該当セルの値取得
        cell_value = cell.value
        # 該当セルに値が存在する場合表示
        if cell_value is not None:
            print(cell.coordinate, cell_value)

# ロードしたExcelファイルを閉じる
workbook.close()
実行結果
C16 ふれんずのなまえ
E16 ひとこと
C17 チーター
E17 私は一人が気楽でいいの！
C18 プロングホーン
E18 ともに速さを磨いていこうじゃないか！
C19 G・ロードランナー
E19 飛んじゃいけないなんてルールはなかったぜぇ

結果は当然ながらExcel座標の場合と同様です。
iter_rows関数にはそれぞれ以下の引数を渡しています。

openpyxl_excel_5.PNG

引数 渡した値 説明
min_col 3 C16=(3, 16)の横軸座標の値
min_row 16 C16=(3, 16)の縦軸座標の値
max_col 9 I19=(9, 19)の横軸座標の値
max_row 19 I19=(9, 19)の縦軸座標の値

こちらは柔軟性は低いかもしれませんが、新たにtuple型のデータを生成しないメリットもあります。
また座標を数値で扱うため直感的ではありませんが、色々と処理がしやすいです。

役に立つ機能

今回、色々と説明した通り、OpenPyXLでExcelのデータを取り扱う際は、文字列のExcel座標または数値の座標のどちらかを使う必要があります。
直感的にはExcel座標が分かりやすいですが、様々な計算がしやすい数値座標も魅力的です。
そこでOpenPyXLではExcel座標と数値座標をお互い変換する機能「openpyxl.utils.cell」が備わっています。
これらを使うことで、より容易にExcelデータの取り扱いが可能となります。

役に立つ機能の例
from openpyxl import load_workbook
from openpyxl.utils.cell import *

# Excel座標の横軸(アルファベット)を数字(1から始まる)に変換
coord_col_idx = column_index_from_string('C')
print('横軸の座標Cを数字変換：', coord_col_idx)

# 数値座標の横軸(数値)をアルファベット(Aから始まる)に変換
coord_col_str = get_column_letter(3)
print('横軸の座標3をアルファベット変換：', coord_col_str)

# Excel座標をtuple型へ変換
coord_tuple_str = coordinate_from_string('C12')
print('C12座標をtuple型へ変換：', coord_tuple_str)

# Excel座標を数値座標のtuple型へ変換
coord_tuple_idx = coordinate_to_tuple('C12')
print('C12座標を数値座標のtuple型へ変換:', coord_tuple_idx)

# Excel座標による範囲指定を数値のtuple型へ変換
range_idx = range_boundaries('C16:I19')
print('Excel座標による範囲を数値座標の範囲へ変換：', range_idx)
実行結果
横軸の座標Cを数字変換： 3
横軸の座標3をアルファベット変換： C
C12座標をtuple型へ変換： ('C', 12)
C12座標を数値座標のtuple型へ変換: (12, 3)
Excel座標による範囲を数値座標の範囲へ変換： (3, 16, 9, 19)

参考にしたサイト

95
125
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
95
125