Pythonスキルの習得

【 Python ✖️ Excel 】OpenPyXLとPandasによる、頻繁に使用する操作 使い方まとめ

【 Python ✖️ Excel 】OpenPyXLとPandasによる、頻繁に使用する操作 まとめ
​

こんにちは、Zero-Cheeseです。

今回は、PythonによるExcel操作で、よく使用する操作のご紹介をします。

対象者：

  • Python初心者以上の方

本記事で紹介している内容

  • Excelファイルの読込み処理
  • 読み込んだファイルに、書込み処理
  • （フォント、セルの書式も、代表的なものをご紹介）
  • Excelファイルの保存処理

目次
  1. Excelを操作するための、ライブラリ紹介
  2. ライブラリのインストール
  3. 「OpenPyXL」の使い方
  4. 「Pandas」の使い方（Excelに関係する部分のみ）
  5. さいごに

Excelを操作するための、ライブラリ紹介

よく利用される、2つのライブラリを紹介します。

「OpenPyXL」

ExcelファイルをPythonから、操作するためのライブラリです。

  • Excelファイルの、特定のワークシート、セルを指定して読み書き
  • Excelの書式まで読み書き可能（フォント、背景色等も対応）
  • グラフも作成可能（本記事では、割愛）

現在のExcelファイル形式（拡張子：.xlsx）は、「Office Open XML」という規格に準拠しています。

「OpenPyXL」は、「Office Open XML」に準拠したファイルを、扱う事ができます。

つまり

  • LibreOffice Calc（無料です。）
  • Apache OpenOffice Calc（無料です。）

で作成したファイルも、扱う事ができます。

「Pandas」

Pythonでデータ分析の、必須ライブラリです。

主なメリットは以下の通りになります。

  • ほとんどのデータ分析が可能
  • Excelでは処理オーバーなデータ量でも、メモリが許す限り処理可能
  • 表計算に比べ、処理速度が早い
  • 機械学習の前処理としても、よく利用される
  • Excelの読み書きは可能だが、書式付き書き込み等は、不可

Excelに書き込む際、書式にこだわる場合、「OpenPyXL」を使用するのが、オススメです。

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

どちらも、pipにて、インストール可能です。

# 「OpenPyXL」ライブラリのインストール
pip install openpyxl

# 「Pandas」ライブラリのインストール
pip install pandas

pipに関してまして、詳細に興味がありましたら、下記記事までどうぞ。

【Python】pipの使い型入門 - コマンドライン、Anaconda、PyCharmからの操作方法を解説 -
【Python】pipの使い方入門 - コマンドライン、Anaconda、PyCharmからの操作方法を解説 -Python入門者を対象に、pipの使い型を解説した記事です。 コマンドライン、Anaconda、PyCharmからの操作方法を、わかりやすく解説しました。 ...

「OpenPyXL」の使い方

「OpenPyXL」でのExcel操作、全体イメージを把握する

主なイメージ

  • Excelファイルを読込み、「Workbook」オブジェクトを取得
  • 「Workbook」オブジェクトから、「WorkSheet」オブジェクトを取得
  • 「WorkSheet」オブジェクトから、列単位で操作したい場合は「Column」オブジェクト、行単位は「Row」オブジェクトを取得
  • 1つのセルへの操作は、「Cell」オブジェクトを使用する。（「WorkSheet」、「Column」、「Row」オブジェクトから取得可能）

以下、ご紹介したイメージの順番で、ご紹介しています。

そして最後に、Excelファイルへの保存方法を、解説しています。

Excelファイルの読込んで、「Workbook」オブジェクトを取得

下記コードで、取得できます。

import openpyxl as px

wb = px.load_workbook('excel_file.xlsx')
print(type(wb))

# 下記コードは、最後に記載
wb.close()

実行結果
<class 'openpyxl.workbook.workbook.Workbook'>

「Workbook」オブジェクトから、「WorkSheet」オブジェクトを取得

読み込んだExcelファイルの、シート名を取得する

