PythonでExcelファイル(xlsx)を読み書きするopenpyxlの使い方

Pythonのライブラリopenpyxlを使うとExcelファイル(.xlsx)を読み書き(入出力)できます。使い方を説明します。 BitBucketのレポジトリと公式ドキュメントは以下のリンクから。

openpyxl / openpyxl — Bitbucket openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 2.5.3 documentation

PythonでExcelファイルを扱うライブラリの違いや使い分けなどは

openpyxlは既存のExcelファイルの書式を保ったまま編集・追記(セルの値の変更、新たなセルの追加など)したい場合に便利です。書式などは気にせず数値や文字列のデータを読み込んで分析するのであればpandasがおすすめ。

ここでは以下の内容について説明します。

openpyxlのインストール Excelファイルの読み込み セルの編集・追記 ワークシートの追加・コピー・削除 Excelファイルの書き込み(新規作成・上書き保存)

openpyxlのインストール

pipでインストールできる(環境によってはpip3)。

$ pip install openpyxl

Excelファイルの読み込み

openpyxlでは以下のクラスが定義されています。

Workbookクラス: ワークブック全体 Worksheetクラス: 一つのシート Cellクラス: 一つのセル

ここでは以下のxlsxファイルを例にセルの値を取得する方法を説明します。

sample.xlsx

sheet1とsheet2の二つのワークシートを持ちます。sheet1の中身は以下の通り。 A B C one 11 12 13 two 21 22 23 three 31 32 33

openpyxlをインポート。結果を見やすくするためにpprintモジュールもインポートしています。

import openpyxl
import pprint


openpyxl.load_workbook()にExcelファイルのパスを指定してWorkbookオブジェクトを取得。sheetnames()属性でシート名一覧のリストを取得できます。
wb = openpyxl.load_workbook('data/src/sample.xlsx')

print(type(wb))
# <class 'openpyxl.workbook.workbook.Workbook'>

print(wb.sheetnames)
# ['sheet1', 'sheet2']

Workbookオブジェクトから[シート名]でWorksheetオブジェクトを取得。

sheet = wb['sheet1']

print(type(sheet))
# <class 'openpyxl.worksheet.worksheet.Worksheet'>

