pythonでエクセルを操作する方法

はじめに

DBのデータを集計してエクセルに記入する作業があったのですが、毎度毎度同じ作業をするのに飽きたのでpythonを使って自動的に記入するようにしました。その時にエクセルの操作で一部迷ったのでその注意事項含めて基本的な操作を実際のエクセルと共にまとめました。

環境

• python3:3.6.5
• openpyxl:3.0.7

openpyxlのインストール

エクセルを操作するためにopenpyxlを使用します。
openpyxlのインストールは普通にpipでインストールすれば良いだけです。

pip install openpyxl

基本的なopenpyxlの使い方

openpyxlは以下の要素を通して、エクセルを操作します。

• workbook(エクセルファイル)
• sheet
• cell

ワークブック（エクセルファイル）の作り方

0からエクセルファイルを作成する方法を記載します。
しかし、線や背景やフォントといった書式設定をプログラムで設定するのは手間なので元になるエクセルを用意して、それを読み込んで一部変更する方法がお勧めです。

import openpyxl
from openpyxl.workbook.workbook import Workbook
# 新規のワークブック（エクセル）を作成
wb = Workbook()
# 作成したワークブックをファイルに書き出す
wb.save('file/empty_excel.xlsx')
# ワークブックを閉じる
wb.close()

使用したエクセル

今回の使用したエクセルは2シートのエクセルです。

ワークブック（エクセルファイル）の読み込み

エクセルの読み込みには、load_workbook(ファイルパス) を使用して読み込みます。
読み込んだ後はワークシートとセルを抜き出して操作します。

import openpyxl

# ファイルパスを指定して読み込み
work_book = openpyxl.load_workbook('file/input.xlsx')
# シート名を表示
print(work_book.sheetnames)

実行結果

['合算数一覧', '追加数一覧']

ワークシートの抜き出し

ワークブックからワークシートを抜き出すためには、2通りの方法があります。

• for文を使用して順次読み込む方法
• シート名を指定して読み込む方法

import openpyxl

# ファイルパスを指定して読み込み
work_book = openpyxl.load_workbook('file/input.xlsx')
# シートを順番に抜き出す
for work_sheet in work_book:
    print('シート名: ' + work_sheet.title)
# シート名を指定して抜き出す
added_sheet = work_book['追加数一覧']
print('抜き出しシート名: ' + added_sheet.title)

実行結果

シート名: 合算数一覧
シート名: 追加数一覧
抜き出しシート名: 追加数一覧

セルの抜き出し

セルの抜き出し方は色々ありますが、良く使うものをまとめます。

• 行を順次抜き出す方法
• 列を順次抜き出す方法
• 行・列・セルを指定して抜き出す方法

行を順次抜き出す方法はiter_rows、列を順次抜き出す方法はiter_cols を使用して抜き出します。
下の例で与えているmin_row とmin_colは、それぞれ行と列の読み込みを開始する位置を与えています。
抜き出した後は、配列が取れるためインデックスの指定やループを使うことでセルを特定してセルの操作をします。

# 行を順次抜き出す方法
for row in added_sheet.iter_rows(min_row=2, min_col=3):
    # インデックスの指定でセルを特定して抜き出す
    print('1列目: ' + str(row[0].value) + ', 2列目: ' + str(row[1].value) + ', 3列目: ' + str(row[2].value))

# 列を順次抜き出す方法
for col in added_sheet.iter_cols(min_row=2, min_col=3):
    print('1列目: ' + str(col[0].value) + ', 2列目: ' + str(col[1].value) + ', 3列目: ' + str(col[2].value))

for row in added_sheet.iter_rows():
    # ループでセルを抜き出す
    for col in row:
        print(col.value)

# セルを指定して抜き出す
cell = added_sheet['C3']
print('C3の値: ' + str(cell.value))
# 列を指定して抜き出す
cols = added_sheet['C']
print(cols)
# 行を指定して抜き出す
rows = added_sheet['3']
print(rows)

実行結果

上のループ部分はセル1つ1つを出力しているため省略します。

