Pythonライブラリ(openpyxl)によるExcelのセルの扱い方とその応用【徹底解説】

スポンサーリンク
Python外部ライブラリ(openpyxl)② openpyxl

前回に続きMS-Office ExcelをPythonで操作するライブラリとして「openpyxl」の解説をしていきます。

前回の記事では、ライブラリの概要の紹介とExcelファイルを新規作成・保存するといった「基本操作に関すること」とopenpyxlを使う上で必ず理解しておかなければならない「オブジェクト種類とその階層構造について」について取り上げました。

前回の記事はこちらになります。

「openpyxl」の基本を押さえてから以降の内容に進まれた方が理解しやすいと思います。また、重複する解説は割愛していますので、まずはこちらの関連記事を一読されることをお勧めします。

さて、連載2回目となる今回では、セル(Cellオブジェクト)の扱いについてさらに詳しく解説を進めていきます。

表計算ソフトであるExcelを使う主たる目的は、セルに入力されたデータを使って様々な処理(演算)を施こすことで分析・統計データとしてまとめることだと思います。

そこで今回は、openpyxlを使ってセルの値をどのように「取得して」・「処理」していくのか、具体例を示しながら解説します。

Excelのその他の重要な機能である、セルの書式(スタイル)設定「フィルタ・並び替え」「グラフ化」などはは別記事でまとめる予定です。本記事の解説範囲と今後の連載予定は次のとおりです。

Part2_目次_改
図1 記事の解説範囲

この記事を読むことでopenpyxlによるExcel操作に関する、次のようなことが「できる・わかる」ようになりますので最後までお付き合いください。

➀. セル(Cellオブジェクト)の追加方法とその有効範囲について

➁. セル(Cellオブジェクト)の取得と値の設定について

➂. セルの結合や数式の挿入・セル範囲の定義付け について

本サイトでの紹介例は一例です。また、関数などの省略可能なオプション引数などについては割愛していますので、詳細や不明点などは必要に応じて公式サイトなどを参照してください。

記事内で動作確認した開発環境とバージョン情報は次のとおりとなります。異なる環境・バージョンのライブラリを使う際はこの点、ご留意ください。

・Python 3.7.6(64bit)

・openpyxl 3.0.3

・JupyterNotebook 6.0.3

・Microsoft Office Personal 2016

それでは、次節からは具体的な各種オブジェクトの扱い方について詳しく解説していきます。

スポンサーリンク

1. セル(Cellオブジェクト)の操作

前回の記事でもセル(Cellオブジェクト)について少し触れましたが、セルとはExcelを操作するための最も基本となる最小単位となります。

そのため、openpyxlにはセルの操作に関連するオブジェクトやメソッド・プロパティなどが多数提供されており、ユーザーはそれらを理解し使いこなす必要があります。

本節では、Cellオブジェクトについて、「追加」「取得と操作方法」「セル範囲の定義と管理」といった前回の記事からさらに深堀りして解説していきます。

1.1 Cellオブジェクトの追加と取得

openpyxlでデータ処理を行うにはまずはWorksheetオブジェクト(Row/Columnオブジェクト)からCellオブジェクトを取得して値の設定や読取り、その他の操作を行います。(後述)

そのため、Excelファイルを新規作成した場合(Cellオブジェクトない場合)はオブジェクトを「新規追加」する必要があります。また既存ファイルを読込む場合などで既にセル何らかの値が入っている場合は、そこからオブジェクトを「取得」することができます。

Cellオブジェクトの追加と取得フロー
図2 Cellオブジェクトの追加と取得

openpyxlにてセルの追加と取得をするには様々な方法(書式)があります。前回の記事にも纏めましたが、肝になるところなので本節でもう一度整理したいと思います。

1.1.1 単一セル(Cellオブジェクト)の追加(取得)

はじめに単一のセル(Cellオブジェクト)を追加する方法について解説します。

書式は以下のように「インデックス指定」によるものと「cell()メソッド」によるものの2つがあり、戻り値はいずれもCellオブジェクトです。

Cellオブジェクト

