見出し画像

【第１回目】ネットカフェの料金シミュレーションをしてみよう〜Openpyxl編〜

きぃ＠ITでQOL向上

ネットカフェの料金はグラフで表すとどんな感じになるのか？そして、何分止まったら何円かかるといった予算を簡単に組めたらなと思ったのがそもそものきっかけでした。きっかけが塵も積もれば山となり、ネットカフェ料金シミュレーションを作りたいなというモチベーションが高まった結果、スクリプトを組み、無事完成へと持ち運ぶ事ができました。

ざっとどんなものを作ったのかをいち早く知りたい方は下の動画を見てぜひともチェックしてみてくださいね😊↓

またコードはgitで公開 しているので、みなさん、各自でいじって楽しんでくださいね。

それでは連載の第１回目としまして、主にopenpyxlの使い方に関して重点的に解説していきます。

【補足】
第２回目では本格的に数式を交えた解説となります。openpyxlすでに熟知された方はこのNoteを読まずに第２回目の連載まで待ってもらえればと思います。

まずはExcelファイルを読み込む

load_workbook関数は既存の状態だとマクロ付きExcelファイルに対応していない

openpyxl.load_workbook関数(以下load_workbook関数)はそもそもExcelのブックのオブジェクトを返す関数ですが、注意点があります。何も設定してない状態だと、VBA付きのExcelファイル（以下.xlsmファイル）を読み込み、何らかの加工を施して保存したとき、エラーが発生し開くことができません。これで困ってしまい途方にくれ、時間が過ぎるばかりでどうにかならないのかと探していたところ、load_workbook関数に

keep_vbaのパラメータをTrue

にすることで、.xlsmファイルであっても読み込みに対応させることができます。したがって、keep_vbaの引数をTrueにする必要 があります。※何も加えないと.xlsmファイルを保存したときにエラーが発生します。

openpyxlで保存したとき、Excelのマクロを開けないメッセージ。
↑openpyxlでマクロ付きファイルを保存したとき、keep_vbaで指定していないとエラーになる。
wb=openpyxl.load_workbook(FILEPATH,keep_vba=True)#expanduserは~をホームディレクトリに置き換える関数。

load_workbook関数の詳しい使い方はこちらのリンクに掲載してます。また時間のあるときに見てみてください。（英語）

Excelファイルを読み込めたら…

Excelファイルを読み込めたら、次はシートを読み込みます。シートの読み込み方は、先程読み込んだオブジェクトwbのとなりの[]の中に、

wb[‘シート名’]

と入力します。
実際のコードでは、2つのシートを指定し、変数I_RANGE、O_RANGEに「入力」、「レポート」のシートに格納しています。「入力」のシートがネットカフェの料金表を入力するシート。「レポート」のシートが今回スクリプトによって出力されるシートになります。まずは出力するシート「レポート」のシートを初期化します。

O_RANGE.delete_rows(200)#初期化

上のコードの意味は１行目から２００行目を削除せよということを意味しています。

I_RANGE,O_RANGE=[wb['入力'],wb['レポート']]

上のように読み込みたいシートをキーで指定します。これで、指定したシートのオブジェクトを読めました。また、シートが指定できるとセルの範囲が指定でき、セルの範囲に対応したオブジェクトを返します。ちなみに、セルの指定形式はA1形式で行っています。ここまで説明したことを整理すると、図解で説明すると以下のような感じになります。

シート→...→列（columns）

上の図からでもわかるようにセルで指定したオブジェクトを使ってループ関数を回すと１行ごとにたどることができ、また、ループ関数で重ねると、列をたどることができます。
代表的なコードは以下のコードになります。(research_InternetCafe:27行目参照)

for rowNum,row in enumerate(I_RANGE['A1:Z11']):
	…

ちなみにセルの範囲入力の仕方は、Excelの関数式で使うセルの範囲指定と同じです。Excelに慣れてる方なら直感的に浮かぶコードだと思います。

表だとわかるように罫線をしこう。

まずはコードを見てみましょう。

for i,d_item in enumerate(DicData.items()):
    cell_pos=i+1
    #網掛け、罫線の設定。
    h_fill=PatternFill(patternType='solid',fgColor='0000ff')
    theFont=Font(bold=True,color='ffffff')
    theSide=Side(color='000000',style='thin')
    theBorder=Border(left=theSide,right=theSide,top=theSide,bottom=theSide)

    header_cell=O_RANGE.cell(column=cell_pos,row=1)
    header_cell.value=d_item[0]
    header_cell.fill=h_fill
    header_cell.font=theFont
    header_cell.border=theBorder
    for j,val in enumerate(d_item[1]):
        row_pos=j+2
        val_cell=O_RANGE.cell(column=cell_pos,row=row_pos)
        val_cell.border=theBorder
        val_cell.value=val

重要なところを黄色マーカーで表しました。表だとわかりやすいように、

openpyxl.stylesにあるフォントの設定(Fontオブジェクト)や、線の設定(Sideオブジェクト)、罫線の設定(Borderオブジェクト)

を使って設定します。１つずつ解説していきますね。

セルの罫線・網掛けの設定。

ここまで準備ができればあとはセルに入れていくだけです。そこで

header_cell.value=d_item[0]
header_cell.fill=h_fill
header_cell.font=theFont
header_cell.border=theBorder

header_cellと呼ばれる変数に見出しセルが指定されています。あとはcell
のプロパティに先ほど設定されたオブジェクトを入れるだけですが、以下のパラメータがあります。

Cellのプロパティ。罫線・網掛けで設定したオブジェクトを入れる。

これで罫線が適用できるようになりました。罫線を設定できると、どれが表かわかりやすくなるので、あえてやってみました。

罫線完成イメージ
罫線完成イメージ。どれが表かわかりやすい。

セルに関数式を入力するときはエラーがないことを確認してから上書き保存するようにしよう。

次にセルの値にExcelの関数式を入れる場合、エラーが出ないように入力する必要があるということです。

import openpyxl
wb=openpyxl.Workbook()
active_sheet=wb.active
active_sheet.title='テスト'
selected_cell=active_sheet['A1']
selected_cell.value='=A4+'
wb.save('test.xlsx')

上のように数式を間違えて入力してしまうPythonスクリプトだと、下のエラーメッセージが出てしまいます。ファイル自体を開くことができるものの、

数式は入力されない状態となるだけでなく、Excel側は壊れたファイルだと認識してしまった結果、上書き保存を行うこと

ができなくなります。

数式間違いによるエラーメッセージ

したがって、必ず関数式にエラーが立っていないかどうか検証します。関数式のエラーが立っていないと確認できてから、上書き保存しましょう。確認ができない場合はファイルを別に作っておいたほうが無難でしょう。
このようにopenpyxlの特性を理解してスクリプトを組むことで、Pythonで培った知識のもとでExcelファイルの自動化をおこなうことができるというわけですね。

まとめ

openpyxlはちょっとクセがありますが、慣れればPythonで気持ちよくExcelファイルを操作することができます。
VBAもopenpyxlも両方知ることで、加工の自動化を効率的にすることができます。みなさんも試してみてください。

えっ！？これではネットカフェ料金シミュレーション作れないのではないか🤯！？

って思われた方がいるのかもしれません。第２回では数式を交えてネットカフェの料金シミュレーションをどのようにプログラミングしていくのかを解説します。ではでは😊

いいなと思ったら応援しよう！