Pythonでエクセルシートを操作 : openpyxl

Pythonでなにをしたらいいのかわからない

エクセルVBAの代わりにPythonでマクロ作ってみたら？

Pythonには機械学習で便利なPackageがたくさんあるのはいいことですが、そもそもは汎用的なプログラミング言語ということで他にもいろいろできます。

業務の自動化に使おうというのも最近多いですよね。

本もたくさん出ています。こちらおすすめではありますがもうちょっと薄い本でもいいと思います。

ということで一番使うであろうエクセルをpythonを使って操作してみたいと思います。

1: Pythonでエクセルを操作するメリット
2: Pythonでエクセルファイルを操作してみる
3: Openpyxlでエクセルを操作 : 表のsum集計

3.1: Step 0 : openpyxlで使う基本的な操作

3.1.1: ファイルを開ける : openpyxl.load_workbook()
3.1.2: セルを指定し、読み書きする : Worksheet.cell()
3.1.3: ファイルを保存する : Workbook.save()

3.2: Step 1 : エクセルのセルに関数を入れる
3.3: Step 2 : Pythonで計算
3.4: Step 3 : Pandasで計算

4: やってみた感想

Pythonでエクセルを操作するメリット

エクセルの自動化と言えばVBAですが今回はPythonでマクロを書いてみます。

VBAでできることをpythonでやる意味がないというのが一般的な意見かと思われます。しかしこちらの記事でやったようにpdfファイルを読んだりとエクセルの範囲を超えた操作が簡単にできるのは便利ですよね。これもVBAでやることは可能ですが設定は必要です。

もう一つ、今回Google Colaboratoryでやってみましたがちゃんと出来ました。ということはエクセルをインストールしていないマシンで処理しても出来るということですね。自分のPCの環境を汚したりリソースを使わずに処理できるのは魅力的です。

シートを開くにはエクセルをインストールしたマシンは必要ですけど。

Pythonでエクセルファイルを操作してみる

何か目的を持って動くものを作ることが学習効率良いと聞いたので、エクセルの表に合計値を加えるマクロを作ってみようと思います。売上データの集計とかよくある業務ですよね。

元データはこんな感じで作りました。

この表に3つの方法で計算した集計結果を出力したいと思います。

エクセル関数を書き込む
Python内で計算した値を入れる
Pandasで計算してから結果を入れる

Openpyxlでエクセルを操作 : 表のsum集計

Step 0 : openpyxlで使う基本的な操作

先に公式ページからよく使うであろう関数を眺めておきましょう。

下記の簡単なものはこのページに載っています。

ファイルを開ける : openpyxl.load_workbook()

wb = load_workbook('ファイルのパス')
ws = wb.active

“wb”等と変数を作っていれておくと後々便利です。

引数はファイルパスを入れます。

“ws”等、操作対象のシートをしておきましょう。

セルを指定し、読み書きする : Worksheet.cell()

ws.cell(row=4, column=2) #指定
ws.cell(row=4, column=2).value #読み
ws.cell(row=4, column=2, value=4) #書き

“A5″等の指定の仕方もできますが、for文作ったりするときに番号の方が都合がいいので一個覚えるなら行と列の数指定が便利です。

ファイルを保存する : Workbook.save()

wb.save('ファイルのパス')

Step 1 : エクセルのセルに関数を入れる

では先程のエクセルファイルにsum関数を書き込んでみます。

セルのvalueとして式自体を書けばいいですね。セルが複数あるのでfor文で繰り返しましょう。

# openpyxlのインストールとインポート
!pip install openpyxl
import openpyxl

# エクセルファイルを読む
wb = openpyxl.load_workbook('/content/input/test.xlsx')
ws = wb.active

# エクセルの関数を入れる
## 列名を入れる
ws.cell(row=1, column=5, value="sum_excel")

## 関数を入れる
ws.cell(row=2,column=5,value="=sum(B2:D2)")

for i in range(2,7):
  ws.cell(row=i,column=5,value="=sum(B"+str(i)+":D"+str(i)+")")

wb.save("/content/output/test.xlsx")

ちゃんと関数が入ってますね。

Step 2 : Pythonで計算

せっかくpythonを使っているのにシート内で計算しては意味がないのでpythonで計算した結果をセルに書き込みます。

# pythonで計算してからセルに入れる

# エクセルファイルを読む
wb = openpyxl.load_workbook('/content/output/test.xlsx')
ws = wb.active

## 列名を入れる
ws.cell(row=1, column=6, value="sum_python")

## 順番に数を3つ足してセルにいれるを5行繰り返す
for j in range(2,7):
  sum = 0
  for i in range(2,5):
    sum = sum + ws.cell(row=j, column=i).value

    ws.cell(row=j, column= 6).value = sum #セルに記入

wb.save("/content/output/test.xlsx")

Step 3 : Pandasで計算

そもそもpythonにはpandasという便利なライブラリがあるのでいちいちfor文で計算しなくてもよいですね。この方がいろいろpython内で複雑な計算ができるので良いです。

# pandasで計算してからセルに入れる

# pandasをインポート
import pandas as pd

# エクセルからデータを読む
wb = openpyxl.load_workbook('/content/output/test.xlsx')
ws = wb.active

# data部分
values = []
for row in ws["B2:D6"]:
  data = []
  for col in row:
    data.append(col.value)

  values.append(data)

# colum部分
columns = []
for i in range(2,5):  
  columns.append(ws.cell(row=1, column=i).value)

# DataFrameにする
df = pd.DataFrame(values, index=None)
df.index = range(1,6)
df.columns = columns

# dataframeでsumを計算
df["sum_pandas"] = df.sum(axis=1)

#確認用
print(df)

# エクセルに出力
ws.cell(row=1, column=7).value = "sum_pandas"

for i in range(2,7):
  ws.cell(row=i, column=7).value = df.loc[i-1,"sum_pandas"]

wb.save("/content/output/test.xlsx")

確認のためDataFrameの方も出力してみました。.sumだけで計算できて便利。

エクセルにもちゃんと出力されました。

やってみた感想

VBAと同じことができるならより汎用性の高いプログライング言語であるPythonを使った方がお得な気がします。特にPandasは使える様になるとデータ整理が便利だと聞くので良さそうですね。

それ以上にメリットと感じたのはエクセルの入っていない別のマシンで計算しておいてアプトプットされたファイルを自分のマシンで開けばいいということでした。

VBAを使った業務効率改善はたくさんやりましたが、マクロが動いている間マシンを触ると作り方によっては不具合があったりするのでよいですよね。

逆にエクセル内で完結しないので、人に渡す時は環境が違うことを考慮する必要ありますね。

アプリなんかも作ってみたい！

サモアリブログ

このブログを検索

投稿

Top : 記事一覧