➀. Worksheetオブジェクト[cell_adr]

インデックス: cell_adr:セルアドレス方式(‘A1’,’B2’など)で指定する

戻り値:Cellオブジェクト


➁. Worksheetオブジェクト.cell(row, column, val)

引数1:row: A1セルを「1」とする行番号(整数)を指定

引数2:column: A1セルを「1」とする列番号(整数)を指定 ※

引数3:val:セルに設定する値(データ)を指定する(オプショナル引数)

(※ 列名’A’, ’B’などと指定するとエラーになるので注意)

戻り値:Cellオブジェクト

追加(取得)したセルは前述したとおりExcelを構成する最小構成単位ですので、直接もしくはvalueプロパティをつかって値(データ)を設定できます。また、➁の cell()メソッド の引数val値を直接指定することも可能です。

List1にプログラム例を示します。

[A]は「インデックス指定」、[B]は「Cell()メソッド」の使用例です。

from openpyxl import Workbook

wb=Workbook()  
ws=wb.active

# [A] ------------------------------------------------------------------
# Worksheetオブジェクトのインデックス指定による
# Cellオブジェクトの追加と値の設定

ws['B4']
print(ws['B4'])        # >><Cell 'Sheet'.B4> Cellオブジェクトを追加
print(ws['B4'].value)  # >>None デフォルトの値は空(None)

ws['B4']=4            # 'B4'セルに値を設定(valueプロパティ無し)
print(ws['B4'].value) # >>4 'B4'セルに値(4)を確認

ws['B5'].value=5      # 'B5'セルに値を設定(valueプロパティ有り)
print(ws['B5'].value) # >>5 'B5'セルに値(5)を確認


# [B] ------------------------------------------------------------------
# Worksheetオブジェクトのcell()メソッドによる
# Cellオブジェクトの取得と値の設定

ws.cell(row=4, column=2).value=10        # 名前付き引数指定・valueプロパティによる値の設定
print(ws.cell(row=4, column=2).value)    # >>10 セルの値を確認 

ws.cell(row=4, column=3, value=20)       # 引数valueでセルの値も同時に設定できる
print(ws.cell(row=4, column=3).value)    # >>20 セルの値を確認

ws.cell(4, 4, 30)                        # 順番指定引数でも当然問題ない
print(ws.cell(4, 4, 30).value)           # >>30 セルの値を確認

1.1.2 複数セル(Cellオブジェクト)の追加(取得)

単一セルだけではなく、一度に複数のセルを追加する方法も幾つか用意されています。セル範囲(行単位・列単位・もしくはその両方)を指定することで効率的にCellオブジェクトを追加することができます。

データの処理形態に応じて各メソッドを以下のように使い分けるとよいでしょう。

➀ 特定の位置からのセル範囲にオブジェクトを追加する場合

インデックス指定iter_rows()iter_cols()メソッド

➁ A1セルから下方へ(行方向へ)行単位でセルを追記していく場合

append()メソッド

インデックス_iter_row_appendメソッドの使い分け
図3 メソッドの使い分け

各メソッドの書式は次のとおりで、いずれの書式も戻り値はCellオブジェクトを要素とするコンテナ(タプル・ジェネレータ)となります。よって、コンテナのまま一度に複数のCellオブジェクトに値(データ)を設定することはできません

コンテナから一つずつ要素(Cellオブジェクト)を取り出して設定する必要があります。これに関しては<1.1.4>で後述します。

Cellオブジェクト

➀. Worksheetオブジェクト[range_adr]

インデックス: range_adr:セル範囲をアドレス方式(“A1″:”C3” 又は“A1:C3”)で指定する

戻り値:Cellオブジェクトのコンテナ(タプル)


➁. Worksheetオブジェクト.append(row_data)

引数1:row_data:1行分のセルの値を要素とするリスト

戻り値:なし


➂-1. Worksheetオブジェクト.iter_rows(min_row, max_row, min_col, max_col)

➂-2. Worksheetオブジェクト.iter_cols(min_row, max_row, min_col, max_col)

