デスクワークを超絶快適にするおすすめギアたち詳しく見る

【Python】Google ColabでGoogleドライブのExcelを操作をしよう!(OpenPyXL)

この記事でわかること!

  • PythonでExcelを操作するためによく使われるOpenPyXLライブラリの使い方がわかる。
  • Google Colaboratoryを使って無料で簡単にOpenPyXLを実行して、Google Drive上のExcelを操作する方法がわかる。
目次

PythonでExcel操作するには?

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

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

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というエクセルファイルが作成されます。試してみてください。

ぽこがみさま

次のステップから目的ベースで、OpenPyXLのメソッドやプロパティの解説をしていきます。

ワークブックを操作する。(ファイル操作)

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

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

ワークブック(Excelファイル)の読み込む

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

ワークブックを最適化モードで開く。(Optimised Modes)

サイズの大きなExcelファイルを扱う場合、通常のOpenPyXLの操作では処理しきれないことがあります。その時は2つの最適化モードOptimised Modes)を使ってワークブックを読み書きします。この2つのモードを使うことで一定のメモリ消費で無制限にデータの読み書きができます。

読み取り専用モード(Read-only Modes)

# 読み取り専用モードでブックを読み込む。
wb = op.load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data']

書き込み専用モード(Write-only Modes)

# 書き込み専用モードでブックを新規作成する。
wb = op.Workbook(write_only=True)
ws = wb.create_sheet()
じょじお

最適化モードは通常のモードと挙動が変わります。下記のWarningについて理解して使用してください。

・Unlike a normal workbook, a newly-created write-only workbook does not contain any worksheets; a worksheet must be specifically created with the create_sheet() method.
・In a write-only workbook, rows can only be added with append(). It is not possible to write (or read) cells at arbitrary locations with cell() or iter_rows().
・It is able to export unlimited amount of data (even more than Excel can handle actually), while keeping memory usage under 10Mb.
・A write-only workbook can only be saved once. After that, every attempt to save the workbook or append() to an existing worksheet will raise an openpyxl.utils.exceptions.WorkbookAlreadySaved exception.
・Everything that appears in the file before the actual cell data must be created before cells are added because it must written to the file before then. For example, freeze_panes should be set before cells are added.

Optimized modeのWarning

ワークブック(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のカラーコードを指定する。
OpenPyXLのsheet_properties.tabColorの実行結果

セル(単一)の操作

セルに値を書き込む。(単一)

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

セルの値を取得する。

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

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

セルにExcel関数を入力する

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

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

セル(セル範囲・列・行)の操作

最終行の取得

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

セルの書式設定

OpenPyXLのnumber_formatの実行結果
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()
DataFrameの出力結果

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

おすすめのPythonスクール(デイトラ)

じょじお

デイトラのPythonオンラインコースのコスパが良かったのでレビュー記事を書きました。

まとめ

じょじお

OpenPyXLの基本的な使い方とメソッドでした。

ぽこがみさま

このブログではRPA・ノーコードツール・VBA/GAS/Pythonを使った業務効率化などについて発信しています。
参考になりましたらブックマーク登録お願いします!

Pythonおすすめ書籍

▲Python入門者向けの書籍です。デスクワークの業務効率化方面を中心に自動化するスクリプトを作成することができます。Excel・Word・PDF・デスクトップアプリ化・メールなどなど。身近な作業を自動化しながら学べるので事務員の方やエンジニアの方幅広くお勧めできます。

▲Pythonでデータ分析するのに超絶おすすめです。データ分析でよく使うPandasモジュールを中心にデータ加工から分析までの基礎を理解できます。

▲Pythonのお作法なんかが書かれています。

この記事が気に入ったら
フォローしてね!

シェアしてくれたらウレシイ(·:゚д゚:·)
  • URLをコピーしました!
  • URLをコピーしました!
目次
閉じる