『退屈なことはPythonにやらせよう』その10(12章:Excel)

どうも、セメントの犬です。今回も、『退屈なことはPythonにやらせよう』をやっていきます!

10回目の今日は、12章のExcelシートです。

前回Webスクレイピングでけっこう大変な思いをしたので、今回はサクっとやっていきたいですね。

前回の記事はこちらです↓

10回目の今回はサードパーティ製モジュールの、「openpyxlモジュール」を使っていきます。

Pythonを使ってエクセルシートをいじるためのモジュールですね。importがうまくいかない場合は、コマンドラインかターミナルからインストールしておきましょう。

今回の目次


1. openpyxlモジュールでのExcelシート基本操作

openpyxlでのExcelシートの読み込み

openpyxlを使用する際の基本の流れは以下の通りになります。

  • モジュールのインポート
  • ワークブックの読み込み:openpyxl.load_workbook(“ワークブックのパス”) / 新規にシートを作成する場合はopenpyxl.Workbook()
  • シートの指定:wbオブジェクト.get_sheet_by_name=(“シートの名前”)
  • 行いたい作業をする
  • シートを書き出す:wb.save(“保存するワークブックのファイル名”)

実際のコードでは以下のような形です。

#モジュールの読み込み(pip installが事前に必要)
import openpyxl

#既にあるワークブックの読み込み
wb = openpyxl.load_wrokbook("ワークブックのパス")
#新規のワークブックの作成をする場合
wb = openpyxl.Workbook()

#Do something awesome

#保存
wb.save("書き込み先のファイル名.xlsx")

シートの読み込みと保存はこんな感じです。続いて、シートの中身のセルを選択する方法を書いておきます。

#cell
sheet["A1"].value #A1の値を取得する
sheet.cell(row=1, column=3).value #x, y 座標を用いてセルを指定する(A1が(1,1)となる)

この辺の操作は、演習プロジェクトの1番でも使ってます。順番にマス目を埋めていくような動かし方をforループで実装しています。


2. openpyxlモジュールでのグラフ作成

openpyxlでグラフを作る流れ

基本の流れは以下のような感じになります。

  • Referenceオブジェクトの作成
  • Referenceオブジェクトを渡してSeriesオブジェクトを作成
  • Chartオブジェクトを作成
  • ChartオブジェクトにSeriesオブジェクトを追加する
  • Chartオブジェクトのx, y, w, h属性を設定
  • WorksheetオブジェクトにChartオブジェクトを追加する

Referenceオブジェクトには

  • sheet
  • min_col
  • min_row
  • max_col
  • max_row

を渡します。選択したい範囲の角をそれぞれ渡す感じですね。

ref_obj = openpyxl.chart.References(sheet, min_col = int, min_row=int, max_col = int, max_row = int)
series_obj = openpyxl.chart.Series(ref_obj, title = "title whatever you want")
chart_obj = openpyxl.chart.BarChart()
chart_obj.append(series_obj)

#位置
chart_obj.y = int
chart_obj.x = int

#図のサイズ
chart_obj.w = int
chart_obj.h = int

#put figure on worksheet
sheet.add_chart(chart_obj)
wb.save("ファイルのパス")

openpyxlで作れるグラフ

openpyxlの公式ドキュメンテーションに行くと、さまざまなタイプのグラフが作れることが分かります。サンプルコードもあるので、チェックしてみてください。リンクはこちら

いちおう、個人的によく使うかなというものを書き出しておくと

  • Area Chart
  • Line Chart (いわゆる折れ線)
  • Scatter Charts (散布図)
  • Pie Charts(円グラフ)
  • Surface Charts(等値線図のようなやつ、地形図の等高線みたいなやつ)
  • Stock Charts (箱ひげ図)

という感じになります。3Dの図を作れるやつもあります。


3. 12章の演習問題

演習1. 掛け算の表を作成する