C3の値: 1
(<Cell '追加数一覧'.C1>, <Cell '追加数一覧'.C2>, <Cell '追加数一覧'.C3>, <Cell '追加数一覧'.C4>, <Cell '追加数一覧'.C5>, <Cell '追加数一覧'.C6>)
(<Cell '追加数一覧'.A3>, <Cell '追加数一覧'.B3>, <Cell '追加数一覧'.C3>, <Cell '追加数一覧'.D3>, <Cell '追加数一覧'.E3>, <Cell '追加数一覧'.F3>, <Cell '追加
数一覧'.G3>)

行・列の挿入

行・列の挿入はワークシートに対して行います。
行の挿入は抜き出したワークシート.insert_cols、列の挿入は抜き出したワークシート.insert_cols で行います。

# 5列目に列を挿入
added_sheet.insert_cols(5)
# 5行目に行を挿入
added_sheet.insert_rows(5)
# 挿入したブックを保存する
work_book.save('file/output.xlsx')

実行結果

結果を見るとわかるように書式設定が挿入部分に設定されていません。

値の入力

値の入力はセルに対して行います。
セル.value = 入力したい値で設定します

# E3に1を入力する
added_sheet['E3'].value = 1
# 挿入したブックを保存する
work_book.save('file/output.xlsx')

openpyxlでエクセルを操作する時の注意点

openpyxlでエクセルを操作する時に困ったものは以下のものがありました。

• 挿入した行列に書式設定が設定されていない
• エクセルの数式で表示される値が取得できない
• 保存したときに書式が値に上書きされた

注意点の解決方法

挿入した行列に書式設定が設定されていない

上の挿入で説明したものを例に書式設定をします。
書式設定はセルの_style に登録されているため、元となるセルからコピーをすることで適用させます。

from copy import copy
# 挿入する
added_sheet.insert_cols(5)
added_sheet.insert_rows(5)
# 書式設定元となる列を取得する
copy_style_col = added_sheet['F']
# 挿入した列を抜き出す
insert_col = added_sheet['E']

for row_num, col in enumerate(copy_style_col):
    if col.has_style:
        # 列中のセルに対して書式をコピーする
        insert_col[row_num]._style = copy(col._style)

実行結果

分かりやすいように列だけ書式をコピーしてみました。

行をコピーするときは行を抜き出して同じようにセルに書式をコピーしてあげればできます。

エクセルの数式で計算される値が取得できない

普通にファイルを読み込むと数式が取得できてしまい、値が取得できませんでした。
値を取得したいときは、ファイルを読み込むときにdata_onlyをtrueにすると取得できます。

print('data_onlyを指定しない場合')
work_book = openpyxl.load_workbook('file/input.xlsx')
sum_sheet = work_book['合算数一覧']
print('C3: ' + str(sum_sheet['C3'].value) + ', C4: ' + str(sum_sheet['C4'].value) + ', C5: ' + str(sum_sheet['C5'].value))
work_book.close()

print('data_onlyを指定する場合')
work_book = openpyxl.load_workbook('file/input.xlsx', data_only=True)
sum_sheet = work_book['合算数一覧']
print('C3: ' + str(sum_sheet['C3'].value) + ', C4: ' + str(sum_sheet['C4'].value) + ', C5: ' + str(sum_sheet['C5'].value))

実行結果

data_onlyを指定しない場合
C3: =SUM(追加数一覧!E3:G3), C4: =SUM(追加数一覧!E4:G4), C5: =SUM(追加数一覧!E5:G5)
data_onlyを指定する場合
C3: 6, C4: 30, C5: 20

保存したときに書式が値に上書きされた

エクセルの数式の値を取得して、pythonで処理後にエクセルを保存したらエクセルの関数が消えてしまいました。
データオンリーでエクセルを読み込むと処理をしていない数式もすべて消えてしまうため、データオンリーをFalseで読み込む必要がありました。

work_book = openpyxl.load_workbook('file/input.xlsx', data_only=True)
work_book.save('file/data_only.xlsx')
work_book.close()

work_book = openpyxl.load_workbook('file/input.xlsx')
work_book.save('file/non_data_only.xlsx')

実行結果

データオンリーで保存したエクセル

データオンリーなしで保存したエクセル

おわりに

エクセルをpythonで操作する方法をまとめました。簡単にエクセルを操作できるため、ちょっとした業務の自動化に使えて便利でした。
さらに、seleniumやSQLを使用することもできるためやろうと思えばかなり色々なことができるため、
面倒な作業は全てプログラムに任せる勢いで色々自動化したいなと思っています。