【Python】Google ColabでGoogleドライブのExcelを操作をしよう！（OpenPyXL）

PythonでExcel操作するには？

PythonでExcelを操作するためのライブラリ達の比較

ライブラリ	特徴
OpenPyXL	パフォーマンスと機能のバランスを意識しながら作られていると作者が語っている。
xlrd, xlwt	古いライブラリなので使わない方が良さそう。更新も長らく止まっている。古いExcel（xls）に対応している。
Pandas	Excelを読み込むためのライブラリではないけどExcelを読み込む機能も持っている。データ分析に特化しているので読み込み・書き込み以外の機能を使えない。
XlsxWriter	OpenPyXLと比較すると、若干パフォーマンスが上で機能面で劣る。

OpenPyXLを使ってみよう！

OpenPyXL（おーぷんぱいえくせる・おーぷんぱいえっくすえる）は、Pythonを使ってExcelを操作する時によく使われる人気のライブラリです。

OpenPyXLのメリット

OpenPyXLはExcelアプリケーションがインストールされていないパソコンでも使うことができます。

他のライブラリに比べて高機能でパフォーマンスも悪くないです。

Google ColaboratoryでOpenPyXLを試してみよう！

今回はGoogle Colaboratory（Google Colab：グーグルコラボ）を使ってPythonします。Googleアカウントさえあれば無料でクラウド上のJupyter NotebookライクなPython環境を利用できます。GPUも使えます。

Google colabでOpenPyXLを使う方法。

Google colabでOpenPyXLを使うメリット。

Google Colabには最初からOpenPyXLがインストールされています。

Google colabでGoogle Driveと接続する方法。

ファイルの出力先、読み込みのときにGoogle DriveとGoogle Colabを連携させておくと便利です。コネクト方法は下記の記事で紹介しています。

OpenPyXLの基本

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

OpenPyXLがご自分のPythonにインストールされていない場合はインストールします。Google colabでは最初からインストールされているので不要です。インストールコマンドは下記のいずれかです。

# pip環境にインストールする場合
pip install openpyxl


# Google colabでは最初からOpenPyXLはインストールされているが、明示的にインストールする場合、pipの頭に「！」を付けます。
!pip install openpyxl

OpenPyXLライブラリの読み込み

環境が整いましたらスクリプトを書いていきます。OpenPyXLを使うときは、スクリプトの先頭にimportコマンドでOpenPyXLライブラリを読み込む宣言文を書きます。OpenPyXLは文字数が多いので慣例的にopという別名を付けて読み込まれることが多いです。

import openpyxl as op

Excelを操作する基本的なスクリプト

下記はOpenPyXLを使ったExcel操作の基本的なスクリプトです。newExcel1.xlsxというExcelファイルをGoogle colabとリンクしたGoogle Drive上に作成して、A1セルに値を書き込んでいます。

import openpyxl as op

#Google colab上のGoogle Driveの作業フォルダ
folder = r"drive/MyDrive/" 

# 新規ワークブックの作成 
wb = op.Workbook()

# シートのアクティブ化（一番最初のシート）
ws = wb.active

# セルに書き込み
ws["A1"] = "This value is written in python by openpyxl!!!"

# Excelファイルの保存
# 同名のファイルが存在する場合 上書きします。
wb.save(folder + "newExcel1.xlsx")

# Excelファイルを閉じる
wb.close()

▲上記のスクリプトを実行するとGoogleドライブにnewExcelというエクセルファイルが作成されます。試してみてください。

ワークブックを操作する。（ファイル操作）

ワークブック（Excelファイル）を新規作成する

# 新規ワークブックの作成 
wb = op.Workbook()

ワークブック（Excelファイル）の読み込む

# 既存Excelブックの読み込み
filename = "myExcel.xlsx"
wb = op.load_workbook(filename)

ワークブック（Excelファイル）を保存する

ワークブックを変更・操作を加えたら変更を保存するためにsaveメソッドを実行する必要があります。このメソッドは同じ名前のファイルが存在する場合上書きしてしまいますので注意してください。

# 同名のファイルが存在する場合上書きする。
filename = r"drive/MyDrive/newExcel.xlsx"
wb.save(filename)

ワークブック（Excelファイル）閉じる。

# Excelファイルを閉じる。
wb.close()

ワークブックの操作がおわったら最後にcloseメソッドを実行してExcelファイルを閉じます。

Excelファイルを今日の日付で保存する。

import openpyxl as op

folder = r"drive/MyDrive/"

wb = op.Workbook()
datetime.datetime.now()

# 今日の日付を取得
now = datetime.datetime.now()
# strftimeで日付のフォーマットを指定します。
filename = folder + "myExcel" + now.strftime('%Y%m%d_%H%M%S') + '.xlsx'

wb.save(filename)
wb.close()


# 作成されるファイルの名前の例
# >>>myExcel20220418_131622.xlsx

ワークシートを操作する。

ワークシートを新しく挿入する。

