note.nkmk.me

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

Date: 2018-05-06 / tags: Python, Excel

Pythonのライブラリopenpyxlを使うとExcelファイル(.xlsx)を読み書き(入出力)できる。使い方を説明する。

BitBucketのレポジトリと公式ドキュメントは以下のリンクから。

PythonでExcelファイルを扱うライブラリの違いや使い分けなどは以下の記事を参照。

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

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

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

openpyxlのインストール

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

$ pip install openpyxl

Excelファイルの読み込み

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

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

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

sheet1sheet2の二つのワークシートを持つ。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')

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

スポンサーリンク
シェア
このエントリーをはてなブックマークに追加

関連カテゴリー

関連記事