コマンドラインから引数として整数Nを受け取り、N×Nの掛け算の表をExcelシートとして作る。

実際のコードはこんな感じになりました。

#! python3
# multiplicationTable.py  -  コマンドラインから数字Nを受け取り、N×Nの掛け算の方を生成する

#debug
import logging
logging.basicConfig(level=logging.DEBUG,
format = " %(asctime)s - %(levelname)s - %(message)s")
logging.debug("プログラム開始")
logging.disable(logging.CRITICAL)

#checking_project
import sys, openpyxl
from openpyxl.utils import get_column_letter

number = int(sys.argv[1])
logging.debug("Nは{}です".format(number))

#preparing workbook and sheet
wb = openpyxl.Workbook()
sheet = wb.active


for i in range(1,number+1):
    #putting numbers at first level    
    row_cell = sheet.cell(row = (i+1), column=1)
    col_cell = sheet.cell(row = 1 , column = (i+1))
    row_cell.value = i
    col_cell.value = i

for i in range(1,number+1):
    #putting calculated numbers
    for j in range(1,number+1):
        cal_cell = sheet.cell(row=i+1, column=j+1)
        r_cell = sheet.cell(row=i+1, column = 1).value
        c_cell = sheet.cell(row = 1, column = j+1).value
        cal_cell.value = r_cell * c_cell
        logging.debug("{}は{}です".format(cal_cell, cal_cell.value))

wb.save("check12_1.xlsx")

debugのところは、勉強のためにあえて書いている感じです。
printに頼らないデバックを早く身に着けたいものですね。

内容としては、まずそれぞれの1行(列)目に数字をいれていきます。in range(1, number+1)とすると、1から始まってnumber+1(引数に6を渡した場合は7まで)の数字を1刻みに入れてくれます。これを使って、1マスずつずらしながら数字を入れています。

このコードを実行すると、pythonファイルのあるフォルダに”check12_1.xlsx”が生成されます。Excelで開いてみると、下のような感じになります。

result_check12_1.py

いい感じにできていますね。最初はエクセルシートのほうに、掛け算の数式を入れようとしていました。ところが、数式の計算結果が表示されず、計算式が表示されてしまいました。既にあるシートを読み込む場合だと、”data_only = True” という引数を wb.load_workbookに渡せばよいのですが、新規作成の場合はこの引数は無いようです。そのため、解答としては、Pythonで計算した値を渡す感じにしました。空のブックを作って、一旦保存してまた呼び出せばdata_onlyでも出来なくはないんでしょうけど、すごく無駄ですからね。ただ、Pythonを使わない人にExcelファイルとして結果を渡す場合は、計算式が残るようにしたほうがいいですね。ここでもう一つ問題になったのが、セルの名前です。iとjを使ってセルを移動している場合、数式の中にこれを渡すと、”A1″のように変換されていない値が返されていました。つまりは、数式に i や j が残ったままになっていました。これも何か対策する方法があるのでしょうか?もしかして半角スペースが良くなかった?

演習2. 空行を挿入する

コマンドラインから3つの引数を受け取り、空行を挿入する。

コマンドラインで受け取る引数は

  1. 空行の開始位置
  2. 空行の数
  3. 空行を挿入する.xlsx ファイル

です。

答えのコードはこんな感じになりました。

#! python3
# check12_2.py  -  空行を挿入する
# Usage
# python check12_2.py <空行を挿入する行> <空行の数> <ワークシートのファイル名>

#debug
import logging
logging.basicConfig(level=logging.DEBUG,
format = " %(asctime)s - %(levelname)s - %(message)s")
logging.debug("プログラム開始")
logging.disable(logging.CRITICAL)

import sys, openpyxl
from openpyxl.utils import get_column_letter

N = int(sys.argv[1])
M = int(sys.argv[2])
logging.debug(N)

x_file =  sys.argv[3]

wb = openpyxl.load_workbook(x_file)
sheet = wb.active