シート名をリストとして、取得します。

例）下記のExcelファイルを読込んだ場合：

print(wb.sheetnames)
# ⬆︎ 出力結果（例）
# ['Sheet1', 'Sheet2', 'Sheet3']

「WorkSheet」オブジェクトの取得

◆ 「シート名」を指定して、取得する場合
ws = wb['Sheet1']

◆ 「インデックス」を指定して、取得する場合

Excel「シートタブ」は、左から 0, 1, 2 とインデックスが振られています。

# 下記は、'Sheet1'を選択している
ws = wb.worksheets[0]

◆ 「Active」な「シート」を取得する

Excelを開いた時点（「Workbook」オブジェクトを取得した時点）で、1つの「シート」が、「Active」な状態になっています。

「Active」な「シート」は、最後にExcelを保存した際に、作業していた「シート」になります。

ws = wb.active

「WorkSheet」オブジェクトから、「Column」オブジェクトの取得 ➡︎「Cell」オブジェクトの取得

範囲を指定せずに、「Column」オブジェクトを取得するケース

下記のように、B3〜B7まで入力されている、Excelを読み込んだとします。

「Column」オブジェクトの取得には、

  • ws.columns

を使用します。

「Column」オブジェクトは、generatorです。

generatorをご存知ない方へ：

本記事の範囲では、配列みたいなモノと考えて頂いて大丈夫です。

「Colunn」オブジェクトから、「Cell」オブジェクトを取得するコードは、以下の通りです。

for col in ws.columns:
    print(col)

実行結果
# (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>, <Cell 'Sheet1'.A4>, <Cell 'Sheet1'.A5>, <Cell 'Sheet1'.A6>, <Cell 'Sheet1'.A7>)
# (<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)

A1セルから記載されているセルまで、列単位で、「Cell」オブジェクトを要素とする、タプルが取得できます。

つまりこの場合、

  • A1〜A7の各セル
  • B1〜B7の各セル

が、タプルで取得されます。

範囲を指定して「Column」オブジェクトを取得するケース

例えば、B3 〜 C7 範囲を指定するには、

  • ws.iter_cols(min_row=3, min_col=2, max_row=7, max_col=3)

のように、指定します。（generatorが返ります。）

（インデックスが、1から開始になっています。）

  • min_row=3 → 3行目
  • min_col=2 → B列

で、B3〜 という意味になります。（maxも同様の考え方）

実際の使い方（例）はこちらです。

sheet_range = ws.iter_cols(min_row=3, min_col=2, max_row=7, max_col=3)

for col in sheet_range:
     print(col)

実行結果
#(<Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)
#(<Cell 'Sheet1'.C3>, <Cell 'Sheet1'.C4>, <Cell 'Sheet1'.C5>, <Cell 'Sheet1'.C6>, <Cell 'Sheet1'.C7>)

「WorkSheet」オブジェクトから、「Row」オブジェクトの取得 ➡︎「Cell」オブジェクトの取得

範囲を指定せずに、「Row」オブジェクトを取得するケース

「Column」オブジェクトと同じ、使用方法になります。

下記のように、B2〜F2セルまで入力されている、Excelを読み込んだとします。

この場合、「WorkSheet」オブジェクトから「Row」オブジェクト、「Cell」オブジェクトを取得するコードは、以下の通りになります。

for col in ws.rows:
    print(col)

実行結果
#(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>, <Cell 'Sheet1'.D1>, <Cell 'Sheet1'.E1>, <Cell 'Sheet1'.F1>)
#(<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>, <Cell 'Sheet1'.E2>, <Cell 'Sheet1'.F2>)
#(<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>, <Cell 'Sheet1'.E3>, <Cell 'Sheet1'.F3>)

範囲を指定して「Row」オブジェクトを取得するケース

例えば、B3 〜 C7 範囲を指定するには、

  • ws.iter_rows(min_row=3, min_col=2, max_row=7, max_col=3)

のように、指定します。（generatorが返ります。）