Worksheetオブジェクトから['A2]のようなエクセルのセル指定文字列でCellオブジェクトを取得。Cellオブジェクトの属性valueでそのセルの値を取得できます。

cell = sheet['A2']

print(type(cell))
# <class 'openpyxl.cell.cell.Cell'>

print(cell.value)
# one

Worksheetオブジェクトのcell()メソッドで行番号、列番号を指定してCellオブジェクトを取得することもできます。xlrdやxlwtと違い、1始まりなので気をつけてください。

cell = sheet.cell(row=2, column=1)

print(type(cell))
# <class 'openpyxl.cell.cell.Cell'>

print(cell.value)
# one

任意の範囲のセルの値を2次元配列として取得

Worksheetオブジェクトから['A2:C4']のように範囲を指定すると、Cellオブジェクトを要素とする2次元のタプル(タプルのタプル)を取得できます。

pprint.pprint(sheet['A2:C4'])
# ((<Cell 'sheet1'.A2>, <Cell 'sheet1'.B2>, <Cell 'sheet1'.C2>),
#  (<Cell 'sheet1'.A3>, <Cell 'sheet1'.B3>, <Cell 'sheet1'.C3>),
#  (<Cell 'sheet1'.A4>, <Cell 'sheet1'.B4>, <Cell 'sheet1'.C4>))

['A2:C4']のようなエクセルのセル指定文字列ではなく、iter_rows()メソッドで行番号、列番号の範囲を指定してジェネレータとして取得することもできます。ジェネレータはlist()でリスト化できます。

g = sheet.iter_rows(min_row=2, max_row=4, min_col=1, max_col=3)

print(type(g))
# <class 'generator'>

pprint.pprint(list(g))
# [(<Cell 'sheet1'.A2>, <Cell 'sheet1'.B2>, <Cell 'sheet1'.C2>),
#  (<Cell 'sheet1'.A3>, <Cell 'sheet1'.B3>, <Cell 'sheet1'.C3>),
#  (<Cell 'sheet1'.A4>, <Cell 'sheet1'.B4>, <Cell 'sheet1'.C4>)]

以下のようなリスト内包表記を利用した関数を定義すると2次元タプルの各要素(Cellオブジェクト)から値を取得して2次元配列とすることができます。

def get_value_list(t_2d):
    return([[cell.value for cell in row] for row in t_2d])

l_2d = get_value_list(sheet['A2:C4'])

pprint.pprint(l_2d, width=40)
# [['one', 11.0, 12.0],
#  ['two', 21.0, 22.0],
#  ['three', 31.0, 32.0]]

iter_rows()と組み合わせると、1始まりの行番号・列番号で範囲を指定して2次元配列(リストのリスト)として取得する関数を定義できます。

def get_list_2d(sheet, start_row, end_row, start_col, end_col):
    return get_value_list(sheet.iter_rows(min_row=start_row,
                                          max_row=end_row,
                                          min_col=start_col,
                                          max_col=end_col))

l_2d = get_list_2d(sheet, 2, 4, 1, 3)

pprint.pprint(l_2d, width=40)
# [['one', 11.0, 12.0],
#  ['two', 21.0, 22.0],
#  ['three', 31.0, 32.0]]

Worksheetオブジェクトのvalues属性でシートのすべての要素のジェネレータを取得できます。

g_all = sheet.values

print(type(g_all))
# <class 'generator'>

pprint.pprint(list(g_all), width=40)
# [(None, 'A', 'B', 'C'),
#  ('one', 11.0, 12.0, 13.0),
#  ('two', 21.0, 22.0, 23.0),
#  ('three', 31.0, 32.0, 33.0)]

values属性の場合、元のExcelファイルによっては空白Noneの列や行が生じる場合もあるので気をつけてください。

セルの編集・追記

エクセルのセル指定文字列で指定したCellオブジェクトには新しい値を代入できます。

sheet['C1'] = 'XXX'
sheet['E1'] = 'new'

pprint.pprint(list(sheet.values), width=40)
# [(None, 'A', 'XXX', 'C', 'new'),
#  ('one', 11.0, 12.0, 13.0, None),
#  ('two', 21.0, 22.0, 23.0, None),
#  ('three', 31.0, 32.0, 33.0, None)]

cell()メソッドの場合は引数valueに新たな値を渡すとその値がセルに書き込まれる。

sheet.cell(row=2, column=5, value=14)

pprint.pprint(list(sheet.values), width=40)
# [(None, 'A', 'XXX', 'C', 'new'),
#  ('one', 11.0, 12.0, 13.0, 14),
#  ('two', 21.0, 22.0, 23.0, None),
#  ('three', 31.0, 32.0, 33.0, None)]

いずれの場合も、既にデータがあるセルに対しては上書き、データがないセルに対しては新たなセルの追加になります。 任意の位置のセルに2次元配列を書き込み 以下のような関数を定義すると、2次元配列(リストのリスト)を書き込むことができます。引数start_row, start_colに2次元配列が書き込まれる行番号と列番号を1始まりで指定します。

def write_list_2d(sheet, l_2d, start_row, start_col):
    for y, row in enumerate(l_2d):
        for x, cell in enumerate(row):
            sheet.cell(row=start_row + y,
                       column=start_col + x,
                       value=l_2d[y][x])

l_2d = [['four', 41, 42, 43], ['five', 51, 52, 53]]

write_list_2d(sheet, l_2d, 5, 1)

pprint.pprint(list(sheet.values), width=40)
# [(None, 'A', 'XXX', 'C', 'new'),
#  ('one', 11.0, 12.0, 13.0, 14),
#  ('two', 21.0, 22.0, 23.0, None),
#  ('three', 31.0, 32.0, 33.0, None),
#  ('four', 41, 42, 43, None),
#  ('five', 51, 52, 53, None)]

ワークシートの追加・コピー・削除

Workbookオブジェクトのcreate_sheet()メソッドで新たなワークシートを追加できます。引数に新たなシート名を指定します。

sheet_new = wb.create_sheet('sheet_new')

print(wb.worksheets)
# [<Worksheet "sheet1">, <Worksheet "sheet2">, <Worksheet "sheet_new">]

sheet_new['A1'] = 'new sheet!'

print(list(sheet_new.values))
# [('new sheet!',)]

ワークシートのコピーはcopy_worksheet()メソッド。引数に指定するのはシート名ではなくWorksheetオブジェクト。

sheet_copy = wb.copy_worksheet(wb['sheet1'])

print(wb.worksheets)
# [<Worksheet "sheet1">, <Worksheet "sheet2">, <Worksheet "sheet_new">, <Worksheet "sheet1 Copy">]

pprint.pprint(list(sheet_copy.values))
# [(None, 'A', 'XXX', 'C', 'new'),
#  ('one', 11.0, 12.0, 13.0, 14),
#  ('two', 21.0, 22.0, 23.0, None),
#  ('three', 31.0, 32.0, 33.0, None),
#  ('four', 41, 42, 43, None),
#  ('five', 51, 52, 53, None)]

ワークシートの削除はremove_sheet()メソッド。引数に指定するのはシート名ではなくWorksheetオブジェクト。

wb.remove_sheet(wb['sheet1 Copy'])

print(wb.worksheets)
# [<Worksheet "sheet1">, <Worksheet "sheet2">, <Worksheet "sheet_new">]

Excelファイルの書き込み(新規作成・上書き保存)

Workbookオブジェクトのsave()メソッドにパスを指定するとファイルとして保存されます。 新たなパスの場合は新規作成、既存のファイルのパスを指定すると上書き保存となります。上書きの場合、元のファイルのデータは削除されるので気をつけてください。

wb.save('data/dst/openpyxl_sample.xlsx')

既存のファイルに追記したい場合は、対象のファイルを読み込んで編集後、同じファイルパスに書き込み(保存)します。編集したデータで上書きされます。

Last Updated: 6/26/2019, 10:34:03 PM