wb_new = openpyxl.Workbook()
sheet_new = wb_new.active

for i in range(0, N):
    j = 1
    for cell_obj in list(sheet.rows)[i]:
        logging.debug("count is {}".format(j))
        logging.debug("cell_objは{}".format(cell_obj.value))
        sheet_new[(get_column_letter(j)+str(i+1))] = (cell_obj.value)
        logging.debug(sheet_new[(get_column_letter(j)+str(i+1))])
        j += 1

for i in range(N+M, sheet.max_row + M):
    j = 1
    for cell_obj in list(sheet.rows)[i-M]:
        logging.debug("count is {}".format(j))
        logging.debug("cell_objは{}".format(cell_obj.value))
        sheet_new[(get_column_letter(j)+str(i+1))] = (cell_obj.value)
        logging.debug(sheet_new[(get_column_letter(j)+str(i+1))])
        j += 1

wb_new.save("inserted_{}".format(x_file))

出力されたファイルはExcelで開くとこんな感じです。

check12_2_result

演習3. 行と列の入れ替え

タイトルの通りです。「行と列の入れ替え」をPythonで実装します。せっかくなので、以下のコードを使って、入れ替え対象のエクセルファイルを用意しました。

#! python3
# prepare_check12_3.py  -  横長のExcelファイルを生成する(縦3*横12)
#debug
import logging
logging.basicConfig(level=logging.DEBUG,
format = " %(asctime)s - %(levelname)s - %(message)s")
logging.debug("プログラム開始")
#logging.disable(logging.CRITICAL)

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active


for i in range(1,13):
    #putting numbers at first level    
    cell_1 = sheet.cell(row = 1, column= i)
    cell_2 = sheet.cell(row = 2, column = i)
    cell_3 = sheet.cell(row = 3, column = i)

    cell_1.value = "neko"
    cell_2.value = "cat"
    cell_3.value = "dog"

wb.save("ch12_3_pre.xlsx")

準備したxlsxファイルがこんな感じです。

check12_3_prepare

作戦としては、二重のforループを使って、rowとcolumnを入れ替えていきます。実際に書いたコードは以下の通りです。

#! python3
# prepare_check12_3.py  -  横長のExcelファイルを生成する(縦3*横12)
#debug
import logging
logging.basicConfig(level=logging.DEBUG,
format = " %(asctime)s - %(levelname)s - %(message)s")
logging.debug("プログラム開始")
#logging.disable(logging.CRITICAL)

#モジュール読み込み
import openpyxl

#シートの読み込み
wb = openpyxl.load_workbook("ch12_3_pre.xlsx")
sheet = wb.active
#出力先シートの準備
wb_new = openpyxl.Workbook()
sheet_new = wb_new.active

#行と列の入れ替え
for i in range(1, sheet.max_row + 1):         #iが横
    for j in range(1, sheet.max_column + 1):  #jが縦

        #putting values to new sheet
        sheet_new.cell(row=j, column=i).value = sheet.cell(row = i, column = j).value  

#Save to new file
wb_new.save("t_ch12_3_pre.xlsx")

これはけっこう単純というか、機械的にいける感じですね。実際にこのコードで出力したものが、以下のスクショになります。

check12_3_result

コードの流れとしては

  • 入力ファイルの読み込み
  • 出力ファイルの準備
  • ループを使って、rowとcolumnの番号を入れかえ
  • 新しいシートを保存

という感じになります。

演習4. テキストファイルからスプレッドシートに変換する

課題は表題の通り、テキストファイルをスプレッドシートに変換するものです。条件として、複数のテキストファイルを読み込むことが与えられているので、とりあえず複数のテキストファイルを作成するコードを書きました。9章でも同じようなコードを書いていた(リンクはこちら)ので、ほぼ流用しています。

#! python3
# pre_check12_4.py  -  複数のテキストファイルを作成する