# ワークシート新規挿入する。
wb.create_sheet(title="new_St", index=0) #一番左にnew_Stを挿入する

# ワークシート新規挿入する。 
wb.create_sheet() #オプションは無くても良い。

##オプションについて
# index=0は一番左に挿入
# title="シート名"

ワークシートをコピーする。

source = wb.active
target = wb.copy_worksheet(source)

ワークシートをひとつ取得する。（最初のシート）

# 最初のシートをアクティブにする。
ws = wb.active

ワークシートをひとつ取得する。（シートインデックスで指定）

# シートのアクティブ化（1番左のシート）
ws1 = wb.worksheets[0]

# シートのアクティブ化（2番目のシート）
ws2 = wb.worksheets[1]

# シートのアクティブ化（3番目のシート）
ws3 = wb.worksheets[2]

ワークシートをひとつ取得する。（シート名で指定）

シート名でワークシートを特定するには次の2つの方法があります。

# ワークシートをアクティブ化（ワークシート名を使って）①
ws = wb["Sheet1"]

# ワークシートをアクティブ化（ワークシート名を使って）②
ws = wb.get_sheet_by_name("testSheet1")

すべてのワークシートをループで操作する。

すべてのワークシートを操作するにはworkbookオブジェクトをfor inループするとワークシートを1個ずつ取り出すことができます。

# すべてのワークシートをループ処理する。
for s in wb:
  s["A1"] = "Hello Excel." #現在のシートのA1セルに書込み
  print(s.title) #現在のシートのシート名を表示する

#実行結果
# >Sheet1
# >Sheet2
# >Sheet3

ワークシート名を取得する。（特定のワークシート）

# ワークシート名の取得①
ws = wb["Sheet1"]
print(ws.title)
## 実行結果
# > Sheet1

# ワークシート名の取得②
ws = wb.worksheets[0]
print(ws.title)
## 実行結果
# > Sheet1

ワークシート名を取得する。（すべてのシート名の一覧）

print(wb.sheetnames)

# 実行結果は文字列のリストで返ってくるよ。
# >['st', 'testSheet', 'testSheet2', 'testSheet3']

ワークシート名を変更・設定する。

ワークシート名を指定せずに新規作成すると自動的にシート名が付与されます。シート名を変更するにはシートのtitleプロパティに値をセットします。

ws.title = "New Title"

ワークシートを削除する。

シートを削除するにはremove_sheetメソッドを使います。シートが存在しない場合エラーになります。

# シートの削除①
wb.remove_sheet(wb["sheet1"]) #シート名ではなくシートオブジェクトを引数に渡します。

ワークシートのタブ色を指定する。

# ワークシートタブカラーの変更
ws.sheet_properties.tabColor = "1072BA" #RRGGBBのカラーコードを指定する。

セル（単一）の操作

セルに値を書き込む。（単一）

# セルに値の代入
ws["a1"] = "insert value from openpyxl!"

セルの値を取得する。

# セルの値を取得する（単一セルの場合）
value = ws["A1"].value
print(value)

# 実行結果
#> "insert value from openpyxl!"

セルにExcel関数を入力する

関数を入力する場合は、そのままセルオブジェクトにExcel関数を代入します。

# 関数を入力する。
# Excel関数の書式をそのまま入力する。
st["B4"] = "=sum(B1:B3)"

セル（セル範囲・列・行）の操作

最終行の取得

print(ws.max_row)

最終列の取得

print(ws.max_column)

セルの値（データ）を読み込む

セルそのものをオブジェクトとして読み込むのではなく値を取り出す場合はvaluesメソッドを使用します。

for row in ws.iter_rows(min_row=1, max_col=3, max_row=ws.max_row, values_only=True):
  print(row)

# 実行結果
('Data1', 'Data2', 'Data3')
(1, 2, 3)
(11, 12, 13)
(21, 22, 23)
(31, 32, 33)

セルの値（データ）を読み込む

ws.valuesでワークシート上のすべての値を一括で読み込みます。

data = ws.values

for row in data:
   print(row)

# 実行結果
('Data1', 'Data2', 'Data3')
(1, 2, 3)
(11, 12, 13)
(21, 22, 23)
(31, 32, 33)

セルに値を書き込む（リストを行に追加）

Excelの表に行を追記するにはワークシートオブジェクトのappendメソッドを使います。自動的に最終行を読み取ってその下にリストの値を挿入してくれます。表の列とリストの要素数が一致していなくてもエラーにはなりません。

# セルに値を書き込む（1行）
mylist = [["product_ccc",10000,900]]
for r in mylist:
  ws.append(r)


# セルに値を書き込む（複数行）
mylist = [["product_aaa",10000,1000],
          ["product_bbb",9000,900],
          ["product_ccc",500,100]]
for r in mylist:
  ws.append(r)

セルに値を書き込む（辞書から）

# セルの書き込み(辞書)
myDic = [{"item":"product_aaa","price":1000,"cost":300},
           {"item":"product_bbb","price":2000,"cost":600},
           {"item":"product_ccc","price":3000,"cost":900}]