引数1:min_row:イテレーションを開始する行 以下全てオプショナル引数

引数2:max_row:イテレーションを終了する行

引数3:min_col:イテレーションを開始する列

引数4:max_col:イテレーションを終了する列

戻り値:Row/Columnオブジェクト(Cellオブジェクトのタプル)のジェネレーター

List2にプログラム例を示します。

[A]はインデックス指定、[B]はappend()メソッド、[C][D]はiter_**()メソッドを使用しています。

また、[A]の戻り値はタプル、[C][D]の戻り値はジェネレータとなります。

from openpyxl import Workbook

wb=Workbook()  
ws=wb.active

# [A]------------------------------------------------------------------
# Worksheetオブジェクトのインデックス指定による
# Cellオブジェクトの追加と値の設定

print(ws["A1:C3"])
print(ws["A1":"C3"])

# Cellオブジェクトを追加と同時に
# Cellオブジェクトを要素としたタプルを追加(複数行ある場合は2次元タプルとなる)
# >>((<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, … <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>))


# [B]------------------------------------------------------------------
# append()メソッドによるCellオブジェクトの追加
# 最終行に値を設定する(メソッドの戻り値はなし)

data = [1, 2, 3, 4]
ws.append(data)


# [C]------------------------------------------------------------------
# iter_rows()メソッドによるCellオブジェクトの追加

# Rowオブジェクトのジェネレータが生成される
print(ws.iter_rows(min_row=6, max_row=7, min_col=3, max_col=5))
# >><generator object Worksheet._cells_by_row at 0x0000025205EC58C8>


# [D]------------------------------------------------------------------
# iter_cols()メソッドによるCellオブジェクトの追加

# Columnオブジェクトのジェネレータが生成される
print(ws.iter_cols(min_row=6, max_row=7, min_col=7, max_col=9))
# >><generator object Worksheet._cells_by_col at 0x0000025205EC58C8>


wb.save('Multi_Cell_gen.xlsx')

1.1.3 Cellオブジェクトの追加範囲

Cellオブジェクトの追加には気を付けるべきことがあります。それは、基準となる「A1セル」と新規追加したCellオブジェクト間の空間には自動的にNull文字(空)を値としたCellオブジェクトが生成(追加)されるといったものです。

以下(図3)に具体例を示します。cell()メソッド で「C3セル」にCellオブジェクトを追加(値はセットしてもしなくてもどちらでも可)したとします。すると、A1セル~C3セルの間の空白セルも自動的にNull文字を値としたCellオブジェクトが追加されることになります。自動的に追加される点がポイントです。

オブジェクト取得の有効範囲
図3 Cellオブジェクトの追加範囲

これにより、次節以降で説明するCellオブジェクト取得専用のメソッドやプロパティで、Null文字を含めたこの範囲の全てのCellオブジェクトを取得することができます。

言い換えると、新規ファイルにC3セルのCellオブジェクトを追加したとすると、A1~C2,A3,B3のCellオブジェクトを別途追加することなく、直ぐにそれらにアクセス・値の取得・設定ができるようになります。

1.1.4 Celllオブジェクトの取得

これまでに、Cellオブジェクトを追加(取得)する方法について解説しました。

Cellオブジェクトの取得だけに特化した便利な書式(インデックス指定・プロパティ)が用意されていますので幾つかを紹介します。

インデックスを使って個々のCellオブジェクトを取得する書式は以下のようなものがあります。

インデックスの基底は「0」と「1」が混在しているので注意してください。

Cellオブジェクト(単一取得)

➀. Worksheetオブジェクト[row_index][col_index]

インデックス1: row_index:A1セルを「1」とする行番号(整数)を指定

インデックス2: col_index:A1セルを「1」とする列番号(整数)を指定 ※

(※ 列名’A’, ’B’などと指定するとエラーになるので注意)

戻り値:Cellオブジェクト


➁. Rowオブジェクト[row_index]

インデックス:row_index:A1セルを「0」とする行番号(整数)を指定 ※

(※タプルのインデックスなので0始まりなので注意)