（インデックスが、1から開始になっています。）

  • min_row=3 → 3行目
  • min_col=2 → B列

で、B3〜 という意味になります。（maxも同様の考え方）

「Row」オブジェクト取得の場合、下記でもOKです。

  • ws[‘B3′:’C7’]

ただしこちらは、タプル型が返ります。（iter_rowsは、generatorが返ります。）

実際の使い方（例）はこちらです。

sheet_range = ws.iter_rows(min_row=3, min_col=2, max_row=7, max_col=3)
# sheet_range = ws['B3':'C7']  でもOK

for row in sheet_range:
    print(row)

実行結果
#(<Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>)
#(<Cell 'Sheet1'.B4>, <Cell 'Sheet1'.C4>)
#(<Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>)
#(<Cell 'Sheet1'.B6>, <Cell 'Sheet1'.C6>)
#(<Cell 'Sheet1'.B7>, <Cell 'Sheet1'.C7>)

「WorkSheet」オブジェクトから、直接「Cell」オブジェクトを取得する方法

Excelの番地を指定して、取得する方法

例）B2を読込みたい場合：

cell_B2 = ws['B2']
# 下記でもOK　（列と行を数字で指定）
# cell_B2 = ws.cell(column=2, row=2)

print(cell_B2)

上記コード中の、ws.cell(column=2, row=2)の意味ですが、2行目２列目のセル、つまりB2セル）を表しています。
実行結果
# <Cell 'Sheet1'.B2>

Cellオブジェクトに対する操作

前章で紹介したコードを、実行している前提（「cell_B2」という変数名で、「Cell」オブジェクトを取得している）とします。

値を取得する

  • [Cellオブジェクト].value

で取得可能です。

# 変数 a に代入する場合
a = cell_B2.value

セルが空白の場合

  • None

になります。

Excel座標の取得

  • [Cellオブジェクト].coordinate

で取得可能です。

取得結果は、セル座標（「B2」等の文字列）になります。

# 変数 a に代入する場合
a = cell_B2.coordinate

Excel座標（列番号）の取得

  • [Cellオブジェクト].column

で取得可能です。

取得結果は、列番号(1から始まる数値)になります。

# 変数 a に代入する場合
a = cell_B2.column

Excel座標（行番号）の取得

  • [Cellオブジェクト].row

で取得可能です。

取得結果は、行番号(1から始まる数値)になります。

# 変数 a に代入する場合
a = cell_B2.row

値を書き込む

  • [Cellオブジェクト].value = [代入したい値、文字列]

で代入可能です。

# 　数字の　5　を代入したい場合
cell_B2_value = 5
# 文字列　aaa を代入したい場合
cell_B2_value = 'aaa'

Excelファイルの保存処理をしないと、上記の変更は、保存されません。

セルのフォントを指定したい場合（文字サイズ、文字色 等も含む）

  • [Cellオブジェクト].font = [Fontオブジェクト]

で指定できます。

「Font」オブジェクトは、 importする必要があります。

from openpyxl.styles import Font

# フォント名：メイリオ、フォントサイズ：16、太字、赤色　にしたい場合
font = Font(name='メイリオ', size=16, bold=True, color='ff0000')
cell_B2.font = font

セルの書式を指定したい場合

  • [Cellオブジェクト].number_format = [表示形式文字列]

で指定できます。

# 下記をimportしている前提の、コードです。
# import openpyxl as px

# 文字列に指定したい場合
cell_B2.number_format = px.styles.numbers.FORMAT_TEXT
# 整数に指定したい場合
cell_B2.number_format = px.styles.numbers.FORMAT_NUMBER
# 小数点（２桁表示）に指定したい場合
cell_B2.number_format = px.styles.numbers.FORMAT_NUMBER_00
# %（小数点なし）に指定したい場合
cell_B2.number_format = px.styles.numbers.FORMAT_PERCENTAGE
# %（小数点 2桁）に指定したい場合
cell_B2.number_format = px.styles.numbers.FORMAT_PERCENTAGE_00
# 日時に指定したい場合（yyyy-mm-dd 表示）
cell_B2.number_format = px.styles.numbers.FORMAT_DATE_YYYYMMDD2