k = 1
for row in myDic:
  ws3.cell(k,1,row['item'])
  ws3.cell(k,2,row['price'])
  ws3.cell(k,3,row['cost'])
  k += 1

行単位の操作

ワークシートオブジェクトのiter_rowsを使うと行単位でアクセスすることができます。

import openpyxl as op
folder = r"drive/MyDrive/"
filename = "python_book.xlsx"

wb = op.load_workbook(folder + filename)

# 例）1行目から最終行までループします。列数は最終使用列まで。
for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=ws.max_row):
  for cell in row:
    print(cell)

wb.close()


# 実行結果
<Cell 'Sheet'.A1>
<Cell 'Sheet'.B1>
<Cell 'Sheet'.C1>
<Cell 'Sheet'.A2>
<Cell 'Sheet'.B2>
<Cell 'Sheet'.C2>
<Cell 'Sheet'.A3>
<Cell 'Sheet'.B3>
<Cell 'Sheet'.C3>
<Cell 'Sheet'.A4>
<Cell 'Sheet'.B4>
<Cell 'Sheet'.C4>
<Cell 'Sheet'.A5>
<Cell 'Sheet'.B5>
<Cell 'Sheet'.C5>

列単位の操作

import openpyxl as op

folder = r"drive/MyDrive/"
filename = "python_book.xlsx"

wb = op.load_workbook(folder + filename)

for col in ws.iter_cols(min_row=1, max_col=3, max_row=1):
  for cell in col:
    print(cell.value)

wb.close()


# 実行結果
Data1
Data2
Data3

列の削除

# F列から3列分削除する（F:H列）
ws.delete_cols(6, 3)

行の挿入

# 7行目に行の挿入
ws.insert_rows(7)

列の列幅・行の高さ

# 列のセル幅の調整する。
ws.column_dimensions["A"].width = 50 #A列のセル幅を50px

# 行の高さを調整する。
ws.row_dimensions[1].height = 100 #1行目の高さを100px

セルの書式設定

import datetime
import openpyxl as op

wb = op.Workbook()
ws = wb.active

ws['A1'] = datetime.datetime.now()
ws['A1'].number_format = 'yyyy-mm-dd h:mm:ss'

ws['A2'] = datetime.datetime.now()
ws['A2'].number_format = 'yyyy/mm/dd hh:mm:ss'

ws['A3'] = datetime.datetime.now()
ws['A3'].number_format = 'yyyy年mm月dd日'

アウトライン（行・列のグループ化）の設定

アウトラインはExcelの列や行をグルーピングして開閉ボタンを付けることができるExcelの機能です。列の数が多いExcelはスクロールが面倒ですし見づらくなりがちですが、アウトライン化することで閲覧がしやすくなります。

# アウトラインの設定（列）
# 列名は文字列で指定する
ws.column_dimensions.group("A","D",outline_level=1, hidden=True) 

# アウトラインの設定（行）
# 行番号は数字で指定する
ws.row_dimensions.group(1,4,1,outline_level=1,hidden=False)


## オプションについて
# Hidden=True でグループを閉じる。# Hidden=Falseでグループを展開する

OpenPyXLのその他の機能

その他の機能はリファレンスを確認してください。

OpenPyXLライブラリ
https://openpyxl.readthedocs.io/en/stable/

OpenPyXLとPandasの連携について

Excelの分析はPandasの方が向いている

OpenPyXLには、データ分析で有名なライブラリであるPandasで作成したDataFrame（多次元配列）を簡単にExcel出力できるメソッドがあります。

複雑なデータの分析やクレンジングはOpenPyXLよりもPandasの方が得意なので、Pandasでデータ分析・加工を行いOpenPyXLで結果をExcel出力するという処理もよく利用される選択肢です。

Pandasの使い方については下記の記事で解説していますのでご覧ください。

Pandas DataFrameを出力する方法！

openpyxl.utils.dataframeの中にあるdataframe_to_rowsをインポートしましょう。下記はdataframe_to_rowsの使用例です。

from openpyxl.utils.dataframe import dataframe_to_rows
import openpyxl as op
import pandas as pd

# データフレーム用配列
arr = [[1,2,3],[11,12,13], [21,22,23], [31,32,33]]
# データフレームのヘッダー用配列
header = ["Data1","Data2","Data3"]

# データフレームを作成
df = pd.DataFrame(arr,columns=header)

wb = op.Workbook()
ws = wb.active

# データフレームをExcelに書き出す。
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

wb.save(r"drive/MyDrive/dataframe_book.xlsx")
wb.close()

上図のようにとても簡単にデータフレームをExcelにダンプできました。ヘッダーもつけることができて便利です。

おすすめのPythonスクール（デイトラ）

先着1000名まで1万円引きキャンペーン実施中！（8月31日まで）

＼買い切りだからコスパ最高・永久にユーザーコミュニティ参加可能／

まとめ