戻り値: Cellオブジェクト


➂. Columnオブジェクト[col_index]

インデックス:col_index: A1セルを「0」とする列番号(整数)を指定 ※

(※タプルのインデックスなので0始まりなので注意)

戻り値: Cellオブジェクト

一度に複数のCellオブジェクト(Range)を取得するには次のようなプロパティが用意されています。

取得できるオブジェクトの範囲は、「A1セル」からの値(Noneを含めて)がセットされている最終行・最終列までの全ての範囲が対象となります。

<1.1.2項>のようにタプル形式のコンテナが戻り値になります。

Cellオブジェクト(複数取得)

➀. Worksheetオブジェクト[cell_range]

引数: cell_range : cell_range : セルの始点:セルの終点(‘A1:C3’といったように)アドレス範囲指定する

戻り値: Rowオブジェクトのタプル

➁. Worksheetオブジェクト.rowsプロパティ


戻り値: Rowオブジェクト(Cellのタプル形式)のジェネレータ

➂. Worksheetオブジェクト.columnsプロパティ

戻り値: Columnオブジェクト(cellのタプル形式)のジェネレータ


➃. Worksheetオブジェクト.valuesプロパティ

戻り値:全Rowオブジェクト(Cellの値のタプル形式)のジェネレータ

実行例は前回の記事を参照してください。

関連記事[Rowオブジェクト]> <関連記事[Columnオブジェクト]

1.2 セルにデータを設定する

Excelを使う最終目的はデータ分析やグラフや表などで可視化することですので、ワークシートに必要な値(データ)を入力しなければなりません。

本節では、<1.1節>で追加・取得したCellオブジェクトに具体的な値(数値や文字列、数式)を設定する方法を解説します。

さらにExcel作業でよく使う「複数セルを結合する」、「セル範囲に名前を付けて管理する」といった操作についても本節で取り上げます。

1.2.1 データの設定

Cellオブジェクトに値を設定するには valueプロパティ を使うことになります。<1.1.2節>や<1.1.4節>でも解説しましたが、複数のCellオブジェクトのコンテナであるRow/Column/Range(セル範囲)オブジェクトにはvalueプロパティやそれに近い機能は提供されておらず、個々のCellオブジェクトにアンパック(分解)した上で「valueプロパティ」を適用することになります。(図4)

Cellオブジェクトのアンパック
図4 セルにデータを設定

文章だけでは理解しずらいので、実際にセルに値を設定する簡単なコード例を紹介します。

for…in…:構文を2重に使ってコンテナの要素をCellオブジェクトにまで分解した後、valueプロパティで値を設定しています。

from openpyxl import Workbook

wb=Workbook()  
ws=wb.active

#------------------------------------------------------------------
# Worksheetオブジェクトのインデックス指定による
# Cellオブジェクトの追加と値の設定

# 個々のセルに分解してセルの値を設定する
for row in ws["A1:C3"]:
    for cell in row:
        cell.value = 1


#------------------------------------------------------------------
# append()メソッドによるCellオブジェクトの追加
# 最終行に値を設定する(メソッドの戻り値はなし)

data = [1, 2, 3, 4]
ws.append(data)


#------------------------------------------------------------------
# iter_rows()メソッドによるCellオブジェクトの追加        

for row in ws.iter_rows(min_row=6, max_row=7, min_col=3, max_col=5):
    print(row)
    # >>(<Cell 'Sheet'.C6>, <Cell 'Sheet'.D6>, <Cell 'Sheet'.E6>)
    # >>(<Cell 'Sheet'.C7>, <Cell 'Sheet'.D7>, <Cell 'Sheet'.E7>)
    
    # 個々のセルに分解してセルの値を設定する
    for cell in row:
        cell.value = 'iter_rows'


#------------------------------------------------------------------
# iter_cols()メソッドによるCellオブジェクトの追加