その他の書式に関しては、こちらにソースコードが公開されています。

ご参照ください。

Excelファイルの保存

「Workbook」オブジェクトを使用します。

今まで紹介した「Cell」オブジェクト等を使って、変更した内容は、すべて反映されます。

上書き保存をする場合

読み込んだファイル名と同じ名前を、引数に指定します。

# 'excel_file.xlsx'で読み込んだ場合
wb.save('excel_file.xlsx')

別名保存をする場合

読み込んだファイル名と異なる名前を、引数に指定します。

# 'excel_file.xlsx'で読み込んだ場合
# 'test.xlsx'で保存したいケース
wb.save('test.xlsx')

「Pandas」の使い方（Excelに関係する部分のみ）

Pandasによるデータ分析の紹介は、膨大な量となってしまうので、別記事で扱うようにします。

ここでは、Pandasによる、Excelファイルの読書き方法をご紹介します。

Pandasによる、Excelファイルの読込み

  • pandas.read_excel()

で、読込む事ができます。

pandas.DataFrameとして、読み込まれます。

代表的なコードを記します。

import pandas as pd

# excel_file.xlsx を読込む場合
df = pd.read_excel('excel_file.xlsx')

# シート名を指定して読込む場合
df = pd.read_excel('excel_file.xlsx', sheet_name='Sheet1')

# DataFrameの、column, index　に該当する、Excelの行、列を指定して読込む場合
# DataFrameのcolumn → 下記メソッドの header (0始まり)
# DataFrameのindex → 下記メソッドの index_col  (0始まり)
df = pd.read_excel('excel_file.xlsx', sheet_name='Sheet1',
                   header=0, index_col=0)
上記コードの最終行、header=None, index_col=None を指定した場合：
DataFrameのcolumn, indexは、読込んだexcelファイルの特定の行、列が使用されず、0からの始まる連番となります。

Pandasによる、Excelファイルへの書込み

dfという変数名に、pandas.DataFrame型で、値が代入されている場合、

  • df.to_excel( )

で保存する事ができます。

代表的なコードを記します。

# シート名を指定する場合 （省略すると、Sheet1 になる。） 第1引数は、ファイルパスを指定
df.to_excel('dst/pandas2excel.xlsx', sheet_name='sheet_name')

# DataFrameのindex, columnを出力する必要のない場合
df.to_excel('dst/pandas2excel.xlsx', sheet_name='sheet_name',
            index=False, header=False)
Excelへの書込みにおいては、OpenPyXLを使った方が、細かい設定をする事ができます。

さいごに

Excelで、プログラムを走らせるといったら、マクロが代表的だと思います。

しかしマクロで、複雑な算術処理を実行したい場合、とたんにコードが複雑化する傾向にあると思います。

特に

  • 複雑な統計処理
  • 機械学習

みたいな事例になると、Pythonを利用した方が、簡単にコードを記載する事ができます。

Pythonは、算術処理系のライブラリが豊富なのが、メリットだと思っています。

もしプログラミングで副業に興味がある場合、Excelに関する案件は、比較的多いように思われます。

副業で稼ぐための、Udemyおすすめ講座も、記事にしています。

ご興味がありましたら、併せてどうぞ。

フリーランス、副業で稼ぐためのスキル習得　Udemy おすすめプログラミング講座 - Python編 - | 副業実績 & E資格者が厳選
フリーランス、副業で稼ぐためのスキル習得　Udemy おすすめプログラミング講座 - Python編 - | 副業実績 & E資格者が厳選副業で稼ぐための、おすすめUdemy講座を紹介した記事です。実際にプログラミングによる副業活動を実践しており、またディープラーニングのE資格も保有している筆者が、厳選した講座になっています。...

それではまた、お会いしましょう！

RELATED POST