import os

os.makedirs("NEKO", exist_ok = True)

for i in range(1,10):
    created_file = open(os.path.join("NEKO", "neko{:03}.txt".format(i)) , "w")
    created_file.write(r"""neko{}です
neko
neko
neko
最終行です""".format(i))

    created_file.close()

このコードで新たに作成したテキストファイルをスプレッドシートに変換します。クリア条件を整理しておくと

  • 複数のテキストファイルをスプレッドシートに変換する
  • テキストの1行につき、スプレッドシートの1行を使う
  • 最初のテキストファイルをA列、2番目のテキストファイルはB列のように、ファイルによって列を変更する。

ということで、書いたコードがこんな感じです。enumerateっていうのを使ってみました。変数を二つ渡して、enumerateにリストオブジェクトを渡した場合、一つ目の変数のカウント、二つ目の変数にリストの要素を入れることができます。

#! python3
# check12_4.py  -  複数のテキストファイルをスプレッドシートに変換する


import os, openpyxl

wb = openpyxl.Workbook()
sheet = wb["Sheet"]


for i, txtfile in enumerate(os.listdir("NEKO")):
    f = open(os.path.join("NEKO", txtfile), "r")
    lines = f.readlines()

    for j in range(1, len(lines) + 1 ):
        sheet.cell(row = j, column = i+1).value = lines[j-1]
    
    f.close()

wb.save("ch12_4.xlsx")

実際に出力されたExcelシートがこちらになります。

check12_4_result

文章のセンス皆無かよっていうツッコミはさておき、とりあえずきちんと出力ができていますね。

今回気づいたのですが、sheetを選択する時に、「sheet = wb.get_sheet_by_name(“シートの名前”)」とすると警告が出ていました。
正しくは「sheet = wb[“シートの名前”]」ってしてね?っていう警告がでてました。

warning_to_use_of_sheet
もらった警告がこちら

あんまりコードが長くなかったのと、これまで書いたものの使いまわしで行けてしまう感じだったので、デバッグのコードは省いています…
でも一応最初からシートに書き込むのではなく、ファイルのリストを表示してみたり、readlines()の挙動を確かめたりするのにprint()を使ってしまいました……

演習5. スプレッドシートからテキストファイルに変換する

演習4の逆をやります。すなわち、

  • スプレッドシートの1列を一つのテキストファイルとして書き出す
  • スプレッドシートの改行は、テキストファイル中の改行と対応する

実際に書いたコードがこちらです。

#! python3
# check12_5.py  -  スプレッドシートを複数のテキストファイルに変換する


import os, openpyxl

wb = openpyxl.load_workbook("ch12_4.xlsx")
sheet = wb["Sheet"]


for i in range(1, sheet.max_column + 1):
    f = open(os.path.join("NEKO", "r_neko{:03}.txt".format(i)), "w")

    for j in range(1, sheet.max_row + 1):
        f.write(str(sheet.cell(row = j, column = i).value))
      
    f.close()

4で作成したエクセルシートをそのまま読み込みました。最初は「f.write(str(sheet.cell(row = j, column = i).value))」の後に「+”\n”」とつけていたのですが、これだと余計な改行が1行入ってしまいます。write()メソッドで既に改行がデフォルトになってるんですかね。

出力されたテキストファイルの例を一つ紹介しておくと、

check12_5_result

という感じになっていました。きちんと元に戻っています。


4. まとめ

openpyxlモジュールを活用すれば、かなりサクサクとExcelシートをPythonでいじれることがわかりました。今後も積極的に使っていきたいところです。一つ不満なのは、シートの項目とかをサッとチェックする方法があまり紹介されていなかったことです。Pandasとかに渡せばいいのかな?

次回はPDFをPythonでいじっていきます!リンクはこちら↓

『退屈なことはPythonにやらせよう』その10(12章:Excel)” に対して2件のコメントがあります。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です