for col in ws.iter_cols(min_row=6, max_row=7, min_col=7, max_col=9):
    print(col)
    # >>(<Cell 'Sheet'.G6>, <Cell 'Sheet'.G7>)
    # >>(<Cell 'Sheet'.H6>, <Cell 'Sheet'.H7>)
    # >>(<Cell 'Sheet'.I6>, <Cell 'Sheet'.I7>)
    
    # 個々のセルに分解してセルの値を設定する
    for cell in col:
        cell.value = 'iter_cols'

<List3>の実行結果は以下のようになりました。詳細は図5のコメントを参照ください。

Cellオブジェクトに値を設定
図5 List3の実行結果

1.2.2 数式の設定

CellオブジェクトにExcel関数を適用することもできます。通常のExcel関数と同様に=(イコール記号)に続けて計算式(Operator)を記述します。そして全体の式を “(ダブルクオーテーション) もしくは

‘(シングルクオーテーション) で囲みます。(式中に”(ダブル)が含まれている場合は全体を’(シングル)で囲みます。)

そして最後にそれをCellオブジェクトに設定します。この際、「valueプロパティ」の有無は不問となります。

コード例を<List4>に示します。List4では、valueプロパティ無しでExel関数を設定しています。絶対参照も同様に使えます。

from openpyxl import Workbook

wb=Workbook()
ws=wb.active


# Excel関数をセルに適用する

# 計算式を”(ダブルクオーテーション)で囲む
ws["A2"] = "=AVERAGE(C1:C5)"

# 計算式を’(シングルオーテーション)で囲む
ws["A3"] = '=AVERAGE(C1:C5)'

# 参照先は絶対参照でも問題なし
ws["A4"] = "=AVERAGE($C$1:$C$5)"

# 計算式中に文字列などで"を使っている場合は計算式
# を'(シングルクオーテーション)で囲む
ws["A1"] = '=IF(B1>=50,"50以上","50未満")'


wb.save('formula.xlsx')

1.2.3 セルの結合

複数のセルを結合して1つのセルにマージすることも簡単にできます。結合はWorksheetオブジェクトの「merge_cells()メソッド」、逆に結合を解除するときは「unmerge_cells()メソッド」を使います。引数には結合範囲を’B2:F4’のようにA1アドレス指定もしくは、行列数で指定します。

また、結合したセルに値やスタイル書式を適用する際は、結合領域の最左上セルに適用させます。それ以外のセルに適用するとエラーとなります。

さらに、罫線(Borderオブジェクト)は周囲のセル一つ一つに対して適用する必要がありますので注意してください。

それでは、実際のコードで確認してみましょう。

[A]ではA1アドレス指定でセル範囲を結合しB2セルに書式を設定しています。また[B]では行・列それぞれの始点(引数:start_row, start_column)と終点(引数:end_row, end_column) を整数で指定しています。

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

#(A) merge_cells()メソッドによる複数セルの結合(A1アドレス指定)
ws.merge_cells('B2:F4')

# 結合セル領域の左上のCellオブジェクト(B2)の取得
top_left_cell = ws['B2']

# 結合セル全体の装飾
# 左上のCellオブジェクト(B2)にスタイル書式を設定することで結合セル全体に装飾される
# B3, C2など左上以外のセルを指定するとエラーになるので注意
# また、枠線などは一度に設定できずに個々のセル毎に設定していく

top_left_cell.value = "Merged Cell!"
top_left_cell.alignment = Alignment(horizontal="center", vertical="center")

#(B) merge_cells()メソッドによる複数セルの結合(行列数指定)
ws.merge_cells(start_row= 7, end_row=9, start_column=2, end_column=3)


wb.save('sample_Merged-Cell.xlsx')

<List5>の実行結果は以下のようになりました。

merge_cellsの結果
図6 List5の実行結果

1.2.4 セルの範囲に名前を付ける

特定のセル範囲(Rangeオブジェクト)に名前を定義して管理できます。セル範囲の定義と関連する属性は「DefinedNameオブジェクト」で管理されます。

書式は次のとおりで、引数に様々な属性を持たせることができます。

DefinedNameオブジェクト

from openpyxl import workbook

workbook.defined_name.DefinedName(name, attr_text)


引数は全てオプショナル引数で初期値は’None’以下以外にもComment, descriptionなど多数あるため(名前付き引数で指定した方が良い)

引数:name:定義名を文字列で指定

引数:attr_text:定義範囲 (例 ‘Sheet1!$E$12:$G$14’)

戻り値:DefinedNameオブジェクト

もちろん、セル範囲は複数持たせることができ、DefinedNameListオブジェクトコレクションで要素を「DefinedNameオブジェクト」として管理します。

各要素の取得は definedNameプロパティ コレクションへの追加は append()メソッド にて行います。

DefinedNameListオブジェクト
図7 DefinedNameオブジェクトの取得と追加

それでは、実際のコードで確認してみましょう。

コードの概要はExcelファイルを読込んで定義済みのセル範囲を確認[A]した後に、新規にセル範囲の定義を追加[C]しています。<List7>

from openpyxl import workbook
from openpyxl import load_workbook

wb=load_workbook('sample_Defined_Names.xlsx')

#(A)------------------------------------------------------------------------------
# オブジェクトの種類を確認
print(type(wb.defined_names))                   # >><class 'openpyxl.workbook.defined_name.DefinedNameList'>
print(type(wb.defined_names.definedName))       # >><class 'list'>
print(type(wb.defined_names.definedName[0]))    # >><class 'openpyxl.workbook.defined_name.DefinedName'>


#(B)------------------------------------------------------------------------------
# 定義したセル範囲名と領域のアドレスを取得

for defined in wb.defined_names.definedName:
    print(defined.name)          # >> my_range1
    print(defined.attr_text)     # >> Sheet1!$C$6:$E$8
    
    # その他、DefinedNameオブジェクトには数多くの属性が用意されている
    # Comment, description, hidden … いずれの属性も初期値は'None'


#(C)------------------------------------------------------------------------------
# 新規のセル範囲名の定義を追加

# DefinedNameオブジェクトの生成
new_range=workbook.defined_name.DefinedName(name='new_range', attr_text='Sheet1!$E$12:$G$14')
print(type(new_range))  # >><class 'openpyxl.workbook.defined_name.DefinedName'>

# DefinedNameListオブジェクトのappend()メソッドで新規定義した
# セル範囲(DefinedNameオブジェクト)を追加
wb.defined_names.append(new_range)


#(D)------------------------------------------------------------------------------
# 定義したセル範囲名と領域のアドレスを取得

for defined in wb.defined_names.definedName:
    print(defined.name)          # >> my_range1,          new_range
    print(defined.attr_text)     # >> Sheet1!$C$6:$E$8    Sheet1!$E$12:$G$14
    
    # 新たに追加したセル範囲名(new_range)が出力される

wb.save('sample_Defined_Names.xlsx')

List7の実行結果は以下のようになり、セル範囲(new_range)が追加されました。

セル範囲の定義付け結果
図8 List6の実行結果

2. まとめ

いかがでしたでしょうか?

今回はMS-OfficeのExcelを操作する外部ライブラリとして「openpyxl」を取り上げ、セル(Cellオブジェクト)の扱い方を中心に解説してきました。

Excelを使ったデータ処理は、セルを経由して行われることが基本となりますから、本稿の解説内容をぜひ活用していただきたいと思います。

ここで、この記事内容のポイントをまとめておきます。

➀. セルに値を設定するためには、Cell()メソッドなどを使ってCellオブジェクトを追加・取得する必要がある。

➁. 複数のセル範囲(Range)に一括でデータや書式を設定することができないため、個々のセルに分解して値を設定・取得する必要がある。

➂. セルに「数式(Excel関数)を設定する」、「結合する」、「セル範囲に名前を定義して管理する」こともできる。


さて次回は、「セルの書式設定」について取り上げます。セルに書式を施すことで、フォントや塗り潰し・装飾など見栄えを整えることができるようになります。

ぜひ、次の記事も参考にして頂けたら幸いです。リンク先はこちらになります。↓

最後までお読みいただきありがとうございました。

タイトルとURLをコピーしました